[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trx_line_dist_id,
sum(decode(recoverable_flag, 'Y', rec_nrec_tax_amt_funcl_curr, 0)) as recov_tax,
sum(decode(recoverable_flag, 'N', rec_nrec_tax_amt_funcl_curr, 0)) as non_recov_tax
FROM ZX_REC_NREC_DIST_GT
GROUP BY trx_line_dist_id;
DELETE ZX_TRX_HEADERS_GT;
l_log_msg := 'Deleted ZX_TRX_HEADERS_GT';
INSERT INTO ZX_TRX_HEADERS_GT (internal_organization_id,
icx_session_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
ledger_id,
legal_entity_id,
rounding_bill_to_party_id,
default_taxation_country,
quote_flag,
document_sub_type)
VALUES(p_tax_head_tbl(1).org_id,
p_tax_head_tbl(1).icx_session_id,
201,
'REQUISITION',
'REQUISITION',
'REQ_CREATED',
p_tax_head_tbl(1).trx_id,
p_tax_head_tbl(1).trx_date,
p_tax_head_tbl(1).ledger_id,
l_legal_entity_id,
p_tax_head_tbl(1).rndg_bill_to_party_id,
p_tax_head_tbl(1).taxation_country,
'Y',
p_tax_head_tbl(1).doc_subtype);
l_log_msg := 'Inserted into ZX_TRX_HEADERS_GT';
DELETE ZX_TRANSACTION_LINES_GT;
l_log_msg := 'Deleted from ZX_TRANSACTION_LINES_GT';
INSERT INTO ZX_TRANSACTION_LINES_GT( application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_class,
line_level_action,
trx_line_type,
trx_line_date,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
line_amt_includes_tax_flag,
line_amt,
trx_line_quantity,
unit_price,
product_id,
product_fisc_classification,
product_org_id,
uom_code,
product_type,
product_code,
product_category,
ship_to_party_id,
ship_from_party_id,
bill_to_party_id,
bill_from_party_id,
ship_from_party_site_id,
bill_from_party_site_id,
ship_to_location_id,
ship_from_location_id,
bill_to_location_id,
ship_third_pty_acct_id,
ship_third_pty_acct_site_id,
assessable_value,
historical_flag,
trx_line_currency_code,
trx_line_currency_conv_date,
trx_line_currency_conv_rate,
trx_line_currency_conv_type,
trx_line_mau,
trx_line_precision,
historical_tax_code_id,
input_tax_classification_code,
user_upd_det_factors_flag,
account_ccid,
bill_From_location_id
)
VALUES(201,
'REQUISITION',
'REQUISITION',
p_tax_line_tbl(i).trx_id,
'LINE',
p_tax_line_tbl(i).trx_line_id,
'INVOICE',
'CREATE',
'ITEM',
p_tax_line_tbl(i).trx_line_date,
p_tax_line_tbl(i).trx_business_cat,
p_tax_line_tbl(i).line_intended_use,
p_tax_line_tbl(i).user_fiscal_class,
'N',
p_tax_line_tbl(i).line_amt,
p_tax_line_tbl(i).trx_line_quantity,
p_tax_line_tbl(i).unit_price,
p_tax_line_tbl(i).product_id,
p_tax_line_tbl(i).prod_fiscal_class,
p_tax_line_tbl(i).product_org_id,
p_tax_line_tbl(i).uom_code,
p_tax_line_tbl(i).product_type,
p_tax_line_tbl(i).product_code,
p_tax_line_tbl(i).product_category,
p_tax_line_tbl(i).ship_to_party_id,
p_tax_line_tbl(i).ship_from_party_id,
p_tax_line_tbl(i).bill_to_party_id,
p_tax_line_tbl(i).bill_from_party_id,
p_tax_line_tbl(i).ship_from_party_site_id,
p_tax_line_tbl(i).bill_from_party_site_id,
p_tax_line_tbl(i).ship_to_location_id,
(SELECT hzps.location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id),
p_tax_line_tbl(i).bill_to_location_id,
p_tax_line_tbl(i).ship_third_pty_id,
p_tax_line_tbl(i).ship_third_pty_site_id,
p_tax_line_tbl(i).assessable_value,
'N',
p_tax_line_tbl(i).currency_code,
p_tax_line_tbl(i).currency_date,
p_tax_line_tbl(i).currency_rate,
p_tax_line_tbl(i).currency_type,
p_tax_line_tbl(i).currency_min_unit,
p_tax_line_tbl(i).currency_precision,
p_tax_line_tbl(i).historical_tax_id,
p_tax_line_tbl(i).input_tax_class,
p_tax_line_tbl(i).user_override_flag,
acc_ccid,
(SELECT hzps.location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id) /* 8680577 - Passing Location Id as Bill From Location Id */
);
l_log_msg := 'Inserted record into ZX_TRANSACTION_LINES_GT.';
DELETE ZX_ITM_DISTRIBUTIONS_GT;
l_log_msg := 'Deleted ZX_ITM_DISTRIBUTIONS_GT';
INSERT INTO ZX_ITM_DISTRIBUTIONS_GT (application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
trx_line_dist_id,
dist_level_action,
trx_line_dist_date,
item_dist_number,
dist_intended_use,
task_id,
award_id,
project_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
trx_line_dist_amt,
trx_line_dist_qty,
trx_line_quantity,
account_ccid,
historical_flag,
OVERRIDING_RECOVERY_RATE)
VALUES(201,
'REQUISITION',
'REQUISITION',
p_tax_dist_tbl(i).trx_id,
p_tax_dist_tbl(i).trx_line_id,
'LINE',
p_tax_dist_tbl(i).trx_line_dist_id,
'CREATE',
p_tax_dist_tbl(i).trx_line_dist_date,
p_tax_dist_tbl(i).item_dist_number,
p_tax_dist_tbl(i).intended_use,
p_tax_dist_tbl(i).task_id,
p_tax_dist_tbl(i).award_id,
p_tax_dist_tbl(i).project_id,
p_tax_dist_tbl(i).expenditure_type,
p_tax_dist_tbl(i).expenditure_org_id,
p_tax_dist_tbl(i).expenditure_item_date,
p_tax_dist_tbl(i).trx_line_dist_amt,
p_tax_dist_tbl(i).trx_line_dist_quantity,
p_tax_dist_tbl(i).trx_line_quantity,
p_tax_dist_tbl(i).account_ccid,
'N',
p_tax_dist_tbl(i).recovery_rate_override);
l_log_msg := 'Inserted record into ZX_ITM_DISTRIBUTIONS_GT. DistId=' || p_tax_dist_tbl(i).trx_line_dist_id;
SELECT default_taxation_country,
document_sub_type
FROM ZX_TRX_HEADERS_GT;
SELECT TRX_LINE_ID,
INPUT_TAX_CLASSIFICATION_CODE,
TRX_BUSINESS_CATEGORY,
PRODUCT_FISC_CLASSIFICATION,
PRODUCT_TYPE,
LINE_INTENDED_USE,
USER_DEFINED_FISC_CLASS,
ASSESSABLE_VALUE,
PRODUCT_CATEGORY,
USER_UPD_DET_FACTORS_FLAG
FROM ZX_TRANSACTION_LINES_GT;
DELETE ZX_TRX_HEADERS_GT;
DELETE ZX_TRANSACTION_LINES_GT;
l_log_msg := 'Inserting into ZX_TRX_HEADERS_GT';
INSERT INTO ZX_TRX_HEADERS_GT (internal_organization_id,
icx_session_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
ledger_id,
legal_entity_id,
rounding_bill_to_party_id,
default_taxation_country,
quote_flag,
document_sub_type)
VALUES(p_tax_head_tbl(1).org_id,
p_tax_head_tbl(1).icx_session_id,
201,
'REQUISITION',
'REQUISITION',
'REQ_CREATED',
p_tax_head_tbl(1).trx_id,
p_tax_head_tbl(1).trx_date,
p_tax_head_tbl(1).ledger_id,
l_legal_entity_id,
p_tax_head_tbl(1).rndg_bill_to_party_id,
NULL,
'Y',
NULL);
l_log_msg := 'Inserting into ZX_TRANSACTION_LINES_GT';
IF p_tax_line_tbl(i).tax_attribute_update_code IS NULL THEN
Begin
SELECT 'UPDATE'
INTO l_line_level_action
FROM ZX_LINES_DET_FACTORS
WHERE trx_id = p_tax_line_tbl(i).trx_id
AND trx_line_id = p_tax_line_tbl(i).trx_line_id
AND USER_UPD_DET_FACTORS_FLAG = 'Y';
l_line_level_action := p_tax_line_tbl(i).tax_attribute_update_code;
INSERT INTO ZX_TRANSACTION_LINES_GT(application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_class,
line_level_action,
trx_line_type,
trx_line_date,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
line_amt_includes_tax_flag,
line_amt,
trx_line_quantity,
unit_price,
product_id,
product_fisc_classification,
product_org_id,
uom_code,
product_type,
product_code,
product_category,
ship_to_party_id,
ship_from_party_id,
bill_to_party_id,
bill_from_party_id,
ship_from_party_site_id,
bill_from_party_site_id,
ship_to_location_id,
ship_from_location_id,
bill_to_location_id,
ship_third_pty_acct_id,
ship_third_pty_acct_site_id,
assessable_value,
historical_flag,
trx_line_currency_code,
trx_line_currency_conv_date,
trx_line_currency_conv_rate,
trx_line_currency_conv_type,
trx_line_mau,
trx_line_precision,
historical_tax_code_id,
input_tax_classification_code,
user_upd_det_factors_flag,
DEFAULTING_ATTRIBUTE1,
Bill_From_location_id
)
VALUES(201,
'REQUISITION',
'REQUISITION',
p_tax_line_tbl(i).trx_id,
'LINE',
p_tax_line_tbl(i).trx_line_id,
'INVOICE',
l_line_level_action,
'ITEM',
p_tax_line_tbl(i).trx_line_date,
NULL,
NULL,
NULL,
'N',
p_tax_line_tbl(i).line_amt,
p_tax_line_tbl(i).trx_line_quantity,
p_tax_line_tbl(i).unit_price,
p_tax_line_tbl(i).product_id,
NULL,
p_tax_line_tbl(i).product_org_id,
p_tax_line_tbl(i).uom_code,
NULL,
p_tax_line_tbl(i).product_code,
NULL,
p_tax_line_tbl(i).ship_to_party_id,
p_tax_line_tbl(i).ship_from_party_id,
p_tax_line_tbl(i).bill_to_party_id,
p_tax_line_tbl(i).bill_from_party_id,
p_tax_line_tbl(i).ship_from_party_site_id,
p_tax_line_tbl(i).bill_from_party_site_id,
p_tax_line_tbl(i).ship_to_location_id,
(SELECT hzps.location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id),
p_tax_line_tbl(i).bill_to_location_id,
p_tax_line_tbl(i).ship_third_pty_id,
p_tax_line_tbl(i).ship_third_pty_site_id,
NULL,
'N',
p_tax_line_tbl(i).currency_code,
p_tax_line_tbl(i).currency_date,
p_tax_line_tbl(i).currency_rate,
p_tax_line_tbl(i).currency_type,
p_tax_line_tbl(i).currency_min_unit,
p_tax_line_tbl(i).currency_precision,
p_tax_line_tbl(i).historical_tax_id,
NULL,
NULL,
p_tax_line_tbl(i).product_org_id,
(SELECT hzps.location_id
FROM hz_party_sites hzps
WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id)
);
PROCEDURE delete_all_tax_attr (p_org_id IN NUMBER,
p_trx_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_all_tax_lines';
l_transaction_rec.event_type_code := 'REQ_DELETED';
l_log_msg := 'ZX global_document_update(+)';
zx_api_pub.global_document_update(p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_rec => l_transaction_rec);
l_log_msg := 'ZX global_document_update returned status=' || l_return_status;
l_log_msg := 'ZX global_document_update(-)';
'Exception at POR_TAX_PVT.delete_all_tax_attr():'
|| ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
|| ' Progress= ' || l_progress || '; ROLLBACK Complete.');
END delete_all_tax_attr;
PROCEDURE insert_line_det_attr (p_tax_info_tbl IN POR_INSERT_TAX_OBJ_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_procedure_name CONSTANT VARCHAR2(30) := 'insert_line_det_attr';
l_transaction_rec.event_type_code := 'REQ_DELETED';
l_log_msg := 'ZX global_document_update(+)';
zx_api_pub.global_document_update(p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_rec => l_transaction_rec);
l_log_msg := 'ZX global_document_update returned status=' || l_return_status;
l_log_msg := 'ZX global_document_update(-)';
SELECT hzps.location_id INTO ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_LOCATION_ID(i)
FROM hz_party_sites hzps
WHERE hzps.party_site_id =p_tax_info_tbl(i).ship_from_party_site_id and rownum =1 ;
SELECT pvs.location_id INTO ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_FROM_LOCATION_ID(i)
from hz_party_sites pvs /* - Passing Location Id as Bill From Location Id */
WHERE pvs.party_site_id =p_tax_info_tbl(i).ship_from_party_site_id and rownum =1 ;
l_log_msg := 'Inserted record into ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl for trx_line_id=' || p_tax_info_tbl(1).trx_line_id;
l_log_msg := 'ZX insert_line_det_factors(+)';
zx_api_pub.insert_line_det_factors(p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_log_msg := 'ZX insert_line_det_factors returned status=' || l_return_status;
l_log_msg := 'ZX insert_line_det_factors(-)';
'Exception at POR_TAX_PVT.insert_line_det_attr():'
|| ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
|| ' Progress= ' || l_progress || '; ROLLBACK Complete.');
END insert_line_det_attr;
l_log_msg := 'Inserted record into ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl';
zx_api_pub.copy_insert_line_det_factors(p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);