[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Added not null columns in the insert statement of update_rg_slno procedure
8. 06-Dec-2005 rallamse for Bug#4773914, Version 120.2
1) Added a new function get_operating_unit
This function get_operating_unit returns operating unit based on
inventory organization id.
9. 27-Dec-2005 Bug 4906958. Added by Lakshmi Gopalsami Version 120.4
Derived the value for default LE if the value is not retrieved via
default BSV.
10. 26-FEB-2007 SSAWANT , File version 120.7
Forward porting the change in 11.5 bug 4903380 to R12 bug no 5039365 .
Added a function return_valid_date. This function would take varchar2 as input. If
this input is a date then it would return the same otherwise it would return NULL.
This function is currently used in JAINASST.rdf and JAINYEDE.rdf.
Dependency
----------
Yes
11 18-MAY-2007 Bgowrava for Bug#6053352 , file version 120.8
Modified the date related codes to satisfy the GSCC compilance.
12. 09-Mar-2010 Jia for GL Drilldown ER
Add a new function if_IL_drilldown that is used to Enable/Disable drilldown button
according OFI journal source and journal categories.
13. 07-Jun-2010 Modified by Jia for bug#9736876
Issue: TAXES POSTED TO PLA IN FOREIGN CURRENCY
Fix: Modified the return value in function currency_conversion.
14. 21-Sep-2012 mmurtuza for bug 14583195
Issue: POAPPRV WORKFLOW NOT SHOWING UP THE VALUE OF NON RECOVERABLE TAX
Fix: Added call to AD_EVENT_REGISTRY_PKG.Is_Event_Done in FUNCTION check_jai_exists to check JAI tables
15. 15-Mar-2013 mmurtuza for bug 16456183
Issue: WHILE BOOKING THE ORDER CURRENCY CONVERSION VALIDATION IS MISSING
Fix: Commented use of cursor get_curr_rate and used select statement instead
*********************************************************************************************************/
G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
SELECT currency_code
FROM gl_ledgers
WHERE ledger_id = cp_set_of_books_id;
SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = p_inventory_orgn_id;
PROCEDURE update_rg_slno(
pn_organization_id IN NUMBER,
pn_location_id IN NUMBER,
pv_register_type IN VARCHAR2,
pn_fin_year IN NUMBER,
pn_txn_amt IN NUMBER,
pn_slno OUT NOCOPY NUMBER,
pn_opening_balance OUT NOCOPY NUMBER,
pn_closing_balance OUT NOCOPY NUMBER
) IS
ln_fin_year NUMBER;
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.update_rg_slno';
PROCEDURE insert_record(
pn_organization_id NUMBER,
pn_location_id NUMBER,
pn_current_fin_year NUMBER,
pv_register_type VARCHAR2)
IS
PRAGMA autonomous_transaction;
INSERT INTO JAI_CMN_RG_SLNOS
(organization_id, location_id, current_fin_year, register_type, slno, balance,created_by, creation_date, last_updated_by, last_update_date )
VALUES
(pn_organization_id, pn_location_id, pn_current_fin_year, pv_register_type, 0, 0, fnd_global.user_id , sysdate, fnd_global.user_id , sysdate);
END insert_record;
UPDATE JAI_CMN_RG_SLNOS
SET slno = NVL(slno,0) + 1,
balance = NVL(balance,0) + pn_txn_amt
WHERE register_type = pv_register_type
AND organization_id = pn_organization_id
AND location_id = pn_location_id
RETURNING slno, balance, current_fin_year INTO ln_slno, ln_closing_balance, ln_fin_year;
insert_record(pn_organization_id, pn_location_id, pn_fin_year, pv_register_type);
UPDATE JAI_CMN_RG_SLNOS
SET slno = NVL(slno,0) + 1,
balance = NVL(balance,0) + pn_txn_amt
WHERE register_type = pv_register_type
AND organization_id = pn_organization_id
AND location_id = pn_location_id
RETURNING slno, balance, current_fin_year INTO ln_slno, ln_closing_balance, ln_fin_year;
UPDATE JAI_CMN_RG_SLNOS
SET slno = 1,
current_fin_year = pn_fin_year
WHERE register_type = pv_register_type
AND organization_id = pn_organization_id
AND location_id = pn_location_id
RETURNING slno, current_fin_year INTO ln_slno, ln_fin_year;
END update_rg_slno;
Select currency_code from gl_sets_of_books
where set_of_books_id = c_set_of_books_id;
CURSOR Chk_Derived_Type_Cur IS SELECT Derive_type FROM Fnd_Currencies
WHERE Currency_Code in (v_frm_curr);
Changed the select to get the rate into cursor.
*/
/*Commented use of cursor bug mmurtua for bug 16456183*/
/*CURSOR get_curr_rate(p_to_curr IN varchar2,
p_from_curr IN varchar2) IS
SELECT Conversion_Rate
FROM Gl_Daily_Rates
WHERE To_Currency = p_to_curr
and From_Currency = p_from_curr
and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
and Conversion_Type = c_conversion_type;*/
Removed the select and changed the same into a cursor.
*/
/*Commented use of cursor and used select statement bug mmurtua for bug 16456183*/
/*OPEN get_curr_rate(v_func_curr,v_frm_curr);
SELECT Conversion_Rate INTO ret_value
FROM Gl_Daily_Rates
WHERE To_Currency = v_func_curr
and From_Currency = v_frm_curr
and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
and Conversion_Type = c_conversion_type;
Removed the select and changed the same into a cursor.
*/
/*Commented use of cursor and used select statement bug mmurtua for bug 16456183*/
/*OPEN get_curr_rate(v_func_curr,v_frm_curr);
SELECT Conversion_Rate INTO ret_value
FROM Gl_Daily_Rates
WHERE To_Currency = v_func_curr
and From_Currency = v_frm_curr
and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
and Conversion_Type = c_conversion_type;
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 = lv_name; --rchandan for bug#4428980
CURSOR C_Uom_Type(p_uom_code varchar2) IS Select upper(um_type),upper(ref_um) From sy_uoms_mst
Where upper(um_code) = upper(p_uom_code);
Select std_factor From sy_uoms_mst
Where um_code = p_uom_code;
Select type_factor From ic_item_cnv
Where um_type = p_uom_type and
item_id = p_item_id;
Select item_um From ic_item_mst
Where item_id = p_item_id;
Select excise_calc_base -- , assessable_value (Commented as Assessable Value is picked by other conditions now )
From JAI_OPM_ITM_MASTERS
Where item_id = p_item_id;
Select Pricelist_Id
From JAI_OPM_CUSTOMERS
Where Cust_id = p_cust_id ;
Select a.Base_Price
From Op_Prce_Itm a ,op_prce_eff b
Where a.pricelist_id = b.pricelist_id
And a.Pricelist_Id = p_Pricelist_id
And a.Item_Id = p_item_id
And sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate) ;
Select assessable_value
From JAI_OPM_ITM_MASTERS
Where item_id = p_item_id;
SELECT XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_org_id)
FROM dual;
SELECT progress_payment_flag
FROM po_doc_style_headers
WHERE style_id = cp_style_id;
SELECT style_id
FROM po_headers_all
WHERE po_header_id = cp_po_header_id;
select user_je_source_name
from gl_je_sources gjs
where gjs.je_source_name = pn_je_source;
select user_je_category_name
from gl_je_categories gjc
where gjc.je_category_name = pn_je_category;
SELECT to_date(attribute_value, 'DD/MM/YYYY')
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = p_regime_id
AND organization_id = p_organization_id
AND location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(SELECT '1'
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = p_regime_id
AND attribute_code IN 'INV_ORG_CLASSIFICATION'
AND attribute_value <> 'ORGANIZATION'
AND organization_id = p_organization_id
AND location_id = p_location_id)
OR
NOT EXISTS
(SELECT '1'
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = p_regime_id
AND attribute_code IN 'SERVICE TYPE'
AND attribute_value <> 'OTHER'
AND organization_id = p_organization_id
AND location_id = p_location_id)
);