[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.
*********************************************************************************************************/
FUNCTION get_currency_code(p_operating_unit_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE) RETURN VARCHAR2 IS
/* Bug 5243532. Added by Lakshmi Gopalsami.
Removed the cursor c_set_of_books and c_sob_currency
and implemented using caching logic.
*/
ln_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
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.
*/
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.
*/
OPEN get_curr_rate(v_func_curr,v_frm_curr);
Removed the select and changed the same into a cursor.
*/
OPEN get_curr_rate(v_func_curr,v_frm_curr);
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;