[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT csi.install_location_id,
-- csi.location_id
csi.install_location_type_code
FROM csi_item_instances csi,
okc_k_items cim,
okc_k_lines_b inst,
okc_k_lines_b ib,
okc_line_styles_b lse
WHERE csi.instance_id = TO_NUMBER(cim.object1_id1)
AND cim.cle_id = ib.id
AND ib.cle_id = inst.id
AND inst.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM2'
AND inst.cle_id = cp_fin_asset_id ;
SELECT ib.id inst_item_id
FROM okc_k_lines_b inst,
okc_k_lines_b ib
WHERE ib.cle_id = inst.id
AND inst.lse_id = 43
AND inst.cle_id = cp_fin_asset_id;
SELECT object_id1_new
FROM okl_txl_itm_insts
WHERE kle_id = cp_kle_id;
SELECT party_site_id
FROM hz_party_site_uses
WHERE party_site_use_id = cp_party_site_use_id;
SELECT hzp.location_id
FROM HZ_PARTY_SITES HZP
WHERE HZP.PARTY_SITE_ID = cp_party_site_id;
SELECT HZP.PARTY_SITE_ID
FROM HZ_PARTY_SITES HZP,
HZ_PARTY_SITE_USES HZU
WHERE HZP.LOCATION_ID = cp_location_id
AND HZP.party_site_id = HZU.PARTY_SITE_ID
AND HZU.SITE_USE_TYPE = 'INSTALL_AT' ;
SELECT /*a.CUST_ACCT_SITE_ID */
b.site_use_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID
AND b.site_use_code = 'SHIP_TO'
AND a.party_site_id = c.party_site_id
AND a.cust_account_id = cp_cust_acct_id
AND a.org_id = MO_GLOBAL.get_current_org_id --NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) sechawla 09-mar-11 11717200
AND c.party_site_id = cp_inst_loc_id
AND c.location_id = cp_loc_id;
SELECT '1'
FROM okl_tax_sources
WHERE khr_id = p_chr_id
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE
AND alc_serialized_yn IN ('Y', 'L')
AND rownum = 1;
| okl_tax_sources_pub.update_tax_sources()
|
| PARAMETERS
| p_source_trx_id -- Source Transaction Identifier
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 11-APR-07 RRAVIKIR Created
|
*=======================================================================*/
PROCEDURE validate_tax_code(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trx_date IN DATE,
p_tbc_code IN VARCHAR2 DEFAULT NULL,
p_ufc_code IN VARCHAR2 DEFAULT NULL,
p_pc_code IN VARCHAR2 DEFAULT NULL,
x_valid_code OUT NOCOPY VARCHAR2) IS
-- Local variables
l_api_name CONSTANT VARCHAR2(30) := 'validate_tax_code';
SELECT '1'
FROM zx_fc_business_categories_v
WHERE classification_code = cp_tbc_code
AND effective_from <= cp_trx_date
AND (effective_to >= cp_trx_date OR effective_to is NULL);
SELECT '1'
FROM zx_fc_user_defined_v
WHERE classification_code = cp_ufc_code
AND effective_from <= cp_trx_date
AND (effective_to >= cp_trx_date OR effective_to is NULL);
SELECT '1'
FROM zx_fc_product_categories_v
WHERE classification_code = cp_pc_code
AND effective_from <= cp_trx_date
AND (effective_to >= cp_trx_date OR effective_to is NULL);
SELECT tax_rate_id,
tax_rate_code,
tax_exemption_id,
tax_rate,
tax_date,
line_amt,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
tax_line_number,
tax_regime_id,
tax_regime_code,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_apportionment_line_number,
legal_entity_id,
trx_number,
trx_date,
tax_jurisdiction_id,
tax_jurisdiction_code,
tax_type_code,
tax_currency_code,
taxable_amt_tax_curr,
trx_currency_code,
minimum_accountable_unit,
precision,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
tax_determine_date,
taxable_amt,
tax_amt
FROM zx_detail_tax_lines_gt
WHERE trx_id = cp_trx_id
AND application_id = G_OKL_APPLICATION_ID
AND trx_level_type = G_TRX_LEVEL_TYPE
AND entity_code = cp_entity_code
AND event_class_code = cp_event_class_code;
SELECT id
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND trx_line_id = cp_trx_line_id;
'INSERT INTO OKL_TAX_TRX_DETAILS');
l_tax_lines_tbl.DELETE;
l_tax_lines_tbl(i).program_update_date := null;
l_tax_lines_tbl(i).last_updated_by := G_USER_ID;
l_tax_lines_tbl(i).last_update_date := SYSDATE;
l_tax_lines_tbl(i).last_update_login := G_LOGIN_ID;
INSERT INTO okl_tax_trx_details VALUES l_tax_lines_tbl(indx);
| okl_tax_sources_pub.update_tax_sources()
|
| PARAMETERS
| p_asset_id -- Asset Identifier
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 26-Jul-07 RRAVIKIR Created
|
*=======================================================================*/
PROCEDURE populate_ser_split_total_tax(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_asset_id IN NUMBER,
p_serialized_asset IN VARCHAR2) IS
-- Local variables
l_api_name CONSTANT VARCHAR2(30) := 'populate_ser_split_total_tax';
SELECT id
FROM okl_tax_sources
WHERE kle_id = cp_kle_id
AND sty_id = cp_sty_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_CONTRACTS_ENTITY_CODE
AND event_class_code = G_BOOKING_EVENT_CLASS_CODE;
SELECT SUM(total_tax)
FROM okl_tax_sources
WHERE kle_id = cp_kle_id
AND sty_id = cp_sty_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_CONTRACTS_ENTITY_CODE
AND event_class_code = G_BOOKING_EVENT_CLASS_CODE;
SELECT SUM(assessable_value)
FROM okl_tax_sources
WHERE kle_id = cp_kle_id
AND sty_id = cp_sty_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_CONTRACTS_ENTITY_CODE
AND event_class_code = G_BOOKING_EVENT_CLASS_CODE;
SELECT DISTINCT sty_id
FROM okl_tax_sources
WHERE kle_id = cp_kle_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_CONTRACTS_ENTITY_CODE
AND event_class_code = G_BOOKING_EVENT_CLASS_CODE;
'Calling okl_tax_sources_pub.update_tax_sources for updating Total Tax for all Lines in Tax Sources');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_tbl => l_txsv_tbl
,x_txsv_tbl => lx_txsv_tbl);
| okl_tax_sources_pub.update_tax_sources()
|
| PARAMETERS
| p_source_trx_id -- Source Transaction Identifier
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 11-APR-07 RRAVIKIR Created
|
*=======================================================================*/
PROCEDURE populate_alc_total_tax(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_asset_id IN NUMBER,
p_request_id IN NUMBER,
p_serialized_asset IN VARCHAR2 DEFAULT NULL) IS
-- Local variables
l_api_name CONSTANT VARCHAR2(30) := 'populate_alc_total_tax';
SELECT id
FROM okl_tax_sources
WHERE trx_id IN (SELECT id
FROM okl_trx_assets
WHERE req_asset_id = cp_request_id)
AND kle_id = cp_kle_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE;
SELECT id
FROM okl_tax_sources
WHERE trx_id IN (SELECT id
FROM okl_trx_assets
WHERE req_asset_id = cp_request_id)
AND kle_id = cp_kle_id
AND sty_id = cp_sty_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_INACTIVE_STATUS
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE;
SELECT SUM(total_tax)
FROM okl_tax_sources
WHERE trx_id IN (SELECT id
FROM okl_trx_assets
WHERE req_asset_id = cp_request_id)
AND kle_id = cp_kle_id
AND sty_id = cp_sty_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_INACTIVE_STATUS
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE;
SELECT SUM(total_tax)
FROM okl_tax_sources
WHERE trx_id IN (SELECT id
FROM okl_trx_assets
WHERE req_asset_id = cp_request_id)
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE;
SELECT DISTINCT sty_id
FROM okl_tax_sources
WHERE trx_id IN (SELECT id
FROM okl_trx_assets
WHERE req_asset_id = cp_request_id)
AND kle_id = cp_kle_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_INACTIVE_STATUS
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE;
'Calling okl_tax_sources_pub.update_tax_sources for updating Total Tax for all Lines in Tax Sources');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_tbl => l_txsv_tbl
,x_txsv_tbl => lx_txsv_tbl);
| PRIVATE PROCEDURE update_tax_sources_total
|
| DESCRIPTION
| This procedure fetches the tax amount from ZX_LINES and updates
| the tax sources
|
| CALLED FROM
| process_contract_reversal_tax()
| process_quote_tax()
| process_asset_loc_tax()
| process_booking_upfront_tax()
| process_quoting_upfront_tax()
| process_tax_determ_override()
| process_tax_details_override()
| process_rebook_upfront_tax()
|
| CALLS PROCEDURES/FUNCTIONS
| okl_tax_sources_pub.update_tax_sources()
| populate_alc_total_tax()
|
| PARAMETERS
| p_source_trx_id -- Source Transaction Identifier
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 11-APR-07 RRAVIKIR Created
|
*=======================================================================*/
PROCEDURE update_tax_sources_total(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_source_trx_id IN NUMBER,
p_source_trx_name IN VARCHAR2 DEFAULT NULL,
p_serialized_asset IN VARCHAR2 DEFAULT NULL) IS
-- Local variables
l_api_name CONSTANT VARCHAR2(30) := 'update_tax_sources_total';
SELECT id, trx_line_id,application_id, event_class_code, entity_code,
trx_level_type
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_INACTIVE_STATUS
AND adjusted_doc_trx_id IS NOT NULL;
SELECT id, trx_line_id,application_id, event_class_code, entity_code,
trx_level_type
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS;
SELECT id, trx_line_id,application_id, event_class_code, entity_code,
trx_level_type
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND tax_call_type_code = G_TAX_SCHEDULE
AND tax_line_status_code = G_ACTIVE_STATUS;
SELECT id, trx_line_id,application_id, event_class_code, entity_code,
trx_level_type
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND tax_call_type_code = G_INVOICE_TAX
AND tax_line_status_code = G_ACTIVE_STATUS;
SELECT SUM(tax_amt) total_tax
FROM zx_lines
WHERE trx_id = cp_trx_id
AND trx_line_id = cp_trx_line_id
AND application_id = cp_application_id
AND event_class_code = cp_event_class_code
AND entity_code = cp_entity_code
AND trx_level_type = cp_trx_level_type
AND nvl(cancel_flag, 'N') <> 'Y';
SELECT SUM(tax_amt) total_tax
FROM zx_detail_tax_lines_gt
WHERE trx_id = cp_trx_id
AND trx_line_id = cp_trx_line_id
AND application_id = cp_application_id
AND event_class_code = cp_event_class_code
AND entity_code = cp_entity_code
AND trx_level_type = cp_trx_level_type
AND nvl(cancel_flag, 'N') <> 'Y';
SELECT id, trx_line_id,application_id, event_class_code, entity_code,
trx_level_type
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_INACTIVE_STATUS;
FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total','Begin(+)');
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total.',
'p_init_msg_list :'||p_init_msg_list);
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total.',
'p_source_trx_id :'||p_source_trx_id);
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total.',
'p_source_trx_name :'||p_source_trx_name);
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total.',
'p_serialized_asset :'||p_serialized_asset);
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total',
'Calling okl_tax_sources_pub.update_tax_sources for updating Total Tax for all Lines in Tax Sources');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_tbl => l_txsv_tbl
,x_txsv_tbl => lx_txsv_tbl);
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total',
'Return Status' || l_return_status);
FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total ','End(-)');
FND_LOG.STRING(G_DEBUG_LEVEL_EXCEPTION,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total ',
'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
FND_LOG.STRING(G_DEBUG_LEVEL_EXCEPTION,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total ',
'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
FND_LOG.STRING(G_DEBUG_LEVEL_EXCEPTION,'OKL_PROCESS_SALES_TAX_PVT.update_tax_sources_total ',
'EXCEPTION :'||sqlerrm);
END update_tax_sources_total;
SELECT id, trx_line_id,application_id, event_class_code, entity_code,
trx_level_type
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS;
'Calling okl_tax_sources_pub.update_tax_sources for making Tax Sources Reportable');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_tbl => l_txsv_tbl
,x_txsv_tbl => lx_txsv_tbl);
SELECT tax_determine_date,
tax_rate_id,
tax_rate_code,
taxable_amt,
tax_exemption_id,
tax_rate,
tax_amt,
tax_date,
line_amt,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code ,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
tax_line_number ,
tax_regime_id ,
tax_regime_code ,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_apportionment_line_number,
legal_entity_id,
trx_number,
trx_date,
tax_jurisdiction_id,
tax_jurisdiction_code,
tax_type_code,
tax_currency_code ,
taxable_amt_tax_curr,
trx_currency_code,
minimum_accountable_unit,
precision,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
FROM okl_tax_trx_details
WHERE trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND application_id = p_application_id
AND trx_level_type = p_trx_level_type
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code;
'INSERT INTO zx_detail_tax_lines_gt');
INSERT INTO zx_detail_tax_lines_gt(
tax_line_id,
tax_determine_date,
tax_rate_id,
tax_rate_code,
taxable_amt,
tax_exemption_id,
tax_rate,
tax_amt,
tax_date,
line_amt,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code ,
trx_id,
trx_line_id,
trx_level_type,
trx_line_number,
tax_line_number ,
tax_regime_id ,
tax_regime_code ,
tax_id,
tax,
tax_status_id,
tax_status_code,
tax_apportionment_line_number,
legal_entity_id,
trx_number,
trx_date,
tax_jurisdiction_id,
tax_jurisdiction_code,
tax_type_code,
tax_currency_code ,
taxable_amt_tax_curr,
trx_currency_code,
minimum_accountable_unit,
precision,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number) VALUES
(
zx_lines_s.nextval,
l_get_tax_data_rec.tax_determine_date,
l_get_tax_data_rec.tax_rate_id,
l_get_tax_data_rec.tax_rate_code,
l_get_tax_data_rec.taxable_amt,
l_get_tax_data_rec.tax_exemption_id,
l_get_tax_data_rec.tax_rate,
l_get_tax_data_rec.tax_amt,
l_get_tax_data_rec.tax_date,
l_get_tax_data_rec.line_amt,
l_get_tax_data_rec.internal_organization_id,
l_get_tax_data_rec.application_id,
l_get_tax_data_rec.entity_code,
l_get_tax_data_rec.event_class_code,
l_get_tax_data_rec.event_type_code ,
l_get_tax_data_rec.trx_id,
l_get_tax_data_rec.trx_line_id,
l_get_tax_data_rec.trx_level_type,
l_get_tax_data_rec.trx_line_number,
l_get_tax_data_rec.tax_line_number ,
l_get_tax_data_rec.tax_regime_id ,
l_get_tax_data_rec.tax_regime_code ,
l_get_tax_data_rec.tax_id,
l_get_tax_data_rec.tax,
l_get_tax_data_rec.tax_status_id,
l_get_tax_data_rec.tax_status_code,
l_get_tax_data_rec.tax_apportionment_line_number,
l_get_tax_data_rec.legal_entity_id,
l_get_tax_data_rec.trx_number,
l_get_tax_data_rec.trx_date,
l_get_tax_data_rec.tax_jurisdiction_id,
l_get_tax_data_rec.tax_jurisdiction_code,
l_get_tax_data_rec.tax_type_code,
l_get_tax_data_rec.tax_currency_code,
l_get_tax_data_rec.taxable_amt_tax_curr,
l_get_tax_data_rec.trx_currency_code,
l_get_tax_data_rec.minimum_accountable_unit,
l_get_tax_data_rec.precision,
l_get_tax_data_rec.currency_conversion_type,
l_get_tax_data_rec.currency_conversion_rate,
l_get_tax_data_rec.currency_conversion_date,
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
1);
| OKL_TAX_SOURCES_PUB.update_tax_sources()
|
|
| PARAMETERS
| p_source_trx_id -- Source transaction ID
| p_source_trx_name -- Source transaction Name
| p_source_table -- Source table
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 11-APR-07 RRAVIKIR Created
|
*=======================================================================*/
PROCEDURE process_contract_reversal_tax(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_source_trx_id IN NUMBER,
p_source_trx_name IN VARCHAR2,
p_source_table IN VARCHAR2) IS
------------- Reverse contract -------------------------
--p_source_trx_id ---> okl_trx_assets.id
--p_source_trx_name ---> 'Reverse'
--p_source_table ---> 'OKL_TRX_CONTRACTS'
--------------------------------------------------
-- Local variables
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
SELECT id, khr_id, kle_id, asset_number, trx_id, trx_line_id, entity_code,
event_class_code, trx_level_type, tax_call_type_code, sty_id,
trx_business_category, tax_line_status_code, sel_id, reported_yn,
line_name, application_id, tax_reporting_flag, default_taxation_country,
product_category, user_defined_fisc_class, line_intended_use,
tax_classification_code, inventory_item_id, bill_to_cust_acct_id,
org_id, legal_entity_id, line_amt, assessable_value, total_tax,
product_type, product_fisc_classification, trx_date, provnl_tax_determination_date,
try_id, ship_to_location_id, ship_to_party_site_id, ship_to_party_id,
bill_to_party_site_id, bill_to_location_id, bill_to_party_id,
ship_to_cust_acct_site_use_id, bill_to_cust_acct_site_use_id,
trx_currency_code, currency_conversion_date, currency_conversion_rate,
currency_conversion_type
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS;
SELECT a.try_id, a.khr_id, a.legal_entity_id, a.date_transaction_occurred
FROM okl_trx_contracts a, okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
l_newtaxsourcesrev_tbl.DELETE;
l_tax_src_tbl.DELETE;
l_prevtaxsourcesbk_tbl.delete;
l_newtaxsourcesrev_tbl(i).program_update_date := null;
l_newtaxsourcesrev_tbl(i).last_updated_by := G_USER_ID;
l_newtaxsourcesrev_tbl(i).last_update_date := SYSDATE;
l_newtaxsourcesrev_tbl(i).last_update_login := G_LOGIN_ID;
UPDATE okl_tax_sources SET tax_line_status_code = G_INACTIVE_STATUS WHERE id = l_tax_src_tbl(i);
INSERT INTO okl_tax_sources VALUES l_newtaxsourcesrev_tbl(indx);
'Calling "update_tax_sources_total" for updating Total Tax for all Lines in Tax Sources');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id);
SELECT
b.site_use_id,
c.party_site_id,
c.party_id,
c.location_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE
a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = 'SHIP_TO'
AND b.site_use_id = cp_site_use_id -- asaanka changed for bug # 6612165
AND a.party_site_id = c.party_site_id
AND a.cust_account_id = p_cust_acct_id;
SELECT
c.party_site_id,
c.party_id,
c.location_id,
b.site_use_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE
a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = 'BILL_TO'
AND b.site_use_id = (SELECT DECODE(okcl.bill_to_site_use_id, null, okch.bill_to_site_use_id, okcl.bill_to_site_use_id)
FROM okc_k_headers_all_b okch, okc_k_lines_v okcl
WHERE okcl.dnz_chr_id = okch.id
AND okcl.id = p_fin_asset_id)
AND a.party_site_id = c.party_site_id
AND a.cust_account_id = p_cust_acct_id;
SELECT
c.party_site_id,
c.party_id,
c.location_id,
b.site_use_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE
a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = 'BILL_TO'
AND b.site_use_id = (SELECT bill_to_site_use_id
FROM okc_k_headers_all_b
WHERE id = p_khr_id)
AND a.party_site_id = c.party_site_id
AND a.cust_account_id = p_cust_acct_id;
SELECT result_code tbc_code,
try_id,
sty_id ,
book_class_code,
tax_country_code,
'Y' select_yn -- All TBCs returned by this cursor are selected initially
FROM okl_tax_attr_definitions
WHERE (try_id = cp_try_id OR try_id IS NULL)
AND RESULT_TYPE_CODE = 'TBC_CODE'
AND NVL(expire_flag, 'N') = 'N';
TYPE tbcdefs_selected_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_tbcdefs_selected_tbl tbcdefs_selected_tbl_type;
selected_count NUMBER;
l_selected_tbc_pos NUMBER;
'j '||j||' l_tbcdefs_tbl(j).select_yn '||l_tbcdefs_tbl(j).select_yn );
IF l_tbcdefs_tbl(j).select_yn = 'Y' THEN
IF i = 1 THEN -- stream type
IF p_sty_id IS NULL THEN
IF l_tbcdefs_tbl(j).sty_id IS NULL THEN
tbc_set_count := tbc_set_count + 1;
l_tbcdefs_tbl(j).select_yn := 'N';
l_tbcdefs_tbl(j).select_yn := 'N';
l_tbcdefs_tbl(j).select_yn := 'N';
l_tbcdefs_tbl(j).select_yn := 'N';
l_tbcdefs_selected_tbl(k) := j;
l_tbcdefs_tbl(j).select_yn := 'N';
l_tbcdefs_selected_tbl(k) := j;
l_tbcdefs_tbl(j).select_yn := 'N';
'l_tbcdefs_tbl(j).select_yn '||l_tbcdefs_tbl(j).select_yn);
l_tbc_code := l_tbcdefs_tbl(l_tbcdefs_selected_tbl(1)).tbc_code;
selected_count := tbc_set_count;
'l_tbcdefs_selected_tbl.count '||l_tbcdefs_selected_tbl.count );
'i '||i||' selected_count '||selected_count );
l_det_null_tbl.DELETE;
IF selected_count > 1 THEN
FOR k IN l_tbcdefs_selected_tbl.FIRST .. l_tbcdefs_selected_tbl.LAST LOOP -- finally selected indexes
-- At this point all the selecetd TBCs will either have Null determinant
-- value or determinant value matching with the calling transaction
IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.get_tbc_code.', 'k '||k);
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).try_id IS NULL THEN
l_det_null_tbl(j) := l_tbcdefs_selected_tbl(k);
l_selected_tbc_pos := l_tbcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_tbcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).sty_id IS NULL THEN
l_det_null_tbl(j) := l_tbcdefs_selected_tbl(k);
l_selected_tbc_pos := l_tbcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_tbcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).book_class_code IS NULL THEN
l_det_null_tbl(j) := l_tbcdefs_selected_tbl(k);
l_selected_tbc_pos := l_tbcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_tbcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_tbcdefs_tbl(l_tbcdefs_selected_tbl(k)).tax_country_code IS NULL THEN
l_det_null_tbl(j) := l_tbcdefs_selected_tbl(k);
l_selected_tbc_pos := l_tbcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_tbcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
ELSIF selected_count = 1 THEN
EXIT;
'selected_count outside 2nd loop '||selected_count);
IF selected_count = 1 THEN
IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.get_tbc_code.',
'l_selected_tbc_pos '||l_selected_tbc_pos);
'l_selected_tbc_pos tbc_code '||l_tbcdefs_tbl(l_selected_tbc_pos).tbc_code);
l_tbc_code := l_tbcdefs_tbl(l_selected_tbc_pos).tbc_code;
ELSIF selected_count > 1 THEN -- still more than one TBCs are selected
l_tbc_code := NULL;
SELECT result_code pc_code,
sty_id,
purchase_option_code,
int_disclosed_code,
title_trnsfr_code,
sale_lease_back_code,
lease_purchased_code,
tax_country_code,
'Y' select_yn -- All PCs returned by this cursor are selected initially
FROM okl_tax_attr_definitions
WHERE (sty_id = p_sty_id OR sty_id IS NULL)
AND result_type_code = 'PC_CODE'
AND NVL(expire_flag, 'N') = 'N';
TYPE pcdefs_selected_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_pcdefs_selected_tbl pcdefs_selected_tbl_type;
selected_count NUMBER;
l_selected_pc_pos NUMBER;
'j '||j||' l_pcdefs_tbl(j).select_yn '||l_pcdefs_tbl(j).select_yn );
IF l_pcdefs_tbl(j).select_yn = 'Y' THEN
IF i = 1 THEN -- purchase option
IF p_purchase_option_code IS NULL THEN
IF l_pcdefs_tbl(j).purchase_option_code IS NULL THEN
pc_set_count := pc_set_count + 1;
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_selected_tbl(k) := j;
l_pcdefs_tbl(j).select_yn := 'N';
l_pcdefs_selected_tbl(k) := j;
l_pcdefs_tbl(j).select_yn := 'N';
'l_pcdefs_tbl(j).select_yn '||l_pcdefs_tbl(j).select_yn);
l_pc_code := l_pcdefs_tbl(l_pcdefs_selected_tbl(1)).pc_code;
selected_count := pc_set_count;
'l_pcdefs_selected_tbl.count '||l_pcdefs_selected_tbl.count );
'i '||i||' selected_count '||selected_count );
l_det_null_tbl.DELETE;
IF selected_count > 1 THEN
FOR k IN l_pcdefs_selected_tbl.FIRST .. l_pcdefs_selected_tbl.LAST LOOP -- finally selected indexes
-- At this point all the selecetd PCs will either have Null determinant value or determinant value matching with the calling transaction
IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.get_pc_code.', 'k '||k);
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).sty_id IS NULL THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).purchase_option_code IS NULL THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).int_disclosed_code = 'N' THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).title_trnsfr_code = 'N' THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).sale_lease_back_code = 'N' THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).lease_purchased_code = 'N' THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_pcdefs_tbl(l_pcdefs_selected_tbl(k)).tax_country_code IS NULL THEN
l_det_null_tbl(j) := l_pcdefs_selected_tbl(k);
l_selected_pc_pos := l_pcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_pcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
ELSIF selected_count = 1 THEN
EXIT;
'selected_count outside 2nd loop '||selected_count);
IF selected_count = 1 THEN
l_pc_code := l_pcdefs_tbl(l_selected_pc_pos).pc_code;
ELSIF selected_count > 1 THEN -- still more than one PCs are selected
l_pc_code := NULL;
SELECT result_code ufc_code,
try_id,
sty_id ,
purchase_option_code,
equip_usage_code,
vendor_site_id,
age_of_equip_from,
age_of_equip_to,
pdt_id,
int_disclosed_code ,
title_trnsfr_code,
sale_lease_back_code,
lease_purchased_code,
term_quote_type_code,
term_quote_reason_code,
tax_country_code,
'Y' select_yn -- all ufcs returned by this cursor are selected initially
FROM okl_tax_attr_definitions
WHERE (try_id = cp_try_id OR try_id IS NULL)
AND RESULT_TYPE_CODE = 'UFC_CODE'
AND NVL(expire_flag, 'N') = 'N';
TYPE ufcdefs_selected_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_ufcdefs_selected_tbl ufcdefs_selected_tbl_type;
selected_count NUMBER;
l_selected_ufc_pos NUMBER;
'j '||j||' l_ufcdefs_tbl(j).select_yn '||l_ufcdefs_tbl(j).select_yn );
IF l_ufcdefs_tbl(j).select_yn = 'Y' THEN
IF i = 1 THEN -- stream type
IF p_sty_id IS NULL THEN
IF l_ufcdefs_tbl(j).sty_id IS NULL THEN
ufc_set_count := ufc_set_count + 1;
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_selected_tbl(k) := j;
l_ufcdefs_tbl(j).select_yn := 'N';
l_ufcdefs_selected_tbl(k) := j;
l_ufcdefs_tbl(j).select_yn := 'N';
'k '||k||' l_ufcdefs_selected_tbl(k) '||l_ufcdefs_selected_tbl(k));
'l_ufcdefs_tbl(j).select_yn '||l_ufcdefs_tbl(j).select_yn);
l_ufc_code := l_ufcdefs_tbl(l_ufcdefs_selected_tbl(1)).ufc_code;
selected_count := ufc_set_count;
'l_ufcdefs_selected_tbl.count '||l_ufcdefs_selected_tbl.count );
'i '||i||' selected_count '||selected_count );
l_det_null_tbl.DELETE;
IF selected_count > 1 THEN
FOR k IN l_ufcdefs_selected_tbl.FIRST .. l_ufcdefs_selected_tbl.LAST LOOP -- finally selected indexes
-- At this point all the selecetd ufcs will either have Null determinant value or determinant value matching with the calling transaction
IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.get_ufc_code.','k '||k);
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).try_id IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).sty_id IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).term_quote_type_code IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).term_quote_reason_code IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).purchase_option_code IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).equip_usage_code IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).vendor_site_id IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF (l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).age_of_equip_from IS NULL AND
l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).age_of_equip_to IS NULL) THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).pdt_id IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).int_disclosed_code = 'N' THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).title_trnsfr_code = 'N' THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).sale_lease_back_code = 'N' THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).lease_purchased_code = 'N' THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).select_yn = 'Y' THEN -- if still selected
IF l_ufcdefs_tbl(l_ufcdefs_selected_tbl(k)).tax_country_code IS NULL THEN
l_det_null_tbl(j) := l_ufcdefs_selected_tbl(k);
l_selected_ufc_pos := l_ufcdefs_selected_tbl(k);
IF det_match_cnt = selected_count THEN --k THEN
NULL; -- all records have matching values, do nothing
NULL; -- keep all records selected. Don't exclude yet.
selected_count := 1;
ELSE -- more than one but less than total selected records have matching values
FOR p IN l_det_null_tbl.FIRST .. l_det_null_tbl.LAST LOOP
l_ufcdefs_tbl(l_det_null_tbl(p)).select_yn := 'N';
selected_count := selected_count - 1;
ELSIF selected_count = 1 THEN
EXIT;
'selected_count outside 2nd loop '||selected_count);
IF selected_count = 1 THEN
l_ufc_code := l_ufcdefs_tbl(l_selected_ufc_pos).ufc_code;
ELSIF selected_count > 1 THEN -- still more than one ufcs are selected
l_ufc_code := NULL;
SELECT set_of_books_id
FROM ar_system_parameters_all
WHERE org_id = cp_org_id;
px_tax_sources_tbl(p_tax_call_cnt).program_update_date := null;
px_tax_sources_tbl(p_tax_call_cnt).last_updated_by := G_USER_ID;
px_tax_sources_tbl(p_tax_call_cnt).last_update_date := SYSDATE;
px_tax_sources_tbl(p_tax_call_cnt).last_update_login := G_LOGIN_ID;
SELECT set_of_books_id
FROM ar_system_parameters_all
WHERE org_id = cp_org_id;
SELECT a.id, a.code, a.stream_type_purpose, b.meaning
FROM okl_strm_type_b a, fnd_lookups b
WHERE a.stream_type_purpose = 'FEE_PAYMENT'
AND a.billable_yn = 'Y'
AND a.stream_type_purpose = b.lookup_code
AND b.lookup_type = 'OKL_STREAM_TYPE_PURPOSE'
AND b.enabled_flag = 'Y'
AND upper(a.code) like decode(cp_sty_code, NULL, a.code, '%', a.code, upper(cp_sty_code)||'%');
SELECT id
FROM okl_st_gen_tmpt_sets
WHERE org_id = cp_org_id
--asawanka bug fix 4659674 start
AND product_type = 'FINANCIAL';
SELECT id
FROM okl_st_gen_templates
WHERE gts_id = cp_gts_id
AND tmpt_status = G_ACTIVE_STATUS;
SELECT primary_sty_id
FROM okl_st_gen_tmpt_lns
WHERE gtt_id = cp_gtt_id
AND primary_yn = 'Y';
FOR l_strmtype_rec IN l_strmtype_csr(p_sty_code) LOOP -- All/User selected billable stream types of purpose 'FEE_PAYMENT'
l_strm_type_cnt := l_strm_type_cnt + 1;
SELECT cust_acct_id, bill_to_site_use_id, currency_code, org_id,
conversion_type, conversion_rate, conversion_rate_date, start_date
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT precision, minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = cp_currency_code
AND enabled_flag = 'Y'
AND NVL(start_date_active, sysdate) <= sysdate
AND NVL(end_date_active, sysdate) >= sysdate;
SELECT cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE name = 'Invoice-OKL';
SELECT pdt_id, deal_type
FROM okl_k_headers
WHERE id = cp_khr_id;
SELECT name
FROM OKC_K_LINES_v
WHERE id = cp_fin_asset_id;
SELECT id
FROM okc_k_lines_b
WHERE cle_id = cp_fin_asset_id
AND lse_id = 34 ; -- model line LSE ID
SELECT object1_id1 --is fk to po_vendors.vendor_id
FROM okc_k_party_roles_b
WHERE cle_id = cp_line_id --fee/service top line id for fee and service, model line id for asset line
AND rle_code = 'OKL_VENDOR';
SELECT vendor_site_id
FROM po_vendor_sites
WHERE vendor_id = cp_vendor_id
AND primary_pay_site_flag = 'Y';
SELECT a.dnz_khr_id, a.start_date, a.end_date, a.try_id, a.legal_entity_id, a.org_id
FROM okl_trx_requests a, okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT strm.khr_id,
strm.kle_id, -- can be null for K level streams
lse.lty_code,
NULL asset_number,
NULL trx_id,
NULL trx_line_id,
NULL entity_code,
NULL event_class_code,
NULL trx_level_type,
NULL adjusted_doc_entity_code,
NULL adjusted_doc_event_class_code,
NULL adjusted_doc_trx_id,
NULL adjusted_doc_trx_line_id,
NULL adjusted_doc_trx_level_type,
NULL adjusted_doc_number,
NULL adjusted_doc_date,
G_TAX_SCHEDULE tax_call_type_code,
strm.sty_id stream_type_id,
NULL trx_business_category,
'ACTIVE' tax_line_status_code,
selm.id sel_id,
'N' reported_yn,
typ.code Stream_Type,
typ.billable_yn Billable,
strm.id stream_id,
strm.transaction_number,
selm.amount,
selm.stream_element_date stream_element_date,
NULL tax_sources_id,
NULL asset_line_id,
NULL ship_to_siteuseid,
NULL inventory_item_id
FROM okl_strm_type_b typ,
okl_streams strm,
okl_strm_elements selm,
okc_k_lines_b lines,
okc_line_styles_b lse
WHERE strm.sty_id = typ.id
AND strm.id = selm.stm_id
AND typ.billable_yn = 'Y'
AND strm.say_code ='CURR'
AND strm.active_yn = 'Y'
AND strm.purpose_code IS NULL
AND strm.khr_id = cp_contract_id
AND selm.stream_element_date BETWEEN cp_date_from AND cp_date_to
AND strm.kle_id = lines.id(+)
AND lines.lse_id = lse.id (+)
AND lse.lty_code (+) <> 'INSURANCE'
AND NOT EXISTS -- tax lines don't exist in AR ( This condition is added to pick up stream elements billed under
-- old solution : date_billed may not be null but billing may not have been run completely, so tax
-- may not have been calculated in AR
(SELECT NULL
FROM okl_bpd_tld_ar_lines_v cnsld
WHERE cnsld.sel_id = selm.id)
ORDER BY strm.kle_id, strm.sty_id;
SELECT strm.khr_id,
strm.kle_id, -- can be null for K level streams
strm.sty_id stream_type_id,
selm.id sel_id,
selm.stream_element_date stream_element_date,
cnsld.customer_trx_id,
cnsld.customer_trx_line_id,
selm.amount --SECHAWLA 22-APR-08 6975983 : added
FROM okl_strm_type_b typ,
okl_streams strm,
okl_strm_elements selm,
okc_k_lines_b lines,
okc_line_styles_b lse,
okl_bpd_tld_ar_lines_v cnsld
WHERE strm.sty_id = typ.id
AND strm.id = selm.stm_id
AND typ.billable_yn = 'Y'
AND strm.say_code ='CURR'
AND strm.active_yn = 'Y'
AND strm.purpose_code IS NULL
AND strm.khr_id = cp_contract_id
AND selm.stream_element_date BETWEEN cp_date_from AND cp_date_to
AND selm.date_billed IS NOT NULL -- pick billed stream elements
AND strm.kle_id = lines.id(+)
AND lines.lse_id = lse.id (+)
AND lse.lty_code (+) <> 'INSURANCE'
AND cnsld.sel_id = selm.id
AND cnsld.khr_id = strm.khr_id;
SELECT hdr.id trx_id, det.id trx_line_id
FROM okl_trx_ar_invoices_b hdr, okl_txl_ar_inv_lns_b line, okl_txd_ar_ln_dtls_b det
WHERE det.sel_id = cp_sel_id
AND det.sty_id = cp_sty_id
AND det.til_id_details = line.id
AND line.tai_id = hdr.id;
SELECT DISTINCT strm.kle_id fin_asset_id -- is DISTINCT req ? can an asset line have more than one stream types ?
FROM okl_strm_type_b typ,
okl_streams strm,
okc_k_lines_b lines,
okc_line_styles_b sty
WHERE strm.sty_id = typ.id
AND typ.billable_yn = 'Y'
AND strm.say_code ='CURR'
AND strm.active_yn = 'Y'
AND strm.purpose_code IS NULL
AND strm.khr_id = CP_KHR_ID
AND strm.kle_id = lines.id
AND lines.lse_id = sty.id
AND sty.lty_code = 'FREE_FORM1';
SELECT name, lse_id
FROM OKC_K_LINES_v
WHERE id = cp_fin_asset_id;
SELECT typ.code Stream_Type,
strm.id stream_id,
strm.kle_id line_id,
strm.sty_id stream_type_id,
sty.lty_code
FROM okl_strm_type_b typ,
okl_streams strm,
okc_k_lines_b lines,
okc_line_styles_b sty
WHERE strm.sty_id = typ.id
AND typ.billable_yn = 'Y'
AND strm.say_code ='CURR'
AND strm.active_yn = 'Y'
AND strm.purpose_code IS NULL
AND strm.khr_id = cp_khr_id
AND strm.kle_id = lines.id(+)
AND lines.lse_id = sty.id (+)
AND sty.lty_code (+) <> 'INSURANCE';
SELECT cim.object1_id1
FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
WHERE cle.lse_id = lse.id
AND lse.lty_code = cp_line_type
AND cim.cle_id = cle.id
AND cle.id = cp_fee_serviced_asset_line_id;
SELECT cim_model.object1_id1,
cim_model.object1_id2
FROM okc_k_items cim_model,
okc_k_lines_b cleb_model,
okc_line_styles_b lseb_model
WHERE cim_model.cle_id = cleb_model.id
AND cleb_model.cle_id = cp_fin_asset_id
AND lseb_model.id = cleb_model.lse_id
AND lseb_model.lty_code = 'ITEM';
SELECT *
FROM okl_tax_trx_details
WHERE txs_id = cp_txs_id;
SELECT object1_id1 --is fk to po_vendors.vendor_id
FROM okc_k_party_roles_b
WHERE cle_id = cp_line_id --fee/service top line id for fee and service, model line id for asset line
AND rle_code = 'OKL_VENDOR';
SELECT vendor_site_id
FROM po_vendor_sites
WHERE vendor_id = cp_vendor_id
AND primary_pay_site_flag = 'Y';
SELECT SUM(zx.tax_amt) tax_amt
FROM zx_lines zx
WHERE zx.trx_id = cp_trx_id
AND zx.trx_line_id = cp_trx_line_id
AND zx.entity_code = G_AR_ENTITY_CODE
AND zx.event_class_code = G_INVOICE_EVENT_CLASS_CODE
AND zx.application_id = G_AR_APPLICATION_ID;
SELECT zx.line_amt,
zx.taxable_amt,
zx.trx_date,
zx.tax_determine_date,
zx.trx_currency_code,
zx.currency_conversion_type,
zx.currency_conversion_rate,
zx.currency_conversion_date,
zxdet.product_category,
zxdet.trx_business_category,
zxdet.product_fisc_classification,
zxdet.user_defined_fisc_class,
zxdet.product_type,
zxdet.internal_organization_id,
zxdet.input_tax_classification_code,
zx.legal_entity_id,
zxdet.line_intended_use
FROM zx_lines zx,
zx_lines_det_factors zxdet
WHERE zx.trx_line_id = cp_trx_line_id
AND zx.trx_id = cp_trx_id
AND zx.internal_organization_id = MO_GLOBAL.get_current_org_id
AND zx.entity_code = G_AR_ENTITY_CODE
AND zx.event_class_code = G_INVOICE_EVENT_CLASS_CODE
AND zx.application_id = G_AR_APPLICATION_ID
AND zx.trx_line_id = zxdet.trx_line_id
AND zx.trx_id = zxdet.trx_id
AND zx.internal_organization_id = zxdet.internal_organization_id
AND zx.entity_code = zxdet.entity_code
AND zx.event_class_code = zxdet.event_class_code
AND zx.application_id = zxdet.application_id
AND ROWNUM = 1;
SELECT zx.tax_rate_id,
zx.tax_rate_code,
zx.tax_exemption_id,
zx.tax_rate,
zx.tax_date,
zx.line_amt,
zx.internal_organization_id,
zx.application_id,
zx.entity_code,
zx.event_class_code,
zx.event_type_code,
zx.trx_id,
zx.trx_line_id,
zx.trx_level_type,
zx.trx_line_number,
zx.tax_line_number,
zx.tax_regime_id,
zx.tax_regime_code,
zx.tax_id,
zx.tax,
zx.tax_status_id,
zx.tax_status_code,
zx.tax_apportionment_line_number,
zx.legal_entity_id,
zx.trx_number,
zx.trx_date,
zx.tax_jurisdiction_id,
zx.tax_jurisdiction_code,
zx.tax_type_code,
zx.tax_currency_code,
zx.taxable_amt_tax_curr,
zx.trx_currency_code,
zx.minimum_accountable_unit,
zx.precision,
zx.currency_conversion_type,
zx.currency_conversion_rate,
zx.currency_conversion_date,
zx.tax_determine_date,
zx.taxable_amt,
zx.tax_amt,
zxdet.product_category,
zxdet.trx_business_category,
zxdet.product_fisc_classification,
zxdet.user_defined_fisc_class,
zxdet.product_type
FROM zx_lines zx,
zx_lines_det_factors zxdet
WHERE zx.trx_id = cp_trx_id
AND zx.trx_line_id = cp_trx_line_id
AND zx.internal_organization_id = MO_GLOBAL.get_current_org_id
AND zx.entity_code = G_AR_ENTITY_CODE
AND zx.event_class_code = G_INVOICE_EVENT_CLASS_CODE
AND zx.application_id = G_AR_APPLICATION_ID
AND zx.trx_line_id = zxdet.trx_line_id
AND zx.trx_id = zxdet.trx_id
AND zx.internal_organization_id = zxdet.internal_organization_id
AND zx.entity_code = zxdet.entity_code
AND zx.event_class_code = zxdet.event_class_code
AND zx.application_id = zxdet.application_id;
l_bill_ts_tx_src_tbl.DELETE;
l_k_billed_streams_tbl.DELETE;
l_tax_sources_tbl.DELETE;
l_tax_trx_details_tbl.DELETE;
l_tax_sources_tbl(k).program_update_date := null;
l_tax_sources_tbl(k).last_updated_by := G_USER_ID;
l_tax_sources_tbl(k).last_update_date := SYSDATE;
l_tax_sources_tbl(k).last_update_login := G_LOGIN_ID;
l_tax_trx_details_tbl(i).program_update_date := null;
l_tax_trx_details_tbl(i).last_updated_by := G_USER_ID;
l_tax_trx_details_tbl(i).last_update_date := SYSDATE;
l_tax_trx_details_tbl(i).last_update_login := G_LOGIN_ID;
'before Insert into okl_tax_sources ');
INSERT INTO okl_tax_sources VALUES l_tax_sources_tbl(indx);
'before Insert into okl_tax_trx_details ');
INSERT INTO okl_tax_trx_details VALUES l_tax_trx_details_tbl(indx);
l_finassets_tbl.DELETE;
l_kstreamtypes_tbl.DELETE;
lx_tax_codes_tbl.DELETE;
l_tax_sources_tbl.DELETE;
l_contract_streams_tbl.DELETE;
l_tax_sources_tbl(k).program_update_date := null;
l_tax_sources_tbl(k).last_updated_by := G_USER_ID;
l_tax_sources_tbl(k).last_update_date := SYSDATE;
l_tax_sources_tbl(k).last_update_login := G_LOGIN_ID;
'before Insert into okl_tax_sources ');
INSERT INTO okl_tax_sources VALUES l_tax_sources_tbl(indx);
'Calling "update_tax_sources_total" .. ');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id,
p_source_trx_name => p_source_trx_name );
SELECT khr_id, date_effective_from, early_termination_yn, qtp_code,
legal_entity_id, qrs_code
FROM okl_trx_quotes_b
WHERE id = cp_qte_id;
SELECT id
FROM okl_tax_sources
WHERE trx_id = cp_trx_id;
SELECT ID
FROM okl_tax_trx_details
WHERE txs_id = cp_txs_id;
SELECT txl.id, txl.sty_id, txl.kle_id,
txl.qlt_code, txl.amount, txl.try_id, cle.name
FROM okl_txl_quote_lines_b txl, okc_k_lines_v cle
WHERE qte_id = cp_qte_id
AND qlt_code NOT IN ('AMCFIA', 'AMCTAX', 'AMYOUB', 'AMCSDD')
AND txl.kle_id = cle.id (+);
SELECT name, lse_id
FROM okc_k_lines_v
WHERE id = cp_line_id;
SELECT cim_model.object1_id1,
cim_model.object1_id2
FROM okc_k_items cim_model,
okc_k_lines_b cleb_model,
okc_line_styles_b lseb_model
WHERE cim_model.cle_id = cleb_model.id
AND cleb_model.cle_id = cp_fin_asset_id
AND lseb_model.id = cleb_model.lse_id
AND lseb_model.lty_code = 'ITEM';
SELECT fma_id
FROM okl_tax_basis_override
WHERE try_id = cp_try_id
AND org_id = MO_GLOBAL.get_current_org_id;
SELECT name
FROM okl_formulae_b
WHERE id = cp_fma_id;
l_prevtaxsrc_tbl.DELETE;
'Deleteing prev tax source transactions for this quote ');
l_prevtaxtrxdet_tbl.DELETE;
DELETE FROM OKL_TAX_TRX_DETAILS WHERE id = l_prevtaxtrxdet_tbl(i);
'Deleted from okl_tax_trx_details... ');
DELETE FROM OKL_TAX_SOURCES WHERE id = l_prevtaxsrc_tbl(i);
'Deleted from okl_tax_sources... ');
l_txlquotelines_tbl.DELETE;
l_tax_sources_tbl.DELETE;
INSERT INTO okl_tax_sources VALUES lx_tax_sources_tbl(indx);
'Insert into okl_tax_sources ');
'Calling "update_tax_sources_total" .. ');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id,
p_source_trx_name => p_source_trx_name );
| OKL_TAX_SOURCES_PUB.update_tax_sources()
| get_k_hdr_tax_params()
| get_asset_tax_params()
| get_location_party_ids()
| get_ufc_code()
| OKL_TAX_INTERFACE_PVT.calculate_tax()
|
|
| PARAMETERS
| p_source_trx_id -- Source transaction ID
| p_source_trx_name -- Source transaction Name
| p_source_table -- Source table
| p_tax_call_type -- Tax call type (Values - 'ESTIMATED'/ 'ACTUAL')
| p_serialized_asset -- Serialized Asset (Values - 'Y'/'N')
| p_request_id -- Id of OKL_trx_Requests
| p_alc_final_call -- Determines if the asset location change final
| call is being made for the serialized asset
| (Values - null/'N'/'Y')
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 08-APR-05 SECHAWLA Created
|
*=======================================================================*/
PROCEDURE process_asset_loc_tax(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_source_trx_id IN NUMBER,
p_source_trx_name IN VARCHAR2,
p_source_table IN VARCHAR2,
p_tax_call_type IN VARCHAR2,
p_serialized_asset IN VARCHAR2,
p_request_id IN NUMBER,
p_alc_final_call IN VARCHAR2) IS
-------------Asset Location Change --------------
--p_source_trx_id ---> okl_trx_assets.id
--p_source_trx_name ---> 'Asset Relocation' (changed from Internal Asset Creation to Asset Relocation)
--p_source_table ---> 'OKL_TRX_ASSETS'
--p_tax_call_type ---> Tax call type
--p_serialized_asset ---> Serialized Asset (Y/N)
--p_request_id ---> Id of OKL_trx_Requests
--------------------------------------------------
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
SELECT a.try_id, a.date_trans_occurred, a.legal_entity_id
FROM okl_trx_assets a , okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT kle_id, -- Id of INST_ITEM line
OBJECT_ID1_NEW, -- ID of okx_party_site_uses_v
DNZ_CLE_ID -- id of FREE_FORM1
FROM okl_txl_itm_insts
WHERE tas_id = cp_id;
SELECT location_id, party_site_id
FROM okx_party_site_uses_v
WHERE id1 = cp_id1
AND site_use_type = 'INSTALL_AT';
SELECT id, org_id, entity_code, event_class_code
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND kle_id = cp_kle_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND application_id = G_OKL_APPLICATION_ID
AND trx_level_type = G_TRX_LEVEL_TYPE
AND adjusted_doc_trx_id IS NULL
AND adjusted_doc_trx_line_id IS NULL;
SELECT chr_id, name
FROM okc_k_lines_v
WHERE id = cp_kle_id;
SELECT typ.code Stream_Type,
strm.id stream_id,
strm.kle_id line_id,
strm.sty_id stream_type_id
FROM okl_strm_type_b typ,
okl_streams strm
WHERE strm.sty_id = typ.id
AND typ.billable_yn = 'Y'
AND strm.say_code ='CURR'
AND strm.active_yn = 'Y'
AND strm.purpose_code IS NULL
AND strm.khr_id = cp_khr_id
AND strm.kle_id = cp_kle_id;
SELECT sum(amount)
FROM okl_strm_elements
WHERE stm_id = cp_stm_id;
SELECT fma_id
FROM okl_tax_basis_override
WHERE try_id = cp_try_id
AND org_id = MO_GLOBAL.get_current_org_id;
SELECT cim_model.object1_id1,
cim_model.object1_id2
FROM okc_k_items cim_model,
okc_k_lines_b cleb_model,
okc_line_styles_b lseb_model
WHERE cim_model.cle_id = cleb_model.id
AND cleb_model.cle_id = cp_fin_asset_id
AND lseb_model.id = cleb_model.lse_id
AND lseb_model.lty_code = 'ITEM';
SELECT name
FROM okl_formulae_b
WHERE id = cp_fma_id;
SELECT entity_code, event_class_code
FROM okl_tax_sources
WHERE application_id = cp_app_id
AND trx_id = cp_trx_id
AND trx_level_type = G_TRX_LEVEL_TYPE
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND entity_code = G_ASSETS_ENTITY_CODE
AND event_class_code = G_ALC_EVENT_CODE
AND ROWNUM = 1;
SELECT count(*)
FROM okc_k_lines_v okcl,
okc_line_styles_v lse
WHERE okcl.cle_id = p_parent_line_id
AND okcl.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM2';
SELECT count(*)
FROM okl_strm_type_b typ,
okl_streams strm
WHERE strm.sty_id = typ.id
AND typ.billable_yn = 'Y'
AND strm.say_code ='CURR'
AND strm.active_yn = 'Y'
AND strm.purpose_code IS NULL
AND strm.khr_id = cp_khr_id
AND strm.kle_id = cp_kle_id;
SELECT b.site_use_id,
c.party_site_id,
c.party_id,
c.location_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE a.CUST_ACCT_SITE_ID = b.CUST_ACCT_SITE_ID
AND b.site_use_code = 'SHIP_TO'
AND a.party_site_id = c.party_site_id
AND a.cust_account_id = cp_cust_acct_id
AND a.org_id = MO_GLOBAL.get_current_org_id
AND c.party_site_id = cp_inst_loc_id
AND c.location_id = cp_loc_id;
l_tax_src_tbl.DELETE;
UPDATE OKL_TAX_SOURCES SET TAX_LINE_STATUS_CODE = G_INACTIVE_STATUS WHERE id = l_tax_src_tbl(i);
'Updated existing tax lines status to INACIVE');
INSERT INTO okl_tax_sources VALUES lx_tax_sources_tbl(indx);
' inserted into okl_tax_sources ' );
'Calling "update_tax_sources_total" .. ');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id,
p_source_trx_name => p_source_trx_name,
p_serialized_asset => p_serialized_asset);
| OKL_TAX_SOURCES_PUB.update_tax_sources()
|
|
| PARAMETERS
| p_source_trx_id -- Source transaction ID
| p_source_trx_name -- Source transaction Name
| p_source_table -- Source table
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 08-APR-05 SECHAWLA Created
|
*=======================================================================*/
PROCEDURE process_split_asset_tax(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_source_trx_id IN NUMBER,
p_source_trx_name IN VARCHAR2,
p_source_table IN VARCHAR2) IS
-------------Split Asset -------------------------
--p_source_trx_id ---> okl_trx_assets.id
--p_source_trx_name ---> 'Split Asset'
--p_source_table ---> 'OKL_TRX_ASSETS'
--------------------------------------------------
-- validate source trx id
CURSOR l_trxassets_csr(cp_trx_id IN NUMBER, cp_trx_type_name IN VARCHAR2) IS
SELECT a.try_id, a.date_trans_occurred
FROM okl_trx_assets a, okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT txl.id, okc.cle_id kle_id, -- financial asset to be split
txl.dnz_khr_id,
txl.asset_number,
txl.current_units
FROM okl_txl_assets_b txl,
okc_k_lines_b okc
WHERE txl.tas_id = cp_id
AND txl.kle_id = okc.id
AND txl.dnz_khr_id = okc.dnz_chr_id;
SELECT txd.id, okc.cle_id target_kle_id, txd.asset_number, txd.split_percent, txd.quantity
FROM okl_txd_assets_b txd,
okc_k_lines_b okc
WHERE tal_id = cp_id
AND txd.target_kle_id = okc.id;
SELECT id, khr_id, kle_id, asset_number, trx_id, trx_line_id, entity_code, event_class_code,
trx_level_type, tax_call_type_code, sty_id, trx_business_category, reported_yn,
sel_id, line_name, application_id, tax_reporting_flag, default_taxation_country,
product_category, user_defined_fisc_class, line_intended_use,
tax_classification_code, inventory_item_id, bill_to_cust_acct_id, legal_entity_id,
line_amt, assessable_value, total_tax, product_type,
product_fisc_classification, trx_date, provnl_tax_determination_date,
try_id, ship_to_location_id, ship_to_party_site_id, ship_to_party_id,
bill_to_party_site_id, bill_to_location_id, bill_to_party_id,
ship_to_cust_acct_site_use_id, bill_to_cust_acct_site_use_id,
trx_currency_code, currency_conversion_type, currency_conversion_rate,
currency_conversion_date, org_id, alc_serialized_yn
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND kle_id = cp_kle_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND ADJUSTED_DOC_TRX_ID IS NULL
AND ADJUSTED_DOC_TRX_LINE_ID IS NULL;
SELECT *
FROM okl_tax_trx_details
WHERE txs_id = cp_txs_id;
SELECT dnz_chr_id, name
FROM okc_k_lines_v
WHERE id = cp_kle_id;
SELECT cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE name = 'Invoice-OKL';
SELECT precision, minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = cp_currency_code
AND enabled_flag = 'Y'
AND NVL(start_date_active, sysdate) <= sysdate
AND NVL(end_date_active, sysdate) >= sysdate;
l_tax_sources_tbl.DELETE;
l_tax_src_tbl.DELETE;
l_tax_sources_tbl(k).program_update_date := NULL;
l_tax_sources_tbl(k).last_updated_by := G_USER_ID;
l_tax_sources_tbl(k).last_update_date := SYSDATE;
l_tax_sources_tbl(k).last_update_login := G_LOGIN_ID;
UPDATE okl_tax_sources SET tax_line_status_code = G_INACTIVE_STATUS WHERE id = l_tax_src_tbl(i);
' Insert Adjusting tax sources - Start');
INSERT INTO okl_tax_sources VALUES l_tax_sources_tbl(indx);
' Insert Adjusting tax sources - End');
'Calling "update_tax_sources_total" for updating Total Tax for all Lines in Tax Sources');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id,
p_source_trx_name => p_source_trx_name);
l_new_tax_sources_tbl.DELETE;
l_new_tax_sources_tbl(new_tax_count).program_update_date := NULL;
l_new_tax_sources_tbl(new_tax_count).last_updated_by := G_USER_ID;
l_new_tax_sources_tbl(new_tax_count).last_update_date := SYSDATE;
l_new_tax_sources_tbl(new_tax_count).last_update_login := G_LOGIN_ID;
' Insert Split asset tax sources - Start');
INSERT INTO okl_tax_sources VALUES l_new_tax_sources_tbl(indx);
' Insert Split asset tax sources - End');
'Calling "update_tax_sources_total" .. ');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id);
SELECT a.try_id, a.khr_id, a.legal_entity_id
FROM okl_trx_contracts a , okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT a.khr_id_new, a.legal_entity_id
FROM okl_trx_contracts a , okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT id
FROM okl_trx_types_v
WHERE NAME = 'Booking';
SELECT org_id, application_id, entity_code, event_class_code
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND application_id = G_OKL_APPLICATION_ID
AND trx_id = cp_trx_id
AND trx_level_type = G_TRX_LEVEL_TYPE
AND ROWNUM = 1;
SELECT id
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND application_id = G_OKL_APPLICATION_ID
AND trx_id = cp_trx_id
AND trx_level_type = G_TRX_LEVEL_TYPE;
SELECT id
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND application_id = G_OKL_APPLICATION_ID
AND trx_level_type = G_TRX_LEVEL_TYPE;
SELECT cle.id fin_asset_id, cle.name asset_number
FROM okc_k_lines_v cle
WHERE cle.chr_id = cp_chr_id
AND cle.lse_id = 33
AND sts_code NOT IN ('ABANDONED', 'EXPIRED', 'REVERSED', 'TERMINATED');*/
SELECT cle.id fin_asset_id, cle.name asset_number
FROM okc_k_lines_v cle
,okl_k_lines kle
WHERE cle.chr_id = cp_chr_id
AND cle.lse_id = 33
AND kle.id = cle.id
AND NVL(kle.re_lease_yn,'N') = 'N'
AND sts_code NOT IN ('ABANDONED', 'EXPIRED', 'REVERSED', 'TERMINATED');
SELECT rgpb.cle_id line_id,
styb.id stream_type_id,
styb.CODE stream_type_code,
sum(nvl((rulb2.RULE_INFORMATION3 * rulb2.RULE_INFORMATION6),0) + nvl(rulb2.RULE_INFORMATION8,0)) line_amt
FROM
okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.dnz_chr_id = cp_chr_id
AND rgpb.chr_id = cp_chr_id
AND rgpb.cle_id IS NULL
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND rulb.dnz_chr_id = cp_chr_id
AND styb.id = rulb.object1_id1
AND rulb2.object2_id1 = rulb.id
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
-- Bug #15992711 : Start
-- modification to look at re_lease_yn flag at line level
AND EXISTS ( SELECT '1'
FROM okl_k_lines kle
WHERE NVL(kle.re_lease_yn,'N') = 'N'
AND kle.id = rgpb.cle_id)
-- Bug #15992711 : End
GROUP BY rgpb.cle_id, styb.id, styb.CODE
-- Bug 9682559
-- , rulb2.RULE_INFORMATION6
-- End Bug 9682559
UNION
SELECT rgpb.cle_id line_id,
styb.id stream_type_id,
styb.CODE stream_type_code,
sum(nvl((rulb2.RULE_INFORMATION3 * rulb2.RULE_INFORMATION6),0) + nvl(rulb2.RULE_INFORMATION8,0)) line_amt
FROM okc_k_lines_b cleb,
okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.dnz_chr_id = cp_chr_id
AND rgpb.chr_id IS NULL
AND rgpb.cle_id = cleb.id
AND cleb.dnz_chr_id = cp_chr_id
AND cleb.lse_id = 33
AND cleb.sts_code NOT IN ('ABANDONED', 'EXPIRED', 'REVERSED', 'TERMINATED')
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND rulb.dnz_chr_id = cp_chr_id
AND styb.id = rulb.object1_id1
AND rulb2.object2_id1 = rulb.id
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
-- Bug #15992711 : Start
-- modification to look at re_lease_yn flag at line level
AND EXISTS ( SELECT '1'
FROM okl_k_lines kle
WHERE NVL(kle.re_lease_yn,'N') = 'N'
AND kle.id = rgpb.cle_id)
-- Bug #15992711 : End
GROUP BY rgpb.cle_id, styb.id, styb.CODE;
SELECT cim_model.object1_id1,
cim_model.object1_id2
FROM okc_k_items cim_model,
okc_k_lines_b cleb_model,
okc_line_styles_b lseb_model
WHERE cim_model.cle_id = cleb_model.id
AND cleb_model.cle_id = cp_fin_asset_id
AND lseb_model.id = cleb_model.lse_id
AND lseb_model.lty_code = 'ITEM';
SELECT fma_id
FROM okl_tax_basis_override
WHERE try_id = cp_try_id
AND org_id = MO_GLOBAL.get_current_org_id;
SELECT name
FROM Okl_formulae_b
WHERE id = cp_fma_id;
l_delete_data BOOLEAN := FALSE;
l_prev_tax_source_tbl.DELETE;
l_delete_data := TRUE;
DELETE FROM OKL_TAX_SOURCES WHERE id = l_prev_tax_source_tbl(i);
IF (l_delete_data) THEN
l_transaction_rec.internal_organization_id := l_internal_organization_id;
'OKL_TAX_INTERFACE_PVT.update_document');
OKL_TAX_INTERFACE_PVT.update_document(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_rec => l_transaction_rec);
l_finassets_tbl.DELETE;
l_k_asset_pymnts_tbl.DELETE;
INSERT INTO okl_tax_sources VALUES lx_tax_sources_tbl(indx);
'Insert into okl_tax_sources ');
'Calling "update_tax_sources_total" .. ');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id);
| update_tax_sources_total()
| okl_tax_sources_pub.update_tax_sources()
| process_final_upfront_tax()
|
|
| PARAMETERS
| p_source_trx_id -- Source transaction ID
| p_source_trx_name -- Source transaction Name
| p_source_table -- Source table
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 08-APR-05 SECHAWLA Created
| 29-NOV-05 SECHAWLA 4772640 Modified to merge Rebook trx type setups
| into 'Booking' trx type
|
*=======================================================================*/
PROCEDURE process_rebook_upfront_tax(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_source_trx_id IN NUMBER,
p_source_trx_name IN VARCHAR2,
p_source_table IN VARCHAR2) IS
-------------Rebook --------------
--p_source_trx_id ---> okl_trx_contracts.id
--p_source_trx_name ---> 'Rebook'
--p_source_table ---> 'OKL_TRX_CONTRACTS'
--------------------------------------------------
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
SELECT a.try_id, a.khr_id, a.khr_id_new, a.date_transaction_occurred
FROM okl_trx_contracts a , okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT id, khr_id, kle_id, asset_number, trx_id, trx_line_id, entity_code,
event_class_code, trx_level_type, tax_call_type_code, sty_id,
trx_business_category, tax_line_status_code, sel_id, reported_yn,
line_name, application_id, tax_reporting_flag, default_taxation_country,
product_category, user_defined_fisc_class, line_intended_use,
tax_classification_code, inventory_item_id, bill_to_cust_acct_id,
org_id, legal_entity_id, line_amt, assessable_value, total_tax,
product_type, product_fisc_classification, trx_date, provnl_tax_determination_date,
try_id, ship_to_location_id, ship_to_party_site_id, ship_to_party_id,
bill_to_party_site_id, bill_to_location_id, bill_to_party_id,
ship_to_cust_acct_site_use_id, bill_to_cust_acct_site_use_id,
trx_currency_code, currency_conversion_date, currency_conversion_rate,
currency_conversion_type
FROM okl_tax_sources
WHERE khr_id = cp_khr_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS
AND application_id = G_OKL_APPLICATION_ID
AND trx_level_type = G_TRX_LEVEL_TYPE
AND ADJUSTED_DOC_TRX_ID IS NULL
AND ADJUSTED_DOC_TRX_LINE_ID IS NULL;
SELECT id, kle_id
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND khr_id = cp_khr_id
AND tax_call_type_code = G_UPFRONT_TAX
AND tax_line_status_code = G_ACTIVE_STATUS;
SELECT orig_system_id1
FROM okc_k_lines_b
WHERE id = cp_kle_id;
SELECT id
FROM okc_k_lines_b
WHERE orig_system_id1 = cp_orig_system_id;
l_tax_sources_tbl.DELETE;
l_tax_src_tbl.DELETE;
l_prevtaxsources_tbl.DELETE;
l_tax_sources_tbl(i).program_update_date := NULL;
l_tax_sources_tbl(i).last_updated_by := G_USER_ID;
l_tax_sources_tbl(i).last_update_date := SYSDATE;
l_tax_sources_tbl(i).last_update_login := G_LOGIN_ID;
UPDATE OKL_TAX_SOURCES SET TAX_LINE_STATUS_CODE = G_INACTIVE_STATUS WHERE id = l_tax_src_tbl(i);
INSERT INTO okl_tax_sources VALUES l_tax_sources_tbl(indx);
'Calling "update_tax_sources_total" for updating Total Tax for all Lines in Tax Sources');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id,
p_source_trx_name => p_source_trx_name);
'Calling okl_tax_sources_pub.update_tax_sources for synching back original contract info for all Lines in Tax Sources');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_tbl => l_txsv_tbl
,x_txsv_tbl => lx_txsv_tbl);
SELECT expected_start_date, product_id, interest_disclosed,
transfer_of_title, sale_and_lease_back, purchase_of_lease, usage_category,
age_of_equipment, parent_object_code , parent_object_id, legal_entity_id
FROM okl_lease_quotes_b
WHERE id = cp_trx_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = 'Sales Quote'
AND language = 'US';
SELECT lop.currency_code, lop.cust_acct_id, lop.prospect_id, --> (party id)
lop.org_id, lsq.expected_start_date, lop.currency_conversion_type,
lop.currency_conversion_rate, lop.currency_conversion_date, lsq.line_intended_use,
pdt.name
FROM okl_lease_opportunities_b lop, okl_lease_quotes_b lsq, okl_products pdt
WHERE lsq.parent_object_id = lop.id
AND lsq.parent_object_code = 'LEASEOPP'
AND lsq.product_id = pdt.id
AND lsq.id = cp_id;
SELECT lap.currency_code, lap.cust_acct_id, lap.prospect_id, --> (party id)
lap.org_id, lsq.expected_start_date, lap.currency_conversion_type,
lap.currency_conversion_rate, lap.currency_conversion_date, lsq.line_intended_use,
pdt.name
FROM okl_lease_applications_b lap, okl_lease_quotes_b lsq, okl_products pdt
WHERE lsq.parent_object_id = lap.id
AND lsq.parent_object_code = 'LEASEAPP'
AND lsq.primary_quote = 'Y'
AND lsq.product_id = pdt.id
AND lsq.id = cp_id;
SELECT id
FROM okl_tax_sources
WHERE tax_call_type_code = G_UPFRONT_TAX
AND trx_id = cp_trx_id;
SELECT ID
FROM okl_tax_trx_details
WHERE txs_id = cp_txs_id;
SELECT AST.ID asset_id, asset_number, install_site_id
FROM OKL_ASSETS_B AST
WHERE AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND AST.PARENT_OBJECT_ID = cp_quote_id;
SELECT inv_item_id, supplier_id
FROM okl_asset_components_b
WHERE asset_id = cp_asset_id
AND primary_component = 'Y';
SELECT cfo.source_id , ast.asset_number, caf.sty_id,
sum(nvl((cfl.amount * cfl.number_of_periods),0) + nvl(cfl.stub_amount,0)) taxable_basis
FROM OKL_ASSETS_B ast, okl_cash_flow_objects cfo, okl_cash_flows caf, okl_cash_flow_levels cfl
WHERE AST.PARENT_OBJECT_CODE = 'LEASEQUOTE'
AND AST.PARENT_OBJECT_ID = cp_quote_id
AND ast.id = cfo.source_id
AND cfo.oty_code = 'QUOTED_ASSET'
AND cfo.source_table = 'OKL_ASSETS_B'
AND cfo.id = caf.cfo_id
AND caf.cft_code = 'PAYMENT_SCHEDULE'
AND caf.sts_code IN ('CURRENT', 'WORK')
AND caf.id = cfl.caf_id
GROUP BY cfo.source_id , ast.asset_number, caf.sty_id
UNION
SELECT cfo.source_id , null, caf.sty_id,
sum(nvl((cfl.amount * cfl.number_of_periods),0) + nvl(cfl.stub_amount,0)) taxable_basis
FROM okl_cash_flow_objects cfo, okl_cash_flows caf, okl_cash_flow_levels cfl
WHERE cfo.oty_code = 'LEASE_QUOTE'
AND cfo.source_table = 'OKL_LEASE_QUOTES_B'
AND cfo.source_id = cp_quote_id
AND cfo.id = caf.cfo_id
AND caf.cft_code = 'PAYMENT_SCHEDULE'
AND caf.sts_code IN ('CURRENT', 'WORK')
AND caf.id = cfl.caf_id
GROUP BY cfo.source_id , null, caf.sty_id;
SELECT
b.site_use_id,
c.party_site_id,
c.party_id,
c.location_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c,
hz_parties d
WHERE
a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = 'BILL_TO'
AND d.party_id = cp_party_id
AND a.party_site_id = c.party_site_id
AND c.party_id = d.party_id;
SELECT
b.site_use_id,
c.party_site_id,
c.party_id,
c.location_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c,
hz_parties d
WHERE
a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = 'SHIP_TO'
AND d.party_id = cp_party_id
AND a.party_site_id = c.party_site_id
AND c.party_id = d.party_id;
SELECT fma_id
FROM okl_tax_basis_override
WHERE try_id = cp_try_id
AND org_id = MO_GLOBAL.get_current_org_id;
SELECT name
FROM Okl_formulae_b
WHERE id = cp_fma_id;
SELECT cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE name = 'Invoice-OKL';
SELECT precision, minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = cp_currency_code
AND enabled_flag = 'Y'
AND NVL(start_date_active, sysdate) <= sysdate
AND NVL(end_date_active, sysdate) >= sysdate;
SELECT vendor_site_id
FROM po_vendor_sites
WHERE vendor_id = cp_vendor_id
AND primary_pay_site_flag = 'Y';
SELECT hzp.location_id
FROM HZ_PARTY_SITES hzp, hz_party_site_uses hsu
WHERE hsu.PARTY_SITE_USE_ID = cp_party_site_id
AND hzp.party_Site_id = hsu.party_site_id;
SELECT b.site_use_id, c.party_site_id, c.party_id, c.location_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = 'SHIP_TO'
AND a.party_site_id = c.party_site_id
AND a.cust_account_id = cp_cust_acct_id
AND a.org_id = MO_GLOBAL.get_current_org_id
AND c.party_site_id = cp_inst_loc_id
AND c.location_id = cp_loc_id;
l_prevtaxsources_tbl.DELETE;
l_prevtaxtrxdet_tbl.DELETE;
DELETE FROM OKL_TAX_TRX_DETAILS WHERE id = l_prevtaxtrxdet_tbl(i);
DELETE FROM OKL_TAX_SOURCES WHERE id = l_prevtaxsources_tbl(i);
'Deleted prev tax sources and lines for this sales quote ' );
l_quoteassets_tbl.DELETE;
l_q_asset_cashflows_tbl.DELETE;
INSERT INTO okl_tax_sources VALUES lx_tax_sources_tbl(indx);
'Insert into okl_tax_sources ');
'Calling "update_tax_sources_total" .. ');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_source_trx_id,
p_source_trx_name => p_source_trx_name );
SELECT tax_upfront_yn, tax_invoice_yn
FROM OKL_SYSTEM_PARAMS_ALL
WHERE org_id = cp_org_id;
SELECT rul.rule_information5
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okl_trx_requests trx
WHERE rgp.dnz_chr_id = trx.dnz_khr_id
AND rul.rgp_id = rgp.id
AND rgp.rgd_code = 'LAHDTX'
AND rul.rule_information_category = 'LASTPR'
AND trx.id = cp_trx_id
AND trx.request_type_code = 'TAX_SCHEDULES';
SELECT cle.dnz_chr_id
FROM okl_trx_assets trx,
okl_txl_itm_insts inst,
okc_k_lines_b cle
WHERE trx.tas_type = 'ALG'
AND inst.tas_id = trx.ID
AND cle.ID = inst.kle_id
AND trx.id = p_trx_id;
SELECT chr.ID
FROM okl_trx_contracts trx,
okc_k_headers_b chr
WHERE chr.ID = trx.khr_id
AND trx.ID = p_trx_id ;
SELECT chr.ID
FROM okc_k_headers_b chr
WHERE chr.orig_system_id1 = p_chr_id
AND chr.sts_code <> 'ABANDONED';
| This procedure is called by update tax common components for setting the
| tax security context before invoking Tax Determinant Lov's
|
| CALLED FROM Tax Common components
|
|
| CALLS PROCEDURES/FUNCTIONS
| OKL_TAX_INTERFACE_PVT.set_tax_security_context
|
|
| PARAMETERS
| p_internal_org_id -- Operatng Unit Identifier
| p_legal_entity_id -- Legal Entity Identifier
| p_transaction_date -- Transaction Date
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 24-JAN-07 RRAVIKIR Created
*=======================================================================*/
PROCEDURE set_tax_security_context(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_internal_org_id IN NUMBER,
p_legal_entity_id IN NUMBER,
p_transaction_date IN DATE) IS
l_api_version CONSTANT NUMBER := 1;
SELECT org_id, trx_line_id, application_id, event_class_code,
entity_code, trx_level_type, khr_id, product_category, user_defined_fisc_class,
trx_business_category, line_intended_use, alc_serialized_yn
FROM okl_tax_sources
WHERE id = cp_tax_sources_id;
SELECT SUM(zx_lines.tax_amt) total_tax
FROM zx_lines
WHERE trx_id = cp_trx_id
AND trx_line_id = cp_trx_line_id
AND application_id = cp_application_id
AND event_class_code = cp_event_class_code
AND entity_code = cp_entity_code
AND trx_level_type = cp_trx_level_type
AND nvl(cancel_flag, 'N') <> 'Y';
'Calling okl_tax_sources_pub.update_tax_sources for Updating Tax determinants');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_rec => l_txsv_rec
,x_txsv_rec => lx_txsv_rec);
'Calling "update_tax_sources_total" for updating Total Tax for all Lines in Tax Sources');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_trx_id,
p_source_trx_name => 'Asset Relocation');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_trx_id);
'Calling okl_tax_sources_pub.update_tax_sources for updating Total Tax for current line in Tax Sources');
okl_tax_sources_pub.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_rec => l_txsv_rec
,x_txsv_rec => lx_txsv_rec);
'Calling "update_tax_sources_total" for updating Total Tax for all Lines in Tax Sources');
update_tax_sources_total(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_trx_id => p_trx_id);
SELECT hdr.id hdr_id, hdr.khr_id, hdr.date_invoiced, hdr.try_id try_id,
hdr.qte_id, hdr.ibt_id , hdr.legal_entity_id, line.id line_id, line.kle_id,
line.isl_id, det.sty_id, det.amount
FROM okl_trx_ar_invoices_b hdr, okl_txl_ar_inv_lns_b line,
okl_txd_ar_ln_dtls_b det
WHERE det.id = cp_det_line_id
AND det.til_id_details = line.id
AND line.tai_id = hdr.id;
SELECT name
FROM okl_trx_types_v
WHERE id = cp_try_id;
SELECT id
FROM okl_trx_types_v
WHERE name = cp_trx_type_name;
SELECT qtp_code, qrs_code, early_termination_yn
FROM okl_trx_quotes_b
WHERE id = cp_qte_id;
SELECT stream_type_purpose
FROM okl_strm_type_b
WHERE id = cp_sty_id;
SELECT lse.lty_code
FROM okc_k_lines_b line, okc_line_styles_b lse
WHERE line.lse_id = lse.id
AND line.id = cp_kle_id;
SELECT name
FROM okc_k_lines_v
WHERE id = cp_line_id;
SELECT cim.object1_id1
FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
WHERE cle.lse_id = lse.id
AND lse.lty_code = cp_line_type
AND cim.cle_id = cle.id
AND cle.id = cp_fee_serviced_asset_line_id;
SELECT inventory_item_id, ship_from_org_id, ship_to_org_id
FROM oe_order_lines_all
WHERE line_id = cp_line_id;
SELECT cim_model.object1_id1,
cim_model.object1_id2
FROM okc_k_items cim_model,
okc_k_lines_b cleb_model,
okc_line_styles_b lseb_model
WHERE cim_model.cle_id = cleb_model.id
AND cleb_model.cle_id = cp_fin_asset_id
AND lseb_model.id = cleb_model.lse_id
AND lseb_model.lty_code = 'ITEM';
SELECT set_of_books_id
FROM ar_system_parameters_all
WHERE org_id = cp_org_id;
SELECT fma_id
FROM okl_tax_basis_override
WHERE try_id = cp_try_id
AND org_id = MO_GLOBAL.get_current_org_id;
SELECT name
FROM okl_formulae_b
WHERE id = cp_fma_id;
SELECT hdr.id hdr_id, line.khr_id, hdr.date_invoiced, hdr.try_id try_id,
hdr.qte_id, hdr.legal_entity_id, line.id line_id, line.kle_id,
line.sty_id, line.amount, hdr.ipvs_id
FROM okl_trx_ap_invoices_b hdr, okl_txl_ap_inv_lns_b line
WHERE line.id = cp_line_id
AND line.tap_id = hdr.id;
SELECT vendor_id
FROM po_vendor_sites
WHERE vendor_site_id = cp_vendor_site_id;
SELECT classification_name
FROM zx_fc_intended_use_v
WHERE classification_code = p_intend_use_code;
SELECT id, org_id, application_id, entity_code, event_class_code
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND application_id = G_OKL_APPLICATION_ID
AND TAX_CALL_TYPE_CODE = G_UPFRONT_TAX
AND TAX_LINE_STATUS_CODE = G_ACTIVE_STATUS;
SELECT id, org_id, entity_code, event_class_code
FROM okl_tax_sources
WHERE trx_id = cp_trx_id
AND TAX_CALL_TYPE_CODE = G_UPFRONT_TAX
AND TAX_LINE_STATUS_CODE = G_ACTIVE_STATUS;
SELECT '1'
FROM okl_trx_contracts a , okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
SELECT '1'
FROM okl_trx_contracts_all a , okl_trx_types_tl b
WHERE a.id = cp_trx_id
AND a.try_id = b.id
AND b.name = cp_trx_type_name
AND language = 'US';
UPDATE okl_tax_sources SET tax_line_status_code = G_CANCELLED_STATUS
WHERE id = l_taxsources_prbk_rec.id;
IF (i > 1 AND p_source_trx_name = 'Rebook') THEN -- Update Tax Sources
IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_PROCESS_SALES_TAX_PVT.cancel_document_tax.',
'Calling OKL_TAX_SOURCES_PUB.update_tax_sources ');
OKL_TAX_SOURCES_PUB.update_tax_sources(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txsv_tbl => l_txsv_tbl
,x_txsv_tbl => lx_txsv_tbl);
OKL_TAX_INTERFACE_PVT.update_document(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_transaction_rec => l_transaction_rec);
END IF; -- Update Tax Sources