[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Called from Update Tax Categories form */
jai_cmn_mtax_pkg.process_tax_cat_update
(
p_err_buf => p_err_buf
,p_ret_code => p_ret_code
,p_org_id => p_org_id
,p_document_type => p_document_type
,p_from_date => p_from_date
,p_to_date => p_to_date
,p_supplier_id => p_supplier_id
,p_supplier_site_id => p_supplier_site_id
,p_customer_id => p_customer_id
,p_customer_site_id => p_customer_site_id
,p_old_tax_category => p_old_tax_category
,p_new_tax_category => p_new_tax_category
,p_document_no => p_document_no
,p_release_no => p_release_no
,p_document_line_no => p_document_line_no
,p_shipment_no => p_shipment_no
,p_override_manual_taxes => p_override_manual_taxes
,p_commit_interval => p_commit_interval
,p_process_partial => p_process_partial
,p_debug => p_debug
,p_trace => p_trace
,p_dbms_output => p_dbms_output
,p_tax_cat_update_id => p_source_id
);
procedure process_tax_cat_update
(
p_err_buf OUT NOCOPY VARCHAR2
,p_ret_code OUT NOCOPY VARCHAR2
,p_org_id IN NUMBER --1
,p_document_type IN VARCHAR2 default null --2
,p_from_date IN DATE --3
,p_to_date IN DATE --4
,p_supplier_id IN NUMBER --5
,p_supplier_site_id IN NUMBER --6
,p_customer_id IN NUMBER --7
,p_customer_site_id IN NUMBER --8
,p_old_tax_category IN NUMBER --9
,p_new_tax_category IN NUMBER --10
,p_document_no IN VARCHAR2 --11
,p_release_no IN NUMBER --12
,p_document_line_no IN NUMBER --13
,p_shipment_no IN NUMBER --14
,p_override_manual_taxes IN CHAR DEFAULT 'N'--15
,p_commit_interval IN NUMBER DEFAULT 50 --16
,p_process_partial IN CHAR DEFAULT 'N' --17
,p_debug IN CHAR DEFAULT 'N' --18
,p_trace IN CHAR DEFAULT 'N' --19
,p_dbms_output IN CHAR DEFAULT 'N' -- this can be used when developer tests this from backened to get dbms output at important points
,p_tax_cat_update_id IN jai_cmn_taxctg_updates.tax_category_update_id%type
)
is
begin
/* For all supported document types do mass tax changes */
for r_doc_type in ( select flex_value document_type
from fnd_flex_values_vl flxvals
, fnd_flex_value_sets flxvsets
where flxvsets.flex_value_set_id = flxvals.flex_value_set_id
and flxvsets.flex_value_set_name = 'JAINMTCH_PO_DOCUMENT_TYPES'
)
loop
fnd_file.put_line( fnd_file.log, 'Processing mass update for document type='|| r_doc_type.document_type);
for r_tax_cat in (select tax_category_id
from jai_cmn_taxctg_updates
where tax_category_update_id = p_tax_cat_update_id
and invoke_mass_tax_update_flag = 'Y'
)
loop
do_tax_redefaultation
(
p_err_buf => p_err_buf
,p_ret_code => p_ret_code
,p_org_id => p_org_id
,p_document_type => r_doc_type.document_type
,pv_from_date => p_from_date
,pv_to_date => p_to_date
,p_supplier_id => p_supplier_id
,p_supplier_site_id => p_supplier_site_id
,p_customer_id => p_customer_id
,p_customer_site_id => p_customer_site_id
,p_old_tax_category => r_tax_cat.tax_category_id
,p_new_tax_category => r_tax_cat.tax_category_id
,p_document_no => p_document_no
,p_release_no => p_release_no
,p_document_line_no => p_document_line_no
,p_shipment_no => p_shipment_no
,pv_override_manual_taxes => p_override_manual_taxes
,pn_commit_interval => p_commit_interval
,pv_process_partial => p_process_partial
,pv_debug => p_debug
,pv_trace => p_trace
--,p_dbms_output => p_dbms_output
);
fnd_file.put_line( fnd_file.log, 'Mass update completed for document type='|| r_doc_type.document_type);
end process_tax_cat_update;
SELECT enable_trace
FROM fnd_concurrent_programs a, fnd_application b
WHERE b.application_short_name = 'PO'
AND b.application_id = a.application_id
AND a.concurrent_program_name = cp_conc_name; --'JAINMTCH';
SELECT a.sid, a.serial#, b.spid
FROM v$session a, v$process b
WHERE audsid = userenv('SESSIONID')
AND a.paddr = b.addr;
CURSOR get_dbname IS SELECT name FROM v$database;
SELECT po_header_id
FROM po_headers_all
WHERE segment1 = p_document_no
AND type_lookup_code = p_document_type
AND (p_org_id IS NULL OR org_id = p_org_id);
SELECT po_line_id
FROM po_lines_all
WHERE po_header_id = p_po_header_id AND line_num = p_document_line_no;
SELECT line_location_id
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id
AND shipment_num = p_shipment_no
AND shipment_type = p_shipment_type
AND ( (p_release_id IS NULL) OR (p_release_id IS NOT NULL AND po_release_id = p_release_id));
SELECT po_release_id
FROM po_releases_all
WHERE po_header_id = p_po_header_id AND release_num = p_release_no;
SELECT header_id
FROM oe_order_headers_all
WHERE order_number = p_order_number
AND (p_org_id IS NULL OR org_id = p_org_id);
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id AND line_number = p_line_no;
SELECT requisition_header_id
FROM po_requisition_headers_all
WHERE segment1 = p_requisition_no
AND type_lookup_code = p_document_type
AND (p_org_id IS NULL OR org_id = p_org_id);
SELECT requisition_line_id
FROM po_requisition_lines_all
WHERE requisition_header_id = p_requisition_header_id AND line_num = p_requisition_line_no;
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_uom;
select a.tax_category_id, a.tax_id, a.line_no lno,
a.precedence_1 p_1,
a.precedence_2 p_2,
a.precedence_3 p_3,
a.precedence_4 p_4,
a.precedence_5 p_5,
a.precedence_6 p_6,-- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
a.precedence_7 p_7,
a.precedence_8 p_8,
a.precedence_9 p_9,
a.precedence_10 p_10,
b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date,
-- DECODE(UPPER(b.tax_type), 'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD', 1, jai_constants.tax_type_exc_edu_cess,1, 'TDS', 2, 0) tax_type_val,
b.mod_cr_percentage, b.vendor_id, b.tax_type, nvl(b.rounding_factor,0) rounding_factor
from JAI_CMN_TAX_CTG_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.tax_category_id = p_tax_category_id
AND a.tax_id = b.tax_id
ORDER BY a.line_no;
SELECT rowid, tax_line_no
FROM JAI_PO_TAXES
WHERE line_focus_id = p_line_focus_id
AND tax_category_id IS NULL
ORDER BY tax_line_no;
SELECT rowid, tax_line_no
FROM JAI_OM_OE_SO_TAXES
WHERE line_id = p_line_id
AND tax_category_id IS NULL
ORDER BY tax_line_no;
SELECT rowid, tax_line_no
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = p_requisition_line_id
AND tax_category_id IS NULL
ORDER BY tax_line_no;
SELECT inventory_organization_id
FROM hr_locations
WHERE location_id = p_location_id;
SELECT nvl(cust_acct_site_id , 0) address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
SELECT b.operand list_price
FROM JAI_CMN_CUS_ADDRESSES a, qp_list_lines b, qp_pricing_attributes c
WHERE a.customer_id = p_customer_id
AND a.address_id = p_address_id
AND a.price_list_id = b.list_header_id
AND c.list_line_id = b.list_line_id
AND c.product_attr_value = p_inventory_item_id
AND c.product_uom_code = p_uom_code
AND p_ordered_date BETWEEN TRUNC(NVL(b.start_date_active, v_today))
AND TRUNC(NVL(b.end_date_active, v_today));
SELECT list_price, unit_code
FROM so_price_list_lines
WHERE price_list_id = p_price_list_id
AND inventory_item_id = p_inventory_item_id
AND unit_code = p_uom_code
AND trunc(p_ordered_date) BETWEEN trunc(nvl( start_date_active, p_ordered_date))
AND trunc(nvl( end_date_active, SYSDATE));
SELECT vendor_name
FROM Po_Vendors
WHERE Vendor_Name = p_vendor_id;
SELECT Vendor_Site_Code
FROM po_vendor_sites_all A
WHERE a.vendor_site_id = p_vendor_site_id;
SELECT Vendor_Id
FROM Po_Vendors
WHERE Vendor_Name = p_sugg_vendor_name;
SELECT Vendor_Site_Id
FROM Po_Vendor_Sites_All A
WHERE A.Vendor_Id = p_vendor_id
AND A.Vendor_Site_Code = p_sugg_vendor_loc
AND (p_org_id IS NULL OR a.org_id = p_org_id);
SELECT 1 source, jipll.rowid,
jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
nvl(plla.quantity_billed,0) quantity_billed, nvl(plla.quantity,0) shipment_qty,
nvl(plla.quantity_received,0) quantity_received, nvl(plla.quantity_accepted,0) quantity_accepted,
nvl(plla.quantity_rejected,0) quantity_rejected, nvl(plla.quantity_cancelled,0) quantity_cancelled,
plla.ship_to_organization_id, plla.ship_to_location_id,
plla.unit_meas_lookup_code, plla.price_override, plla.shipment_type,
pla.item_id, pla.unit_meas_lookup_code line_uom,
pha.segment1 document_no, pla.line_num, plla.shipment_num
FROM po_headers_all pha, po_lines_all pla, po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jipll
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = jipll.line_location_id
AND plla.shipment_type = p_shipment_type
AND ((p_document_no is null) OR (p_document_no is not null and pha.segment1=p_document_no ))
AND pha.type_lookup_code = p_document_type
AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
AND ((p_shipment_no IS NULL) OR (p_shipment_no IS NOT NULL AND plla.shipment_num = p_shipment_no ))
AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
AND ( (p_vendor_id IS NULL)
OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
AND ( (p_vendor_site_id IS NULL)
OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
AND ( (p_old_tax_category_id IS NULL)
OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
AND (p_org_id IS NULL OR pha.org_id = p_org_id)
AND (plla.cancel_flag IS NULL OR plla.cancel_flag <> 'Y' )
AND trunc(plla.creation_date) BETWEEN p_from_date AND p_to_date
AND ( plla.closed_code IS NULL OR plla.closed_code IN (
jai_constants.closed_code_open ,
jai_constants.closed_code_inporcess ,
jai_constants.closed_code_approved ,
jai_constants.closed_code_preapproved ,
jai_constants.closed_code_req_appr ,
jai_constants.closed_code_incomplete ))
--'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
UNION -- if there are no base records in po_line_locations_all but JAI_PO_LINE_LOCATIONS have
SELECT 2 source, jipll.rowid,
jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
0 quantity_billed, 0 shipment_qty,
0 quantity_received, 0 quantity_accepted,
0 quantity_rejected, 0 quantity_cancelled,
-1 ship_to_organization_id, -1 ship_to_location_id,
null unit_meas_lookup_code, 0 price_override, null shipment_type,
pla.item_id, pla.unit_meas_lookup_code line_uom,
pha.segment1 document_no, pla.line_num, -1 shipment_num
FROM po_headers_all pha, po_lines_all pla, JAI_PO_LINE_LOCATIONS jipll
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = jipll.po_line_id
AND ((p_document_no IS NULL) or (p_document_no is NOT NULL and pha.segment1 = p_document_no ))
AND pha.type_lookup_code = p_document_type
AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
AND ( (p_vendor_id IS NULL)
OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
AND ( (p_vendor_site_id IS NULL)
OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
AND ( (p_old_tax_category_id IS NULL)
OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
AND (p_org_id IS NULL OR pha.org_id = p_org_id)
AND (pla.cancel_flag IS NULL OR pla.cancel_flag <> 'Y' )
AND trunc(pla.creation_date) BETWEEN p_from_date AND p_to_date
AND ( pla.closed_code IS NULL OR pla.closed_code IN (
jai_constants.closed_code_open ,
jai_constants.closed_code_inporcess ,
jai_constants.closed_code_approved ,
jai_constants.closed_code_preapproved ,
jai_constants.closed_code_req_appr ,
jai_constants.closed_code_incomplete ))
--'OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE') )
and (jipll.line_location_id IS NULL OR jipll.line_location_id = 0);
SELECT 3 src, pra.po_release_id, jipll.rowid,
jipll.line_focus_id, jipll.line_location_id, jipll.po_line_id, jipll.po_header_id, jipll.tax_category_id,
pha.type_lookup_code, pha.vendor_id, pha.vendor_site_id, pha.currency_code,
pha.rate, pha.rate_date, pha.rate_type, pha.ship_to_location_id hdr_ship_to_location_id,
plla.quantity_billed, plla.quantity shipment_qty,
plla.quantity_received, plla.quantity_accepted,
plla.quantity_rejected, plla.quantity_cancelled,
plla.ship_to_organization_id, plla.ship_to_location_id,
plla.unit_meas_lookup_code, plla.price_override, plla.shipment_type,
pla.item_id, pla.unit_meas_lookup_code line_uom,
pra.release_num, pha.segment1 document_no, pla.line_num, plla.shipment_num
FROM po_headers_all pha, po_lines_all pla,
po_line_locations_all plla, JAI_PO_LINE_LOCATIONS jipll, po_releases_all pra
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.po_line_id = jipll.po_line_id
AND plla.line_location_id = jipll.line_location_id
AND pra.po_header_id = pha.po_header_id
AND plla.po_release_id = pra.po_release_id
AND plla.shipment_type = p_shipment_type
AND ((p_document_no IS NULL) OR (p_document_no IS NOT NULL and pha.segment1 = p_document_no ))
AND pha.type_lookup_code = p_document_type
AND ((p_release_no is null) OR (p_release_no is not null and pra.release_num = p_release_no ))
AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND pla.line_num = p_document_line_no ))
AND ((p_shipment_no IS NULL) OR (p_shipment_no IS NOT NULL AND plla.shipment_num = p_shipment_no ))
AND v_today BETWEEN nvl( pha.start_date, v_today) AND nvl(pha.end_date, v_today)
AND ( (p_vendor_id IS NULL)
OR (p_vendor_id IS NOT NULL AND pha.vendor_id = p_vendor_id) )
AND ( (p_vendor_site_id IS NULL)
OR (p_vendor_site_id IS NOT NULL AND pha.vendor_site_id = p_vendor_site_id) )
AND ( (p_old_tax_category_id IS NULL)
OR (p_old_tax_category_id IS NOT NULL AND jipll.tax_category_id = p_old_tax_category_id) )
AND (p_org_id IS NULL OR plla.org_id = p_org_id)
AND (plla.cancel_flag IS NULL OR plla.cancel_flag <> 'Y' )
AND trunc(pra.creation_date) BETWEEN p_from_date AND p_to_date
AND ( plla.closed_code IS NULL OR plla.closed_code IN
(jai_constants.closed_code_open ,
jai_constants.closed_code_inporcess ,
jai_constants.closed_code_approved ,
jai_constants.closed_code_preapproved ,
jai_constants.closed_code_req_appr ,
jai_constants.closed_code_incomplete ));
SELECT jirl.rowid,
jirl.requisition_line_id, jirl.requisition_header_id, jirl.tax_category_id,
prha.type_lookup_code, --, prha.currency_code hdr_currency_code,
prla.quantity, -- plla.quantity_received, plla.quantity_delivered, plla.quantity_cancelled,
prla.item_id, prla.unit_meas_lookup_code line_uom, prla.unit_price,
prla.currency_unit_price, prla.currency_code, prla.rate, prla.rate_date, prla.rate_type,
prla.suggested_vendor_name, prla.suggested_vendor_location,
prla.destination_organization_id, prla.deliver_to_location_id, prla.source_organization_id,
prla.source_type_code, -- this tells whether source is VENDOR or INVENTORY. If vendor then suggested vendor will be there
prha.segment1 document_no, prla.line_num
FROM po_requisition_headers_all prha, po_requisition_lines_all prla, JAI_PO_REQ_LINES jirl
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = jirl.requisition_line_id
AND ((p_document_no is null) OR (p_document_no is not null and prha.segment1 = p_document_no ))
AND prha.type_lookup_code = p_document_type
AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND prla.line_num = p_document_line_no ))
AND ( (p_suggested_vendor_name IS NULL) OR (p_suggested_vendor_name IS NOT NULL
AND prla.suggested_vendor_name = p_suggested_vendor_name) )
AND ( (p_suggested_vendor_location IS NULL) OR (p_suggested_vendor_location IS NOT NULL
AND prla.suggested_vendor_location = p_suggested_vendor_location))
AND ( (p_old_tax_category_id IS NULL)
OR (p_old_tax_category_id IS NOT NULL AND jirl.tax_category_id = p_old_tax_category_id) )
AND (p_org_id IS NULL OR prla.org_id = p_org_id)
AND (prla.cancel_flag IS NULL OR prla.cancel_flag <> 'Y' )
AND trunc(prla.creation_date) BETWEEN p_from_date AND p_to_date
AND ((prla.closed_date IS NULL) OR (prla.closed_date <= v_today))
AND ( prla.closed_code IS NULL OR prla.closed_code IN (
jai_constants.closed_code_open ,
jai_constants.closed_code_inporcess ,
jai_constants.closed_code_approved ,
jai_constants.closed_code_preapproved ,
jai_constants.closed_code_req_appr ,
jai_constants.closed_code_incomplete ));
SELECT jisl.rowid, jisl.tax_category_id,
oola.header_id, oola.line_id, oola.ship_to_org_id,
oola.inventory_item_id, nvl(oola.ordered_quantity,0) ordered_quantity,
nvl(oola.shipped_quantity,0) shipped_quantity, -- oola.cancelled_quantity,
oola.order_quantity_uom, oola.ship_from_org_id warehouse_id,
jisl.selling_price, jisl.assessable_value,
-- NVL(ooha.org_id,0) org_id,
ooha.sold_to_org_id customer_id,
ooha.source_document_id, ooha.order_number,
ooha.price_list_id, -- ooha.order_category,
ooha.transactional_curr_code currency_code, ooha.conversion_type_code, ooha.conversion_rate,
ooha.conversion_rate_date conversion_date,
ooha.ordered_date date_ordered, ooha.creation_date,
ooha.order_type_id, ooha.order_number document_no, oola.line_number
FROM oe_order_headers_all ooha, oe_order_lines_all oola, JAI_OM_OE_SO_LINES jisl
WHERE ooha.header_id = oola.header_id
AND oola.line_id = jisl.line_id
AND oola.open_flag = 'Y'
AND ((p_document_no is null) OR (p_document_no is not null and ooha.order_number = p_document_no ))
AND ((p_document_line_no IS NULL) OR (p_document_line_no IS NOT NULL AND oola.line_number = p_document_line_no ))
AND ((ooha.cancelled_flag IS NULL) OR (ooha.cancelled_flag <> 'Y'))
AND ( oola.cancelled_quantity IS NULL OR oola.cancelled_quantity = 0 )
AND oola.line_category_code IN ('ORDER', 'MIXED') -- = 'R'
AND oola.flow_status_code not in ('CLOSED','CANCELLED','SHIPPED') --added by ssawant for bug 5604272
AND ((p_customer_id IS NULL)
OR (p_customer_id IS NOT NULL AND oola.sold_to_org_id = p_customer_id))
AND ((p_customer_site_id IS NULL)
OR (p_customer_site_id IS NOT NULL AND oola.ship_to_org_id = p_customer_site_id))
AND ((p_old_tax_category_id IS NULL)
OR (p_old_tax_category_id IS NOT NULL AND jisl.tax_category_id = p_old_tax_category_id))
AND (p_org_id IS NULL OR oola.org_id = p_org_id)
AND trunc( nvl(ooha.ordered_date, ooha.creation_date)) BETWEEN p_from_date AND p_to_date
ORDER BY oola.header_id, oola.line_id;
SELECT price_list_id
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = p_vendor_id
AND Vendor_Site_Id = p_vendor_site_id;
SELECT operand
FROM qp_List_Lines_v
WHERE list_header_id = p_price_list_id
AND product_Id = p_inv_item_id
AND product_uom_code = p_line_uom
AND NVL( start_date_active, v_today - 1 ) <= v_today
AND NVL( end_date_active, v_today + 1 ) >= v_today;
related message or error message is updated in the records processed table( JAI_CMN_MTAX_UPD_DTLS)
2. 27/01/2005 Harshita J for Bug #3765133 . FileVersion# 115.1
Changes made in the Procedure to capture tax_amounts for adhoc taxes.
3. 12/03/2005 Bug 4210102. Added by LGOPALSA - Version 115.2
(1) Added Check file syntax in dbdrv
(2) Added NOCOPY for OUT Parameter
(3) Added CVD and Customs education cess
4. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.3
The Assessable Value is calculated for the transaction. For this, a call is
made to the function ja_in_vat_assessable_value_f.sql with the parameters
relevant for the transaction. This assessable value is again passed to the
procedure that calucates the taxes.
Base bug - #4245089
5. 28/04/2005 rallamse for Bug#4336482, Version 116.1
For SEED there is a change in concurrent "JAINMTCH" to use FND_STANDARD_DATE with STANDARD_DATE format
Procedure ja_in_mass_tax_changes signature modified by converting p_from_date, p_to_date of DATE datatype
to pv_to_date, pv_to_date of varchar2 datatype. The varchar2 values are converted to DATE fromat
using fnd_date.canonical_to_date function.
6. 08-Jun-2005 Version 116.2 jai_cmn_mtax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
7. 13-Jun-2005 File Version: 116.3
Ramananda for bug#4428980. Removal of SQL LITERALs is done
8. 25-Aug-2005 Aiyer bug 4565665,File Version 120.3
Issue : Concurrent program India- Mass Tax REcalculation (JAINMTCH) was throwing the following errors
1. Wrong Number of arguments or types to do_tax_redefaultation .
2. Cannot insert null into JAI_CMN_MTAX_HDRS_ALL.
Reason and Fix:-
1. As the concurrent program JAINMCTH does not have the parameter pv_dbms_output hence the reported error.
This parameter was previously added to debug from backend with dbms_output.
However as dbms_out.put_line is not standards compliant hence was modified to fnd_file.put_line
Now as the parameter pv_debug is already present both in the concurrent program registration and the current procedure
hence removed the pv_dbms_output from both spec and body and instead used the pv_debug for capturing the debug info.
This can now be also enabled from conc program.
2. Last_update_date and last_updated_by are not nulls in table JAI_CMN_MTAX_HDRS_ALL however the current procedure
was not inserting any value in this columnns, hence the reported error. Fixed this issue by adding these columns in the insert
statement.
Dependency Due to this bug:-
jai_cmn_mtax.pls (120.2)
9 25-Aug-2006 Bug 5490479, Added by aiyer, File version 120.7
Issue:-
Org_id parameter in all MOAC related Concurrent programs is getting derived from the profile org_id
As this parameter is hidden hence not visible to users and so users cannot choose any other org_id from the security profile.
Fix:-
1. The Multi_org_category field for all MOAC related concurrent programs should be set to 'S' (indicating single org reports).
This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
security profile.
2. Remove the default value for the parameter p_org_id and make it Required false, Display false. This would ensure that null value gets passed
to the called procedures/ reports.
3. Change the called procedures/reports. Remove the use of p_org_id and instead derive the org_id using the function mo_global.get_current_org_id
This change has been made many procedures and reports.
In the current procedure use of p_org_id is removed and instead ln_org_id a new local variable is defined . Value for it is derived as mentioend above
and replaced at all places where p_org_id was being used.
10 18-05-2007 added by ssawant for bug 5604272
Cursor " CURSOR c_main_so" is modified.
It currently checks the status of a line thru a field called open_flag.
The correct way is to check the flow_Status_code field in the
oe_order_lines_all table . For a closed / Cancelled / SHIPPED order line the
values would be CLOSED CANCELLED SHIPPED respectively. So "AND oola.flow_status_code not in
('CLOSED','CANCELLED','SHIPPED')" condition is added.
===============================================================================
Future Dependencies
Version Author Dependencies Comments
115.2 LGOPALSA IN60106 + Added Cess tax code
4146708
115.3 hjujjuru 4245089 VAT Implelentationfnd_file.put_line(fnd_file.log,
120.3 Aiyer R12 JAI A Changed for bug 4565665. Spec and body change in jai_cmn_mtax_pkg
--------------------------------------------------------------------------------------------------------------------------*/
/* Ramananda for File.Sql.35 */
p_override_manual_taxes := nvl(pv_override_manual_taxes, jai_constants.no);
SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
Value,SUBSTR (value,1,INSTR(value,',') -1)) INTO v_utl_location
FROM v$parameter
WHERE name = 'utl_file_dir';
INSERT INTO JAI_CMN_MTAX_HDRS_ALL
(
batch_id,
org_id,
document_type,
from_date,
to_date,
supplier_id,
supplier_site_id,
customer_id,
customer_site_id,
old_tax_category,
new_tax_category,
process_partial,
document_no,
release_no,
document_line_no,
shipment_no,
commit_interval,
override_manual_taxes,
error_message,
creation_date,
created_by,
last_update_date, /* Aiyer for the bug 4565665. Added the columns last_update_date and last_updated_by */
last_updated_by,
program_application_id,
program_id,
program_login_id,
request_id
)
VALUES
(
--v_batch_id,
JAI_CMN_MTAX_HDRS_ALL_S.nextval,
ln_org_id,
p_document_type,
p_from_date,
p_to_date,
p_supplier_id,
p_supplier_site_id,
p_customer_id,
p_customer_site_id,
p_old_tax_category,
p_new_tax_category,
p_process_partial,
p_document_no,
p_release_no,
p_document_line_no,
p_shipment_no,
p_commit_interval,
p_override_manual_taxes,
null,
SYSDATE,
v_created_by,/* Aiyer for the bug 4565665. Added the columns last_update_date and last_updated_by */
SYSDATE,
v_created_by,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID')
)returning batch_id into v_batch_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
UPDATE JAI_CMN_MTAX_HDRS_ALL
SET error_message = v_message
WHERE batch_id = v_batch_id;
INSERT INTO JAI_CMN_MTAX_UPD_DTLS ( MTAX_DTL_ID,
batch_id,
detail_id,
document_type,
document_no,
document_line_no,
shipment_no,
old_tax_category_id,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES (
jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id,
shipment_rec.line_focus_id,
shipment_rec.shipment_type,
shipment_rec.document_no,
shipment_rec.line_num,
shipment_rec.shipment_num,
shipment_rec.tax_category_id,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
insert into JAI_PO_TAXES
(tax_line_no,po_line_id,po_header_id,
line_focus_id,tax_id, tax_amount,
creation_date,created_by,
last_update_date, last_updated_by,last_update_login)
SELECT
A.tax_line_no,A.po_line_id,A.po_header_id,
-A.line_focus_id,A.tax_id, A.tax_amount,
A.creation_date,A.created_by,
A.last_update_date, A.last_updated_by,A.last_update_login
FROM
JAI_PO_TAXES A,
JAI_CMN_TAXES_ALL B
WHERE
A.tax_id = B.tax_id AND
line_focus_id = shipment_rec.line_focus_id AND
NVL(adhoc_flag,'N') = 'Y';
DELETE FROM JAI_PO_TAXES
WHERE line_focus_id = shipment_rec.line_focus_id;
v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
INSERT INTO JAI_PO_TAXES(
line_location_id, tax_line_no, po_line_id, po_header_id,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id, currency, tax_rate, qty_rate, uom,
tax_amount, tax_type, vendor_id, modvat_flag,
tax_target_amount, line_focus_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, tax_category_id
) VALUES (
v_line_location_id, j, shipment_rec.po_line_id, shipment_rec.po_header_id,
tax_rec.p_1,
tax_rec.p_2,
tax_rec.p_3,
tax_rec.p_4,
tax_rec.p_5,
tax_rec.p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
tax_rec.p_7,
tax_rec.p_8,
tax_rec.p_9,
tax_rec.p_10,
tax_rec.tax_id, shipment_rec.currency_code, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
0, tax_rec.tax_type, v_vendor_id, v_modvat,
0, shipment_rec.line_focus_id, SYSDATE,
v_created_by, SYSDATE, v_user_id,
v_login_id, v_dflt_tax_category_id
);
/* Harshita - Update the tax_amount in the latest records
to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
UPDATE
JAI_PO_TAXES a
SET
tax_amount = (SELECT tax_amount
FROM JAI_PO_TAXES
where tax_id = a.tax_id
and line_focus_id = -shipment_rec.line_focus_id)
WHERE
line_focus_id = shipment_rec.line_focus_id
and tax_id in (SELECT tax_id
FROM JAI_PO_TAXES
where line_focus_id = -shipment_rec.line_focus_id);
UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
UPDATE JAI_PO_TAXES SET tax_line_no = j
WHERE rowid = tax_rec.rowid;
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_dflt_tax_category_id
WHERE rowid = shipment_rec.rowid;
ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
-- v_message := v_message_01;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
/* Temporary data stored previously will be flushed using following DELETE */
DELETE FROM JAI_PO_TAXES
WHERE line_focus_id = -shipment_rec.line_focus_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = shipment_rec.line_focus_id;
INSERT INTO jai_cmn_mtax_upd_dtls (
mtax_dtl_id,
batch_id,
detail_id,
document_type,
document_no,
document_line_no,
shipment_no,
old_tax_category_id,
new_tax_category_id,
error_reason,
program_application_id,
program_id,
program_login_id,
request_id ,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES ( jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id,
shipment_rec.line_focus_id,
shipment_rec.shipment_type,
shipment_rec.document_no,
shipment_rec.line_num,
shipment_rec.shipment_num,
shipment_rec.tax_category_id,
v_dflt_tax_category_id,
v_message,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
INSERT INTO jai_cmn_mtax_upd_dtls ( mtax_dtl_id,
batch_id,
detail_id,
document_type,
document_no,
release_no,
document_line_no,
shipment_no,
old_tax_category_id,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES (
jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id,
releases_rec.line_focus_id,
releases_rec.shipment_type,
releases_rec.document_no,
releases_rec.release_num,
releases_rec.line_num,
releases_rec.shipment_num,
releases_rec.tax_category_id,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
insert into JAI_PO_TAXES
(tax_line_no,po_line_id,po_header_id,
line_focus_id,tax_id, tax_amount,
creation_date,created_by,
last_update_date, last_updated_by,last_update_login)
SELECT
A.tax_line_no,A.po_line_id,A.po_header_id,
-A.line_focus_id,A.tax_id, A.tax_amount,
A.creation_date,A.created_by,
A.last_update_date, A.last_updated_by,A.last_update_login
FROM
JAI_PO_TAXES A,
JAI_CMN_TAXES_ALL B
WHERE
A.tax_id = B.tax_id AND
line_focus_id = releases_rec.line_focus_id AND
NVL(adhoc_flag,'N') = 'Y';
DELETE FROM JAI_PO_TAXES
WHERE line_focus_id = releases_rec.line_focus_id;
v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
INSERT INTO JAI_PO_TAXES(
line_location_id, tax_line_no, po_line_id, po_header_id,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id, currency, tax_rate, qty_rate, uom,
tax_amount, tax_type, vendor_id, modvat_flag,
tax_target_amount, line_focus_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, tax_category_id
) VALUES (
releases_rec.line_location_id, j, releases_rec.po_line_id, releases_rec.po_header_id,
tax_rec.p_1,
tax_rec.p_2,
tax_rec.p_3,
tax_rec.p_4,
tax_rec.p_5,
tax_rec.p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
tax_rec.p_7,
tax_rec.p_8,
tax_rec.p_9,
tax_rec.p_10,
tax_rec.tax_id, releases_rec.currency_code, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
0, tax_rec.tax_type, v_vendor_id, v_modvat,
0, releases_rec.line_focus_id, SYSDATE,
v_created_by, SYSDATE, v_user_id,
v_login_id, v_dflt_tax_category_id
);
/* Harshita - Update the tax_amount in the latest records
to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
UPDATE
JAI_PO_TAXES a
SET
tax_amount = (SELECT tax_amount
FROM JAI_PO_TAXES
where tax_id = a.tax_id
and line_focus_id = -releases_rec.line_focus_id)
WHERE
line_focus_id = releases_rec.line_focus_id
and tax_id in (SELECT tax_id
FROM JAI_PO_TAXES
WHERE line_focus_id = -releases_rec.line_focus_id);
UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
UPDATE JAI_PO_TAXES SET tax_line_no = j
WHERE rowid = tax_rec.rowid;
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = v_dflt_tax_category_id
WHERE rowid = releases_rec.rowid;
ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
-- v_message := v_message_01;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
/* Temporary data stored previously will be flushed using following DELETE */
DELETE FROM JAI_PO_TAXES
WHERE line_focus_id = -releases_rec.line_focus_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = releases_rec.line_focus_id;
INSERT INTO JAI_CMN_MTAX_UPD_DTLS
(
mtax_dtl_id,
batch_id,
detail_id,
document_type,
document_no,
release_no,
document_line_no,
shipment_no,
old_tax_category_id,
new_tax_category_id,
error_reason,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES ( JAI_CMN_MTAX_UPD_DTLS_S.nextval,
v_batch_id,
releases_rec.line_focus_id,
releases_rec.shipment_type,
releases_rec.document_no,
releases_rec.release_num,
releases_rec.line_num,
releases_rec.shipment_num,
releases_rec.tax_category_id,
v_dflt_tax_category_id,
v_message,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
mtax_dtl_id,
batch_id,
detail_id,
document_type,
document_no,
document_line_no,
old_tax_category_id,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES (
jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id,
reqn_rec.requisition_line_id,
reqn_rec.type_lookup_code,
reqn_rec.document_no,
reqn_rec.line_num,
reqn_rec.tax_category_id,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
insert into JAI_PO_REQ_LINE_TAXES
(requisition_line_id,tax_line_no,
tax_id, tax_amount,
creation_date,created_by,
last_update_date, last_updated_by,last_update_login)
SELECT
-A.requisition_line_id,A.tax_line_no,
A.tax_id, A.tax_amount,
A.creation_date,A.created_by,
A.last_update_date, A.last_updated_by,A.last_update_login
FROM
JAI_PO_REQ_LINE_TAXES A,
JAI_CMN_TAXES_ALL B
WHERE
A.tax_id = B.tax_id AND
requisition_line_id = reqn_rec.requisition_line_id AND
NVL(adhoc_flag,'N') = 'Y' ;
DELETE FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = reqn_rec.requisition_line_id;
v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
INSERT INTO JAI_PO_REQ_LINE_TAXES(
requisition_line_id, requisition_header_id, tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id, tax_rate, qty_rate, uom,
tax_amount, tax_target_amount, tax_type, modvat_flag, vendor_id, currency,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, tax_category_id
) VALUES (
reqn_rec.requisition_line_id, reqn_rec.requisition_header_id, j,
tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
0, 0, tax_rec.tax_type, v_modvat, v_tax_vendor_id, v_currency_code,
SYSDATE, v_created_by, SYSDATE,
v_created_by, v_login_id, v_dflt_tax_category_id
);
/* Harshita - Update the tax_amount in the latest records
to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
UPDATE
JAI_PO_REQ_LINE_TAXES a
SET
tax_amount = (SELECT tax_amount
FROM JAI_PO_REQ_LINE_TAXES
where tax_id = a.tax_id
and requisition_line_id = -reqn_rec.requisition_line_id)
WHERE
requisition_line_id = reqn_rec.requisition_line_id
and tax_id in (SELECT tax_id
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = -reqn_rec.requisition_line_id);
UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
UPDATE JAI_PO_REQ_LINE_TAXES SET tax_line_no = j
WHERE rowid = tax_rec.rowid;
UPDATE JAI_PO_REQ_LINES
SET tax_category_id = v_dflt_tax_category_id
WHERE rowid = reqn_rec.rowid;
ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
-- v_message := v_message_01;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
/* Temporary data stored previously will be flushed using following DELETE */
DELETE FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = -reqn_rec.requisition_line_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = reqn_rec.requisition_line_id;
fnd_file.put_line(fnd_file.log,'Ex. Record Not found so inserting record');
INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
mtax_dtl_id,
batch_id,
detail_id,
document_type,
document_no,
document_line_no,
old_tax_category_id,
new_tax_category_id,
error_reason,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES (
jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id,
reqn_rec.requisition_line_id,
reqn_rec.type_lookup_code,
reqn_rec.document_no,
reqn_rec.line_num,
reqn_rec.tax_category_id,
v_dflt_tax_category_id,
v_message,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
mtax_dtl_id,
batch_id,
detail_id,
document_type,
document_no,
document_line_no,
old_tax_category_id,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES (
jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id,
so_rec.line_id,
'SO',
so_rec.document_no,
so_rec.line_number,
so_rec.tax_category_id,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
insert into JAI_OM_OE_SO_TAXES
(line_id,tax_line_no,header_id,
tax_id, tax_amount,
creation_date,created_by,
last_update_date, last_updated_by,last_update_login)
SELECT
-A.line_id,A.tax_line_no,A.header_id,
A.tax_id, A.tax_amount,
A.creation_date,A.created_by,
A.last_update_date, A.last_updated_by,A.last_update_login
FROM
JAI_OM_OE_SO_TAXES A,
JAI_CMN_TAXES_ALL B
WHERE
A.tax_id = B.tax_id AND
line_id = so_rec.line_id AND
NVL(adhoc_flag,'N') = 'Y';
DELETE FROM JAI_OM_OE_SO_TAXES
WHERE line_id = so_rec.line_id;
v_success := 5; -- means the override manual taxes is selected by user and all the attached taxes with the shipment are removed to default the new taxes
INSERT INTO JAI_OM_OE_SO_TAXES(
tax_line_no, line_id, header_id,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id, tax_rate, qty_rate, uom,
tax_amount, base_tax_amount, func_tax_amount,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, tax_category_id
) VALUES (
j, so_rec.line_id, so_rec.header_id,
tax_rec.p_1, tax_rec.p_2, tax_rec.p_3, tax_rec.p_4, tax_rec.p_5,
tax_rec.p_6, tax_rec.p_7, tax_rec.p_8, tax_rec.p_9, tax_rec.p_10,
tax_rec.tax_id, tax_rec.tax_rate, tax_rec.tax_amount, tax_rec.uom_code,
0, null, null,
SYSDATE, v_created_by, SYSDATE, v_user_id,
v_login_id, v_dflt_tax_category_id
);
/* Harshita - Update the tax_amount in the latest records
to the previous tax amounts for all adhoc tax types. -- Bug #3765133*/
UPDATE
JAI_OM_OE_SO_TAXES a
SET
tax_amount = (SELECT tax_amount
FROM JAI_OM_OE_SO_TAXES
where tax_id = a.tax_id
and line_id = -so_rec.line_id)
WHERE
line_id = so_rec.line_id
and tax_id in (SELECT tax_id
FROM JAI_PO_REQ_LINE_TAXES
WHERE line_id = -so_rec.line_id);
UPDATE JAI_CMN_MTAX_UPD_DTLS SET new_tax_category_id = v_dflt_tax_category_id
WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
UPDATE JAI_OM_OE_SO_TAXES SET tax_line_no = j
WHERE rowid = tax_rec.rowid;
UPDATE JAI_OM_OE_SO_LINES
SET assessable_value = v_assessable_value,
vat_assessable_value = ln_vat_assess_value, -- added, Harshita for bug #4245062
tax_amount = nvl(v_line_tax_amount,0),
line_amount = v_line_amount,
line_tot_amount = v_line_amount + nvl(v_line_tax_amount,0),
last_update_date = SYSDATE,
last_updated_by = v_user_id,
last_update_login = v_login_id,
tax_category_id = v_dflt_tax_category_id
WHERE rowid = so_rec.rowid;
ELSE -- Failed to remove old taxes and insert new taxes because of some reason(will be shown in the LOG)
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
/* Temporary data stored previously will be flushed using following DELETE */
DELETE FROM JAI_OM_OE_SO_TAXES
WHERE line_id = -so_rec.line_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
UPDATE JAI_CMN_MTAX_UPD_DTLS SET error_reason = v_message
WHERE batch_id = v_batch_id AND detail_id = so_rec.line_id;
INSERT INTO JAI_CMN_MTAX_UPD_DTLS (
MTAX_DTL_ID,
batch_id,
detail_id,
document_type,
document_no,
document_line_no,
old_tax_category_id,
new_tax_category_id,
error_reason,
program_application_id,
program_id,
program_login_id,
request_id,
created_by ,/* Aiyer for the bug 4565665. Added the who columns */
creation_date ,
last_updated_by ,
last_update_date
)
VALUES (
jai_cmn_mtax_upd_dtls_s.nextval,
v_batch_id, so_rec.line_id,
'SO',
so_rec.document_no,
so_rec.line_number,
so_rec.tax_category_id,
v_dflt_tax_category_id,
v_message,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID'),
v_created_by,/* Aiyer for the bug 4565665. Added the who columns */
sysdate,
v_created_by,
sysdate
);
UPDATE JAI_CMN_MTAX_HDRS_ALL SET error_message = v_message WHERE batch_id = v_batch_id;
SELECT tax_line_no,
nvl(precedence_1, -1) p_1,
nvl(precedence_2, -1) p_2,
nvl(precedence_3, -1) p_3,
nvl(precedence_4, -1) p_4,
nvl(precedence_5, -1) p_5,
nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
nvl(precedence_7, -1) p_7,
nvl(precedence_8, -1) p_8,
nvl(precedence_9, -1) p_9,
nvl(precedence_10, -1) p_10,
tax_id,
tax_category_id
FROM JAI_PO_TAXES
WHERE line_focus_id = p_line_focus_id;
SELECT tax_line_no,
nvl(precedence_1, -1) p_1,
nvl(precedence_2, -1) p_2,
nvl(precedence_3, -1) p_3,
nvl(precedence_4, -1) p_4,
nvl(precedence_5, -1) p_5,
nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
nvl(precedence_7, -1) p_7,
nvl(precedence_8, -1) p_8,
nvl(precedence_9, -1) p_9,
nvl(precedence_10, -1) p_10,
tax_id,
tax_category_id
FROM JAI_OM_OE_SO_TAXES
WHERE line_id = p_line_id;
SELECT tax_line_no,
nvl(precedence_1, -1) p_1,
nvl(precedence_2, -1) p_2,
nvl(precedence_3, -1) p_3,
nvl(precedence_4, -1) p_4,
nvl(precedence_5, -1) p_5,
nvl(precedence_6, -1) p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
nvl(precedence_7, -1) p_7,
nvl(precedence_8, -1) p_8,
nvl(precedence_9, -1) p_9,
nvl(precedence_10, -1) p_10,
tax_id, tax_category_id
FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = p_requisition_line_id;
defaulted and manual taxes. If there is no discrepency then this procedure will not delete any data and
returns a number which signifies that the procedure failed because of some discrepency.
If procedure is successful, then this returns a number greater than 0
and if it returns number less than 0 then this indicates there occured some dependency problem and
v_message variable will contain the error message.
--------------------------------------------------------------------------------------------------------------------------*/
p_success := 1; -- FLAG that indicates the tax recalculation can be applied by deleting old taxes that are defaulted from tax category
DELETE FROM JAI_PO_TAXES
WHERE line_focus_id = p_line_focus_id AND tax_category_id IS NOT NULL;
DELETE FROM JAI_OM_OE_SO_TAXES
WHERE line_id = p_line_id AND tax_category_id IS NOT NULL;
DELETE FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = p_line_id AND tax_category_id IS NOT NULL;
DELETE FROM JAI_PO_TAXES
WHERE line_focus_id = p_line_focus_id
AND tax_category_id IS NOT NULL;
DELETE FROM JAI_OM_OE_SO_TAXES
WHERE line_id = p_line_id
AND tax_category_id IS NOT NULL;
DELETE FROM JAI_PO_REQ_LINE_TAXES
WHERE requisition_line_id = p_line_id
AND tax_category_id IS NOT NULL;