[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM ZX_LINES_DET_FACTORS
WHERE application_id = trx_rec.application_id
AND entity_code = trx_rec.entity_code
AND event_class_code = trx_rec.event_class_code
AND trx_id = trx_rec.trx_id
FOR UPDATE NOWAIT;
| PROCEDURE Update total_inc_tax_amt if present |
* ============================================================*/
PROCEDURE update_total_inc_tax_amt (
p_event_class_rec IN event_class_rec_type ,
x_return_status OUT NOCOPY VARCHAR2
)IS
l_trx_line_tbl ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl%TYPE;
l_api_name CONSTANT VARCHAR2(30) := 'update_total_inc_tax_amt';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN','ZX_API_PUB: update_total_inc_tax_amt(+)');
UPDATE zx_lines_det_factors
SET total_inc_tax_amt = l_trx_line_tbl.total_inc_tax_amt(j)
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 = l_trx_line_tbl.trx_line_id(j)
AND trx_level_type = l_trx_line_tbl.trx_level_type(j);
'ZX_API_PUB: update_total_inc_tax_amt(-)'||' RETURN_STATUS = ' || x_return_status);
END update_total_inc_tax_amt;
SELECT 'Y'
INTO zx_global_structures_pkg.g_ptnr_srvc_subscr_flag
FROM zx_srvc_subscriptions zss
WHERE zss.enabled_flag = 'Y'
AND zss.prod_family_grp_code = nvl(p_event_class_rec.prod_family_grp_code, zss.prod_family_grp_code)
AND exists (select zru.regime_usage_id
from zx_regimes_usages zru
where zru.regime_usage_id = zss.regime_usage_id
and zru.first_pty_org_id = nvl(p_event_class_rec.first_pty_org_id, zru.first_pty_org_id))
AND rownum = 1;
SELECT sync_with_prvdr_flag
INTO l_sync_with_prvdr_flag
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 tax_provider_id = ZX_GLOBAL_STRUCTURES_PKG.tax_regime_tbl(l_regime_index).tax_provider_id
AND sync_with_prvdr_flag = 'Y'
AND rownum = 1;
p_event => 'UPDATE',
p_tax_regime_code => null,
p_provider_id => null,
x_return_status => l_return_status
);
DELETE from zx_lines_det_factors
WHERE application_id = l_event_class_rec.application_id and
entity_code = l_event_class_rec.entity_code and
event_class_code = l_event_class_rec.event_class_code and
trx_id = l_event_class_rec.trx_id;
IF l_event_class_rec.tax_event_type_code ='UPDATE' THEN
ZX_TRL_DETAIL_OVERRIDE_PKG.lock_dtl_tax_lines_for_doc(p_application_id => l_event_class_rec.application_id,
p_entity_code => l_event_class_rec.entity_code,
p_event_class_code => l_event_class_rec.event_class_code,
p_trx_id => l_event_class_rec.trx_id,
x_return_status => l_return_status,
x_error_buffer => l_error_buffer
);
END IF; --tax event type ='UPDATE'
ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl.DELETE;
| Delete from the global structures for every loop on the |
| header document so that there are no hanging/redundant |
| records sitting there |
+--------------------------------------------------------*/
--Calling routine to delete the global structures
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.EVENT_CLASS_MAPPING_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_LINE_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_LEVEL_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_TABLE_NAME.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_VALUE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.LOCATION_HASH_TBL.DELETE;
| Delete from the global structures for every loop on the |
| header document so that there are no hanging/redundant |
| records sitting there |
+--------------------------------------------------------*/
--Calling routine to delete the global structures
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.EVENT_CLASS_MAPPING_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_LINE_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_LEVEL_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_TABLE_NAME.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_VALUE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.LOCATION_HASH_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl.DELETE;
SELECT
INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
-- ENTITY_CODE,
-- EVENT_CLASS_CODE,
QUOTE_FLAG,
ICX_SESSION_ID
FROM ZX_TRX_HEADERS_GT
WHERE rownum = 1;
SELECT distinct
header.event_class_code,
header.application_id,
header.entity_code,
evntmap.processing_precedence
FROM ZX_EVNT_CLS_MAPPINGS evntmap,
ZX_TRX_HEADERS_GT header
WHERE header.application_id = evntmap.application_id
AND header.entity_code = evntmap.entity_code
AND header.event_class_code = evntmap.event_class_code
ORDER BY evntmap.processing_precedence;
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
ICX_SESSION_ID,
EVENT_TYPE_CODE,
TAX_EVENT_TYPE_CODE,
DOC_EVENT_STATUS
FROM ZX_TRX_HEADERS_GT;
ZX_GLOBAL_STRUCTURES_PKG.LOC_GEOGRAPHY_INFO_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl.DELETE;
select event_class_code,
application_id,
entity_code,
internal_organization_id
into l_evnt_cls.event_class_code(1),
l_evnt_cls.application_id(1),
l_evnt_cls.entity_code(1),
l_evnt_cls.internal_organization_id(1)
from ZX_TRX_HEADERS_GT
where rownum=1;
select ZX_LINES_DET_FACTORS_S.nextval
into l_event_id
from dual;
| Call to Update Total Inclusive Tax Amount |
+--------------------------------------------------*/
/* Replace the call to update_total_inc_tax_amt with the merge statement below
update_total_inc_tax_amt(p_event_class_rec => zx_global_structures_pkg.g_event_class_rec,
x_return_status => l_return_status
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||p_api_name,G_PKG_NAME||': '||p_api_name||':update_total_inc_tax_amt returned errors');
USING (SELECT
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
sum(tax_amt) incl_tax_amt
FROM
zx_detail_tax_lines_gt TaxLines
WHERE
tax_amt_included_flag = 'Y'
-- AND mrc_tax_line_flag = 'N'
AND cancel_flag <> 'Y'
GROUP BY
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id
) Temp
ON ( lines_dt.tax_amt_included_flag = 'Y'
--AND lines_dt.total_inc_tax_amt is NULL
AND lines_dt.application_id = temp.application_id
AND lines_dt.entity_code = temp.entity_code
AND lines_dt.event_class_code = temp.event_class_code
AND lines_dt.trx_id = temp.trx_id
AND Lines_dt.trx_level_type = temp.trx_level_type
AND Lines_dt.trx_line_id = temp.trx_line_id
)
WHEN MATCHED THEN
UPDATE SET
total_inc_tax_amt = incl_tax_amt;
| Bug 4948674: Handle delete for O2C products when icx_session_id is NOT NULL |
+------------------------------------------------------------------------------*/
(zx_global_structures_pkg.g_event_class_rec.ICX_SESSION_ID is not null AND
zx_global_structures_pkg.g_event_class_rec.PROD_FAMILY_GRP_CODE = 'O2C')
THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
'Delete lines for transaction header which need not be recorded');
DELETE FROM zx_lines_det_factors
WHERE (APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, TRX_ID) IN
(SELECT /*+ cardinality (ZX_TRX_HEADERS_GT 1) */
APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, TRX_ID
FROM ZX_TRX_HEADERS_GT);
| Handle delete for mark tax lines deleted |
+-----------------------------------------------------*/
--Remove all lines marked for delete by the mark_tax_lines_deleted API
BEGIN
/*
OPEN C_HEADERS;
DELETE from zx_lines_det_factors
WHERE APPLICATION_ID = l_application_id_tbl(i)
AND ENTITY_CODE = l_entity_code_tbl(i)
AND EVENT_CLASS_CODE = l_event_class_code_tbl(i)
AND TRX_ID = l_trx_id_tbl(i)
AND line_level_action ='DELETE';
DELETE /*+ ORDERED USE_NL_WITH_INDEX (Z,ZX_LINES_DET_FACTORS_U1) */ FROM zx_lines_det_factors Z
WHERE (Z.APPLICATION_ID, Z.ENTITY_CODE, Z.EVENT_CLASS_CODE, Z.TRX_ID)
IN (SELECT /*+ unnest cardinality (ZX_TRX_HEADERS_GT 1) */
APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, TRX_ID
FROM ZX_TRX_HEADERS_GT)
AND Z.line_level_action ='DELETE';
UPDATE zx_lines_det_factors
SET tax_reporting_flag ='N'
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id;
DELETE FROM ZX_DETAIL_TAX_LINES_GT;
ZX_GLOBAL_STRUCTURES_PKG.PTNR_TAX_REGIME_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.lte_trx_tbl.DELETE;
UPDATE ZX_TRX_HEADERS_GT headers
SET doc_level_recalc_flag = (SELECT distinct(lines.threshold_indicator_flag)
FROM ZX_LINES_DET_FACTORS lines
WHERE lines.application_id = headers.application_id
AND lines.event_class_code = headers.event_class_code
AND lines.entity_code = headers.entity_code
AND lines.trx_id = headers.trx_id
AND lines.threshold_indicator_flag = 'Y' -- Bug 5210984
);
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
ZX_GLOBAL_STRUCTURES_PKG.LOC_GEOGRAPHY_INFO_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl.DELETE;
select ZX_LINES_DET_FACTORS_S.nextval
into l_event_class_rec.event_id
from dual;
SELECT legal_entity_id,
ledger_id,
trx_date,
related_doc_date,
trx_currency_code,
precision,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
Rdng_ship_to_pty_tx_prof_id,
Rdng_ship_from_pty_tx_prof_id,
Rdng_bill_to_pty_tx_prof_id,
Rdng_bill_from_pty_tx_prof_id,
Rdng_ship_to_pty_tx_p_st_id,
Rdng_ship_from_pty_tx_p_st_id,
Rdng_bill_to_pty_tx_p_st_id,
Rdng_bill_from_pty_tx_p_st_id
INTO l_event_class_rec.legal_entity_id,
l_event_class_rec.ledger_id,
l_event_class_rec.trx_date,
l_event_class_rec.rel_doc_date,
l_event_class_rec.trx_currency_code,
l_event_class_rec.precision,
l_event_class_rec.currency_conversion_type,
l_event_class_rec.currency_conversion_rate,
l_event_class_rec.currency_conversion_date,
l_event_class_rec.RDNG_SHIP_TO_PTY_TX_PROF_ID,
l_event_class_rec.RDNG_SHIP_FROM_PTY_TX_PROF_ID,
l_event_class_rec.RDNG_BILL_TO_PTY_TX_PROF_ID,
l_event_class_rec.RDNG_BILL_FROM_PTY_TX_PROF_ID,
l_event_class_rec.RDNG_SHIP_TO_PTY_TX_P_ST_ID,
l_event_class_rec.RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
l_event_class_rec.RDNG_BILL_TO_PTY_TX_P_ST_ID,
l_event_class_rec.RDNG_BILL_FROM_PTY_TX_P_ST_ID
FROM ZX_LINES_DET_FACTORS
WHERE application_id = p_transaction_rec.application_id
AND entity_code = p_transaction_rec.entity_code
AND event_class_code = p_transaction_rec.event_class_code
AND trx_id = p_transaction_rec.trx_id
AND rownum = 1;
DELETE from zx_lines_det_factors
WHERE application_id = l_event_class_rec.application_id and
entity_code = l_event_class_rec.entity_code and
event_class_code = l_event_class_rec.event_class_code and
trx_id = l_event_class_rec.trx_id;
IF l_event_class_rec.tax_event_type_code ='UPDATE' THEN
ZX_TRL_DETAIL_OVERRIDE_PKG.lock_dtl_tax_lines_for_doc(p_application_id => l_event_class_rec.application_id,
p_entity_code => l_event_class_rec.entity_code,
p_event_class_code => l_event_class_rec.event_class_code,
p_trx_id => l_event_class_rec.trx_id,
x_return_status => l_return_status,
x_error_buffer => l_error_buffer
);
END IF; --tax event type is UPDATE
| Call to Update Total Inclusive Tax Amount |
+--------------------------------------------------*/
/* Replace the call to update_total_inc_tax_amt with the merge statement below
update_total_inc_tax_amt(p_event_class_rec => l_event_class_rec,
x_return_status => l_return_status
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':update_total_inc_tax_amt returned errors');
USING (SELECT
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
sum(tax_amt) incl_tax_amt
FROM
zx_detail_tax_lines_gt TaxLines
WHERE
tax_amt_included_flag = 'Y'
-- AND mrc_tax_line_flag = 'N'
AND cancel_flag <> 'Y'
GROUP BY
application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id
) Temp
ON ( lines_dt.tax_amt_included_flag = 'Y'
--AND lines_dt.total_inc_tax_amt is NULL
AND lines_dt.application_id = temp.application_id
AND lines_dt.entity_code = temp.entity_code
AND lines_dt.event_class_code = temp.event_class_code
AND lines_dt.trx_id = temp.trx_id
AND Lines_dt.trx_level_type = temp.trx_level_type
AND Lines_dt.trx_line_id = temp.trx_line_id
)
WHEN MATCHED THEN
UPDATE SET
total_inc_tax_amt = incl_tax_amt;
SELECT threshold_indicator_flag
INTO x_doc_level_recalc_flag
FROM ZX_LINES_DET_FACTORS
WHERE application_id = l_event_class_rec.application_id
AND event_class_code = l_event_class_rec.event_class_code
AND entity_code = l_event_class_rec.entity_code
AND trx_id = l_event_class_rec.trx_id
AND threshold_indicator_flag = 'Y' -- Bug 5210984
AND rownum = 1;
DELETE from zx_lines_det_factors
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id;
UPDATE zx_lines_det_factors
SET tax_reporting_flag ='N'
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id;
| Handle delete for mark tax lines deleted |
+-----------------------------------------------------*/
DELETE FROM ZX_LINES_DET_FACTORS
WHERE line_level_action ='DELETE'
AND application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id;
| Bug 4948674: Handle delete for O2C products when icx_session_id is NOT NULL |
+------------------------------------------------------------------------------*/
IF l_event_class_rec.ICX_SESSION_ID is not null AND
l_event_class_rec.PROD_FAMILY_GRP_CODE = 'O2C' THEN
DELETE from zx_lines_det_factors
WHERE application_id = l_event_class_rec.application_id and
entity_code = l_event_class_rec.entity_code and
event_class_code = l_event_class_rec.event_class_code and
trx_id = l_event_class_rec.trx_id and
icx_session_id = l_event_class_rec.icx_session_id;
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
ZX_GLOBAL_STRUCTURES_PKG.LOC_GEOGRAPHY_INFO_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.EVENT_CLASS_MAPPING_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_LINE_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.TRX_LEVEL_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_TABLE_NAME.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.LOCATION_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_TYPE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_VALUE.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.GEOGRAPHY_ID.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.LOCATION_HASH_TBL.DELETE;
SELECT distinct
header.event_class_code,
header.application_id,
header.entity_code,
header.internal_organization_id,
evntmap.processing_precedence
FROM ZX_EVNT_CLS_MAPPINGS evntmap,
ZX_TRX_HEADERS_GT header
WHERE header.application_id = evntmap.application_id
AND header.entity_code = evntmap.entity_code
AND header.event_class_code = evntmap.event_class_code
ORDER BY evntmap.processing_precedence;
SELECT distinct
r.tax_regime_id,
t.tax_regime_code,
t.tax_provider_id,
r.effective_from,
r.effective_to
FROM ZX_IMPORT_TAX_LINES_GT t, zx_regimes_b r
WHERE t.tax_provider_id is not null
AND r.tax_regime_code = t.tax_regime_code
AND r.effective_to is null;
select event_class_code,
application_id,
entity_code,
internal_organization_id
into l_evnt_cls.event_class_code(1),
l_evnt_cls.application_id(1),
l_evnt_cls.entity_code(1),
l_evnt_cls.internal_organization_id(1)
from ZX_TRX_HEADERS_GT
where rownum=1;
ZX_GLOBAL_STRUCTURES_PKG.LOC_GEOGRAPHY_INFO_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.tax_calc_flag_tbl.DELETE;
select ZX_LINES_DET_FACTORS_S.nextval
into l_event_id
from dual;
SELECT 1 INTO l_dummy_number FROM ZX_ERRORS_GT WHERE ROWNUM = 1;
DELETE FROM ZX_DETAIL_TAX_LINES_GT;
ZX_GLOBAL_STRUCTURES_PKG.PTNR_TAX_REGIME_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.lte_trx_tbl.DELETE;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_IMPORT_TAX_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_TAX_LINK_GT;
| PROCEDURE synchronize_tax_repository : Updates tax repository |
| There exists only pl/sql version for API |
* ======================================================================*/
PROCEDURE synchronize_tax_repository
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_sync_trx_rec IN sync_trx_rec_type,
p_sync_trx_lines_tbl IN sync_trx_lines_tbl_type%type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'SYNCHRONIZE_TAX_REPOSITORY';
SELECT prod_family_grp_code into l_event_class_rec.prod_family_grp_code
FROM
zx_evnt_cls_mappings
WHERE
application_id = p_sync_trx_rec.application_id
AND entity_code = p_sync_trx_rec.entity_code
AND event_class_code = p_sync_trx_rec.event_class_code;
SELECT tax_event_type_code into l_event_class_rec.tax_event_type_code
FROM
ZX_EVNT_TYP_MAPPINGS
WHERE
application_id = p_sync_trx_rec.application_id
AND entity_code = p_sync_trx_rec.entity_code
AND event_class_code = p_sync_trx_rec.event_class_code
AND event_type_code = p_sync_trx_rec.event_type_code;
SELECT distinct internal_organization_id into
l_event_class_rec.internal_organization_id
FROM
zx_lines_det_factors
WHERE
application_id = p_sync_trx_rec.application_id
AND entity_code = p_sync_trx_rec.entity_code
AND event_class_code = p_sync_trx_rec.event_class_code
AND trx_id = p_sync_trx_rec.trx_id;
| Update zx_lines_det_factors |
+--------------------------------------------------*/
/*Retrieve the sequence id since it has to be same for all updated rows*/
select ZX_LINES_DET_FACTORS_S.nextval
INTO l_event_class_rec.event_id
FROM dual;
UPDATE ZX_LINES
SET TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
UPDATE ZX_LINES_SUMMARY
SET TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
UPDATE ZX_REC_NREC_DIST
SET TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
/* update the header level attributes*/
UPDATE ZX_LINES_DET_FACTORS SET
EVENT_ID = l_event_class_rec.event_id,
--EVENT_TYPE_CODE = p_sync_trx_rec.EVENT_TYPE_CODE, /*bug 3922983*/
TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER,
TRX_DESCRIPTION = p_sync_trx_rec.TRX_DESCRIPTION,
TRX_COMMUNICATED_DATE = p_sync_trx_rec.TRX_COMMUNICATED_DATE,
BATCH_SOURCE_ID = p_sync_trx_rec.BATCH_SOURCE_ID,
BATCH_SOURCE_NAME = p_sync_trx_rec.BATCH_SOURCE_NAME,
DOC_SEQ_ID = p_sync_trx_rec.DOC_SEQ_ID,
DOC_SEQ_NAME = p_sync_trx_rec.DOC_SEQ_NAME,
DOC_SEQ_VALUE = p_sync_trx_rec.DOC_SEQ_VALUE,
TRX_DUE_DATE = p_sync_trx_rec.TRX_DUE_DATE,
TRX_TYPE_DESCRIPTION = p_sync_trx_rec.TRX_TYPE_DESCRIPTION,
SUPPLIER_TAX_INVOICE_NUMBER = decode(p_sync_trx_rec.SUPPLIER_TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,SUPPLIER_TAX_INVOICE_NUMBER,p_sync_trx_rec.SUPPLIER_TAX_INVOICE_NUMBER), --Bug 5910475
SUPPLIER_TAX_INVOICE_DATE = decode(p_sync_trx_rec.SUPPLIER_TAX_INVOICE_DATE,FND_API.G_MISS_DATE,SUPPLIER_TAX_INVOICE_DATE,p_sync_trx_rec.SUPPLIER_TAX_INVOICE_DATE), --Bug 5910475
SUPPLIER_EXCHANGE_RATE = decode(p_sync_trx_rec.SUPPLIER_EXCHANGE_RATE,FND_API.G_MISS_NUM,SUPPLIER_EXCHANGE_RATE,p_sync_trx_rec.SUPPLIER_EXCHANGE_RATE), --Bug 5910475
TAX_INVOICE_DATE = decode(p_sync_trx_rec.TAX_INVOICE_DATE,FND_API.G_MISS_DATE,TAX_INVOICE_DATE,p_sync_trx_rec.TAX_INVOICE_DATE), --Bug 5910475
TAX_INVOICE_NUMBER = decode(p_sync_trx_rec.TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,TAX_INVOICE_NUMBER,p_sync_trx_rec.TAX_INVOICE_NUMBER), --Bug 5910475
PORT_OF_ENTRY_CODE = decode(p_sync_trx_rec.PORT_OF_ENTRY_CODE,FND_API.G_MISS_CHAR,PORT_OF_ENTRY_CODE,p_sync_trx_rec.PORT_OF_ENTRY_CODE) , --Bug 5910475
APPLICATION_DOC_STATUS = decode(p_sync_trx_rec.APPLICATION_DOC_STATUS,FND_API.G_MISS_CHAR,APPLICATION_DOC_STATUS,p_sync_trx_rec.APPLICATION_DOC_STATUS) --Bug 5910475
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
/* update the header level attributes*/
UPDATE ZX_LINES_DET_FACTORS SET
EVENT_ID = l_event_class_rec.event_id,
--EVENT_TYPE_CODE = p_sync_trx_rec.EVENT_TYPE_CODE, /*bug 3922983*/
TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER,
TRX_DESCRIPTION = p_sync_trx_rec.TRX_DESCRIPTION,
TRX_COMMUNICATED_DATE = p_sync_trx_rec.TRX_COMMUNICATED_DATE,
BATCH_SOURCE_ID = p_sync_trx_rec.BATCH_SOURCE_ID,
BATCH_SOURCE_NAME = p_sync_trx_rec.BATCH_SOURCE_NAME,
DOC_SEQ_ID = p_sync_trx_rec.DOC_SEQ_ID,
DOC_SEQ_NAME = p_sync_trx_rec.DOC_SEQ_NAME,
DOC_SEQ_VALUE = p_sync_trx_rec.DOC_SEQ_VALUE,
TRX_DUE_DATE = p_sync_trx_rec.TRX_DUE_DATE,
TRX_TYPE_DESCRIPTION = p_sync_trx_rec.TRX_TYPE_DESCRIPTION,
SUPPLIER_TAX_INVOICE_NUMBER = decode(p_sync_trx_rec.SUPPLIER_TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,SUPPLIER_TAX_INVOICE_NUMBER,p_sync_trx_rec.SUPPLIER_TAX_INVOICE_NUMBER), --Bug 5910475
SUPPLIER_TAX_INVOICE_DATE = decode(p_sync_trx_rec.SUPPLIER_TAX_INVOICE_DATE,FND_API.G_MISS_DATE,SUPPLIER_TAX_INVOICE_DATE,p_sync_trx_rec.SUPPLIER_TAX_INVOICE_DATE), --Bug 5910475
SUPPLIER_EXCHANGE_RATE = decode(p_sync_trx_rec.SUPPLIER_EXCHANGE_RATE,FND_API.G_MISS_NUM,SUPPLIER_EXCHANGE_RATE,p_sync_trx_rec.SUPPLIER_EXCHANGE_RATE), --Bug 5910475
TAX_INVOICE_DATE = decode(p_sync_trx_rec.TAX_INVOICE_DATE,FND_API.G_MISS_DATE,TAX_INVOICE_DATE,p_sync_trx_rec.TAX_INVOICE_DATE), --Bug 5910475
TAX_INVOICE_NUMBER = decode(p_sync_trx_rec.TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,TAX_INVOICE_NUMBER,p_sync_trx_rec.TAX_INVOICE_NUMBER), --Bug 5910475
PORT_OF_ENTRY_CODE = decode(p_sync_trx_rec.PORT_OF_ENTRY_CODE,FND_API.G_MISS_CHAR,PORT_OF_ENTRY_CODE,p_sync_trx_rec.PORT_OF_ENTRY_CODE) , --Bug 5910475
APPLICATION_DOC_STATUS = decode(p_sync_trx_rec.APPLICATION_DOC_STATUS,FND_API.G_MISS_CHAR,APPLICATION_DOC_STATUS,p_sync_trx_rec.APPLICATION_DOC_STATUS) --Bug 5910475
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
UPDATE ZX_LINES
SET TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
UPDATE ZX_LINES_SUMMARY
SET TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
UPDATE ZX_REC_NREC_DIST
SET TRX_NUMBER = p_sync_trx_rec.TRX_NUMBER
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID;
/* update the line level attributes if passed*/
IF (p_sync_trx_lines_tbl.APPLICATION_ID.EXISTS(1)) THEN
FORALL i IN 1..nvl(p_sync_trx_lines_tbl.APPLICATION_ID.LAST,-99)
UPDATE ZX_LINES_DET_FACTORS SET
TRX_LEVEL_TYPE = p_sync_trx_lines_tbl.TRX_LEVEL_TYPE(i),
TRX_LINE_ID = p_sync_trx_lines_tbl.TRX_LINE_ID(i),
TRX_WAYBILL_NUMBER = p_sync_trx_lines_tbl.TRX_WAYBILL_NUMBER(i),
TRX_LINE_DESCRIPTION = p_sync_trx_lines_tbl.TRX_LINE_DESCRIPTION(i),
PRODUCT_DESCRIPTION = p_sync_trx_lines_tbl.PRODUCT_DESCRIPTION(i),
TRX_LINE_GL_DATE = p_sync_trx_lines_tbl.TRX_LINE_GL_DATE(i),
MERCHANT_PARTY_NAME = p_sync_trx_lines_tbl.MERCHANT_PARTY_NAME(i),
MERCHANT_PARTY_DOCUMENT_NUMBER = p_sync_trx_lines_tbl.MERCHANT_PARTY_DOCUMENT_NUMBER(i),
MERCHANT_PARTY_REFERENCE = p_sync_trx_lines_tbl.MERCHANT_PARTY_REFERENCE(i),
MERCHANT_PARTY_TAXPAYER_ID = p_sync_trx_lines_tbl.MERCHANT_PARTY_TAXPAYER_ID(i),
MERCHANT_PARTY_TAX_REG_NUMBER = p_sync_trx_lines_tbl.MERCHANT_PARTY_TAX_REG_NUMBER(i),
ASSET_NUMBER = p_sync_trx_lines_tbl.ASSET_NUMBER(i)
WHERE APPLICATION_ID = p_sync_trx_rec.APPLICATION_ID
AND ENTITY_CODE = p_sync_trx_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_sync_trx_rec.EVENT_CLASS_CODE
AND TRX_ID = p_sync_trx_rec.TRX_ID
AND TRX_LINE_ID = p_sync_trx_lines_tbl.TRX_LINE_ID(i)
AND TRX_LEVEL_TYPE = p_sync_trx_lines_tbl.TRX_LEVEL_TYPE(i);
SELECT trx_date,
related_doc_date,
provnl_tax_determination_date
FROM zx_lines_det_factors
WHERE application_id = c_application_id
AND entity_code = c_entity_code
AND event_class_code = c_event_class_code
AND trx_id = c_trx_id
AND event_id = c_event_id
AND ROWNUM = 1;
ZX_GLOBAL_STRUCTURES_PKG.LOC_GEOGRAPHY_INFO_TBL.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl.DELETE;
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
| PROCEDURE global_document_update : |
* ======================================================================*/
PROCEDURE Global_document_update
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 ,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_rec IN OUT NOCOPY transaction_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'GLOBAL_DOCUMENT_UPDATE';
SAVEPOINT global_document_update_PVT;
| Calling Global Document Update with |
| with validation status |
+-----------------------------------------------*/
ZX_API_PUB.global_document_update(p_api_version,
l_init_msg_list,
p_commit,
p_validation_level,
l_return_status,
x_msg_count,
x_msg_data,
p_transaction_rec,
l_validation_status
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_API_PUB.global_document_update returned errors');
ROLLBACK TO global_document_update_PVT;
ROLLBACK TO global_document_update_PVT;
ROLLBACK TO global_document_update_PVT;
END global_document_update;
| PROCEDURE global_document_update : |
* ======================================================================*/
PROCEDURE global_document_update
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_rec IN OUT NOCOPY transaction_rec_type,
p_validation_status IN ZX_API_PUB.validation_status_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'GLOBAL_DOCUMENT_UPDATE';
SAVEPOINT global_document_update_PVT;
ZX_VALID_INIT_PARAMS_PKG.global_document_update(x_return_status => l_return_status,
p_event_class_rec => l_event_class_rec,
p_trx_rec => p_transaction_rec
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_VALID_INIT_PARAMS_PKG.global_document_update returned errors');
| Update zx_lines_det_factors |
+------------------------------------------------*/
IF l_event_class_rec.tax_event_type_code IN ('DELETE','PURGE') THEN
-- Bug 5200373: Incarporated missing hook to take snapshot of zx_lines_det_factors so that
-- upgraded R11i partner softwares can handle the header level document delete
-- sceanrio.
zx_r11i_tax_partner_pkg.copy_trx_line_for_ptnr_bef_upd(NULL
, l_event_class_rec
, NULL
, 'N'
, NULL
, NULL
, l_return_status);
/*Lock trx line det factors for delete*/
SELECT *
INTO l_lines_det_rec
FROM ZX_LINES_DET_FACTORS
WHERE application_id = p_transaction_rec.application_id
AND entity_code = p_transaction_rec.entity_code
AND event_class_code = p_transaction_rec.event_class_code
AND trx_id = p_transaction_rec.trx_id
AND rownum = 1
FOR UPDATE NOWAIT;
DELETE from ZX_LINES_DET_FACTORS
WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
AND TRX_ID = p_transaction_rec.TRX_ID;
UPDATE ZX_LINES_DET_FACTORS
SET EVENT_TYPE_CODE = l_event_class_rec.event_type_code,
TAX_EVENT_TYPE_CODE = l_event_class_rec.tax_event_type_code,
DOC_EVENT_STATUS = l_event_class_rec.doc_status_code
WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
AND TRX_ID = p_transaction_rec.TRX_ID;
UPDATE ZX_LINES_DET_FACTORS
SET EVENT_TYPE_CODE = l_event_class_rec.event_type_code,
TAX_EVENT_TYPE_CODE = l_event_class_rec.tax_event_type_code,
DOC_EVENT_STATUS = l_event_class_rec.doc_status_code
WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
AND TRX_ID = p_transaction_rec.TRX_ID;
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_VALID_INIT_PARAMS_PKG.global_document_update returned errors');
ROLLBACK TO global_document_update_PVT;
ROLLBACK TO global_document_update_PVT;
ROLLBACK TO global_document_update_PVT;
END global_document_update;
| PROCEDURE Mark_tax_lines_deleted : |
* ======================================================================*/
PROCEDURE Mark_tax_lines_deleted
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_transaction_line_rec IN OUT NOCOPY transaction_line_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'MARK_TAX_LINES_DELETED';
ZX_VALID_INIT_PARAMS_PKG.mark_tax_lines_deleted( x_return_status => l_return_status,
p_transaction_line_rec => p_transaction_line_rec
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_VALID_INIT_PARAMS_PKG.mark_tax_lines_deleted returned errors');
| Call Service Type Mark Tax Lines Deleted |
+--------------------------------------------------*/
ZX_SRVC_TYP_PKG.mark_tax_lines_deleted( p_trx_line_rec => p_transaction_line_rec,
x_return_status => l_return_status
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_SRVC_TYP_PKG.mark_tax_lines_deleted returned errors');
| Update line level action to Deleted |
+--------------------------------------------------*/
IF p_transaction_line_rec.event_type_code is null THEN
BEGIN
SELECT event_type_code
INTO l_event_type_code
FROM ZX_EVNT_TYP_MAPPINGS
WHERE application_id = p_transaction_line_rec.application_id
AND entity_code = p_transaction_line_rec.entity_code
AND event_class_code = p_transaction_line_rec.event_class_code
AND tax_event_type_code = 'UPDATE';
UPDATE ZX_LINES_DET_FACTORS
SET LINE_LEVEL_ACTION = 'DELETE',
EVENT_TYPE_CODE = nvl(p_transaction_line_rec.event_type_code, l_event_type_code),
TAX_EVENT_TYPE_CODE = 'UPDATE'
WHERE application_id = p_transaction_line_rec.application_id
AND entity_code = p_transaction_line_rec.entity_code
AND event_class_code = p_transaction_line_rec.event_class_code
AND trx_id = p_transaction_line_rec.trx_id
AND trx_line_id = p_transaction_line_rec.trx_line_id;
END mark_tax_lines_deleted;
select ZX_LINES_DET_FACTORS_S.nextval
into l_event_class_rec.event_id
from dual;
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
select zx_lines_det_factors_s.nextval
into l_event_class_rec.event_id
from dual;
SELECT distinct
header.event_class_code,
header.application_id,
header.entity_code,
header.internal_organization_id,
evntmap.processing_precedence
FROM ZX_EVNT_CLS_MAPPINGS evntmap,
ZX_TRX_HEADERS_GT header
WHERE header.application_id = evntmap.application_id
AND header.entity_code = evntmap.entity_code
AND header.event_class_code = evntmap.event_class_code
ORDER BY evntmap.processing_precedence;
SELECT INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
LEGAL_ENTITY_ID,
ENTITY_CODE,
EVENT_TYPE_CODE,
EVENT_CLASS_CODE,
TRX_ID,
QUOTE_FLAG,
ICX_SESSION_ID
FROM ZX_TRX_HEADERS_GT
WHERE event_class_code = p_event_class_code
AND application_id = p_application_id
AND entity_code = p_entity_code
AND (validation_check_flag is null OR
validation_check_flag <> 'N');
SELECT APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, TRX_ID, ICX_SESSION_ID,
EVENT_TYPE_CODE, TAX_EVENT_TYPE_CODE, DOC_EVENT_STATUS
FROM ZX_TRX_HEADERS_GT;
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
TRX_LINE_ID,
TRX_LEVEL_TYPE
FROM
ZX_ITM_DISTRIBUTIONS_GT
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND nvl(tax_variance_calc_flag,l_event_class_rec.tax_variance_calc_flag) = 'Y'
AND ref_doc_application_id is not null
AND trx_line_dist_qty is null;
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
TRX_LINE_ID,
TRX_LEVEL_TYPE
FROM
ZX_ITM_DISTRIBUTIONS_GT
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND nvl(tax_variance_calc_flag,l_event_class_rec.tax_variance_calc_flag) = 'Y'
AND ref_doc_application_id is not null
AND unit_price is null;
SELECT l_event_id event_id,
hdr.event_type_code event_type_code,
zetm.tax_event_type_code tax_event_type_code,
zect.status_code doc_event_status,
hdr.application_id application_id,
hdr.entity_code entity_code,
hdr.event_class_code event_class_code,
hdr.trx_id trx_id
FROM zx_trx_headers_gt hdr,
zx_evnt_typ_mappings zetm,
zx_evnt_cls_typs zect
WHERE zetm.event_class_code = hdr.event_class_code
AND zetm.application_id = hdr.application_id
AND zetm.entity_code = hdr.entity_code
AND zetm.event_type_code = hdr.event_type_code
AND zect.tax_event_type_code = zetm.tax_event_type_code
AND zect.tax_event_class_code = zetm.tax_event_class_code
AND zetm.enabled_flag = 'Y'
AND hdr.application_id = c_application_id
AND hdr.entity_code = c_entity_code
AND hdr.event_class_code = c_event_class_code
AND hdr.quote_flag <> 'Y';
| Update zx_lines_det_factors |
+------------------------------------------------*/
--FOR c_rec in c_event_info loop
-- IF c_rec.quote_flag <> 'Y' THEN -- Bug 5646787
--
-- UPDATE ZX_LINES_DET_FACTORS D
-- SET EVENT_ID = c_rec.EVENT_ID,
-- EVENT_TYPE_CODE = c_rec.EVENT_TYPE_CODE,
-- TAX_EVENT_TYPE_CODE = c_rec.TAX_EVENT_TYPE_CODE,
-- DOC_EVENT_STATUS = c_rec.DOC_EVENT_STATUS
-- WHERE
-- D.APPLICATION_ID = c_rec.application_id
-- AND D.ENTITY_CODE = c_rec.ENTITY_CODE
-- AND D.EVENT_CLASS_CODE = c_rec.EVENT_CLASS_CODE
-- AND D.TRX_ID = c_rec.TRX_ID;
SELECT event_class_code,
application_id,
internal_organization_id,
entity_code
INTO l_evnt_cls.event_class_code(1),
l_evnt_cls.application_id(1),
l_evnt_cls.internal_organization_id(1),
l_evnt_cls.entity_code(1)
FROM ZX_TRX_HEADERS_GT
WHERE rownum=1;
select ZX_LINES_DET_FACTORS_S.nextval
into l_event_id
from dual;
UPDATE ZX_LINES_DET_FACTORS D
SET EVENT_ID = l_event_id_tbl(i),
EVENT_TYPE_CODE = l_event_type_code_tbl(i),
TAX_EVENT_TYPE_CODE = l_tax_event_type_code_tbl(i),
DOC_EVENT_STATUS = l_doc_event_status_tbl(i)
WHERE D.APPLICATION_ID = l_application_id_tbl(i)
AND D.ENTITY_CODE = l_entity_code_tbl(i)
AND D.EVENT_CLASS_CODE = l_event_class_code_tbl(i)
AND D.TRX_ID = l_trx_id_tbl(i)
AND D.TRX_LINE_ID IN
(SELECT TL.TRX_LINE_ID
FROM ZX_ITM_DISTRIBUTIONS_GT TL
WHERE TL.APPLICATION_ID = l_application_id_tbl(i)
AND TL.ENTITY_CODE = l_entity_code_tbl(i)
AND TL.EVENT_CLASS_CODE = l_event_class_code_tbl(i)
AND TL.TRX_ID = l_trx_id_tbl(i));
UPDATE ZX_LINES_DET_FACTORS D
SET EVENT_ID = l_event_id_tbl(i),
EVENT_TYPE_CODE = l_event_type_code_tbl(i),
TAX_EVENT_TYPE_CODE = l_tax_event_type_code_tbl(i),
DOC_EVENT_STATUS = l_doc_event_status_tbl(i)
WHERE D.APPLICATION_ID = l_application_id_tbl(i)
AND D.ENTITY_CODE = l_entity_code_tbl(i)
AND D.EVENT_CLASS_CODE = l_event_class_code_tbl(i)
AND D.TRX_ID = l_trx_id_tbl(i);
l_event_id_tbl.DELETE;
l_event_type_code_tbl.DELETE;
l_tax_event_type_code_tbl.DELETE;
l_doc_event_status_tbl.DELETE;
l_application_id_tbl.DELETE;
l_entity_code_tbl.DELETE;
l_event_class_code_tbl.DELETE;
l_trx_id_tbl.DELETE;
select ICX_SESSION_ID,QUOTE_FLAG
INTO l_event_class_rec.ICX_SESSION_ID,l_event_class_rec.QUOTE_FLAG
FROM ZX_TRX_HEADERS_GT
where rownum = 1;
SELECT first_pty_org_id ,
related_doc_date,
adjusted_doc_date,
trx_date,
provnl_tax_determination_date
INTO l_event_class_rec.first_pty_org_id,
l_related_doc_date,
l_adjusted_doc_date,
l_trx_date,
l_prov_tax_det_date
FROM ZX_LINES_DET_FACTORS
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id
AND rownum = 1;
ZX_GLOBAL_STRUCTURES_PKG.rec_nrec_ccid_tbl.DELETE;
DELETE FROM ZX_REC_NREC_DIST_GT;
| Update zx_lines_det_factors |
+------------------------------------------------*/
IF zx_global_structures_pkg.g_event_class_rec.quote_flag <> 'Y' OR
zx_global_structures_pkg.g_event_class_rec.ICX_SESSION_ID is not null
THEN
-- ICX_SESSION_ID / QUOTE_FLAG should be same for all rows
IF zx_global_structures_pkg.g_event_class_rec.ICX_SESSION_ID is not null THEN
BEGIN
OPEN C_HEADERS;
| Bug 4948674: Handle delete for P2P products when icx_session_id is NOT NULL |
+------------------------------------------------------------------------------*/
FORALL i IN l_application_id_tbl.FIRST .. l_application_id_tbl.LAST
DELETE from zx_lines_det_factors
WHERE APPLICATION_ID = l_application_id_tbl(i)
AND ENTITY_CODE = l_entity_code_tbl(i)
AND EVENT_CLASS_CODE = l_event_class_code_tbl(i)
AND TRX_ID = l_trx_id_tbl(i)
AND ICX_SESSION_ID = l_icx_session_id_tbl(i);
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRX_HEADERS_GT;
SELECT COUNT(*) INTO l_dummy_number FROM ZX_TRANSACTION_LINES_GT;
SELECT dist.application_id,
dist.entity_code,
dist.event_class_code,
evnttyp.event_type_code,
dist.tax_event_class_code,
'UPDATE' tax_event_type_code,
'UPDATED' doc_status_code,
evntcls.summarization_flag,
evntcls.retain_summ_tax_line_id_flag
FROM zx_rec_nrec_dist dist,
zx_evnt_cls_mappings evntcls,
zx_evnt_typ_mappings evnttyp,
zx_tax_dist_id_gt distgt
WHERE dist.application_id = evntcls.application_id
AND dist.entity_code = evntcls.entity_code
AND dist.event_class_code = evntcls.event_class_code
AND evnttyp.application_id = evntcls.application_id
AND evnttyp.entity_code = evntcls.entity_code
AND evnttyp.event_class_code = evntcls.event_class_code
AND evnttyp.tax_event_type_code = 'UPDATE'
AND dist.REC_NREC_TAX_DIST_ID = distgt.tax_dist_id;
select ZX_LINES_DET_FACTORS_S.nextval
into l_event_class_rec.event_id
from dual;
| Update zx_lines_det_factors |
+------------------------------------------------*/
UPDATE ZX_LINES_DET_FACTORS
SET EVENT_TYPE_CODE = l_event_class_rec.event_type_code,
TAX_EVENT_TYPE_CODE = l_event_class_rec.tax_event_type_code,
EVENT_ID = l_event_class_rec.event_id,
DOC_EVENT_STATUS = l_event_class_rec.doc_status_code
WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
-- AND TRX_ID = p_transaction_rec.TRX_ID
AND (TRX_ID,TRX_LINE_ID,TRX_LEVEL_TYPE) IN (Select dist.trx_id, dist.trx_line_id,
dist.trx_level_type from zx_rec_nrec_dist dist,
zx_tax_dist_id_gt zxgt
where dist.rec_nrec_tax_dist_id = zxgt.tax_dist_id);
| PROCEDURE Update_tax_dist_gl_date : Updates gl date of a list of Tax Distributions |
| GTT involved : ZX_TAX_DIST_ID_GT |
* ====================================================================================*/
PROCEDURE Update_Tax_dist_gl_date
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gl_date IN DATE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TAX_DIST_GL_DATE';
SAVEPOINT Update_Tax_Dist_GL_Date_PVT;
| Update gl date |
+-------------------------------*/
ZX_TRL_PUB_PKG.update_gl_date(p_gl_date =>p_gl_date,
x_return_status =>l_return_status
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_TRL_PUB_PKG.update_gl_date returned errors');
ROLLBACK TO Update_Tax_Dist_Gl_Date_PVT;
ROLLBACK TO Update_Tax_Dist_GL_Date_PVT;
ROLLBACK TO Update_tax_dist_gl_date_PVT;
END update_tax_dist_gl_date;
| PROCEDURE Update_exchange_rate : Updates Exchange Rate |
+========================================================================*/
PROCEDURE update_exchange_rate
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_rec IN transaction_rec_type,
p_curr_conv_rate IN NUMBER,
p_curr_conv_date IN DATE,
p_curr_conv_type IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EXCHANGE_RATE';
SAVEPOINT Update_Exchange_Rate_PVT;
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_TRL_PUB_PKG.update_gl_date returned errors');
SELECT prod_family_grp_code
INTO l_event_class_rec.prod_family_grp_code
FROM zx_evnt_cls_mappings
WHERE application_id = p_transaction_rec.application_id
AND entity_code = p_transaction_rec.entity_code
AND event_class_code = p_transaction_rec.event_class_code;
SELECT tax_event_type_code
INTO l_event_class_rec.tax_event_type_code
FROM zx_evnt_typ_mappings
WHERE application_id = p_transaction_rec.application_id
AND entity_code = p_transaction_rec.entity_code
AND event_class_code = p_transaction_rec.event_class_code
AND event_type_code = p_transaction_rec.event_type_code;
SELECT ledger_id
INTO l_ledger_id
FROM ZX_LINES_DET_FACTORS
WHERE application_id = p_transaction_rec.application_id
AND entity_code = p_transaction_rec.entity_code
AND event_class_code = p_transaction_rec.event_class_code
AND trx_id = p_transaction_rec.trx_id
AND rownum = 1;
| Call TDS routine update_exchange_rate |
+------------------------------------------------*/
ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate(p_event_class_rec => l_event_class_rec,
p_ledger_id => l_ledger_id,
p_currency_conversion_rate => p_curr_conv_rate,
p_currency_conversion_type => p_curr_conv_type,
p_currency_conversion_date => p_curr_conv_date,
x_return_status => l_return_status
);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,G_PKG_NAME||': '||l_api_name||':ZX_TDS_CALC_SERVICES_PUB_PKG.update_exchange_rate returned errors');
| Update zx_lines_det_factors for currency info |
+------------------------------------------------*/
UPDATE ZX_LINES_DET_FACTORS SET
CURRENCY_CONVERSION_DATE = p_curr_conv_date,
CURRENCY_CONVERSION_RATE = p_curr_conv_rate,
CURRENCY_CONVERSION_TYPE = p_curr_conv_type
WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
AND TRX_ID = p_transaction_rec.TRX_ID;
ROLLBACK TO Update_Exchange_Rate_PVT;
ROLLBACK TO Update_Exchange_Rate_PVT;
ROLLBACK TO Update_Exchange_Rate_PVT;
END Update_Exchange_Rate;
SELECT summarization_flag,
retain_summ_tax_line_id_flag
FROM zx_evnt_cls_mappings
WHERE application_id = p_transaction_rec.application_id
AND entity_code = p_transaction_rec.entity_code
AND event_class_code = p_transaction_rec.event_class_code;
| Update zx_lines_det_factors |
+------------------------------------------------*/
UPDATE ZX_LINES_DET_FACTORS
SET TAX_EVENT_TYPE_CODE = l_event_class_rec.tax_event_type_code,
DOC_EVENT_STATUS = l_event_class_rec.doc_status_code
WHERE APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
AND TRX_ID = p_transaction_rec.TRX_ID;
SELECT zthg.application_id
, zthg.entity_code
, zthg.event_class_code
, zthg.trx_id
, zetm.event_type_code -- Bug 5598384
, zect.tax_event_type_code
, zect.status_code
FROM ZX_TRX_HEADERS_GT zthg
, ZX_EVNT_TYP_MAPPINGS zetm
, ZX_EVNT_CLS_TYPS zect
WHERE zthg.event_class_code = zetm.event_class_code
AND zthg.entity_code = zetm.entity_code
AND zthg.application_id = zetm.application_id
AND zetm.event_type_code = DECODE(zetm.event_class_code, -- Bug 5598384
'INVOICE', 'INV_COMPLETE',
'DEBIT_MEMO', 'DM_COMPLETE',
'CREDIT_MEMO', 'CM_COMPLETE'
)
AND zect.tax_event_class_code = zetm.tax_event_class_code
AND zect.tax_event_type_code = zetm.tax_event_type_code
AND (validation_check_flag is null OR
validation_check_flag <> 'N');
UPDATE ZX_LINES_DET_FACTORS
SET EVENT_TYPE_CODE = l_event_type_code_tbl(i),
TAX_EVENT_TYPE_CODE = l_tax_event_type_code_tbl(i),
DOC_EVENT_STATUS = l_doc_event_status_tbl(i)
WHERE
APPLICATION_ID = l_application_id_tbl(i)
AND ENTITY_CODE = l_entity_code_tbl(i)
AND EVENT_CLASS_CODE = l_event_class_code_tbl(i)
AND TRX_ID = l_trx_id_tbl(i);
SELECT tax_event_class_code, intrcmp_tx_evnt_cls_code
FROM zx_evnt_cls_mappings
WHERE application_id = c_application_id
AND entity_code = c_entity_code
AND event_class_code = c_event_class_code;
SELECT DEF_INTRCMP_TRX_BIZ_CATEGORY
FROM ZX_EVNT_CLS_OPTIONS op, zx_party_tax_profile ptp
WHERE op.application_id = c_application_id
AND op.entity_code = c_entity_code
AND op.event_class_code = c_event_class_code
AND op.FIRST_PTY_ORG_ID = ptp.party_tax_profile_id
AND op.effective_from <= p_effective_date
AND (op.effective_to >= p_effective_date OR op.effective_to IS NULL)
AND ptp.party_id = p_org_id
AND ptp.party_type_code = 'OU';
SELECT internal_organization_id,
application_id
INTO l_internal_organization_id,
l_app_id
FROM ZX_TRX_HEADERS_GT headers
WHERE rownum =1;
INSERT INTO ZX_ERRORS_GT (application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
summary_tax_line_number,
tax_line_id,
trx_line_dist_id,
message_text)
values (errors_tbl.application_id(i),
errors_tbl.entity_code(i),
errors_tbl.event_class_code(i),
errors_tbl.trx_id(i),
errors_tbl.trx_line_id(i),
errors_tbl.trx_level_type(i),
errors_tbl.summary_tax_line_number(i),
errors_tbl.tax_line_id(i),
errors_tbl.trx_line_dist_id(i),
NVL(errors_tbl.message_text(i),'UNEXPECTED_ERROR_DUMP_MSG')
);
errors_tbl.application_id.delete;
errors_tbl.entity_code.delete;
errors_tbl.event_class_code.delete;
errors_tbl.trx_id.delete;
errors_tbl.trx_line_id.delete;
errors_tbl.trx_level_type.delete;
errors_tbl.summary_tax_line_number.delete;
errors_tbl.tax_line_id.delete;
errors_tbl.trx_line_dist_id.delete;
errors_tbl.message_text.delete;
| PROCEDURE insert_line_det_factors : This procedure should be called by |
| products when creating a document or inserting a new transaction line |
| for existing document. This line will be flagged to be picked up by the |
| tax calculation process |
* =======================================================================*/
PROCEDURE insert_line_det_factors (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_duplicate_line_rec IN transaction_line_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LINE_DET_FACTORS';
SAVEPOINT Insert_Line_Det_Factors_PVT;
IF l_event_class_rec.event_type_code = 'INV_UPDATE' THEN
FOR l_line_det_rec in lock_line_det_factors_for_doc(l_event_class_rec)
LOOP
l_record_exists := TRUE;
'Lock the document so that no updates can happen for transaction :' || to_char(l_event_class_rec.trx_id));
SELECT ZX_LINES_DET_FACTORS_S.nextval
INTO l_event_class_rec.event_id
FROM dual;
| Validate and Initializate parameters for Inserting |
| into line_det_factors |
+------------------------------------------------------*/
ZX_VALID_INIT_PARAMS_PKG.insupd_line_det_factors(p_event_class_rec =>l_event_class_rec,
p_trx_line_index => 1,
x_return_status =>l_return_status
);
SELECT
default_taxation_country,
document_sub_type,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
product_fisc_classification,
product_category,
assessable_value,
product_type,
decode(l_event_class_rec.prod_family_grp_code,'P2P',input_tax_classification_code,
'O2C',output_tax_classification_code)
INTO
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.default_taxation_country(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.document_sub_type(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_business_category(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_intended_use(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.user_defined_fisc_class(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_fisc_classification(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_category(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.assessable_value(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_type(1),
l_tax_classification_code
FROM ZX_LINES_DET_FACTORS
WHERE application_id = p_duplicate_line_rec.application_id
AND entity_code = p_duplicate_line_rec.entity_code
AND event_class_code = p_duplicate_line_rec.event_class_code
AND trx_id = p_duplicate_line_rec.trx_id
AND trx_line_id = p_duplicate_line_rec.trx_line_id
AND trx_level_type = p_duplicate_line_rec.trx_level_type;
|Call to insert the lines |
+------------------------------------------*/
ZX_SRVC_TYP_PKG.insupd_line_det_factors(p_event_class_rec => l_event_class_rec,
x_return_status => l_return_status
);
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
ROLLBACK TO Insert_Line_Det_Factors_PVT;
ROLLBACK TO Insert_Line_Det_Factors_PVT;
ROLLBACK TO Insert_Line_Det_Factors_PVT;
END insert_line_det_factors;
| PROCEDURE insert_line_det_factors : This overloaded procedure will be called|
| by iProcurement to insert all the transaction lines with defaulted tax |
| determining attributes into zx_lines_det_factors after complying with the |
| validation process |
* ============================================================================*/
PROCEDURE insert_line_det_factors (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_LINE_DET_FACTORS';
SAVEPOINT Insert_Line_Det_Factors_PVT;
IF l_event_class_rec.event_type_code = 'INV_UPDATE' THEN
FOR l_line_det_rec in lock_line_det_factors_for_doc(l_event_class_rec)
LOOP
l_record_exists := TRUE;
SELECT ZX_LINES_DET_FACTORS_S.nextval
INTO l_event_class_rec.event_id
FROM dual;
| Validate and Initializate parameters for Inserting |
| into line_det_factors |
+------------------------------------------------------*/
ZX_VALID_INIT_PARAMS_PKG.insupd_line_det_factors(p_event_class_rec =>l_event_class_rec,
p_trx_line_index =>1,
x_return_status =>l_return_status
);
|Call to insert the lines |
+------------------------------------------*/
ZX_SRVC_TYP_PKG.insupd_line_det_factors(p_event_class_rec => l_event_class_rec,
x_return_status => l_return_status
);
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
ROLLBACK TO Insert_Line_Det_Factors_PVT;
ROLLBACK TO Insert_Line_Det_Factors_PVT;
ROLLBACK TO Insert_Line_Det_Factors_PVT;
END insert_line_det_factors;
| PROCEDURE update_det_factors_hdr: This procedure should be called by |
| products when updating any of the header attributes on the transaction |
| so that the tax repository is also in sync with the header level updates|
| |
| NOTES: Products will pass intended nullable values as null while they |
| will pass G_MISS_NUM/G_MISS_DATE/G_MISS_CHAR for the attributes where |
| intention is to retain the original values as stored in tax repository |
* =======================================================================*/
PROCEDURE update_det_factors_hdr
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_hdr_det_factors_rec IN header_det_factors_rec_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DET_FACTORS_HDR';
SAVEPOINT Update_Det_Factors_Hdr_PVT;
G_PUB_SRVC := 'UPDATE_DET_FACTORS_HDR';
SELECT 1
INTO l_incomplete_scenario
FROM zx_lines_det_factors
WHERE event_class_code = p_hdr_det_factors_rec.event_class_code
AND application_id = p_hdr_det_factors_rec.application_id
AND entity_code = p_hdr_det_factors_rec.entity_code
AND trx_id = p_hdr_det_factors_rec.trx_id
AND tax_event_type_code = 'VALIDATE_FOR_TAX'
AND rownum = 1;
SELECT zxevnttyp.tax_event_type_code,
zxevnttyp.status_code
INTO l_event_class_rec.tax_event_type_code,
l_event_class_rec.doc_status_code
FROM ZX_EVNT_TYP_MAPPINGS zxevntmap,
ZX_EVNT_CLS_TYPS zxevnttyp
WHERE zxevntmap.event_class_code = p_hdr_det_factors_rec.event_class_code
AND zxevntmap.application_id = p_hdr_det_factors_rec.application_id
AND zxevntmap.entity_code = p_hdr_det_factors_rec.entity_code
AND zxevntmap.event_type_code = p_hdr_det_factors_rec.event_type_code
AND zxevnttyp.tax_event_type_code = zxevntmap.tax_event_type_code
AND zxevnttyp.tax_event_class_code = zxevntmap.tax_event_class_code
AND zxevntmap.enabled_flag = 'Y';
UPDATE ZX_LINES_DET_FACTORS
SET TAX_EVENT_TYPE_CODE = l_event_class_rec.tax_event_type_code,
DOC_EVENT_STATUS = l_event_class_rec.doc_status_code
WHERE APPLICATION_ID = p_hdr_det_factors_rec.APPLICATION_ID
AND ENTITY_CODE = p_hdr_det_factors_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_hdr_det_factors_rec.EVENT_CLASS_CODE
AND TRX_ID = p_hdr_det_factors_rec.TRX_ID;
| Validate and Initializate parameters for Inserting |
| into line_det_factors |
+------------------------------------------------------*/
ZX_VALID_INIT_PARAMS_PKG.insupd_line_det_factors(p_event_class_rec =>l_event_class_rec,
p_trx_line_index => NULL,
x_return_status =>l_return_status
);
|Update the headers only in zx_line_det_factors |
+----------------------------------------------*/
UPDATE ZX_LINES_DET_FACTORS SET
APPLICATION_ID = p_hdr_det_factors_rec.APPLICATION_ID,
ENTITY_CODE = p_hdr_det_factors_rec.ENTITY_CODE,
EVENT_CLASS_CODE = p_hdr_det_factors_rec.EVENT_CLASS_CODE,
EVENT_TYPE_CODE = p_hdr_det_factors_rec.EVENT_TYPE_CODE,
INTERNAL_ORGANIZATION_ID = p_hdr_det_factors_rec.INTERNAL_ORGANIZATION_ID,
LEGAL_ENTITY_ID = p_hdr_det_factors_rec.LEGAL_ENTITY_ID,
TRX_ID = p_hdr_det_factors_rec.TRX_ID,
TRX_DOC_REVISION = decode(p_hdr_det_factors_rec.TRX_DOC_REVISION,FND_API.G_MISS_CHAR,
TRX_DOC_REVISION,
p_hdr_det_factors_rec.TRX_DOC_REVISION),
TRX_DATE = decode(p_hdr_det_factors_rec.TRX_DATE,FND_API.G_MISS_DATE,
TRX_DATE,
p_hdr_det_factors_rec.TRX_DATE),
LEDGER_ID = decode(p_hdr_det_factors_rec.LEDGER_ID,FND_API.G_MISS_NUM,
LEDGER_ID,
p_hdr_det_factors_rec.LEDGER_ID),
INTERNAL_ORG_LOCATION_ID = decode(p_hdr_det_factors_rec.INTERNAL_ORG_LOCATION_ID,FND_API.G_MISS_NUM,
INTERNAL_ORG_LOCATION_ID,
p_hdr_det_factors_rec.INTERNAL_ORG_LOCATION_ID),
TRX_CURRENCY_CODE = decode(p_hdr_det_factors_rec.TRX_CURRENCY_CODE,FND_API.G_MISS_CHAR,
TRX_CURRENCY_CODE,
p_hdr_det_factors_rec.TRX_CURRENCY_CODE),
CURRENCY_CONVERSION_TYPE = decode(p_hdr_det_factors_rec.CURRENCY_CONVERSION_TYPE,FND_API.G_MISS_CHAR,
CURRENCY_CONVERSION_TYPE,
p_hdr_det_factors_rec.CURRENCY_CONVERSION_TYPE),
CURRENCY_CONVERSION_RATE = decode(p_hdr_det_factors_rec.CURRENCY_CONVERSION_RATE,FND_API.G_MISS_NUM,
CURRENCY_CONVERSION_RATE,
p_hdr_det_factors_rec.CURRENCY_CONVERSION_RATE),
CURRENCY_CONVERSION_DATE = decode(p_hdr_det_factors_rec.CURRENCY_CONVERSION_DATE,FND_API.G_MISS_DATE,
CURRENCY_CONVERSION_DATE,
p_hdr_det_factors_rec.CURRENCY_CONVERSION_DATE),
MINIMUM_ACCOUNTABLE_UNIT = decode(p_hdr_det_factors_rec.MINIMUM_ACCOUNTABLE_UNIT,FND_API.G_MISS_NUM,
MINIMUM_ACCOUNTABLE_UNIT,
p_hdr_det_factors_rec.MINIMUM_ACCOUNTABLE_UNIT),
PRECISION = decode(p_hdr_det_factors_rec.PRECISION,FND_API.G_MISS_NUM,
PRECISION,
p_hdr_det_factors_rec.PRECISION),
ESTABLISHMENT_ID = decode(p_hdr_det_factors_rec.ESTABLISHMENT_ID,FND_API.G_MISS_NUM,
ESTABLISHMENT_ID,
p_hdr_det_factors_rec.ESTABLISHMENT_ID),
RECEIVABLES_TRX_TYPE_ID = decode(p_hdr_det_factors_rec.RECEIVABLES_TRX_TYPE_ID,FND_API.G_MISS_NUM,
RECEIVABLES_TRX_TYPE_ID,
p_hdr_det_factors_rec.RECEIVABLES_TRX_TYPE_ID),
RELATED_DOC_APPLICATION_ID = decode(p_hdr_det_factors_rec.RELATED_DOC_APPLICATION_ID,FND_API.G_MISS_NUM,
RELATED_DOC_APPLICATION_ID,
p_hdr_det_factors_rec.RELATED_DOC_APPLICATION_ID),
RELATED_DOC_ENTITY_CODE = decode(p_hdr_det_factors_rec.RELATED_DOC_ENTITY_CODE,FND_API.G_MISS_CHAR,
RELATED_DOC_ENTITY_CODE,
p_hdr_det_factors_rec.RELATED_DOC_ENTITY_CODE),
RELATED_DOC_EVENT_CLASS_CODE = decode(p_hdr_det_factors_rec.RELATED_DOC_EVENT_CLASS_CODE,FND_API.G_MISS_CHAR,
RELATED_DOC_EVENT_CLASS_CODE,
p_hdr_det_factors_rec.RELATED_DOC_EVENT_CLASS_CODE),
RELATED_DOC_TRX_ID = decode(p_hdr_det_factors_rec.RELATED_DOC_TRX_ID,FND_API.G_MISS_NUM,
RELATED_DOC_TRX_ID,
p_hdr_det_factors_rec.RELATED_DOC_TRX_ID),
RELATED_DOC_NUMBER = decode(p_hdr_det_factors_rec.RELATED_DOC_NUMBER,FND_API.G_MISS_CHAR,
RELATED_DOC_NUMBER,
p_hdr_det_factors_rec.RELATED_DOC_NUMBER),
RELATED_DOC_DATE = decode(p_hdr_det_factors_rec.RELATED_DOC_DATE,FND_API.G_MISS_DATE,
RELATED_DOC_DATE,
p_hdr_det_factors_rec.RELATED_DOC_DATE),
DEFAULT_TAXATION_COUNTRY = decode(p_hdr_det_factors_rec.DEFAULT_TAXATION_COUNTRY,FND_API.G_MISS_CHAR,
DEFAULT_TAXATION_COUNTRY,
p_hdr_det_factors_rec.DEFAULT_TAXATION_COUNTRY),
TRX_NUMBER = decode(p_hdr_det_factors_rec.TRX_NUMBER,FND_API.G_MISS_CHAR,
TRX_NUMBER,
p_hdr_det_factors_rec.TRX_NUMBER),
TRX_DESCRIPTION = decode(p_hdr_det_factors_rec.TRX_DESCRIPTION,FND_API.G_MISS_CHAR,
TRX_DESCRIPTION,
p_hdr_det_factors_rec.TRX_DESCRIPTION),
TRX_COMMUNICATED_DATE = decode(p_hdr_det_factors_rec.TRX_COMMUNICATED_DATE,FND_API.G_MISS_DATE,
TRX_COMMUNICATED_DATE,
p_hdr_det_factors_rec.TRX_COMMUNICATED_DATE),
BATCH_SOURCE_ID = decode(p_hdr_det_factors_rec.BATCH_SOURCE_ID,FND_API.G_MISS_NUM,
BATCH_SOURCE_ID,
p_hdr_det_factors_rec.BATCH_SOURCE_ID),
BATCH_SOURCE_NAME = decode(p_hdr_det_factors_rec.BATCH_SOURCE_NAME,FND_API.G_MISS_CHAR,
BATCH_SOURCE_NAME,
p_hdr_det_factors_rec.BATCH_SOURCE_NAME),
DOC_SEQ_ID = decode(p_hdr_det_factors_rec.DOC_SEQ_ID,FND_API.G_MISS_NUM,
DOC_SEQ_ID,
p_hdr_det_factors_rec.DOC_SEQ_ID),
DOC_SEQ_NAME = decode(p_hdr_det_factors_rec.DOC_SEQ_NAME,FND_API.G_MISS_CHAR,
DOC_SEQ_NAME,
p_hdr_det_factors_rec.DOC_SEQ_NAME),
DOC_SEQ_VALUE = decode(p_hdr_det_factors_rec.DOC_SEQ_VALUE,FND_API.G_MISS_CHAR,
DOC_SEQ_VALUE,
p_hdr_det_factors_rec.DOC_SEQ_VALUE),
TRX_DUE_DATE = decode(p_hdr_det_factors_rec.TRX_DUE_DATE,FND_API.G_MISS_DATE,
TRX_DUE_DATE,
p_hdr_det_factors_rec.TRX_DUE_DATE),
TRX_TYPE_DESCRIPTION = decode(p_hdr_det_factors_rec.TRX_TYPE_DESCRIPTION,FND_API.G_MISS_CHAR,
TRX_TYPE_DESCRIPTION,
p_hdr_det_factors_rec.TRX_TYPE_DESCRIPTION),
DOCUMENT_SUB_TYPE = decode(p_hdr_det_factors_rec.DOCUMENT_SUB_TYPE,FND_API.G_MISS_CHAR,
DOCUMENT_SUB_TYPE,
p_hdr_det_factors_rec.DOCUMENT_SUB_TYPE),
SUPPLIER_TAX_INVOICE_NUMBER = decode(p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,
SUPPLIER_TAX_INVOICE_NUMBER,
p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_NUMBER),
SUPPLIER_TAX_INVOICE_DATE = decode(p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_DATE,FND_API.G_MISS_DATE,
SUPPLIER_TAX_INVOICE_DATE,
p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_DATE),
SUPPLIER_EXCHANGE_RATE = decode(p_hdr_det_factors_rec.SUPPLIER_EXCHANGE_RATE,FND_API.G_MISS_NUM,
SUPPLIER_EXCHANGE_RATE,
p_hdr_det_factors_rec.SUPPLIER_EXCHANGE_RATE),
TAX_INVOICE_DATE = decode(p_hdr_det_factors_rec.TAX_INVOICE_DATE,FND_API.G_MISS_DATE,
TAX_INVOICE_DATE,
p_hdr_det_factors_rec.TAX_INVOICE_DATE),
TAX_INVOICE_NUMBER = decode(p_hdr_det_factors_rec.TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,
TAX_INVOICE_NUMBER,
p_hdr_det_factors_rec.TAX_INVOICE_NUMBER),
CTRL_TOTAL_HDR_TX_AMT = decode(p_hdr_det_factors_rec.CTRL_TOTAL_HDR_TX_AMT,FND_API.G_MISS_NUM,
ctrl_total_hdr_tx_amt,
p_hdr_det_factors_rec.CTRL_TOTAL_HDR_TX_AMT),
FIRST_PTY_ORG_ID = l_event_class_rec.first_pty_org_id,
TAX_EVENT_CLASS_CODE = l_event_class_rec.TAX_EVENT_CLASS_CODE,
TAX_EVENT_TYPE_CODE = l_event_class_rec.TAX_EVENT_TYPE_CODE,
DOC_EVENT_STATUS = l_event_class_rec.DOC_STATUS_CODE,
TRX_BATCH_ID = decode(p_hdr_det_factors_rec.TRX_BATCH_ID,FND_API.G_MISS_NUM,
TRX_BATCH_ID,
p_hdr_det_factors_rec.TRX_BATCH_ID),
APPLIED_TO_TRX_NUMBER = decode(p_hdr_det_factors_rec.APPLIED_TO_TRX_NUMBER,FND_API.G_MISS_CHAR,
APPLIED_TO_TRX_NUMBER,
p_hdr_det_factors_rec.APPLIED_TO_TRX_NUMBER),
APPLICATION_DOC_STATUS = decode(p_hdr_det_factors_rec.APPLICATION_DOC_STATUS,FND_API.G_MISS_CHAR,
APPLICATION_DOC_STATUS,
p_hdr_det_factors_rec.APPLICATION_DOC_STATUS),
RDNG_SHIP_TO_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_SHIP_TO_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_TO_PTY_TX_PROF_ID,
l_rdng_ship_to_ptp_id),
RDNG_SHIP_FROM_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_SHIP_FROM_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_FROM_PTY_TX_PROF_ID,
l_rdng_ship_from_ptp_id),
RDNG_BILL_TO_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_BILL_TO_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_BILL_TO_PTY_TX_PROF_ID,
l_rdng_bill_to_ptp_id),
RDNG_BILL_FROM_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_BILL_FROM_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_BILL_FROM_PTY_TX_PROF_ID,
l_rdng_bill_from_ptp_id),
RDNG_SHIP_TO_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_SHIP_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_TO_PTY_TX_P_ST_ID,
l_rdng_ship_to_ptp_st_id),
RDNG_SHIP_FROM_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_SHIP_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
l_rdng_ship_from_ptp_st_id),
RDNG_BILL_TO_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_BILL_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_BILL_TO_PTY_TX_P_ST_ID,
l_rdng_bill_to_ptp_st_id),
RDNG_BILL_FROM_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_BILL_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_BILL_FROM_PTY_TX_P_ST_ID,
l_rdng_bill_from_ptp_st_id),
PORT_OF_ENTRY_CODE = decode(p_hdr_det_factors_rec.PORT_OF_ENTRY_CODE,FND_API.G_MISS_CHAR,
PORT_OF_ENTRY_CODE,
p_hdr_det_factors_rec.PORT_OF_ENTRY_CODE),
TAX_REPORTING_FLAG = decode(p_hdr_det_factors_rec.TAX_REPORTING_FLAG,FND_API.G_MISS_CHAR,
TAX_REPORTING_FLAG,
p_hdr_det_factors_rec.TAX_REPORTING_FLAG),
PROVNL_TAX_DETERMINATION_DATE = decode(p_hdr_det_factors_rec.PROVNL_TAX_DETERMINATION_DATE,FND_API.G_MISS_DATE,
PROVNL_TAX_DETERMINATION_DATE,
p_hdr_det_factors_rec.PROVNL_TAX_DETERMINATION_DATE),
SHIP_THIRD_PTY_ACCT_ID = decode(p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_ID,FND_API.G_MISS_NUM,
SHIP_THIRD_PTY_ACCT_ID,
p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_ID),
BILL_THIRD_PTY_ACCT_ID = decode(p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_ID,FND_API.G_MISS_NUM,
BILL_THIRD_PTY_ACCT_ID,
p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_ID),
SHIP_THIRD_PTY_ACCT_SITE_ID = decode(p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_SITE_ID,FND_API.G_MISS_NUM,
SHIP_THIRD_PTY_ACCT_SITE_ID,
p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_SITE_ID),
BILL_THIRD_PTY_ACCT_SITE_ID = decode(p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_SITE_ID,FND_API.G_MISS_NUM,
BILL_THIRD_PTY_ACCT_SITE_ID,
p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_SITE_ID),
SHIP_TO_CUST_ACCT_SITE_USE_ID = decode(p_hdr_det_factors_rec.SHIP_TO_CUST_ACCT_SITE_USE_ID,FND_API.G_MISS_NUM,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
p_hdr_det_factors_rec.SHIP_TO_CUST_ACCT_SITE_USE_ID),
BILL_TO_CUST_ACCT_SITE_USE_ID = decode(p_hdr_det_factors_rec.BILL_TO_CUST_ACCT_SITE_USE_ID,FND_API.G_MISS_NUM,
BILL_TO_CUST_ACCT_SITE_USE_ID,
p_hdr_det_factors_rec.BILL_TO_CUST_ACCT_SITE_USE_ID),
SHIP_TO_LOCATION_ID = decode(p_hdr_det_factors_rec.SHIP_TO_LOCATION_ID,FND_API.G_MISS_NUM,
SHIP_TO_LOCATION_ID,
p_hdr_det_factors_rec.SHIP_TO_LOCATION_ID),
SHIP_FROM_LOCATION_ID = decode(p_hdr_det_factors_rec.SHIP_FROM_LOCATION_ID,FND_API.G_MISS_NUM,
SHIP_FROM_LOCATION_ID,
p_hdr_det_factors_rec.SHIP_FROM_LOCATION_ID),
BILL_TO_LOCATION_ID = decode(p_hdr_det_factors_rec.BILL_TO_LOCATION_ID,FND_API.G_MISS_NUM,
BILL_TO_LOCATION_ID,
p_hdr_det_factors_rec.BILL_TO_LOCATION_ID),
BILL_FROM_LOCATION_ID = decode(p_hdr_det_factors_rec.BILL_FROM_LOCATION_ID,FND_API.G_MISS_NUM,
BILL_FROM_LOCATION_ID,
p_hdr_det_factors_rec.BILL_FROM_LOCATION_ID),
POA_LOCATION_ID = decode(p_hdr_det_factors_rec.POA_LOCATION_ID,FND_API.G_MISS_NUM,
POA_LOCATION_ID,
p_hdr_det_factors_rec.POA_LOCATION_ID),
POO_LOCATION_ID = decode(p_hdr_det_factors_rec.POO_LOCATION_ID,FND_API.G_MISS_NUM,
POO_LOCATION_ID,
p_hdr_det_factors_rec.POO_LOCATION_ID),
PAYING_LOCATION_ID = decode(p_hdr_det_factors_rec.PAYING_LOCATION_ID,FND_API.G_MISS_NUM,
PAYING_LOCATION_ID,
p_hdr_det_factors_rec.PAYING_LOCATION_ID),
OWN_HQ_LOCATION_ID = decode(p_hdr_det_factors_rec.OWN_HQ_LOCATION_ID,FND_API.G_MISS_NUM,
OWN_HQ_LOCATION_ID,
p_hdr_det_factors_rec.OWN_HQ_LOCATION_ID),
TRADING_HQ_LOCATION_ID = decode(p_hdr_det_factors_rec.TRADING_HQ_LOCATION_ID,FND_API.G_MISS_NUM,
TRADING_HQ_LOCATION_ID,
p_hdr_det_factors_rec.TRADING_HQ_LOCATION_ID),
POC_LOCATION_ID = decode(p_hdr_det_factors_rec.POC_LOCATION_ID,FND_API.G_MISS_NUM,
POC_LOCATION_ID,
p_hdr_det_factors_rec.POC_LOCATION_ID),
POI_LOCATION_ID = decode(p_hdr_det_factors_rec.POI_LOCATION_ID,FND_API.G_MISS_NUM,
POI_LOCATION_ID,
p_hdr_det_factors_rec.POI_LOCATION_ID),
POD_LOCATION_ID = decode(p_hdr_det_factors_rec.POD_LOCATION_ID,FND_API.G_MISS_NUM,
POD_LOCATION_ID,
p_hdr_det_factors_rec.POD_LOCATION_ID),
TITLE_TRANSFER_LOCATION_ID = decode(p_hdr_det_factors_rec.TITLE_TRANSFER_LOCATION_ID,FND_API.G_MISS_NUM,
TITLE_TRANSFER_LOCATION_ID,
p_hdr_det_factors_rec.TITLE_TRANSFER_LOCATION_ID),
SHIP_TO_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_TO_PARTY_ID,FND_API.G_MISS_NUM,
SHIP_TO_PARTY_TAX_PROF_ID,
l_ship_to_ptp_id),
SHIP_FROM_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_FROM_PARTY_ID,FND_API.G_MISS_NUM,
SHIP_FROM_PARTY_TAX_PROF_ID,
l_ship_from_ptp_id),
POA_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POA_PARTY_ID,FND_API.G_MISS_NUM,
POA_PARTY_TAX_PROF_ID,
l_poa_ptp_id),
POO_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POO_PARTY_ID,FND_API.G_MISS_NUM,
POO_PARTY_TAX_PROF_ID,
l_poo_ptp_id),
PAYING_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.PAYING_PARTY_ID,FND_API.G_MISS_NUM,
PAYING_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.PAYING_PARTY_TAX_PROF_ID),
OWN_HQ_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.OWN_HQ_PARTY_ID,FND_API.G_MISS_NUM,
OWN_HQ_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.OWN_HQ_PARTY_TAX_PROF_ID),
TRADING_HQ_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.TRADING_HQ_PARTY_ID,FND_API.G_MISS_NUM,
TRADING_HQ_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.TRADING_HQ_PARTY_TAX_PROF_ID),
POI_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POI_PARTY_ID,FND_API.G_MISS_NUM,
POI_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.POI_PARTY_TAX_PROF_ID),
POD_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POD_PARTY_ID,FND_API.G_MISS_NUM,
POD_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.POD_PARTY_TAX_PROF_ID),
BILL_TO_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_TO_PARTY_ID,FND_API.G_MISS_NUM,
BILL_TO_PARTY_TAX_PROF_ID,
l_bill_to_ptp_id),
BILL_FROM_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_FROM_PARTY_ID,FND_API.G_MISS_NUM,
BILL_FROM_PARTY_TAX_PROF_ID,
l_bill_from_ptp_id),
TITLE_TRANS_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.TITLE_TRANSFER_PARTY_ID,FND_API.G_MISS_NUM,
TITLE_TRANS_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.TITLE_TRANS_PARTY_TAX_PROF_ID),
SHIP_TO_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
SHIP_TO_SITE_TAX_PROF_ID,
l_ship_to_ptp_site_id),
SHIP_FROM_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
SHIP_FROM_SITE_TAX_PROF_ID,
l_ship_from_ptp_site_id),
BILL_TO_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
BILL_TO_SITE_TAX_PROF_ID,
l_bill_to_ptp_site_id),
BILL_FROM_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
BILL_FROM_SITE_TAX_PROF_ID,
l_bill_from_ptp_site_id),
POA_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POA_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POA_SITE_TAX_PROF_ID,
l_poa_ptp_site_id),
POO_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POO_SITE_TAX_PROF_ID,
l_poo_ptp_site_id),
PAYING_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.PAYING_PARTY_SITE_ID,FND_API.G_MISS_NUM,
PAYING_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.PAYING_SITE_TAX_PROF_ID),
OWN_HQ_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.OWN_HQ_PARTY_SITE_ID,FND_API.G_MISS_NUM,
OWN_HQ_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.OWN_HQ_SITE_TAX_PROF_ID),
POI_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POI_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POI_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.POI_SITE_TAX_PROF_ID),
POD_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POD_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POD_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.POD_SITE_TAX_PROF_ID),
TITLE_TRANS_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.TITLE_TRANSFER_PARTY_SITE_ID,FND_API.G_MISS_NUM,
TITLE_TRANS_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.TITLE_TRANS_SITE_TAX_PROF_ID),
HQ_ESTB_PARTY_TAX_PROF_ID = l_hq_estb_ptp_id,
LINE_LEVEL_ACTION = decode(LINE_LEVEL_ACTION, 'CREATE','UPDATE',
'SYNCHRONIZE','UPDATE',
'COPY_AND_CREATE','UPDATE',
LINE_LEVEL_ACTION),
TAX_PROCESSING_COMPLETED_FLAG = 'N',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id
WHERE APPLICATION_ID = p_hdr_det_factors_rec.APPLICATION_ID
AND ENTITY_CODE = p_hdr_det_factors_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_hdr_det_factors_rec.EVENT_CLASS_CODE
AND TRX_ID = p_hdr_det_factors_rec.TRX_ID;
|Update the headers only in zx_line_det_factors |
+----------------------------------------------*/
UPDATE ZX_LINES_DET_FACTORS SET
APPLICATION_ID = p_hdr_det_factors_rec.APPLICATION_ID,
ENTITY_CODE = p_hdr_det_factors_rec.ENTITY_CODE,
EVENT_CLASS_CODE = p_hdr_det_factors_rec.EVENT_CLASS_CODE,
EVENT_TYPE_CODE = p_hdr_det_factors_rec.EVENT_TYPE_CODE,
INTERNAL_ORGANIZATION_ID = p_hdr_det_factors_rec.INTERNAL_ORGANIZATION_ID,
LEGAL_ENTITY_ID = p_hdr_det_factors_rec.LEGAL_ENTITY_ID,
TRX_ID = p_hdr_det_factors_rec.TRX_ID,
TRX_DOC_REVISION = decode(p_hdr_det_factors_rec.TRX_DOC_REVISION,FND_API.G_MISS_CHAR,
TRX_DOC_REVISION,
p_hdr_det_factors_rec.TRX_DOC_REVISION),
TRX_DATE = decode(p_hdr_det_factors_rec.TRX_DATE,FND_API.G_MISS_DATE,
TRX_DATE,
p_hdr_det_factors_rec.TRX_DATE),
LEDGER_ID = decode(p_hdr_det_factors_rec.LEDGER_ID,FND_API.G_MISS_NUM,
LEDGER_ID,
p_hdr_det_factors_rec.LEDGER_ID),
INTERNAL_ORG_LOCATION_ID = decode(p_hdr_det_factors_rec.INTERNAL_ORG_LOCATION_ID,FND_API.G_MISS_NUM,
INTERNAL_ORG_LOCATION_ID,
p_hdr_det_factors_rec.INTERNAL_ORG_LOCATION_ID),
TRX_CURRENCY_CODE = decode(p_hdr_det_factors_rec.TRX_CURRENCY_CODE,FND_API.G_MISS_CHAR,
TRX_CURRENCY_CODE,
p_hdr_det_factors_rec.TRX_CURRENCY_CODE),
CURRENCY_CONVERSION_TYPE = decode(p_hdr_det_factors_rec.CURRENCY_CONVERSION_TYPE,FND_API.G_MISS_CHAR,
CURRENCY_CONVERSION_TYPE,
p_hdr_det_factors_rec.CURRENCY_CONVERSION_TYPE),
CURRENCY_CONVERSION_RATE = decode(p_hdr_det_factors_rec.CURRENCY_CONVERSION_RATE,FND_API.G_MISS_NUM,
CURRENCY_CONVERSION_RATE,
p_hdr_det_factors_rec.CURRENCY_CONVERSION_RATE),
CURRENCY_CONVERSION_DATE = decode(p_hdr_det_factors_rec.CURRENCY_CONVERSION_DATE,FND_API.G_MISS_DATE,
CURRENCY_CONVERSION_DATE,
p_hdr_det_factors_rec.CURRENCY_CONVERSION_DATE),
MINIMUM_ACCOUNTABLE_UNIT = decode(p_hdr_det_factors_rec.MINIMUM_ACCOUNTABLE_UNIT,FND_API.G_MISS_NUM,
MINIMUM_ACCOUNTABLE_UNIT,
p_hdr_det_factors_rec.MINIMUM_ACCOUNTABLE_UNIT),
PRECISION = decode(p_hdr_det_factors_rec.PRECISION,FND_API.G_MISS_NUM,
PRECISION,
p_hdr_det_factors_rec.PRECISION),
ESTABLISHMENT_ID = decode(p_hdr_det_factors_rec.ESTABLISHMENT_ID,FND_API.G_MISS_NUM,
ESTABLISHMENT_ID,
p_hdr_det_factors_rec.ESTABLISHMENT_ID),
RECEIVABLES_TRX_TYPE_ID = decode(p_hdr_det_factors_rec.RECEIVABLES_TRX_TYPE_ID,FND_API.G_MISS_NUM,
RECEIVABLES_TRX_TYPE_ID,
p_hdr_det_factors_rec.RECEIVABLES_TRX_TYPE_ID),
RELATED_DOC_APPLICATION_ID = decode(p_hdr_det_factors_rec.RELATED_DOC_APPLICATION_ID,FND_API.G_MISS_NUM,
RELATED_DOC_APPLICATION_ID,
p_hdr_det_factors_rec.RELATED_DOC_APPLICATION_ID),
RELATED_DOC_ENTITY_CODE = decode(p_hdr_det_factors_rec.RELATED_DOC_ENTITY_CODE,FND_API.G_MISS_CHAR,
RELATED_DOC_ENTITY_CODE,
p_hdr_det_factors_rec.RELATED_DOC_ENTITY_CODE),
RELATED_DOC_EVENT_CLASS_CODE = decode(p_hdr_det_factors_rec.RELATED_DOC_EVENT_CLASS_CODE,FND_API.G_MISS_CHAR,
RELATED_DOC_EVENT_CLASS_CODE,
p_hdr_det_factors_rec.RELATED_DOC_EVENT_CLASS_CODE),
RELATED_DOC_TRX_ID = decode(p_hdr_det_factors_rec.RELATED_DOC_TRX_ID,FND_API.G_MISS_NUM,
RELATED_DOC_TRX_ID,
p_hdr_det_factors_rec.RELATED_DOC_TRX_ID),
RELATED_DOC_NUMBER = decode(p_hdr_det_factors_rec.RELATED_DOC_NUMBER,FND_API.G_MISS_CHAR,
RELATED_DOC_NUMBER,
p_hdr_det_factors_rec.RELATED_DOC_NUMBER),
RELATED_DOC_DATE = decode(p_hdr_det_factors_rec.RELATED_DOC_DATE,FND_API.G_MISS_DATE,
RELATED_DOC_DATE,
p_hdr_det_factors_rec.RELATED_DOC_DATE),
DEFAULT_TAXATION_COUNTRY = decode(p_hdr_det_factors_rec.DEFAULT_TAXATION_COUNTRY,FND_API.G_MISS_CHAR,
DEFAULT_TAXATION_COUNTRY,
p_hdr_det_factors_rec.DEFAULT_TAXATION_COUNTRY),
TRX_NUMBER = decode(p_hdr_det_factors_rec.TRX_NUMBER,FND_API.G_MISS_CHAR,
TRX_NUMBER,
p_hdr_det_factors_rec.TRX_NUMBER),
TRX_DESCRIPTION = decode(p_hdr_det_factors_rec.TRX_DESCRIPTION,FND_API.G_MISS_CHAR,
TRX_DESCRIPTION,
p_hdr_det_factors_rec.TRX_DESCRIPTION),
TRX_COMMUNICATED_DATE = decode(p_hdr_det_factors_rec.TRX_COMMUNICATED_DATE,FND_API.G_MISS_DATE,
TRX_COMMUNICATED_DATE,
p_hdr_det_factors_rec.TRX_COMMUNICATED_DATE),
BATCH_SOURCE_ID = decode(p_hdr_det_factors_rec.BATCH_SOURCE_ID,FND_API.G_MISS_NUM,
BATCH_SOURCE_ID,
p_hdr_det_factors_rec.BATCH_SOURCE_ID),
BATCH_SOURCE_NAME = decode(p_hdr_det_factors_rec.BATCH_SOURCE_NAME,FND_API.G_MISS_CHAR,
BATCH_SOURCE_NAME,
p_hdr_det_factors_rec.BATCH_SOURCE_NAME),
DOC_SEQ_ID = decode(p_hdr_det_factors_rec.DOC_SEQ_ID,FND_API.G_MISS_NUM,
DOC_SEQ_ID,
p_hdr_det_factors_rec.DOC_SEQ_ID),
DOC_SEQ_NAME = decode(p_hdr_det_factors_rec.DOC_SEQ_NAME,FND_API.G_MISS_CHAR,
DOC_SEQ_NAME,
p_hdr_det_factors_rec.DOC_SEQ_NAME),
DOC_SEQ_VALUE = decode(p_hdr_det_factors_rec.DOC_SEQ_VALUE,FND_API.G_MISS_CHAR,
DOC_SEQ_VALUE,
p_hdr_det_factors_rec.DOC_SEQ_VALUE),
TRX_DUE_DATE = decode(p_hdr_det_factors_rec.TRX_DUE_DATE,FND_API.G_MISS_DATE,
TRX_DUE_DATE,
p_hdr_det_factors_rec.TRX_DUE_DATE),
TRX_TYPE_DESCRIPTION = decode(p_hdr_det_factors_rec.TRX_TYPE_DESCRIPTION,FND_API.G_MISS_CHAR,
TRX_TYPE_DESCRIPTION,
p_hdr_det_factors_rec.TRX_TYPE_DESCRIPTION),
DOCUMENT_SUB_TYPE = decode(p_hdr_det_factors_rec.DOCUMENT_SUB_TYPE,FND_API.G_MISS_CHAR,
DOCUMENT_SUB_TYPE,
p_hdr_det_factors_rec.DOCUMENT_SUB_TYPE),
SUPPLIER_TAX_INVOICE_NUMBER = decode(p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,
SUPPLIER_TAX_INVOICE_NUMBER,
p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_NUMBER),
SUPPLIER_TAX_INVOICE_DATE = decode(p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_DATE,FND_API.G_MISS_DATE,
SUPPLIER_TAX_INVOICE_DATE,
p_hdr_det_factors_rec.SUPPLIER_TAX_INVOICE_DATE),
SUPPLIER_EXCHANGE_RATE = decode(p_hdr_det_factors_rec.SUPPLIER_EXCHANGE_RATE,FND_API.G_MISS_NUM,
SUPPLIER_EXCHANGE_RATE,
p_hdr_det_factors_rec.SUPPLIER_EXCHANGE_RATE),
TAX_INVOICE_DATE = decode(p_hdr_det_factors_rec.TAX_INVOICE_DATE,FND_API.G_MISS_DATE,
TAX_INVOICE_DATE,
p_hdr_det_factors_rec.TAX_INVOICE_DATE),
TAX_INVOICE_NUMBER = decode(p_hdr_det_factors_rec.TAX_INVOICE_NUMBER,FND_API.G_MISS_CHAR,
TAX_INVOICE_NUMBER,
p_hdr_det_factors_rec.TAX_INVOICE_NUMBER),
CTRL_TOTAL_HDR_TX_AMT = decode(p_hdr_det_factors_rec.CTRL_TOTAL_HDR_TX_AMT,FND_API.G_MISS_NUM,
ctrl_total_hdr_tx_amt,
p_hdr_det_factors_rec.CTRL_TOTAL_HDR_TX_AMT),
FIRST_PTY_ORG_ID = l_event_class_rec.first_pty_org_id,
TAX_EVENT_CLASS_CODE = l_event_class_rec.TAX_EVENT_CLASS_CODE,
TAX_EVENT_TYPE_CODE = l_event_class_rec.TAX_EVENT_TYPE_CODE,
DOC_EVENT_STATUS = l_event_class_rec.DOC_STATUS_CODE,
TRX_BATCH_ID = decode(p_hdr_det_factors_rec.TRX_BATCH_ID,FND_API.G_MISS_NUM,
TRX_BATCH_ID,
p_hdr_det_factors_rec.TRX_BATCH_ID),
APPLIED_TO_TRX_NUMBER = decode(p_hdr_det_factors_rec.APPLIED_TO_TRX_NUMBER,FND_API.G_MISS_CHAR,
APPLIED_TO_TRX_NUMBER,
p_hdr_det_factors_rec.APPLIED_TO_TRX_NUMBER),
APPLICATION_DOC_STATUS = decode(p_hdr_det_factors_rec.APPLICATION_DOC_STATUS,FND_API.G_MISS_CHAR,
APPLICATION_DOC_STATUS,
p_hdr_det_factors_rec.APPLICATION_DOC_STATUS),
RDNG_SHIP_TO_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_SHIP_TO_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_TO_PTY_TX_PROF_ID,
l_rdng_ship_to_ptp_id),
RDNG_SHIP_FROM_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_SHIP_FROM_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_FROM_PTY_TX_PROF_ID,
l_rdng_ship_from_ptp_id),
RDNG_BILL_TO_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_BILL_TO_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_BILL_TO_PTY_TX_PROF_ID,
l_rdng_bill_to_ptp_id),
RDNG_BILL_FROM_PTY_TX_PROF_ID = decode(p_hdr_det_factors_rec.ROUNDING_BILL_FROM_PARTY_ID,FND_API.G_MISS_NUM,
RDNG_BILL_FROM_PTY_TX_PROF_ID,
l_rdng_bill_from_ptp_id),
RDNG_SHIP_TO_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_SHIP_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_TO_PTY_TX_P_ST_ID,
l_rdng_ship_to_ptp_st_id),
RDNG_SHIP_FROM_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_SHIP_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
l_rdng_ship_from_ptp_st_id),
RDNG_BILL_TO_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_BILL_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_BILL_TO_PTY_TX_P_ST_ID,
l_rdng_bill_to_ptp_st_id),
RDNG_BILL_FROM_PTY_TX_P_ST_ID = decode(p_hdr_det_factors_rec.RNDG_BILL_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
RDNG_BILL_FROM_PTY_TX_P_ST_ID,
l_rdng_bill_from_ptp_st_id),
PORT_OF_ENTRY_CODE = decode(p_hdr_det_factors_rec.PORT_OF_ENTRY_CODE,FND_API.G_MISS_CHAR,
PORT_OF_ENTRY_CODE,
p_hdr_det_factors_rec.PORT_OF_ENTRY_CODE),
TAX_REPORTING_FLAG = decode(p_hdr_det_factors_rec.TAX_REPORTING_FLAG,FND_API.G_MISS_CHAR,
TAX_REPORTING_FLAG,
p_hdr_det_factors_rec.TAX_REPORTING_FLAG),
PROVNL_TAX_DETERMINATION_DATE = decode(p_hdr_det_factors_rec.PROVNL_TAX_DETERMINATION_DATE,FND_API.G_MISS_DATE,
PROVNL_TAX_DETERMINATION_DATE,
p_hdr_det_factors_rec.PROVNL_TAX_DETERMINATION_DATE),
SHIP_THIRD_PTY_ACCT_ID = decode(p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_ID,FND_API.G_MISS_NUM,
SHIP_THIRD_PTY_ACCT_ID,
p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_ID),
BILL_THIRD_PTY_ACCT_ID = decode(p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_ID,FND_API.G_MISS_NUM,
BILL_THIRD_PTY_ACCT_ID,
p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_ID),
SHIP_THIRD_PTY_ACCT_SITE_ID = decode(p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_SITE_ID,FND_API.G_MISS_NUM,
SHIP_THIRD_PTY_ACCT_SITE_ID,
p_hdr_det_factors_rec.SHIP_THIRD_PTY_ACCT_SITE_ID),
BILL_THIRD_PTY_ACCT_SITE_ID = decode(p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_SITE_ID,FND_API.G_MISS_NUM,
BILL_THIRD_PTY_ACCT_SITE_ID,
p_hdr_det_factors_rec.BILL_THIRD_PTY_ACCT_SITE_ID),
SHIP_TO_CUST_ACCT_SITE_USE_ID = decode(p_hdr_det_factors_rec.SHIP_TO_CUST_ACCT_SITE_USE_ID,FND_API.G_MISS_NUM,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
p_hdr_det_factors_rec.SHIP_TO_CUST_ACCT_SITE_USE_ID),
BILL_TO_CUST_ACCT_SITE_USE_ID = decode(p_hdr_det_factors_rec.BILL_TO_CUST_ACCT_SITE_USE_ID,FND_API.G_MISS_NUM,
BILL_TO_CUST_ACCT_SITE_USE_ID,
p_hdr_det_factors_rec.BILL_TO_CUST_ACCT_SITE_USE_ID),
SHIP_TO_LOCATION_ID = decode(p_hdr_det_factors_rec.SHIP_TO_LOCATION_ID,FND_API.G_MISS_NUM,
SHIP_TO_LOCATION_ID,
p_hdr_det_factors_rec.SHIP_TO_LOCATION_ID),
SHIP_FROM_LOCATION_ID = decode(p_hdr_det_factors_rec.SHIP_FROM_LOCATION_ID,FND_API.G_MISS_NUM,
SHIP_FROM_LOCATION_ID,
p_hdr_det_factors_rec.SHIP_FROM_LOCATION_ID),
BILL_TO_LOCATION_ID = decode(p_hdr_det_factors_rec.BILL_TO_LOCATION_ID,FND_API.G_MISS_NUM,
BILL_TO_LOCATION_ID,
p_hdr_det_factors_rec.BILL_TO_LOCATION_ID),
BILL_FROM_LOCATION_ID = decode(p_hdr_det_factors_rec.BILL_FROM_LOCATION_ID,FND_API.G_MISS_NUM,
BILL_FROM_LOCATION_ID,
p_hdr_det_factors_rec.BILL_FROM_LOCATION_ID),
POA_LOCATION_ID = decode(p_hdr_det_factors_rec.POA_LOCATION_ID,FND_API.G_MISS_NUM,
POA_LOCATION_ID,
p_hdr_det_factors_rec.POA_LOCATION_ID),
POO_LOCATION_ID = decode(p_hdr_det_factors_rec.POO_LOCATION_ID,FND_API.G_MISS_NUM,
POO_LOCATION_ID,
p_hdr_det_factors_rec.POO_LOCATION_ID),
PAYING_LOCATION_ID = decode(p_hdr_det_factors_rec.PAYING_LOCATION_ID,FND_API.G_MISS_NUM,
PAYING_LOCATION_ID,
p_hdr_det_factors_rec.PAYING_LOCATION_ID),
OWN_HQ_LOCATION_ID = decode(p_hdr_det_factors_rec.OWN_HQ_LOCATION_ID,FND_API.G_MISS_NUM,
OWN_HQ_LOCATION_ID,
p_hdr_det_factors_rec.OWN_HQ_LOCATION_ID),
TRADING_HQ_LOCATION_ID = decode(p_hdr_det_factors_rec.TRADING_HQ_LOCATION_ID,FND_API.G_MISS_NUM,
TRADING_HQ_LOCATION_ID,
p_hdr_det_factors_rec.TRADING_HQ_LOCATION_ID),
POC_LOCATION_ID = decode(p_hdr_det_factors_rec.POC_LOCATION_ID,FND_API.G_MISS_NUM,
POC_LOCATION_ID,
p_hdr_det_factors_rec.POC_LOCATION_ID),
POI_LOCATION_ID = decode(p_hdr_det_factors_rec.POI_LOCATION_ID,FND_API.G_MISS_NUM,
POI_LOCATION_ID,
p_hdr_det_factors_rec.POI_LOCATION_ID),
POD_LOCATION_ID = decode(p_hdr_det_factors_rec.POD_LOCATION_ID,FND_API.G_MISS_NUM,
POD_LOCATION_ID,
p_hdr_det_factors_rec.POD_LOCATION_ID),
TITLE_TRANSFER_LOCATION_ID = decode(p_hdr_det_factors_rec.TITLE_TRANSFER_LOCATION_ID,FND_API.G_MISS_NUM,
TITLE_TRANSFER_LOCATION_ID,
p_hdr_det_factors_rec.TITLE_TRANSFER_LOCATION_ID),
SHIP_TO_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_TO_PARTY_ID,FND_API.G_MISS_NUM,
SHIP_TO_PARTY_TAX_PROF_ID,
l_ship_to_ptp_id),
SHIP_FROM_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_FROM_PARTY_ID,FND_API.G_MISS_NUM,
SHIP_FROM_PARTY_TAX_PROF_ID,
l_ship_from_ptp_id),
POA_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POA_PARTY_ID,FND_API.G_MISS_NUM,
POA_PARTY_TAX_PROF_ID,
l_poa_ptp_id),
POO_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POO_PARTY_ID,FND_API.G_MISS_NUM,
POO_PARTY_TAX_PROF_ID,
l_poo_ptp_id),
PAYING_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.PAYING_PARTY_ID,FND_API.G_MISS_NUM,
PAYING_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.PAYING_PARTY_TAX_PROF_ID),
OWN_HQ_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.OWN_HQ_PARTY_ID,FND_API.G_MISS_NUM,
OWN_HQ_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.OWN_HQ_PARTY_TAX_PROF_ID),
TRADING_HQ_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.TRADING_HQ_PARTY_ID,FND_API.G_MISS_NUM,
TRADING_HQ_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.TRADING_HQ_PARTY_TAX_PROF_ID),
POI_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POI_PARTY_ID,FND_API.G_MISS_NUM,
POI_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.POI_PARTY_TAX_PROF_ID),
POD_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POD_PARTY_ID,FND_API.G_MISS_NUM,
POD_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.POD_PARTY_TAX_PROF_ID),
BILL_TO_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_TO_PARTY_ID,FND_API.G_MISS_NUM,
BILL_TO_PARTY_TAX_PROF_ID,
l_bill_to_ptp_id),
BILL_FROM_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_FROM_PARTY_ID,FND_API.G_MISS_NUM,
BILL_FROM_PARTY_TAX_PROF_ID,
l_bill_from_ptp_id),
TITLE_TRANS_PARTY_TAX_PROF_ID = decode(p_hdr_det_factors_rec.TITLE_TRANSFER_PARTY_ID,FND_API.G_MISS_NUM,
TITLE_TRANS_PARTY_TAX_PROF_ID,
p_hdr_det_factors_rec.TITLE_TRANS_PARTY_TAX_PROF_ID),
SHIP_TO_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
SHIP_TO_SITE_TAX_PROF_ID,
l_ship_to_ptp_site_id),
SHIP_FROM_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.SHIP_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
SHIP_FROM_SITE_TAX_PROF_ID,
l_ship_from_ptp_site_id),
BILL_TO_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_TO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
BILL_TO_SITE_TAX_PROF_ID,
l_bill_to_ptp_site_id),
BILL_FROM_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.BILL_FROM_PARTY_SITE_ID,FND_API.G_MISS_NUM,
BILL_FROM_SITE_TAX_PROF_ID,
l_bill_from_ptp_site_id),
POA_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POA_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POA_SITE_TAX_PROF_ID,
l_poa_ptp_site_id),
POO_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POO_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POO_SITE_TAX_PROF_ID,
l_poo_ptp_site_id),
PAYING_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.PAYING_PARTY_SITE_ID,FND_API.G_MISS_NUM,
PAYING_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.PAYING_SITE_TAX_PROF_ID),
OWN_HQ_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.OWN_HQ_PARTY_SITE_ID,FND_API.G_MISS_NUM,
OWN_HQ_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.OWN_HQ_SITE_TAX_PROF_ID),
POI_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POI_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POI_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.POI_SITE_TAX_PROF_ID),
POD_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.POD_PARTY_SITE_ID,FND_API.G_MISS_NUM,
POD_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.POD_SITE_TAX_PROF_ID),
TITLE_TRANS_SITE_TAX_PROF_ID = decode(p_hdr_det_factors_rec.TITLE_TRANSFER_PARTY_SITE_ID,FND_API.G_MISS_NUM,
TITLE_TRANS_SITE_TAX_PROF_ID,
p_hdr_det_factors_rec.TITLE_TRANS_SITE_TAX_PROF_ID),
HQ_ESTB_PARTY_TAX_PROF_ID = l_hq_estb_ptp_id,
LINE_LEVEL_ACTION = decode(LINE_LEVEL_ACTION, 'CREATE','UPDATE',
'SYNCHRONIZE','UPDATE',
LINE_LEVEL_ACTION),
TAX_PROCESSING_COMPLETED_FLAG = 'N',
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id
WHERE APPLICATION_ID = p_hdr_det_factors_rec.APPLICATION_ID
AND ENTITY_CODE = p_hdr_det_factors_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_hdr_det_factors_rec.EVENT_CLASS_CODE
AND TRX_ID = p_hdr_det_factors_rec.TRX_ID;
ROLLBACK TO Update_Det_Factors_Hdr_PVT;
ROLLBACK TO Update_Det_Factors_Hdr_PVT;
ROLLBACK TO Update_Det_Factors_Hdr_PVT;
END update_det_factors_hdr;
| PROCEDURE update_line_det_factors : This procedure should be called by |
| products when updating any of the line attributes on the transaction |
| so that the tax repository is also in sync with the line level updates |
| This line will be flagged to be picked up by the tax calculation process|
* =======================================================================*/
PROCEDURE update_line_det_factors (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LINE_DET_FACTORS';
l_user_updated_flag VARCHAR2(1);
SAVEPOINT Update_Line_Det_Factors_PVT;
/*Lock the line so no updates by another user can happen*/
BEGIN
SELECT event_id,
nvl(user_upd_det_factors_flag,'N')
INTO l_event_class_rec.event_id,
l_user_updated_flag
FROM ZX_LINES_DET_FACTORS
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(1)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(1)
FOR UPDATE NOWAIT; --locks the line
/*Lock the line so no updates by another user can happen*/
SELECT event_id,
nvl(user_upd_det_factors_flag,'N')
INTO l_event_class_rec.event_id,
l_user_updated_flag
FROM ZX_LINES_DET_FACTORS
WHERE application_id = l_event_class_rec.application_id
AND entity_code = l_event_class_rec.entity_code
AND event_class_code = l_event_class_rec.event_class_code
AND trx_id = l_event_class_rec.trx_id
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(1)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(1)
FOR UPDATE NOWAIT; --locks the line
| Validate and Initializate parameters for Inserting |
| into line_det_factors |
+------------------------------------------------------*/
IF ( G_LEVEL_EVENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_EVENT,G_MODULE_NAME||l_api_name,
'Validating Transaction: '||
to_char(l_event_class_rec.trx_id)||
' of Application: '||
to_char(l_event_class_rec.application_id) ||
' and Event Class: '||
l_event_class_rec.event_class_code
);
IF l_user_updated_flag = 'N' THEN
--Call the redefaulting APIs only if all tax determining attributes passed as null
l_call_default_APIs := ZX_SRVC_TYP_PKG.decide_call_redefault_APIs (p_trx_line_index => 1);
IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(1) = 'UPDATE' THEN
ZX_SRVC_TYP_PKG.call_redefaulting_APIs(p_event_class_rec => l_event_class_rec,
p_trx_line_index => 1,
x_return_status => l_return_status
);
|Call to update the lines |
+------------------------------------------*/
ZX_SRVC_TYP_PKG.insupd_line_det_factors(p_event_class_rec => l_event_class_rec,
x_return_status => l_return_status
);
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
ROLLBACK TO Update_Line_Det_Factors_PVT;
ROLLBACK TO Update_Line_Det_Factors_PVT;
ROLLBACK TO Update_Line_Det_Factors_PVT;
END update_line_det_factors;
| PROCEDURE copy_insert_line_det_factors : This procedure will be called |
| by iProcurement to insert all the transaction lines into zx_lines_det_factors|
| after copying the tax determining attributes from the source document |
| information passed in. All lines thus inserted will be flagged to be picked |
| up by the tax calculation process |
* ============================================================================*/
PROCEDURE copy_insert_line_det_factors(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_name CONSTANT VARCHAR2(30) := 'COPY_INSERT_LINE_DET_FACTORS';
SELECT zx_lines_det_factors_s.nextval
INTO l_event_class_rec.event_id
FROM dual;
| Validate and Initializate parameters for Inserting |
| into line_det_factors |
+------------------------------------------------------*/
ZX_VALID_INIT_PARAMS_PKG.insupd_line_det_factors(p_event_class_rec =>l_event_class_rec,
p_trx_line_index => 1,
x_return_status =>l_return_status
);
SELECT
default_taxation_country,
document_sub_type,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
product_fisc_classification,
product_category,
assessable_value,
product_type,
decode(l_event_class_rec.prod_family_grp_code,'P2P',input_tax_classification_code,
'O2C',output_tax_classification_code),
user_upd_det_factors_flag --Bug11694368
INTO
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.default_taxation_country(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.document_sub_type(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_business_category(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_intended_use(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.user_defined_fisc_class(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_fisc_classification(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_category(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.assessable_value(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_type(i),
l_tax_classification_code,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.user_upd_det_factors_flag(i)
FROM ZX_LINES_DET_FACTORS
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_application_id(i)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_entity_code(i)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_event_class_code(i)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_id(i)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_line_id(i)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.source_trx_level_type(i);
|Call to insert the lines |
+------------------------------------------*/
ZX_SRVC_TYP_PKG.insupd_line_det_factors(p_event_class_rec => l_event_class_rec,
x_return_status => l_return_status
);
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
END copy_insert_line_det_factors;
| PROCEDURE delete_tax_line_and_distributions: |
* ======================================================================*/
PROCEDURE del_tax_line_and_distributions(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_transaction_line_rec IN OUT NOCOPY transaction_line_rec_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'DEL_TAX_LINE_AND_DISTRIBUTIONS';
| for JAI PO document and line delete |
+---------------------------------------------*/
-- Start : Added for JAI Trigger Elimination : Bug#16483600 JAI ZX Integration
lb_jai_exists := JAI_TAX_PROCESSING_PKG.CHECK_JAI_EXISTS_IN_ZX(
pn_set_of_books_id => NULL,
pv_entity_code => p_transaction_line_rec.entity_code,
pn_trx_id => p_transaction_line_rec.trx_id
);
| Delete transaction line |
+-----------------------------------------*/
DELETE from ZX_LINES_DET_FACTORS
WHERE application_id = p_transaction_line_rec.application_id
AND entity_code = p_transaction_line_rec.entity_code
AND event_class_code = p_transaction_line_rec.event_class_code
AND trx_id = p_transaction_line_rec.trx_id
AND trx_line_id = p_transaction_line_rec.trx_line_id
AND trx_level_type = p_transaction_line_rec.trx_level_type;
| Delete tax line and distributions |
+-----------------------------------------*/
ZX_TRL_PUB_PKG.delete_tax_lines_and_dists(p_application_id => p_transaction_line_rec.application_id,
p_entity_code => p_transaction_line_rec.entity_code,
p_event_class_code => p_transaction_line_rec.event_class_code,
p_trx_id => p_transaction_line_rec.trx_id,
p_trx_line_id => p_transaction_line_rec.trx_line_id,
p_trx_level_type => p_transaction_line_rec.trx_level_type,
x_return_status => l_return_status
);
':ZX_TRL_PUB_PKG.delete_tax_lines_and_dists returned errors');
| PROCEDURE delete_tax_distributions: |
* ======================================================================*/
PROCEDURE delete_tax_distributions(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_transaction_line_rec IN OUT NOCOPY transaction_line_rec_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'DEL_TAX_DISTRIBUTIONS';
| Delete tax distributions |
+-----------------------------------------*/
IF ( G_LEVEL_EVENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_EVENT,G_MODULE_NAME||l_api_name,
'Call TRL service to delete tax distributions'
);
ZX_TRL_PUB_PKG.delete_tax_dists (p_application_id => p_transaction_line_rec.application_id,
p_entity_code => p_transaction_line_rec.entity_code,
p_event_class_code => p_transaction_line_rec.event_class_code,
p_trx_id => p_transaction_line_rec.trx_id,
p_trx_line_id => p_transaction_line_rec.trx_line_id,
p_trx_level_type => p_transaction_line_rec.trx_level_type,
x_return_status => l_return_status
);
':ZX_TRL_PUB_PKG.delete_tax_dists returned errors');
END delete_tax_distributions;
SELECT INTERNAL_ORGANIZATION_ID,
LEGAL_ENTITY_ID,
LEDGER_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
CTRL_TOTAL_HDR_TX_AMT,
TRX_ID,
TRX_DATE,
RELATED_DOC_DATE,
PROVNL_TAX_DETERMINATION_DATE,
TRX_CURRENCY_CODE,
PRECISION,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
ROUNDING_SHIP_TO_PARTY_ID,
ROUNDING_SHIP_FROM_PARTY_ID,
ROUNDING_BILL_TO_PARTY_ID,
ROUNDING_BILL_FROM_PARTY_ID,
RNDG_SHIP_TO_PARTY_SITE_ID,
RNDG_SHIP_FROM_PARTY_SITE_ID,
RNDG_BILL_TO_PARTY_SITE_ID,
RNDG_BILL_FROM_PARTY_SITE_ID,
QUOTE_FLAG,
ESTABLISHMENT_ID
FROM ZX_TRX_HEADERS_GT;
'Calling routine to delete the global structures ');
ZX_GLOBAL_STRUCTURES_PKG.delete_trx_line_dist_tbl;
SELECT application_id,
entity_code,
event_class_code
INTO l_application_id,
l_entity_code,
l_event_class_code
FROM ZX_PURGE_TRANSACTIONS_GT
WHERE rownum=1;
SELECT summarization_flag,
tax_reporting_flag,
tax_recovery_flag
INTO l_summarization_flag,
l_tax_reporting_flag,
l_tax_recovery_flag
FROM ZX_EVNT_CLS_MAPPINGS
WHERE APPLICATION_ID = 222
AND ENTITY_CODE = 'TRANSACTIONS'
AND EVENT_CLASS_CODE = 'INVOICE';
SELECT summarization_flag,
tax_reporting_flag,
tax_recovery_flag
INTO l_summarization_flag,
l_tax_reporting_flag,
l_tax_recovery_flag
FROM ZX_EVNT_CLS_MAPPINGS
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICE';
SELECT summarization_flag,
tax_reporting_flag,
tax_recovery_flag
INTO l_summarization_flag,
l_tax_reporting_flag,
l_tax_recovery_flag
FROM ZX_EVNT_CLS_MAPPINGS
WHERE APPLICATION_ID = l_application_id
AND ENTITY_CODE = l_entity_code
AND EVENT_CLASS_CODE = l_event_class_code;
DELETE
FROM ZX_LINES tax
WHERE (APPLICATION_ID, ENTITY_CODE,EVENT_CLASS_CODE, TRX_ID)
IN (SELECT /*+ INDEX (ZX_PURGE_TRANSACTIONS_GT ZX_PURGE_TRANSACTIONS_GT_U1)*/
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID
FROM ZX_PURGE_TRANSACTIONS_GT purge);
'Number of rows deleted from ZX_LINES = '|| to_char(l_row_count));
DELETE
FROM ZX_LINES_SUMMARY summ
WHERE (APPLICATION_ID, ENTITY_CODE,EVENT_CLASS_CODE,TRX_ID)
IN (SELECT /*+ INDEX (ZX_PURGE_TRANSACTIONS_GT ZX_PURGE_TRANSACTIONS_GT_U1)*/
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID
FROM ZX_PURGE_TRANSACTIONS_GT purge);
'Number of rows deleted from ZX_LINES_SUMMARY = '||to_char(l_row_count));
DELETE
FROM ZX_REC_NREC_DIST dist
WHERE (APPLICATION_ID, ENTITY_CODE,EVENT_CLASS_CODE,TRX_ID)
IN (SELECT /*+ INDEX (ZX_PURGE_TRANSACTIONS_GT ZX_PURGE_TRANSACTIONS_GT_U1)*/
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID
FROM ZX_PURGE_TRANSACTIONS_GT purge);
'Number of rows deleted from ZX_REC_NREC_DIST = '||to_char(l_row_count));
DELETE
FROM ZX_LINES_DET_FACTORS lines
WHERE (APPLICATION_ID, ENTITY_CODE,EVENT_CLASS_CODE, TRX_ID)
IN (SELECT /*+ INDEX (ZX_PURGE_TRANSACTIONS_GT ZX_PURGE_TRANSACTIONS_GT_U1)*/
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID
FROM ZX_PURGE_TRANSACTIONS_GT purge);
'Number of rows deleted from ZX_LINES_DET_FACTORS = '||to_char(l_row_count));
| PROCEDURE update_posting_flag : This procedure will update the posting_flag|
| for the tax distribution ids that are passed in from product. |
* ============================================================================*/
PROCEDURE update_posting_flag(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tax_dist_id_tbl IN tax_dist_id_tbl_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_POSTING_FLAG';
SAVEPOINT update_posting_flag_PVT;
'Call TRD service to update posting flag'
);
ZX_TRD_SERVICES_PUB_PKG.update_posting_flag(p_tax_dist_id_tbl => p_tax_dist_id_tbl,
x_return_status => l_return_status
);
':ZX_TRD_SERVICES_PUB_PKG.update_posting_flag returned errors');
ROLLBACK TO update_posting_flag_PVT;
ROLLBACK TO update_posting_flag_PVT;
ROLLBACK TO update_posting_flag_PVT;
END update_posting_flag;
update zx_lines
set adjusted_doc_application_id = null,
adjusted_doc_entity_code = null,
adjusted_doc_event_class_code = null,
adjusted_doc_trx_id = null,
adjusted_doc_line_id = null,
adjusted_doc_number = null,
adjusted_doc_date = null,
adjusted_doc_trx_level_type = null,
adjusted_doc_tax_line_id = null
where application_id = 222
and entity_code = 'TRANSACTIONS'
and event_class_code = 'CREDIT_MEMO'
and trx_id = p_trx_id;
'Number of Rows updated in zx_lines: '||SQL%ROWCOUNT);
update zx_lines_det_factors
set adjusted_doc_application_id = null,
adjusted_doc_entity_code = null,
adjusted_doc_event_class_code = null,
adjusted_doc_trx_id = null,
adjusted_doc_line_id = null,
adjusted_doc_number = null,
adjusted_doc_trx_level_type = null,
adjusted_doc_date = null
where application_id = 222
and entity_code = 'TRANSACTIONS'
and event_class_code = 'CREDIT_MEMO'
and trx_id = p_trx_id;
'Number of Rows updated in zx_lines_det_factors: '||SQL%ROWCOUNT);