[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_PreviousTaxLines (
p_ship_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_proc_name CONSTANT VARCHAR2(100) := 'Delete_PreviousTaxLines';
DELETE FROM inl_tax_lines tl
WHERE tl.ship_header_id = P_ship_Header_Id
OR (tl.ship_header_id is null
AND EXISTS (SELECT 1
FROM inl_associations assoc
WHERE assoc.ship_header_id = P_ship_Header_Id
AND assoc.from_parent_table_name = 'INL_TAX_LINES'
AND assoc.from_parent_table_id = tl.tax_line_id)
AND NOT EXISTS (SELECT 1
FROM inl_associations assoc, inl_ship_headers sh
WHERE assoc.ship_header_id <> P_ship_Header_Id
AND assoc.ship_header_id = sh.ship_header_id
AND sh.ship_status_code = 'COMPLETED'
AND from_parent_table_name = 'INL_TAX_LINES'
AND from_parent_table_id = tl.tax_line_id))
;
DELETE FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_TAX_LINES'
AND NOT EXISTS (SELECT 1
FROM inl_tax_lines tl
WHERE assoc.from_parent_table_id = tl.tax_line_id)
;
END Delete_PreviousTaxLines;
SELECT pll.quantity,
(pll.quantity * pL.unit_price) amount,
pH.currency_code,
pH.rate_type,
pH.rate_date
INTO l_qty,
l_amt,
l_po_curr_code,
l_po_curr_rate_type,
l_po_curr_rate_date
FROM po_line_locations pll,
po_lines_all pL,
po_headers pH
WHERE pll.line_location_id = p_po_line_location_id
AND pll.po_line_id = pL.po_line_id
AND pll.po_header_id = pH.po_header_id
;
SELECT
sl_zx.tax,
sl_zx.tax_line_id,
nvl(sl_zx.tax_amt_tax_curr,0) tax_amt_tax_curr, --BUG#8307904
nvl(sl_zx.rec_tax_amt_tax_curr,0) rec_tax_amt_tax_curr, --BUG#8307904
sl_zx.tax_currency_code,
sl_zx.tax_currency_conversion_type,
sl_zx.tax_currency_conversion_date,
sl_zx.tax_amt_included_flag,
nvl(sl_zx.trx_line_quantity,0) trx_line_quantity, --BUG#8307904
nvl(sl_zx.line_amt,0) line_amt, --BUG#8307904
sl_zx.trx_currency_code,
sl_zx.ship_line_id,
sl_zx.po_line_location_id,
sl_zx.sl_currency_code,
sl_zx.sl_currency_conversion_type,
sl_zx.sl_currency_conversion_date,
nvl(sl_zx.sl_txn_qty,0) sl_txn_qty, --BUG#8307904
nvl(sl_zx.sl_txn_unit_price,0) sl_txn_unit_price, --BUG#8307904
tl_assoc.inl_tax_line_id,
tl_assoc.inl_tax_line_num
FROM (SELECT
zl.tax,
zl.tax_line_id,
zl.tax_amt_tax_curr,
zl.rec_tax_amt_tax_curr,
zl.tax_currency_code,
zl.tax_currency_conversion_type,
zl.tax_currency_conversion_date,
zl.tax_amt_included_flag,
zl.trx_line_quantity,
zl.line_amt,
zl.trx_currency_code,
sl.ship_header_id,
sl.ship_line_source_id po_line_location_id,
sl.currency_code sl_currency_code,
sl.currency_conversion_type sl_currency_conversion_type,
sl.currency_conversion_date sl_currency_conversion_date,
sl.txn_qty sl_txn_qty,
sl.txn_unit_price sl_txn_unit_price,
sl.ship_line_id
FROM
inl_ship_lines sl,
zx_lines zl
WHERE sl.ship_header_id = p_ship_header_id
AND sl.ship_line_src_type_code = 'PO'
AND sl.ship_line_source_id = zl.trx_line_id
AND zl.application_id = 201
) sl_zx
,(SELECT
tl.tax_line_num inl_tax_line_num,
tl.tax_line_id inl_tax_line_id,
assoc.to_parent_table_id,
tl.source_parent_table_id
FROM inl_adj_tax_lines_v tl,
inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_TAX_LINES'
AND assoc.from_parent_table_id = tl.tax_line_id
AND assoc.to_parent_table_name = 'INL_SHIP_LINES'
AND tl.ship_header_id = p_ship_header_id
AND tl.source_parent_table_name = 'ZX_LINES'
) tl_assoc
WHERE tl_assoc.source_parent_table_id(+) = sl_zx.tax_line_id
AND tl_assoc.to_parent_table_id(+) = sl_zx.ship_line_id
;
l_debug_info := 'Step 1a: Delete data from previous tax Generation/Calculation';
Delete_PreviousTaxLines (
p_ship_header_id => p_ship_header_id,
x_return_status => l_return_status
);
SELECT *
BULK COLLECT INTO l_ship_groups_tbl
FROM inl_ship_line_groups lg
WHERE lg.ship_header_id = p_ship_header_id
order by ship_line_group_id;
SELECT *
BULK COLLECT INTO l_ship_lines_tbl
FROM inl_adj_ship_lines_v sl
WHERE sl.ship_header_id = p_ship_header_id
order by sl.ship_line_group_id, sl.ship_line_id;
l_debug_info := 'Insert IN INL_TAX_LINES getting ID from sequence';
SELECT inl_tax_lines_s.NEXTVAL
INTO l_tax_line_id
FROM DUAL;
SELECT NVL(MAX(tl.tax_line_num),0)+1
INTO l_tax_line_num
FROM inl_tax_lines tl
WHERE tl.ship_header_id = l_tax_ln_tbl(i).ship_header_id
;
l_debug_info := 'Inserting record in INL_TAX_LINES';
INSERT
INTO inl_tax_lines (
tax_line_id , /* 01 */
tax_line_num , /* 02 */
tax_code , /* 03 */
ship_header_id , /* 04 */
adjustment_num , /* 06 */
source_parent_table_name, /* 08 */
source_parent_table_id , /* 09 */
tax_amt , /* 10 */
nrec_tax_amt , /* 11 */
currency_code , /* 12 */
currency_conversion_type, /* 13 */
currency_conversion_date, /* 14 */
currency_conversion_rate, /* 15 */
tax_amt_included_flag , /* 16 */
created_by , /* 17 */
creation_date , /* 18 */
last_updated_by , /* 19 */
last_update_date , /* 20 */
last_update_login /* 21 */
)
VALUES (
l_tax_line_id , /* 01 */
l_tax_line_num , /* 02 */
l_tax_ln_tbl(i).tax_code , /* 03 */
l_tax_ln_tbl(i).ship_header_id , /* 04 */
0 , /* 06 */
l_tax_ln_tbl(i).source_parent_table_name, /* 08 */
l_tax_ln_tbl(i).source_parent_table_id , /* 09 */
l_tax_ln_tbl(i).tax_amt , /* 10 */
l_tax_ln_tbl(i).nrec_tax_amt , /* 11 */
l_tax_ln_tbl(i).currency_code , /* 12 */
l_tax_ln_tbl(i).currency_conversion_type, /* 13 */
l_tax_ln_tbl(i).currency_conversion_date, /* 14 */
l_tax_ln_tbl(i).currency_conversion_rate, /* 15 */
l_tax_ln_tbl(i).tax_amt_included_flag , /* 16 */
fnd_global.user_id , /* 17 */
SYSDATE , /* 18 */
fnd_global.user_id , /* 19 */
SYSDATE , /* 20 */
fnd_global.login_id /* 21 */
);
l_debug_info := 'Inserting record in INL_ASSOCIATIONS';
INSERT
INTO inl_associations (
association_id , /* 01 */
ship_header_id , /* 02 */
from_parent_table_name , /* 03 */
from_parent_table_id , /* 04 */
to_parent_table_name , /* 05 */
to_parent_table_id , /* 06 */
allocation_basis , /* 07 */
allocation_uom_code , /* 08 */
created_by , /* 09 */
creation_date , /* 10 */
last_updated_by , /* 11 */
last_update_date , /* 12 */
last_update_login /* 13 */
)
VALUES (
inl_associations_s.NEXTVAL , /* 01 */
l_tax_ln_tbl(i).ship_header_id , /* 02 */
'INL_TAX_LINES' , /* 03 */
l_tax_line_id , /* 04 */
l_tax_ln_tbl(i).to_parent_table_name , /* 05 */
l_tax_ln_tbl(i).to_parent_table_id , /* 06 */
'VALUE' , /* 07 */
null , /* 08 */
fnd_global.user_id , /* 09 */
SYSDATE , /* 10 */
fnd_global.user_id , /* 11 */
SYSDATE , /* 12 */
fnd_global.login_id /* 13 */
);
SELECT pll.po_header_id, nvl(ph.rate ,1)
INTO x_po_header_id, x_po_header_curr_conv_rate
FROM po_line_locations pll, po_headers ph
WHERE pll.line_location_id = P_Po_Line_Location_Id
AND pll.po_header_id = ph.po_header_id
AND rownum = 1;
SELECT ood.set_of_books_id
INTO l_ledger_id
FROM org_organization_definitions ood
WHERE ood.organization_id = l_ship_header_rec.organization_id
AND rownum = 1;
l_tax_event_type_code := 'UPDATE';
x_event_type_code := 'ARRIVAL UPDATED';
DELETE FROM zx_trx_headers_gt
WHERE trx_id=l_ship_header_rec.ship_header_id;
INSERT INTO zx_trx_headers_gt(
/*01*/ internal_organization_id,
/*02*/ internal_org_location_id,
/*03*/ application_id,
/*04*/ entity_code,
/*05*/ event_class_code,
/*06*/ event_type_code,
/*07*/ trx_id,
/*08*/ hdr_trx_user_key1,
/*09*/ hdr_trx_user_key2,
/*10*/ hdr_trx_user_key3,
/*11*/ hdr_trx_user_key4,
/*12*/ hdr_trx_user_key5,
/*13*/ hdr_trx_user_key6,
/*14*/ trx_date,
/*15*/ trx_doc_revision,
/*16*/ ledger_id,
/*17*/ trx_currency_code,
/*18*/ currency_conversion_date,
/*19*/ currency_conversion_rate,
/*20*/ currency_conversion_type,
/*21*/ minimum_accountable_unit,
/*22*/ precision,
/*23*/ legal_entity_id,
/*24*/ rounding_ship_to_party_id,
/*25*/ rounding_ship_from_party_id,
/*26*/ rounding_bill_to_party_id,
/*27*/ rounding_bill_from_party_id,
/*28*/ rndg_ship_to_party_site_id,
/*29*/ rndg_ship_from_party_site_id,
/*30*/ rndg_bill_to_party_site_id,
/*31*/ rndg_bill_from_party_site_id,
/*32*/ establishment_id,
/*33*/ receivables_trx_type_id,
/*34*/ related_doc_application_id,
/*35*/ related_doc_entity_code,
/*36*/ related_doc_event_class_code,
/*37*/ related_doc_trx_id,
/*38*/ rel_doc_hdr_trx_user_key1,
/*39*/ rel_doc_hdr_trx_user_key2,
/*40*/ rel_doc_hdr_trx_user_key3,
/*41*/ rel_doc_hdr_trx_user_key4,
/*42*/ rel_doc_hdr_trx_user_key5,
/*43*/ rel_doc_hdr_trx_user_key6,
/*44*/ related_doc_number,
/*45*/ related_doc_date,
/*46*/ default_taxation_country,
/*47*/ quote_flag,
/*48*/ ctrl_total_hdr_tx_amt,
/*49*/ trx_number,
/*50*/ trx_description,
/*51*/ trx_communicated_date,
/*52*/ batch_source_id,
/*53*/ batch_source_name,
/*54*/ doc_seq_id,
/*55*/ doc_seq_name,
/*56*/ doc_seq_value,
/*57*/ trx_due_date,
/*58*/ trx_type_description,
/*59*/ document_sub_type,
/*60*/ supplier_tax_invoice_number,
/*61*/ supplier_tax_invoice_date ,
/*62*/ supplier_exchange_rate,
/*63*/ tax_invoice_date,
/*64*/ tax_invoice_number,
/*65*/ tax_event_class_code,
/*66*/ tax_event_type_code,
/*67*/ doc_event_status,
/*68*/ rdng_ship_to_pty_tx_prof_id,
/*69*/ rdng_ship_from_pty_tx_prof_id,
/*70*/ rdng_bill_to_pty_tx_prof_id,
/*71*/ rdng_bill_from_pty_tx_prof_id,
/*72*/ rdng_ship_to_pty_tx_p_st_id,
/*73*/ rdng_ship_from_pty_tx_p_st_id,
/*74*/ rdng_bill_to_pty_tx_p_st_id,
/*75*/ rdng_bill_from_pty_tx_p_st_id,
/*76*/ bill_third_pty_acct_id,
/*77*/ bill_third_pty_acct_site_id,
/*78*/ ship_third_pty_acct_id,
/*79*/ ship_third_pty_acct_site_id,
/*80*/ doc_level_recalc_flag
)
VALUES (
/*01*/ l_ship_header_rec.organization_id, --internal_organization_id
/*02*/ l_ship_header_rec.location_id, --internal_org_location_id
/*03*/ l_application_id, --application_id
/*04*/ G_ENTITY_CODE, --entity_code
/*05*/ p_event_class_code, --event_class_code
/*06*/ x_event_type_code, --event_type_code
/*07*/ l_ship_header_rec.ship_header_id, --trx_id
/*08*/ NULL, --hdr_trx_user_key1
/*09*/ NULL, --hdr_trx_user_key2
/*10*/ NULL, --hdr_trx_user_key3
/*11*/ NULL, --hdr_trx_user_key4
/*12*/ NULL, --hdr_trx_user_key5
/*13*/ NULL , --hdr_trx_user_key6
/*14*/ l_ship_header_rec.ship_date, --trx_date
/*15*/ NULL, --trx_doc_revision
/*16*/ l_ledger_id, --ledger_id
/*17*/ null, -- 07/2007 Currency columns was transfer to lines l_ship_header_rec.CURRENCY_CODE,
/*18*/ null, -- l_ship_header_rec.CURRENCY_CONVERSION_DATE,
/*19*/ null, -- l_ship_header_rec.CURRENCY_CONVERSION_RATE,
/*20*/ null, -- l_ship_header_rec.CURRENCY_CONVERSION_TYPE,
/*21*/ null, -- l_minimum_accountable_unit,
/*22*/ null, -- l_precision,
/*23*/ l_ship_header_rec.legal_entity_id, --legal_entity_id
/*24*/ l_ship_to_organization_id, --rounding_ship_to_party_id
/*25*/ NULL, --rounding_ship_from_party_id
/*26*/ NULL, --rounding_bill_to_party_id
/*27*/ NULL, --rounding_bill_from_party_id
/*28*/ NULL, --rndg_ship_to_party_site_id
/*29*/ NULL, --rndg_ship_from_party_site_id
/*30*/ NULL, --rndg_bill_to_party_site_id
/*31*/ NULL, --rndg_bill_from_party_site_id
/*32*/ NULL, --establishment_id
/*33*/ NULL, --receivables_trx_type_id
/*34*/ NULL, --related_doc_application_id
/*35*/ NULL, --related_doc_entity_code
/*36*/ NULL, --related_doc_event_class_code
/*37*/ NULL, --related_doc_trx_id
/*38*/ NULL, --rel_doc_hdr_trx_user_key1
/*39*/ NULL, --rel_doc_hdr_trx_user_key2
/*40*/ NULL, --rel_doc_hdr_trx_user_key3
/*41*/ NULL, --rel_doc_hdr_trx_user_key4
/*42*/ NULL, --rel_doc_hdr_trx_user_key5
/*43*/ NULL, --rel_doc_hdr_trx_user_key6
/*44*/ NULL, --related_doc_number
/*45*/ NULL, --related_doc_date
/*46*/ l_ship_header_rec.taxation_country, --default_taxation_country
/*47*/ l_quote_flag, --quote_flag
/*48*/ NULL, --ctrl_total_hdr_tx_amt
/*49*/ l_ship_header_rec.ship_num, --trx_number
/*50*/ NULL, --'INL_SHIPMENT', --trx_description
/*51*/ NULL, --trx_communicated_date
/*52*/ NULL, --batch_source_id
/*53*/ NULL, --batch_source_name
/*54*/ NULL, --doc_seq_id
/*55*/ NULL, --doc_seq_name
/*56*/ NULL, --doc_seq_value
/*57*/ NULL, --trx_due_date
/*58*/ NULL, --trx_type_description
/*59*/ l_ship_header_rec.document_sub_type, --document_sub_type
/*60*/ NULL, --supplier_tax_invoice_number
/*61*/ NULL, --supplier_tax_invoice_date
/*62*/ NULL, --supplier_exchange_rate
/*63*/ NULL, --tax_invoice_date
/*64*/ NULL, --tax_invoice_number
/*65*/ NULL, --tax_event_class_code
/*66*/ l_tax_event_type_code, --tax_event_type_code
/*67*/ NULL, --doc_event_status
/*68*/ NULL, --rdng_ship_to_pty_tx_prof_id
/*69*/ NULL, --rdng_ship_from_pty_tx_prof_id
/*70*/ NULL, --rdng_bill_to_pty_tx_prof_id
/*71*/ NULL, --rdng_bill_from_pty_tx_prof_id
/*72*/ NULL, --rdng_ship_to_pty_tx_p_st_id
/*73*/ NULL, --rdng_ship_from_pty_tx_p_st_id
/*74*/ NULL, --rdng_bill_to_pty_tx_p_st_id
/*75*/ NULL, --rdng_bill_from_pty_tx_p_st_id
/*76*/ NULL, --bill_third_pty_acct_id
/*77*/ NULL, --bill_third_pty_acct_site_id
/*78*/ NULL, --ship_third_pty_acct_id
/*79*/ NULL, --ship_third_pty_acct_site_id
/*80*/ l_doc_level_recalc_flag
);
SELECT NVL(precision, 0), NVL(minimum_accountable_unit,(1/power(10,precision)))
INTO l_precision, l_minimum_accountable_unit
FROM fnd_currencies
WHERE currency_code = l_ship_line_list(i).currency_code;
SELECT fob_lookup_code -- From lookup FOB
INTO l_fob_point
FROM po_vendor_sites
WHERE party_site_id = l_ship_line_list(i).party_site_id
AND rownum = 1;
l_line_level_action := 'UPDATE';
trans_lines(i).assessable_value := 0; -- l_line_amount; --updated above
DELETE FROM zx_transaction_lines_gt
WHERE trx_id= l_ship_header_rec.ship_header_id;
l_debug_info := 'Step 15: Bulk Insert into global temp table';
INSERT INTO zx_transaction_lines_gt
VALUES trans_lines(m);
l_debug_info := 'Step 15: Populate pl/sql table inserted: '||sql%rowcount||' line(s)';
FOR curTax in (SELECT zl.tax_line_id
,zl.tax_line_number
,zl.tax_code
,zl.trx_id
,zl.trx_line_id
,zl.tax_amt
,nvl(zl.Nrec_tax_amt,0) Nrec_tax_amt
,zl.tax_amt_included_flag
,zl.tax_currency_code
,zl.tax_currency_conversion_type
,zl.tax_currency_conversion_date
,zl.tax_currency_conversion_rate
,zl.created_by
,zl.creation_date
,zl.last_updated_by
,zl.last_update_date
,zl.last_update_login
,DECODE(lv.source, 'SHIP_LINE' ,'INL_SHIP_LINES'
, 'CHARGE' ,'INL_CHARGE_LINES') source
,oh.ship_TYPE_ID
,inl_tax_lines_s.NEXTVAL tax_line_id_s
FROM zx_lines zl
,inl_ebtax_lines_v lv
,inl_ship_headers oh
WHERE zl.application_id = 9004
AND zl.trx_id = P_ship_Header_Id
AND oh.ship_Header_Id = P_ship_Header_Id
AND lv.ship_line_id = zl.trx_line_id
AND lv.ship_header_id = P_ship_Header_Id)
LOOP
IF l_proc = 'N' THEN
l_proc := 'Y';
INSERT INTO inl_tax_lines (
tax_line_id
,tax_line_num
,tax_code
,ship_header_id
,adjustment_num
,source_parent_table_name
,source_parent_table_id
,tax_amt
,nrec_tax_amt
,currency_code
,currency_conversion_type
,currency_conversion_date
,currency_conversion_rate
,tax_amt_included_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES(
curTax.tax_line_id_s
,curTax.tax_line_number
,curTax.tax_code
,curTax.trx_id
,0
,'ZX_LINES'
,curTax.tax_line_id
,curTax.tax_amt
,curTax.nrec_tax_amt
,curTax.tax_currency_code
,curTax.tax_currency_conversion_type
,curTax.tax_currency_conversion_date
,curTax.tax_currency_conversion_rate
,curTax.tax_amt_included_flag
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.login_id
);
INSERT INTO inl_associations (
association_id /* 01 */
,ship_header_id /* 02 */
,from_parent_table_name /* 03 */
,from_parent_table_id /* 04 */
,to_parent_table_name /* 05 */
,to_parent_table_id /* 06 */
,allocation_basis /* 07 */
,allocation_uom_code /* 08 */
,created_by /* 09 */
,creation_date /* 10 */
,last_updated_by /* 11 */
,last_update_date /* 12 */
,last_update_login) /* 13 */
VALUES(
inl_associations_s.NEXTVAL /* 01 */
,curTax.trx_id /* 02 */
,'INL_TAX_LINES' /* 03 */
,curTax.tax_line_id_s /* 04 */
,curTax.source /* 05 */
,curTax.trx_line_id /* 06 */
,l_allocation_basis /* 07 */
,l_allocation_uom_code /* 08 */
,fnd_global.user_id /* 09 */
,SYSDATE /* 10 */
,fnd_global.user_id /* 11 */
,SYSDATE /* 12 */
,fnd_global.login_id); /* 13 */
UPDATE inl_ship_lines
SET tax_already_calculated_flag = 'Y',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE ship_header_id = p_ship_header_Id;
UPDATE inl_charge_lines
SET tax_already_calculated_flag = 'Y',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE NVL(parent_charge_line_id,charge_line_id) IN (SELECT assoc.from_parent_table_id
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.ship_header_id = p_ship_header_id);
SELECT entity_code INTO G_ENTITY_CODE
FROM zx_evnt_cls_mappings
WHERE application_id = 9004
AND ROWNUM = 1;
l_debug_info := 'Step 1a: Delete data from previous tax Generation/Calculation';
Delete_PreviousTaxLines (
p_ship_header_id => p_ship_header_id,
x_return_status => l_return_status
);
SELECT 'Y'
INTO l_tax_already_calculated
FROM zx_lines_det_factors
WHERE trx_id = l_ship_header_rec.ship_header_id
AND application_id = 9004
AND entity_code = G_ENTITY_CODE
AND event_class_code = l_event_class_code
AND ROWNUM < 2;
SELECT zx.default_taxation_country,
zx.trx_business_category,
zx.product_fisc_classification,
zx.product_category,
zx.line_intended_use,
zx.product_type
INTO l_po_country_code,
x_trx_biz_category,
x_prod_fisc_class_code,
x_prod_category,
x_intended_use,
x_product_type
FROM zx_lines_det_factors zx,
po_line_locations pll
WHERE zx.application_id = 201
AND zx.event_class_code = 'PO_PA'
AND zx.entity_code = 'PURCHASE_ORDER'
AND zx.trx_id = pll.po_header_id
AND zx.trx_line_id = pll.line_location_id
AND zx.trx_level_type = 'SHIPMENT'
AND trx_line_id = p_po_line_location_id;