[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete_flag VARCHAR2_1_tbl_type
);
PROCEDURE PERFORM_UPDATE (
p_tax_lines_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.tax_lines_tbl_type,
p_currency_tab IN OUT NOCOPY ZX_TAX_PARTNER_PKG.tax_currencies_tbl_type,
x_return_status OUT NOCOPY VARCHAR2);
SELECT
internal_organization_id ,
document_type_id ,
transaction_id ,
application_code ,
document_level_action ,
trx_date ,
trx_currency_code ,
/* Bug 5090593:
quote_flag ,
*/
legal_entity_number ,
establishment_number ,
transaction_number ,
transaction_description ,
document_sequence_value ,
transaction_due_date ,
/* Bug 5090593:
transaction_solution_origin ,
*/
allow_tax_calculation ,
transaction_line_id ,
trx_level_type ,
line_level_action ,
line_class ,
transaction_shipping_date ,
transaction_receipt_date ,
transaction_line_type ,
transaction_line_date ,
trx_business_category ,
line_intended_use ,
line_amt_includes_tax_flag ,
line_amount ,
other_inclusive_tax_amount ,
transaction_line_quantity ,
unit_price ,
cash_discount ,
volume_discount ,
trading_discount ,
transportation_charge ,
insurance_charge ,
other_charge ,
product_id ,
uom_code ,
product_type ,
product_code ,
fob_point ,
ship_to_party_number ,
ship_to_party_name ,
ship_from_party_number ,
ship_from_party_name ,
ship_to_loc_id , -- Bug 5090593
ship_to_geography_type1 ,
ship_to_geography_value1 ,
ship_to_geography_type2 ,
ship_to_geography_value2 ,
ship_to_geography_type3 ,
ship_to_geography_value3 ,
ship_to_geography_type4 ,
ship_to_geography_value4 ,
ship_to_geography_type5 ,
ship_to_geography_value5 ,
ship_to_geography_type6 ,
ship_to_geography_value6 ,
ship_to_geography_type7 ,
ship_to_geography_value7 ,
ship_to_geography_type8 ,
ship_to_geography_value8 ,
ship_to_geography_type9 ,
ship_to_geography_value9 ,
ship_to_geography_type10 ,
ship_to_geography_value10 ,
ship_from_loc_id , -- Bug 5090593
ship_from_geography_type1 ,
ship_from_geography_value1 ,
ship_from_geography_type2 ,
ship_from_geography_value2 ,
ship_from_geography_type3 ,
ship_from_geography_value3 ,
ship_from_geography_type4 ,
ship_from_geography_value4 ,
ship_from_geography_type5 ,
ship_from_geography_value5 ,
ship_from_geography_type6 ,
ship_from_geography_value6 ,
ship_from_geography_type7 ,
ship_from_geography_value7 ,
ship_from_geography_type8 ,
ship_from_geography_value8 ,
ship_from_geography_type9 ,
ship_from_geography_value9 ,
ship_from_geography_type10 ,
ship_from_geography_value10 ,
poa_loc_id , -- Bug 5090593
poa_geography_type1 ,
poa_geography_value1 ,
poa_geography_type2 ,
poa_geography_value2 ,
poa_geography_type3 ,
poa_geography_value3 ,
poa_geography_type4 ,
poa_geography_value4 ,
poa_geography_type5 ,
poa_geography_value5 ,
poa_geography_type6 ,
poa_geography_value6 ,
poa_geography_type7 ,
poa_geography_value7 ,
poa_geography_type8 ,
poa_geography_value8 ,
poa_geography_type9 ,
poa_geography_value9 ,
poa_geography_type10 ,
poa_geography_value10 ,
poo_loc_id , -- Bug 5090593
poo_geography_type1 ,
poo_geography_value1 ,
poo_geography_type2 ,
poo_geography_value2 ,
poo_geography_type3 ,
poo_geography_value3 ,
poo_geography_type4 ,
poo_geography_value4 ,
poo_geography_type5 ,
poo_geography_value5 ,
poo_geography_type6 ,
poo_geography_value6 ,
poo_geography_type7 ,
poo_geography_value7 ,
poo_geography_type8 ,
poo_geography_value8 ,
poo_geography_type9 ,
poo_geography_value9 ,
poo_geography_type10 ,
poo_geography_value10 ,
bill_to_party_number ,
bill_to_party_name ,
bill_from_party_number ,
bill_from_party_name ,
bill_to_loc_id , -- Bug 5090593
bill_to_geography_type1 ,
bill_to_geography_value1 ,
bill_to_geography_type2 ,
bill_to_geography_value2 ,
bill_to_geography_type3 ,
bill_to_geography_value3 ,
bill_to_geography_type4 ,
bill_to_geography_value4 ,
bill_to_geography_type5 ,
bill_to_geography_value5 ,
bill_to_geography_type6 ,
bill_to_geography_value6 ,
bill_to_geography_type7 ,
bill_to_geography_value7 ,
bill_to_geography_type8 ,
bill_to_geography_value8 ,
bill_to_geography_type9 ,
bill_to_geography_value9 ,
bill_to_geography_type10 ,
bill_to_geography_value10 ,
bill_from_loc_id , -- Bug 5090593
bill_from_geography_type1 ,
bill_from_geography_value1 ,
bill_from_geography_type2 ,
bill_from_geography_value2 ,
bill_from_geography_type3 ,
bill_from_geography_value3 ,
bill_from_geography_type4 ,
bill_from_geography_value4 ,
bill_from_geography_type5 ,
bill_from_geography_value5 ,
bill_from_geography_type6 ,
bill_from_geography_value6 ,
bill_from_geography_type7 ,
bill_from_geography_value7 ,
bill_from_geography_type8 ,
bill_from_geography_value8 ,
bill_from_geography_type9 ,
bill_from_geography_value9 ,
bill_from_geography_type10 ,
bill_from_geography_value10 ,
account_ccid ,
--applied_from_document_type_id ,
applied_from_transaction_id ,
applied_from_line_id ,
applied_from_trx_level_type,
applied_from_doc_number ,
adjusted_doc_document_type_id ,
adjusted_doc_transaction_id ,
adjusted_doc_line_id ,
adjusted_doc_number ,
adjusted_doc_trx_level_type,
adjusted_doc_date ,
assessable_value ,
trx_line_number ,
trx_line_description ,
product_description ,
header_char1 ,
header_char2 ,
header_char3 ,
header_char4 ,
header_char5 ,
header_char6 ,
header_char7 ,
header_char8 ,
header_char9 ,
header_char10 ,
header_char11 ,
header_char12 ,
header_char13 ,
header_char14 ,
header_char15 ,
header_numeric1 ,
header_numeric2 ,
header_numeric3 ,
header_numeric4 ,
header_numeric5 ,
header_numeric6 ,
header_numeric7 ,
header_numeric8 ,
header_numeric9 ,
header_numeric10 ,
header_date1 ,
header_date2 ,
header_date3 ,
header_date4 ,
header_date5 ,
line_char1 ,
line_char2 ,
line_char3 ,
line_char4 ,
line_char5 ,
line_char6 ,
line_char7 ,
line_char8 ,
line_char9 ,
line_char10 ,
line_char11 ,
line_char12 ,
line_char13 ,
line_char14 ,
line_char15 ,
line_numeric1 ,
line_numeric2 ,
line_numeric3 ,
line_numeric4 ,
line_numeric5 ,
line_numeric6 ,
line_numeric7 ,
line_numeric8 ,
line_numeric9 ,
line_numeric10 ,
line_date1 ,
line_date2 ,
line_date3 ,
line_date4 ,
line_date5 ,
exempt_certificate_number ,
exempt_reason ,
exemption_control_flag
From ZX_O2C_CALC_TXN_INPUT_V;
SELECT count(*)
INTO l_cnt_of_options_gt
FROM ZX_TRX_PRE_PROC_OPTIONS_GT;
SELECT count(*)
INTO l_cnt_of_options_gt
FROM ZX_TRX_PRE_PROC_OPTIONS_GT ztppo
, ZX_LINES_DET_FACTORS zldf
WHERE ztppo.application_id = zldf.application_id
AND ztppo.entity_code = zldf.entity_code
AND ztppo.event_class_code = zldf.event_class_code
AND ztppo.trx_id = zldf.trx_id;
SELECT count(*)
INTO l_cnt_of_hdr_extns_gt
FROM ZX_PRVDR_HDR_EXTNS_GT;
SELECT count(*)
INTO l_cnt_of_line_extns_gt
FROM ZX_PRVDR_LINE_EXTNS_GT;
SELECT count(*)
INTO l_cnt_of_loc_info_gt
FROM ZX_PTNR_LOCATION_INFO_GT;
SELECT count(*)
INTO l_cnt_of_ptnr_neg_line_gt
FROM ZX_PTNR_NEG_LINE_GT;
SELECT distinct application_id,entity_code,event_class_code,
trx_id,trx_level_type,trx_line_id
INTO l_application_id,l_entity_code,l_event_class_code,
l_trx_id,l_trx_level_type,l_trx_line_id
FROM ZX_PRVDR_LINE_EXTNS_GT where rownum = 1;
SELECT output_tax_classification_code,receivables_trx_type_id,internal_organization_id, ship_to_location_id,
bill_to_location_id
INTO l_tax_code, l_trx_type_id, l_org_id, l_ship_to_location_id, l_bill_to_location_id
FROM zx_lines_det_factors
WHERE application_id = l_application_id
and entity_code = l_entity_code
and event_class_code = l_event_class_code
and trx_id = l_trx_id
and trx_line_id = l_trx_line_id
and trx_level_type = l_trx_level_type;
select tax_type_code INTO l_tax_type -- from zx_taxes_b zx, zx_rates_b zr
from zx_sco_taxes zx, -- Changed from clause to use sco views not base tables
zx_sco_rates zr
where zx.tax = zr.tax
AND zx.tax_regime_code = zr.tax_regime_code
AND zr.tax_rate_code = l_tax_code
AND zx.live_for_processing_flag = 'Y' --Bug 7594634
AND rownum = 1;
SELECT tax_calculation_flag into l_tax_flag
FROM ra_cust_trx_types_all rtt
WHERE rtt.cust_trx_type_id = l_trx_type_id
AND org_id = l_org_id;
select record_for_partners_flag,partner_processing_flag
into l_record_for_partners_flag,l_partner_processing_flag
from ZX_TRX_PRE_PROC_OPTIONS_GT
where rownum=1;
delete from zx_jurisdictions_gt;
' Selecting document type for event_class_mapping id '||pg_doc_type_id_tab(i));
select event_class_code, application_id, entity_code, event_class_code
into l_document_type, l_application_id, l_entity_code, l_event_class_code
from zx_evnt_cls_mappings
where event_class_mapping_id = pg_doc_type_id_tab(i);
SELECT SUM(ABS(line_amount))
INTO l_doc_amount
FROM ZX_O2C_CALC_TXN_INPUT_V
WHERE transaction_id = l_doc_trx_id;
IF pg_doc_level_action_tab(i) not in ('CREATE', 'QUOTE', 'UPDATE') THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Unknown header level action' );
select line_level_action into l_line_action
from zx_lines_det_factors
where application_id = l_application_id
and entity_code = l_entity_code
and event_class_code = l_event_class_code
and trx_id = pg_trx_id_tab(I)
and trx_line_id = pg_trx_line_id_tab(I);
ELSIF (pg_doc_level_action_tab(i) = 'UPDATE') then
Perform_update(l_tax_lines_tbl,p_currency_tab,l_return_status);
g_string :='Failed in creating update';
DELETE FROM zx_ptnr_neg_line_gt
WHERE event_class_mapping_id = pg_doc_type_id_tab(I)
and trx_id = pg_trx_id_tab(I)
and trx_line_id = pg_trx_line_id_tab(I)
and trx_level_type = pg_trx_level_type_tab(I);
elsif(pg_doc_level_action_tab(i) = 'UPDATE') Then
if(pg_line_level_action_tab(i) NOT IN ('CREATE', 'UPDATE', 'QUOTE', 'CANCEL', 'DELETE', 'SYNCHRONIZE','RECORD_WITH_NO_TAX')) Then
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Unknown line level action');
PROCEDURE PERFORM_UPDATE (p_tax_lines_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.tax_lines_tbl_type,
p_currency_tab IN OUT NOCOPY ZX_TAX_PARTNER_PKG.tax_currencies_tbl_type,
x_return_status OUT NOCOPY VARCHAR2) is
l_api_name CONSTANT VARCHAR2(30) := 'PERFORM_UPDATE';
elsif(pg_line_level_action_tab(i) in ('UPDATE')) Then
/*First make contra entry*/
BEGIN
SELECT APPLICATION_ID
INTO l_application_id
FROM ZX_PRVDR_LINE_EXTNS_GT
WHERE ROWNUM = 1;
SELECT Count(*)
INTO l_ver_count
FROM ZX_LINES
WHERE APPLICATION_ID = l_application_id
AND TRX_ID = pg_trx_id_tab(I)
AND TRX_LINE_ID = pg_trx_line_id_tab(I)
AND TAX_PROVIDER_ID IS NOT NULL;
elsif(pg_line_level_action_tab(i) in ('DELETE','CANCEL')) Then
l_line_level_action := pg_line_level_action_tab(i);
End PERFORM_UPDATE;
SELECT tax_jurisdiction_code
INTO x_tax_jurisdiction_code
FROM (
SELECT tax_jurisdiction_code
FROM zx_jurisdictions_gt
WHERE tax_regime_code = p_regime_code
AND tax = p_tax
AND substr(tax_jurisdiction_code, 4) BETWEEN '000000000' and '999999999'
AND precedence_level = (SELECT max(precedence_level)
FROM zx_jurisdictions_gt
WHERE tax_regime_code = p_regime_code
AND substr(tax_jurisdiction_code, 4) BETWEEN '000000000' and '999999999'
AND tax = p_tax)
ORDER BY tax_jurisdiction_code)
WHERE rownum = 1 ;
IF (l_line_level_action IN ('CREATE','DELETE','CANCEL')) THEN
IF substrb(nvl(pg_Line_char10_tab(I),'XXXXXXXXX'), 2,9)='XXXXXXXXX' THEN
inv_in_rec.fJurisSFGeoCd := null;
IF (l_line_level_action IN ('CREATE', 'DELETE')) THEN
inv_in_rec.fTDMCustCd := pg_Line_char12_tab(I);
SELECT pty.party_number
INTO inv_in_rec.fTDMCustCd
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
AND ptp.party_id = pty.party_id;
ELSE /*Line level action is delete*/
SELECT line_amt,
trx_line_quantity,
trx_id,
trx_number,
ship_to_geography_type1,
ship_to_geography_value1,
ship_to_geography_type2,
ship_to_geography_value2,
ship_to_geography_type3,
ship_to_geography_value3,
ship_to_geography_type4,
ship_to_geography_value4,
ship_to_geography_type5,
ship_to_geography_value5,
ship_to_geography_type6,
ship_to_geography_value6,
ship_to_geography_type7,
ship_to_geography_value7,
ship_to_geography_type8,
ship_to_geography_value8,
ship_to_geography_type9,
ship_to_geography_value9,
ship_to_geography_type10,
ship_to_geography_value10,
ship_from_geography_type1,
ship_from_geography_value1,
ship_from_geography_type2,
ship_from_geography_value2,
ship_from_geography_type3,
ship_from_geography_value3,
ship_from_geography_type4,
ship_from_geography_value4,
ship_from_geography_type5,
ship_from_geography_value5,
ship_from_geography_type6,
ship_from_geography_value6,
ship_from_geography_type7,
ship_from_geography_value7,
ship_from_geography_type8,
ship_from_geography_value8,
ship_from_geography_type9,
ship_from_geography_value9,
ship_from_geography_type10,
ship_from_geography_value10,
/*poa_geography_type1,
poa_geography_value1,
poa_geography_type2,
poa_geography_value2,
poa_geography_type3,
poa_geography_value3,
poa_geography_type4,
poa_geography_value4,
poa_geography_type5,
poa_geography_value5,
poa_geography_type6,
poa_geography_value6,
poa_geography_type7,
poa_geography_value7,
poa_geography_type8,
poa_geography_value8,
poa_geography_type9,
poa_geography_value9,
poa_geography_type10,
poa_geography_value10,
poo_geography_type1,
poo_geography_value1,
poo_geography_type2,
poo_geography_value2,
poo_geography_type3,
poo_geography_value3,
poo_geography_type4,
poo_geography_value4,
poo_geography_type5,
poo_geography_value5,
poo_geography_type6,
poo_geography_value6,
poo_geography_type7,
poo_geography_value7,
poo_geography_type8,
poo_geography_type9,
poo_geography_value9,
poo_geography_type10,
poo_geography_value10,*/
bill_to_geography_type1,
bill_to_geography_value1,
bill_to_geography_type2,
bill_to_geography_value2,
bill_to_geography_type3,
bill_to_geography_value3,
bill_to_geography_type4,
bill_to_geography_value4,
bill_to_geography_type5,
bill_to_geography_value5,
bill_to_geography_type6,
bill_to_geography_value6,
bill_to_geography_type7,
bill_to_geography_value7,
bill_to_geography_type8,
bill_to_geography_value8,
bill_to_geography_type9,
bill_to_geography_value9,
bill_to_geography_type10,
bill_to_geography_value10,
bill_from_geography_type1,
bill_from_geography_value1,
bill_from_geography_type2,
bill_from_geography_value2,
bill_from_geography_type3,
bill_from_geography_value3,
bill_from_geography_type4,
bill_from_geography_value4,
bill_from_geography_type5,
bill_from_geography_value5,
bill_from_geography_type6,
bill_from_geography_value6,
bill_from_geography_type7,
bill_from_geography_value7,
bill_from_geography_type8,
bill_from_geography_value8,
bill_from_geography_type9,
bill_from_geography_value9,
bill_from_geography_type10,
bill_from_geography_value10,
LINE_EXT_VARCHAR_ATTRIBUTE1,
LINE_EXT_VARCHAR_ATTRIBUTE2,
LINE_EXT_VARCHAR_ATTRIBUTE3,
LINE_EXT_VARCHAR_ATTRIBUTE4,
LINE_EXT_VARCHAR_ATTRIBUTE5,
LINE_EXT_VARCHAR_ATTRIBUTE6,
LINE_EXT_VARCHAR_ATTRIBUTE7,
LINE_EXT_VARCHAR_ATTRIBUTE8,
LINE_EXT_VARCHAR_ATTRIBUTE9,
LINE_EXT_VARCHAR_ATTRIBUTE10,
LINE_EXT_VARCHAR_ATTRIBUTE11,
LINE_EXT_VARCHAR_ATTRIBUTE12,
LINE_EXT_VARCHAR_ATTRIBUTE13,
LINE_EXT_VARCHAR_ATTRIBUTE14,
LINE_EXT_VARCHAR_ATTRIBUTE15,
LINE_EXT_NUMBER_ATTRIBUTE1,
LINE_EXT_NUMBER_ATTRIBUTE2,
LINE_EXT_NUMBER_ATTRIBUTE3,
LINE_EXT_NUMBER_ATTRIBUTE4,
LINE_EXT_DATE_ATTRIBUTE1,
adjusted_doc_date,
trx_date,
exemption_control_flag
INTO arp_line_amount,
arp_quantity,
arp_trx_id,
arp_trx_number,
arp_ship_to_grphy_type1,
arp_ship_to_grphy_value1,
arp_ship_to_grphy_type2,
arp_ship_to_grphy_value2,
arp_ship_to_grphy_type3,
arp_ship_to_grphy_value3,
arp_ship_to_grphy_type4,
arp_ship_to_grphy_value4,
arp_ship_to_grphy_type5,
arp_ship_to_grphy_value5,
arp_ship_to_grphy_type6,
arp_ship_to_grphy_value6,
arp_ship_to_grphy_type7,
arp_ship_to_grphy_value7,
arp_ship_to_grphy_type8,
arp_ship_to_grphy_value8,
arp_ship_to_grphy_type9,
arp_ship_to_grphy_value9,
arp_ship_to_grphy_type10,
arp_ship_to_grphy_value10,
arp_ship_from_grphy_type1,
arp_ship_from_grphy_value1,
arp_ship_from_grphy_type2,
arp_ship_from_grphy_value2,
arp_ship_from_grphy_type3,
arp_ship_from_grphy_value3,
arp_ship_from_grphy_type4,
arp_ship_from_grphy_value4,
arp_ship_from_grphy_type5,
arp_ship_from_grphy_value5,
arp_ship_from_grphy_type6,
arp_ship_from_grphy_value6,
arp_ship_from_grphy_type7,
arp_ship_from_grphy_value7,
arp_ship_from_grphy_type8,
arp_ship_from_grphy_value8,
arp_ship_from_grphy_type9,
arp_ship_from_grphy_value9,
arp_ship_from_grphy_type10,
arp_ship_from_grphy_value10,
/*arp_poa_grphy_type1,
arp_poa_grphy_value1,
arp_poa_grphy_type2,
arp_poa_grphy_value2,
arp_poa_grphy_type3,
arp_poa_grphy_value3,
arp_poa_grphy_type4,
arp_poa_grphy_value4,
arp_poa_grphy_type5,
arp_poa_grphy_value5,
arp_poa_grphy_type6,
arp_poa_grphy_value6,
arp_poa_grphy_type7,
arp_poa_grphy_value7,
arp_poa_grphy_type8,
arp_poa_grphy_value8,
arp_poa_grphy_type9,
arp_poa_grphy_value9,
arp_poa_grphy_type10,
arp_poa_grphy_value10,
arp_poo_grphy_type1,
arp_poo_grphy_value1,
arp_poo_grphy_type2,
arp_poo_grphy_value2,
arp_poo_grphy_type3,
arp_poo_grphy_value3,
arp_poo_grphy_type4,
arp_poo_grphy_value4,
arp_poo_grphy_type5,
arp_poo_grphy_value5,
arp_poo_grphy_type6,
arp_poo_grphy_value6,
arp_poo_grphy_type7,
arp_poo_grphy_value7,
arp_poo_grphy_type8,
arp_poo_grphy_type9,
arp_poo_grphy_value9,
arp_poo_grphy_type10,
arp_poo_grphy_value10,*/
arp_bill_to_grphy_type1,
arp_bill_to_grphy_value1,
arp_bill_to_grphy_type2,
arp_bill_to_grphy_value2,
arp_bill_to_grphy_type3,
arp_bill_to_grphy_value3,
arp_bill_to_grphy_type4,
arp_bill_to_grphy_value4,
arp_bill_to_grphy_type5,
arp_bill_to_grphy_value5,
arp_bill_to_grphy_type6,
arp_bill_to_grphy_value6,
arp_bill_to_grphy_type7,
arp_bill_to_grphy_value7,
arp_bill_to_grphy_type8,
arp_bill_to_grphy_value8,
arp_bill_to_grphy_type9,
arp_bill_to_grphy_value9,
arp_bill_to_grphy_type10,
arp_bill_to_grphy_value10,
arp_bill_from_grphy_type1,
arp_bill_from_grphy_value1,
arp_bill_from_grphy_type2,
arp_bill_from_grphy_value2,
arp_bill_from_grphy_type3,
arp_bill_from_grphy_value3,
arp_bill_from_grphy_type4,
arp_bill_from_grphy_value4,
arp_bill_from_grphy_type5,
arp_bill_from_grphy_value5,
arp_bill_from_grphy_type6,
arp_bill_from_grphy_value6,
arp_bill_from_grphy_type7,
arp_bill_from_grphy_value7,
arp_bill_from_grphy_type8,
arp_bill_from_grphy_value8,
arp_bill_from_grphy_type9,
arp_bill_from_grphy_value9,
arp_bill_from_grphy_type10,
arp_bill_from_grphy_value10,
arp_trx_line_type,
arp_product_code,
arp_cert_num,
arp_state_exempt_reason,
arp_county_exempt_reason,
arp_city_exempt_reason,
arp_district_exempt_rs,
arp_audit_flag,
arp_ship_to_add,
arp_ship_from_add,
arp_poa_add_code,
arp_customer_code,
arp_customer_class,
arp_company_code,
arp_division_code,
arp_state_exempt_percent,
arp_county_exempt_pct,
arp_city_exempt_pct ,
arp_district_exempt_pct,
arp_transaction_date,
arp_adjusted_doc_date,
arp_trx_date,
arp_exemption_control_flag
FROM ZX_PTNR_NEG_LINE_GT
WHERE trx_line_id= pg_trx_line_id_tab(I);
SELECT loc.STATE, loc.COUNTY, loc.CITY, substrb(loc.POSTAL_CODE,1,5)
INTO inv_in_rec.fJurisSTStAbbrv
,inv_in_rec.fJurisSTCoName
,inv_in_rec.fJurisSTCiName
,inv_in_rec.fJurisSTZipCd
FROM hz_locations loc
WHERE loc.location_id = NVL(pg_ship_to_loc_id_tab(I),pg_bill_to_loc_id_tab(I));
IF (l_line_level_action IN ('CREATE','DELETE','CANCEL')) THEN
inv_in_rec.fInvDate := pg_trx_date_tab(i);
SELECT nvl(zd.partner_migrated_flag, 'N') -- Bug 5007293
INTO pg_ugraded_inv_flag_tab(I)
FROM ZX_LINES_DET_FACTORS zd
WHERE zd.event_class_mapping_id = pg_adj_doc_doc_type_id_tab(i)
AND zd.trx_id = pg_adj_doc_trx_id_tab(i)
AND zd.trx_line_id = pg_adj_doc_line_id_tab(i)
AND zd.trx_level_type = pg_adj_doc_trx_lev_type_tab(i)
AND EXISTS (SELECT 'Y'
FROM ZX_LINES zl
WHERE zl.application_id = zd.application_id
AND zl.entity_code = zd.entity_code
AND zl.event_class_code = zd.event_class_code
AND zl.trx_id = pg_adj_doc_trx_id_tab(i)
AND zl.trx_line_id = pg_adj_doc_line_id_tab(i)
AND zl.trx_level_type = pg_adj_doc_trx_lev_type_tab(i)
AND zl.tax = 'LOCATION');
IF (l_line_level_action IN ('CREATE','DELETE','CANCEL')) THEN
inv_in_rec.fInvDate := pg_adj_doc_date_tab(I);
IF (l_line_level_action IN ('CREATE','DELETE','CANCEL')) THEN
inv_in_rec.fInvDate := pg_adj_doc_date_tab(I);
IF (l_line_level_action IN ('CREATE','DELETE','CANCEL')) THEN
inv_in_rec.fInvDate := pg_adj_doc_date_tab(I);
select nvl(ship_to_location_id, bill_to_location_id)
INTO p_location_id
From zx_lines_det_factors
WHERE event_class_mapping_id = pg_doc_type_id_tab(I) and
trx_id = pg_trx_id_tab(I) and
trx_line_id = pg_trx_line_id_tab(I) and
trx_level_type = pg_trx_level_type_tab(I);
select nvl(ship_from_location_id, bill_from_location_id)
INTO p_location_id
From zx_lines_det_factors
WHERE event_class_mapping_id = pg_doc_type_id_tab(I) and
trx_id = pg_trx_id_tab(I) and
trx_line_id = pg_trx_line_id_tab(I) and
trx_level_type = pg_trx_level_type_tab(I);
delete from zx_jurisdictions_gt;
delete from zx_jurisdictions_gt;
SELECT global_attribute2, global_attribute4, global_attribute6, tax_amt,
precision, minimum_accountable_unit, rounding_rule_code
INTO g_StTaxAmt, g_CoTaxAmt, g_CiTaxAmt, g_TotalTaxAmt,
l_precision, l_mau, l_rounding_rule
FROM zx_lines
WHERE application_id = NVL2(pg_adj_doc_doc_type_id_tab(I),222, NULL)
AND entity_code = NVL2(pg_adj_doc_doc_type_id_tab(I),'TRANSACTIONS',NULL)
AND event_class_code = decode(pg_adj_doc_doc_type_id_tab(I), 4, 'INVOICE',
5, 'DEBIT_MEMO',
6, 'CREDIT_MEMO', null)
AND trx_id = NVL2(pg_adj_doc_doc_type_id_tab(I),pg_adj_doc_trx_id_tab(I), NULL)
AND trx_line_id = NVL2(pg_adj_doc_doc_type_id_tab(I),pg_adj_doc_line_id_tab(I), NULL)
AND trx_level_type = NVL2(pg_adj_doc_doc_type_id_tab(I),pg_adj_doc_trx_lev_type_tab(I), NULL);
l_line_level_action:='TAX_LINE_DELETE';
SELECT 1
INTO l_neg_line_gt_exists
FROM zx_ptnr_neg_line_gt
WHERE event_class_mapping_id = p_evnt_cls_mapping_id
AND trx_id = p_transaction_id
AND trx_line_id = p_transaction_line_id
AND trx_level_type = p_trx_level_type;
SELECT APPLICATION_ID,
ENTITY_CODE ,
EVENT_CLASS_CODE,
TRX_ID ,
TRX_LINE_ID,
TRX_LEVEL_TYPE
INTO l_APPLICATION_ID,
l_ENTITY_CODE ,
l_EVENT_CLASS_CODE,
l_TRX_ID ,
l_TRX_LINE_ID,
l_TRX_LEVEL_TYPE
FROM zx_lines_det_factors
WHERE event_class_mapping_id = p_evnt_cls_mapping_id
AND trx_id = p_transaction_id
AND trx_line_id = p_transaction_line_id
AND trx_level_type = p_trx_level_type;
p_update_index => 1,
p_trx_copy_for_tax_update => 'Y' ,
p_regime_code => p_regime_code,
p_tax_provider_id => p_tax_provider_id,
x_return_status => l_return_status);
SELECT
DOCUMENT_TYPE_ID ,
TRANSACTION_ID ,
TRANSACTION_LINE_ID ,
TRX_LEVEL_TYPE ,
COUNTRY_CODE ,
TAX ,
SITUS ,
TAX_JURISDICTION ,
TAX_CURRENCY_CODE ,
TAX_AMOUNT ,
TAX_CURR_TAX_AMT ,
TAX_RATE_PERCENTAGE ,
TAXABLE_AMOUNT ,
EXEMPT_RATE_MODIFIER ,
EXEMPT_REASON ,
TAX_ONLY_LINE_FLAG ,
INCLUSIVE_TAX_LINE_FLAG ,
USE_TAX_FLAG ,
EBIZ_OVERRIDE_FLAG ,
USER_OVERRIDE_FLAG ,
LAST_MANUAL_ENTRY ,
MANUALLY_ENTERED_FLAG ,
CANCEL_FLAG ,
DELETE_FLAG
FROM
ZX_SYNC_TAX_LINES_INPUT_V
ORDER BY
DOCUMENT_TYPE_ID,
TRANSACTION_ID,
TRANSACTION_LINE_ID,
TRX_LEVEL_TYPE;
l_update_mode BOOLEAN; -- Added for Bug#12544630
SELECT internal_organization_id,
document_type_id,
transaction_id,
legal_entity_number,
establishment_number
INTO l_internal_organization_id,
l_document_type_id,
l_transaction_id,
l_legal_entity_number,
l_establishment_number -- Bug 5139731
FROM ZX_SYNC_HDR_INPUT_V;
SELECT count(*)
INTO l_debug_count
FROM zx_detail_tax_lines_gt;
SYNC_TAX_LINES.delete_flag
LIMIT C_LINES_PER_COMMIT;
SELECT event_class_code
,application_id
,entity_code
INTO l_event_class_code
,l_application_id
,l_entity_code
FROM zx_evnt_cls_mappings
WHERE EVENT_CLASS_MAPPING_ID = SYNC_TAX_LINES.document_type_id(1);
' Selecting document type for event_class_mapping id '||SYNC_TAX_LINES.document_type_id(sync_tax_cnt));
select event_class_code
into l_document_type
from zx_evnt_cls_mappings
where event_class_mapping_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt);
select line_amount, adjusted_doc_transaction_id, line_level_action
into l_amount,l_adj_trx_id,l_line_action
from ZX_O2C_CALC_TXN_INPUT_V
where transaction_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt)
and transaction_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt);
SELECT COUNT(*)
INTO l_neg_tax_line_count
FROM ZX_PTNR_NEG_TAX_LINE_GT
WHERE document_type_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt)
AND trx_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt)
AND trx_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt)
AND country_code = SYNC_TAX_LINES.country_code(sync_tax_cnt)
AND tax = SYNC_TAX_LINES.tax(sync_tax_cnt)
AND situs = SYNC_TAX_LINES.situs(sync_tax_cnt);
SELECT COUNT(*)
INTO l_neg_trx_line_count
FROM ZX_PTNR_NEG_LINE_GT
WHERE event_class_mapping_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt)
AND trx_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt)
AND trx_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt);
IF (l_neg_tax_line_count = 1 OR l_neg_trx_line_count = 1) THEN -- Line is present. Hence, it is an UPDATE action --
l_event_type := 'TAX_LINE_UPDATE';
l_update_mode := TRUE;
IF (SYNC_TAX_LINES.delete_flag(sync_tax_cnt) ='Y') THEN -- Delete action --
l_event_type := 'TAX_LINE_DELETE';
IF (l_event_type='TAX_LINE_UPDATE') THEN
IF l_neg_tax_line_count = 1 THEN
BEGIN
SELECT tax_amount
INTO l_amount
FROM ZX_PTNR_NEG_TAX_LINE_GT
WHERE document_type_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt)
AND trx_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt)
AND trx_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt)
AND country_code = SYNC_TAX_LINES.country_code(sync_tax_cnt)
AND tax = SYNC_TAX_LINES.tax(sync_tax_cnt)
AND situs = SYNC_TAX_LINES.situs(sync_tax_cnt);
ELSIF(l_event_type='TAX_LINE_DELETE')then
create_tax_line(SYNC_TAX_LINES.tax(sync_tax_cnt),-1*SYNC_TAX_LINES.tax_amount(sync_tax_cnt),l_return_status);
IF (l_update_mode) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
'Opening tax line processing loop second time.' );
' Selecting document type for event_class_mapping id '||SYNC_TAX_LINES.document_type_id(sync_tax_cnt1));
select event_class_code
into l_document_type
from zx_evnt_cls_mappings
where event_class_mapping_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt1);
select line_amount, adjusted_doc_transaction_id, line_level_action
into l_amount,l_adj_trx_id,l_line_action
from ZX_O2C_CALC_TXN_INPUT_V
where transaction_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt1)
and transaction_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt1);
SELECT COUNT(*)
INTO l_neg_tax_line_count
FROM ZX_PTNR_NEG_TAX_LINE_GT
WHERE document_type_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt1)
AND trx_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt1)
AND trx_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt1)
AND country_code = SYNC_TAX_LINES.country_code(sync_tax_cnt1)
AND tax = SYNC_TAX_LINES.tax(sync_tax_cnt1)
AND situs = SYNC_TAX_LINES.situs(sync_tax_cnt1);
SELECT COUNT(*)
INTO l_neg_trx_line_count
FROM ZX_PTNR_NEG_LINE_GT
WHERE event_class_mapping_id = SYNC_TAX_LINES.document_type_id(sync_tax_cnt1)
AND trx_id = SYNC_TAX_LINES.transaction_id(sync_tax_cnt1)
AND trx_line_id = SYNC_TAX_LINES.transaction_line_id(sync_tax_cnt1);
IF (l_neg_tax_line_count = 1 OR l_neg_trx_line_count = 1) THEN -- Line is present. Hence, it is an UPDATE action --
l_event_type := 'TAX_LINE_UPDATE';
IF (SYNC_TAX_LINES.delete_flag(sync_tax_cnt1) ='Y') THEN -- Delete action --
l_event_type := 'TAX_LINE_DELETE';
IF (l_event_type='TAX_LINE_UPDATE') THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
'SYNC_TAX_LINES.tax_amount(' || sync_tax_cnt1 || ') = ' || SYNC_TAX_LINES.tax_amount(sync_tax_cnt1));
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' in the update part ');
SELECT TRANSACTION_NUMBER, TO_CHAR(TRANSACTION_ID)
INTO l_trx_number, char_ctrx_id
FROM ZX_SYNC_HDR_INPUT_V;
l_statements := 'UPDATE ZX_TAX_VERTEX_AUDIT '||
'SET INVNO = :1' ||
' WHERE TRANSUSERAREA = :2 ';
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated Rows in vertex table == ' || to_char(SQL%ROWCOUNT));
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'ZX_TAX_VERTEX_AUDIT is updated');
PROCEDURE GLOBAL_DOCUMENT_UPDATE
(x_transaction_rec IN zx_tax_partner_pkg.trx_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_messages_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.messages_tbl_type) is
l_cnt_of_options_gt NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'GLOBAL_DOCUMENT_UPDATE';
SELECT count(*)
INTO l_cnt_of_det_factors
FROM ZX_LINES_DET_FACTORS
WHERE event_class_mapping_id = x_transaction_rec.document_type_id
AND trx_id = x_transaction_rec.transaction_id;
SELECT count(*)
INTO l_cnt_of_options_gt
FROM ZX_TRX_PRE_PROC_OPTIONS_GT;
SELECT count(*)
INTO l_cnt_of_hdr_extns_gt
FROM ZX_PRVDR_HDR_EXTNS_GT;
SELECT count(*)
INTO l_cnt_of_line_extns_gt
FROM ZX_PRVDR_HDR_EXTNS_GT;
SELECT count(*)
INTO l_cnt_of_loc_info_gt
FROM ZX_PTNR_LOCATION_INFO_GT;
SELECT count(*)
INTO l_cnt_of_neg_line_gt
FROM ZX_PTNR_NEG_LINE_GT;
select count(*)
into l_cnt_of_trx_hdr_gt
from ZX_TRX_HEADERS_GT
where trx_id = x_transaction_rec.transaction_id;
l_statements := 'DELETE FROM ZX_TAX_VERTEX_AUDIT '||
' WHERE TRANSUSERAREA = :1 ';
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted Rows in vertex table == ' || to_char(SQL%ROWCOUNT));
SELECT
distinct application_id,entity_code,event_class_code,
trx_id,trx_level_type,trx_line_id
INTO l_application_id,l_entity_code,l_event_class_code,
l_trx_id,l_trx_level_type,l_trx_line_id
FROM ZX_PRVDR_LINE_EXTNS_GT where rownum = 1;
SELECT output_tax_classification_code,receivables_trx_type_id,
internal_organization_id into
l_tax_code,l_trx_type_id, l_org_id
FROM zx_lines_det_factors where
application_id = l_application_id
and entity_code = l_entity_code
and event_class_code = l_event_class_code
and trx_id = l_trx_id
and trx_line_id = l_trx_line_id
and trx_level_type = l_trx_level_type;
select tax_type_code INTO l_tax_type -- from zx_taxes_b zx, zx_rates_b zr
from zx_sco_taxes zx, -- Changed from clause to use sco views not base tables
zx_sco_rates zr
where zx.tax = zr.tax
AND zr.tax_rate_code = l_tax_code
AND rownum = 1;
SELECT tax_calculation_flag into l_tax_flag
FROM ra_cust_trx_types_all rtt
WHERE rtt.cust_trx_type_id = l_trx_type_id
AND rtt.org_id = l_org_id;
SELECT trx_line_id,
ship_to_location_id,
bill_to_location_id,
adjusted_doc_trx_id,
adjusted_doc_line_id,
adjusted_doc_trx_level_type,
decode(adjusted_doc_application_id,
222,decode(adjusted_doc_event_class_code,
'INVOICE', 4,
'DEBIT_MEMO', 5,
'CREDIT_MEMO', 6,
NULL
)
,NULL) adjusted_doc_document_type_id
BULK COLLECT INTO pg_trx_line_id_tab,
pg_ship_to_loc_id_tab,
pg_bill_to_loc_id_tab,
pg_adj_doc_trx_id_tab,
pg_adj_doc_line_id_tab,
pg_adj_doc_trx_lev_type_tab,
pg_adj_doc_doc_type_id_tab
FROM ZX_PTNR_NEG_LINE_GT
WHERE event_class_mapping_id = x_transaction_rec.document_type_id
AND trx_id = x_transaction_rec.transaction_id;
SELECT event_class_code
INTO l_document_type
FROM zx_evnt_cls_mappings
WHERE event_class_mapping_id = x_transaction_rec.document_type_id;
SELECT SUM(ABS(line_amt))
INTO l_doc_amount
FROM ZX_PTNR_NEG_LINE_GT
WHERE event_class_mapping_id = x_transaction_rec.document_type_id
AND trx_id = x_transaction_rec.transaction_id;
END GLOBAL_DOCUMENT_UPDATE;
SELECT EVNT_CLS_MAPPING_ID,
TRX_ID,
TAX_REGIME_CODE
FROM ZX_TRX_PRE_PROC_OPTIONS_GT;