The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR Fetch_Book_Id_Cur IS SELECT Set_Of_Books_Id
FROM Org_Organization_Definitions
WHERE Operating_unit = v_operating_id; -- Modified by Ramananda for removal of SQL LITERALs
select Currency_code
from gl_sets_of_books
where set_of_books_id = v_gl_set_of_bks_id; */
FILENAME: Ja_In_Po_Tax_Delete_Trg.sql
CHANGE HISTORY:
S.No Date Author and Details
1 29-Nov-2004 Sanjikum for 4035297. Version 115.1
Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
2 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
DB as required for CASE COMPLAINCE. Version 116.1
3. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
Dependency Due to this Bug:-
The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
ja_in_po_tax_delete_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 Sanjikum
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_po_line_loc_id := pr_old.Line_Location_Id;
DELETE FROM JAI_PO_LINE_LOCATIONS
WHERE Line_Location_Id = v_po_line_loc_id;
DELETE FROM JAI_PO_TAXES
WHERE Line_Location_Id = v_po_line_loc_id;
v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
v_last_upd_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
SELECT From_Type_Lookup_Code, from_header_id,
Type_Lookup_Code, Quotation_Class_Code,
Vendor_id, Vendor_Site_Id, Currency_Code, Ship_To_Location_Id,
style_id --Added by Sanjikum for Bug#4483042
FROM Po_Headers_All
WHERE Po_Header_Id = v_po_hdr_id;
SELECT Inventory_Organization_Id
FROM Hr_Locations
WHERE Location_Id = v_ship_loc_id;
SELECT Item_Id, From_Header_Id, From_Line_Id, Line_Num
FROM Po_Lines_All
WHERE Po_Line_Id = v_po_line_id;
SELECT Unit_Meas_Lookup_Code
FROM Po_Lines_All
WHERE Po_Line_Id = v_po_line_id;
SELECT Set_Of_Books_Id
FROM Org_Organization_Definitions
WHERE Operating_unit = v_operating_id; -- Modified by Ramananda for removal of SQL LITERALs
select Currency_code
from gl_sets_of_books
where set_of_books_id = v_gl_set_of_bks_id; */
SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
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.currency, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.tax_type, a.vendor_id, a.modvat_flag
FROM JAI_PO_TAXES a
WHERE NVL( a.line_location_id, -999 ) = DECODE( v_quot_line_loc_id, -999, -999, v_quot_line_loc_id )
AND Po_Line_Id = v_from_line_id
ORDER BY a.tax_line_no;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;
SELECT Uom_Code
FROM Mtl_Units_Of_Measure
WHERE Unit_Of_Measure = v_temp_uom;
SELECT COUNT(Line_Location_Id)
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Header_Id = v_quot_from_hdr_id;
SELECT tax_modified_flag
FROM JAI_PO_LINE_LOCATIONS
WHERE line_location_id = p_line_location_id;
ja_in_po_tax_insert_trg.sql
---------------------------------------------------------------------------------------------
618.2(OBSOLETE) 3438863 IN60105D2 + 3438863
619.1 3570189 IN60105D2 + 3570189
115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0
ja_in_util_pkg_s.sql 115.0
115.3 4250072 IN60106 +
4035297 +
4245089
--------------------------------------------------------------------------------------------*/
--File.Sql.35 Cbabu
v_shipment_type := pr_new.Shipment_Type;
v_last_upd_dt := pr_new.Last_Update_Date ;
v_last_upd_by := pr_new.Last_Updated_By;
v_last_upd_login := pr_new.Last_Update_Login;
pr_new.PROGRAM_UPDATE_DATE ,
pr_new.USSGL_TRANSACTION_CODE ,
pr_new.GOVERNMENT_CONTEXT ,
pr_new.RECEIVING_ROUTING_ID ,
pr_new.ACCRUE_ON_RECEIPT_FLAG ,
pr_new.CLOSED_REASON ,
pr_new.CLOSED_DATE ,
pr_new.CLOSED_BY ,
pr_new.ORG_ID ,
pr_new.QUANTITY_SHIPPED ,
pr_new.COUNTRY_OF_ORIGIN_CODE ,
pr_new.TAX_USER_OVERRIDE_FLAG ,
pr_new.MATCH_OPTION ,
pr_new.TAX_CODE_ID ,
pr_new.CALCULATE_TAX_FLAG ,
pr_new.CHANGE_PROMISED_DATE_REASON
);
INSERT INTO JAI_PO_COPYDOC_T(
TYPE, PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, LINE_NUM,
SHIPMENT_NUM, ITEM_ID, FROM_HEADER_ID, FROM_TYPE_LOOKUP_CODE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
) Values (
v_type, v_po_hdr_id, v_po_line_id, v_line_loc_id, v_line_num,
v_shipment_num, v_item_id, v_quot_from_hdr_id, v_from_type_lookup_code,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
);
INSERT INTO JAI_PO_QUOT_LINES_T (
po_header_id, po_line_id, line_location_id, from_header_id,
from_line_id, price_override, uom_code, assessable_value,
creation_date, created_by, last_update_date, last_updated_by, last_update_login
) VALUES (
v_po_hdr_id, v_po_line_id, v_line_loc_id, v_q_hdr_id,
v_q_line_id, v_price, v_unit_code, v_assessable_value,
v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
);
jai_po_cmn_pkg.insert_line( 'CATALOG', v_line_loc_id, v_po_hdr_id, v_po_line_id, v_cre_dt,
v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I' , v_service_type_code -- 5879769, brathod
);
jai_po_cmn_pkg.insert_line( v_type_lookup_code, v_line_loc_id, v_po_hdr_id, v_po_line_id, v_cre_dt,
v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I' ,v_service_type_code -- 5879769, brathod
);
v_last_upd_dt DATE ; --File.Sql.35 Cbabu ; := pr_new.last_update_date ;
v_last_upd_by NUMBER ; --File.Sql.35 Cbabu := pr_new.last_updated_by ;
v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.last_update_login ;
SELECT
nvl(a.tax_amount,0) tax_amount,
nvl(b.adhoc_flag, 'N') adhoc_flag
FROM
JAI_PO_TAXES a ,
JAI_CMN_TAXES_ALL b
WHERE
a.tax_id = b.tax_id AND
a.po_line_id = v_po_line_id AND
a.po_header_id = v_po_hdr_id AND
a.line_location_id = v_line_loc_id FOR UPDATE OF a.tax_amount;
SELECT sum(tax_amount )
FROM JAI_PO_TAXES
WHERE
po_line_id = v_po_line_id AND
po_header_id = v_po_hdr_id AND
line_location_id = v_line_loc_id AND
tax_type <> jai_constants.tax_type_tds ; /*'TDS';Ramananda for removal of SQL LITERALs */
SELECT
type_lookup_code ,
quotation_class_code ,
vendor_id ,
vendor_site_id ,
currency_code ,
ship_to_location_id
, rate_type, rate_date, rate, -- Vijay Shankar for Bug #3184673
style_id
FROM
po_headers_all
WHERE
po_header_id = v_po_hdr_id;
SELECT
Inventory_Organization_Id
FROM
hr_locations
WHERE
location_id = v_ship_loc_id;
SELECT
item_id,
unit_meas_lookup_code
FROM
po_lines_all
WHERE
po_line_id = v_po_line_id;
SELECT
NVL( Tax_Modified_Flag, 'N' )
FROM
JAI_PO_LINE_LOCATIONS
WHERE
Po_Line_Id = v_po_line_id AND
Line_Location_Id = v_line_loc_id;
SELECT
SUM( NVL( Tax_Amount, 0 ) )
FROM
JAI_PO_TAXES
WHERE
po_line_id = v_po_line_id AND
line_location_id = v_line_loc_id AND
tax_type <> jai_constants.tax_type_tds ; /*'TDS';Ramananda for removal of SQL LITERALs */
SELECT
sum( nvl( tax_amount, 0 ) )
FROM
JAI_PO_TAXES
WHERE
po_line_id = v_po_line_id AND
line_location_id is null AND
tax_type <> jai_constants.tax_type_tds ; /*'TDS';Ramananda for removal of SQL LITERALs */
SELECT
nvl(1,0)
FROM
JAI_PO_LINE_LOCATIONS
WHERE
po_line_id = v_po_line_id AND
Line_Location_Id = v_line_loc_id;
SELECT
uom_code
FROM
mtl_units_of_measure
WHERE
unit_of_measure = v_uom_measure;
SELECT set_of_books_id
FROM org_organization_definitions
WHERE operating_unit = v_operating_id; -- Modified by Ramananda for removal of SQL LITERALs
SELECT currency_code
FROM gl_sets_of_books
WHERE
set_of_books_id = v_gl_set_of_bks_id; */
SELECT
sob.currency_code
FROM
financials_system_params_all FSP, gl_sets_of_books SOB
WHERE
FSP.set_of_books_id = SOB.set_of_books_id
AND FSP.org_id = v_operating_id ;
FILENAME: Ja_In_Po_Tax_Update_Trg.sql
S.No dd/mm/yyyy Author and Details
------------------------------------------------------------------------------------------
1. 14/08/2003 Aiyer , Bug #3037284 ,File version 616.1
Changed the triggering condition to fire when cancel_flag is 'Y'.
In case of full cancellation of a Purchase order line (no receipt done),
the lines from JAI_PO_TAXES and JAI_PO_LINE_LOCATIONS are deleted.
In case of partial cancellation of purchase order line, apportion the tax amount
in the ratio of quantity_received to original line quantity i.e quantity_received/quantity
in table JAI_PO_TAXES.
Calculate the tax_amount in the table JAI_PO_LINE_LOCATIONS as a sum of all records
in the table JAI_PO_TAXES for that line_locations_id excluding 'TDS'
type of taxes.
The total amount in JAI_PO_LINE_LOCATIONS is calculated as
(po_line_locations_all.quantity_received * po_line_locations_all.price_override)
+
sum of all taxes from JAI_PO_TAXES for that line_location_id .
2 15/10/2003 Vijay Shankar for Bug #3184673, File version 618.1
Adhoc taxes are not apportioned when quantity is changed which is handled with this bug.
Code is added to update JAI_PO_TAXES tables based on old and new quantity for adhoc taxes
3 29-Nov-2004 Sanjikum for 4035297. Version 115.1
Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
Dependency Due to this Bug:-
The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
4. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.2
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. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
DB as required for CASE COMPLAINCE. Version 116.1
6. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
7. 08-Jul-2005 Sanjikum for Bug#4483042 File Version: 116.3
1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
IL functionality should work or not.
8. 8-Jul-2005 File Version: 116.3
rchandan for bug#4479131
The object is modified to eliminate the paddr usage.
9 . 8-Jul-2005 Sanjikum for Bug#4483042, File Version 117.2
1) Added a new column in cursor - check_rfq_quot_cur
10. 15-Jan-2008 Kevin Cheng for Retroactive Price Enhancement
1) Insert change history table;
Ja_In_Po_Tax_Update_Trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 Sanjikum
115.2 4245062 IN60106 + 4245089 hjujjuru 17/03/2005 VAT Implelentation
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
SELECT 'START_JA_IN_PO_TAX_UPDATE_TRG' INTO v_trigger_name FROM DUAL;
v_last_upd_dt := pr_new.last_update_date ;
v_last_upd_by := pr_new.last_updated_by ;
v_last_upd_login := pr_new.last_update_login ;
pr_new.PROGRAM_UPDATE_DATE ,
pr_new.USSGL_TRANSACTION_CODE ,
pr_new.GOVERNMENT_CONTEXT ,
pr_new.RECEIVING_ROUTING_ID ,
pr_new.ACCRUE_ON_RECEIPT_FLAG ,
pr_new.CLOSED_REASON ,
pr_new.CLOSED_DATE ,
pr_new.CLOSED_BY ,
pr_new.ORG_ID ,
pr_new.QUANTITY_SHIPPED ,
pr_new.COUNTRY_OF_ORIGIN_CODE ,
pr_new.TAX_USER_OVERRIDE_FLAG ,
pr_new.MATCH_OPTION ,
pr_new.TAX_CODE_ID ,
pr_new.CALCULATE_TAX_FLAG ,
pr_new.CHANGE_PROMISED_DATE_REASON
);
JAI_RETRO_PRC_PKG.Insert_Price_Changes( pr_old => pr_old
, pr_new => pr_new
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
then delete the line and associated taxes from JAI_PO_LINE_LOCATIONS
and JAI_PO_TAXES and return .
*/
DELETE
JAI_PO_TAXES
WHERE
line_location_id = v_line_loc_id AND
po_line_id = v_po_line_id AND
po_header_id = v_po_hdr_id;
DELETE
JAI_PO_LINE_LOCATIONS
WHERE
po_line_id = v_po_line_id AND
po_header_id = v_po_hdr_id AND
line_location_id = v_line_loc_id;
Now in this scenario update the line and the associated apportioned taxes in the table JAI_PO_LINE_LOCATIONS
and JAI_PO_TAXES
*/
/*
Update the JAI_PO_TAXES with the apportioned tax_amount
The tax amounts are apportioned in a ratio of the (quantity_received\quantity)
Only the taxes which have a adhoc flag set to 'N' can be apportioned.
Taxes which have adhoc flag set to 'Y' would remain unaffected .
*/
FOR cur_rec_get_tax_amount IN rec_get_tax_amount
LOOP
-- following IF commented by Vijay Shankar for Bug #3184673
-- IF cur_rec_get_tax_amount.adhoc_flag = 'N' THEN
UPDATE
JAI_PO_TAXES
SET
tax_amount = (nvl(v_quantity_received,0) / nvl(v_qty,1)) * nvl(cur_rec_get_tax_amount.tax_amount,0)
WHERE
CURRENT OF rec_get_tax_amount;
The record in ja_in_po_line_location has to be updated with the total of all taxes from JAI_PO_TAXES
excluding the TDS type of taxes. The total_amount should be calculated as (quantity_received * price_override) + total of tax amount
*/
OPEN rec_calc_total_tax ;
UPDATE
JAI_PO_LINE_LOCATIONS
SET
tax_amount = l_total_tax_amount ,
total_amount = nvl(pr_new.quantity_received * pr_new.price_override, 0) + nvl(l_total_tax_amount,0)
WHERE
po_line_id = v_po_line_id AND
po_header_id = v_po_hdr_id AND
line_location_id = v_line_loc_id ;
pr_new.PROGRAM_UPDATE_DATE ,
pr_new.USSGL_TRANSACTION_CODE ,
pr_new.GOVERNMENT_CONTEXT ,
pr_new.RECEIVING_ROUTING_ID ,
pr_new.ACCRUE_ON_RECEIPT_FLAG ,
pr_new.CLOSED_REASON ,
pr_new.CLOSED_DATE ,
pr_new.CLOSED_BY ,
pr_new.ORG_ID ,
pr_new.QUANTITY_SHIPPED ,
pr_new.COUNTRY_OF_ORIGIN_CODE ,
pr_new.TAX_USER_OVERRIDE_FLAG ,
pr_new.MATCH_OPTION ,
pr_new.TAX_CODE_ID ,
pr_new.CALCULATE_TAX_FLAG ,
pr_new.CHANGE_PROMISED_DATE_REASON
);
UPDATE JAI_PO_TAXES a
SET tax_amount = (tax_amount * pr_new.quantity/ v_old_qty ),
tax_target_amount = (tax_target_amount * pr_new.quantity/ v_old_qty)
WHERE line_location_id = v_line_loc_id
AND EXISTS (select 1 from JAI_CMN_TAXES_ALL b where b.tax_id = a.tax_id and b.adhoc_flag = 'Y');
DELETE FROM JAI_PO_TAXES
WHERE Po_Line_Id = v_po_line_id
AND NVL( Line_Location_Id, - 999 ) = NVL( v_line_loc_id, -999 );
DELETE FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = v_po_line_id
AND NVL( Line_Location_Id, - 999 ) = NVL( v_line_loc_id, -999 );
jai_po_cmn_pkg.insert_line( 'CATALOG', v_line_loc_id, v_po_hdr_id, v_po_line_id, v_cre_dt,
v_cre_by,
v_last_upd_dt,
v_last_upd_by,
v_last_upd_login,
'I' );
UPDATE JAI_PO_LINE_LOCATIONS
SET Tax_Amount = v_tax_amt,
Total_Amount = v_tot_amt,
Last_Updated_By = v_last_upd_by,
Last_Update_Date = v_last_upd_dt,
Last_Update_Login = v_last_upd_login
WHERE Po_Line_Id = v_po_line_id
AND Line_Location_Id = v_line_loc_id;