Sunday, 11 June 2023
Thursday, 8 June 2023
EBS : Password change from backend in Oracle APPS
How to changes Password from Backend in Oracle Apps
DECLARE
V_pwdchanged BOOLEAN;
BEGIN
v_pwdchanged := fnd_user_pkg.changepassword('TCHHARDS','12345');
IF v_pwdchanged THEN
DBMS_OUTPUT.put_line('password changed Sucessfully');
ELSE
DBMS_OUTPUT.put_line('Failed to change the Password');
END IF;
END;
*********************************************************************************
#Oracle Apps #EBS #fnd_user_pkg #Password
EBS : PLSQL : How to generate ID based on input date : Oracle Apps
How to generate ID based on input date in PLSQL
CREATE OR REPLACE FUNCTION generate_estqs_id (p_quote_date IN DATE)
RETURN NUMBER
IS
lv_month NUMBER;
lv_year NUMBER;
lv_year_next NUMBER;
lv_max_no NUMBER;
lv_from_date DATE;
lv_to_date DATE;
lv_quote_date DATE;
lv_estimtation_id NUMBER;
BEGIN
lv_quote_date := p_quote_date;
SELECT TO_CHAR (lv_quote_date, 'MM')
INTO lv_month
FROM DUAL;
DBMS_OUTPUT.put_line ('lv_month' || '-' || lv_month);
IF lv_month IN ('01', '02', '03')
THEN
SELECT TO_CHAR (lv_quote_date, 'YY') - 1
INTO lv_year
FROM DUAL;
ELSE
SELECT TO_CHAR (lv_quote_date, 'YY')
INTO lv_year
FROM DUAL;
END IF;
lv_year_next := lv_year + 1;
DBMS_OUTPUT.put_line (lv_year || '-' || lv_year_next);
SELECT TO_CHAR ('01-Apr-' || lv_year), TO_CHAR ('31-Mar-' || lv_year_next)
INTO lv_from_date, lv_to_date
FROM DUAL;
DBMS_OUTPUT.put_line (lv_from_date || '-' || lv_to_date);
SELECT NVL (SUBSTR (NVL (MAX (estimation_id), 0),
5,
LENGTH (MAX (estimation_id))
),
0
)
+ 1
INTO lv_max_no
FROM xx_estqs_hdr
WHERE TRUNC (creation_date) BETWEEN lv_from_date AND lv_to_date;
DBMS_OUTPUT.put_line ('lv_max_no' || '-' || lv_max_no);
--lv_ESTIMTATION_id := lv_year || lv_year_next || '0000000' || lv_max_no;
lv_estimtation_id := lv_year || lv_year_next || LPAD (lv_max_no, 8, 0);
RETURN lv_estimtation_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'Unable to Generate ESTIMTATION Id';
END;
EBS : AP : Query to Get List Of Approver for IExpense Transaction using API : Oracle Apps
Query to Get List Of Approver for IExpense Transaction using API
Dynamic Approver List for AME
DECLARE
p_application_id NUMBER;
p_transaction_type VARCHAR2 (200);
p_transaction_id VARCHAR2 (200);
p_apprs_view_type VARCHAR2 (200);
p_coa_insertions_flag VARCHAR2 (200);
p_ame_approvers_list ame_approver_record2_table_ss ;---apps.ame_dynamic_approval_pkg.ame_approver_record2_table_ss;
p_ame_order_type_list ame_insertion_record2_table_ss:= ame_insertion_record2_table_ss(); --apps.ame_dynamic_approval_pkg.ame_insertion_record2_table_ss;
p_all_approvers_count VARCHAR2 (200);
p_warning_msg_name VARCHAR2 (200);
p_error_msg_text VARCHAR2 (200);
BEGIN
p_application_id := 200;
p_transaction_type := 'APEXP';
p_transaction_id := 99878;
p_apprs_view_type := 'Active';
p_coa_insertions_flag := NULL;
-- P_AME_APPROVERS_LIST := NULL; Modify the code to initialize this parameter
-- P_AME_ORDER_TYPE_LIST := NULL; Modify the code to initialize this parameter
p_all_approvers_count := NULL;
p_warning_msg_name := NULL;
p_error_msg_text := NULL;
p_ame_approvers_list := ame_approver_record2_table_ss();
apps.ame_dynamic_approval_pkg.get_ame_apprs_and_ins_list
(p_application_id,
p_transaction_type,
p_transaction_id,
p_apprs_view_type,
p_coa_insertions_flag,
p_ame_approvers_list,
p_ame_order_type_list,
p_all_approvers_count,
p_warning_msg_name,
p_error_msg_text
);
DBMS_OUTPUT.put_line ( 'Alam:-'
|| p_all_approvers_count
|| ','
|| p_warning_msg_name
|| ','
|| p_error_msg_text
);
IF(p_ame_approvers_list.count() ) > 0 THEN
FOR i IN 1..p_ame_approvers_list.count() LOOP
dbms_output.put_line(' approver_order_number =>'|| p_ame_approvers_list(i).approver_order_number
||' **** display_name =>'||p_ame_approvers_list(i).display_name
--' **** Approver Category =>'||p_ame_approvers_list(i).approver_category
--' **** approval_status =>' ||p_ame_approvers_list(i).approval_status
--' **** source =>'||p_ame_approvers_list(i).source
);
END LOOP;
ELSE
dbms_output.put_line(' No Approver Found');
END IF;
END;
-- COMMIT;
END;
MACROS : How to Remove tabs and carriage returns from worksheet cells in Excel
Remove tabs and carriage returns from worksheet cells
Sub CleanUp()
Dim TheCell As Range
On Error Resume Next
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub
Wednesday, 7 June 2023
EBS : PA Costing : Steps of Project Costing Expenditure Booking
Steps of Project Costing of Expenditure Booking
- Expenditure
Entered
select * from pa_expenditures_all where EXPENDITURE_GROUP ='Test' order by creation_date desc;
select * from pa_expenditure_items_all where
EXPENDITURE_ID=878744;
- PRC:
Distribute Labor Costs -> program ran to generate cost.
select acct_event_id,pcd.*
from pa_cost_distribution_lines_all pcd where EXPENDITURE_item_ID in
(1840951,1840952,1840953);
- PRC:
Generate Cost Accounting Events -> Program ran to generate costing
events and data will be generated in XLA
a.
Update eventid in pa_cost_distribution_lines_all
b.
Populate data in -> select * from xla_events where event_id in
(36442497,36442498,36442499);
- PRC:
Create Accounting -> populate data in subledger
select * from xla_ae_headers where event_id in
(36442497,36442498,36442499)
order by creation_date desc;
select * from xla_ae_lines where AE_HEADER_ID in
(35023524,35023525,35023526);
order by creation_date desc;
- Journal
Import -> create journal in GL and post it.
select * from gl_je_lines where reference_6 in ('36442497','36442498','36442499') --event_id
and reference_7 in
('35023524','35023525','35023526') --ae_header_id
and je_header_id in (1685653,1685655,1685654)
select * from gl_je_headers where je_category='Labor Cost' and je_source ='Project Accounting'
and je_header_id in (1685653,1685655,1685654) order by creation_date desc;
EBS : API to run Process Transaction in Oracle Apps : Oracle Apps
API to run Process Transaction in Oracle Apps
This request will pick data from MTL Transaction Interface Table and insert into Standard Table. Below code is used to submit this request from backend.
v_out :=
inv_txn_manager_pub.process_transactions
(p_api_version =>
1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_trans_count => v_trans_count,
p_table =>
1,
p_header_id => r_txn_rec.transaction_header_id
);
Wednesday, 31 May 2023
EBS : OAF : Make text field BOLD in Oracle Apps
How to make text field value bold in OAF Page
1) Go to Field and select item type as "Raw Text"
2) Put below below in Text Box.
<font face="Helvetica" color="red" size="4pt" weight="bold"><b>Learn and gain experience from Tech hards BLOG</b></font>
EBS : API to Explode BOM : Oracle APPS
How to Explode BOM in Oracle ( Bill of Material)
Declare
Cursor cur_rec is
select * from BOM_BILL_OF_MATERIALS_V
where organization_id=85
and assembly_item_id in (835);--,1024,1022,1029);
l_err_msg varchar2(2000); l_error_code varchar2(2000);
BEGIN
delete from bom_explosion_temp;
For I in cur_rec loop
---FND_MESSAGE.DEBUG('1---'||sql%rowcount);
bompexpl.exploder_userexit (verify_flag => 0,
org_id => i.ORGANIZATION_ID,
order_by => 1,
grp_id => 99,
session_id => 5,
levels_to_explode => 60,
bom_or_eng => 1,
impl_flag => 1,
plan_factor_flag => 2,
explode_option => 2,
module => 2,
cst_type_id => 0,
std_comp_flag => 2,
expl_qty => 1,
item_id => i.assembly_item_id,
alt_desg => '',
comp_code => '',
rev_date => NULL,
err_msg => l_err_msg,
error_code => l_error_code
);
dbms_output.put_line(l_err_msg);
/* Formatted on 2015/03/04 17:26 (Formatter Plus v4.8.8) */
INSERT INTO xx_bom_explosion_temp
(top_item_id, plan_level, item_code,
item_desc, item_num,
operation_seq_num, component_quantity,attribute15)
SELECT xbet.top_item_id, plan_level,
LPAD (' ', 2 * (LEVEL - 1)) || TO_CHAR (msi.concatenated_segments) bom_item_code,
msi.description, xbet.item_num,
xbet.operation_seq_num,
xbet.component_quantity -- bet.uom
,
UPPER (bic.inventory_item_status_code) status
FROM bom_explosion_temp xbet,
bom_inventory_components_v bic,
mtl_system_items_kfv msi
WHERE /*xbet.top_item_id = :sample_bom_item_id
AND */ xbet.organization_id = :inv_org_id
AND xbet.component_item_id = bic.component_item_id(+)
AND xbet.bill_sequence_id = bic.bill_sequence_id(+)
AND xbet.component_item_id = msi.inventory_item_id
AND xbet.organization_id = msi.organization_id
START WITH xbet.assembly_item_id IS NULL
CONNECT BY PRIOR xbet.component_item_id = xbet.assembly_item_id;
end loop;
commit;
END;
=========================================================================
Table Scripts to create BOM TEMP TABLE
=========================================================================
CREATE TABLE XX_BOM_EXPLOSION_TEMP
(
TOP_BILL_SEQUENCE_ID NUMBER NOT NULL,
BILL_SEQUENCE_ID NUMBER NOT NULL,
ORGANIZATION_ID NUMBER NOT NULL,
COMPONENT_SEQUENCE_ID NUMBER,
COMPONENT_ITEM_ID NUMBER,
PLAN_LEVEL NUMBER NOT NULL,
EXTENDED_QUANTITY NUMBER,
SORT_ORDER VARCHAR2(2000 BYTE) NOT NULL,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
GROUP_ID NUMBER,
SESSION_ID NUMBER,
SELECT_FLAG VARCHAR2(1 BYTE),
SELECT_QUANTITY NUMBER,
EXTEND_COST_FLAG NUMBER,
TOP_ALTERNATE_DESIGNATOR VARCHAR2(10 BYTE),
TOP_ITEM_ID NUMBER,
CONTEXT VARCHAR2(30 BYTE),
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150 BYTE),
ATTRIBUTE11 VARCHAR2(150 BYTE),
ATTRIBUTE12 VARCHAR2(150 BYTE),
ATTRIBUTE13 VARCHAR2(150 BYTE),
ATTRIBUTE14 VARCHAR2(150 BYTE),
ATTRIBUTE15 VARCHAR2(150 BYTE),
HEADER_ID NUMBER,
LINE_ID NUMBER,
LIST_PRICE NUMBER,
SELLING_PRICE NUMBER,
COMPONENT_YIELD_FACTOR NUMBER,
ITEM_COST NUMBER,
INCLUDE_IN_ROLLUP_FLAG NUMBER,
BASED_ON_ROLLUP_FLAG NUMBER,
ACTUAL_COST_TYPE_ID NUMBER,
COMPONENT_QUANTITY NUMBER,
SHRINKAGE_RATE NUMBER,
SO_BASIS NUMBER,
OPTIONAL NUMBER,
MUTUALLY_EXCLUSIVE_OPTIONS NUMBER,
CHECK_ATP NUMBER,
SHIPPING_ALLOWED NUMBER,
REQUIRED_TO_SHIP NUMBER,
REQUIRED_FOR_REVENUE NUMBER,
INCLUDE_ON_SHIP_DOCS NUMBER,
INCLUDE_ON_BILL_DOCS NUMBER,
LOW_QUANTITY NUMBER,
HIGH_QUANTITY NUMBER,
PICK_COMPONENTS NUMBER,
PRIMARY_UOM_CODE VARCHAR2(3 BYTE),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(25 BYTE),
BASE_ITEM_ID NUMBER,
ATP_COMPONENTS_FLAG VARCHAR2(1 BYTE),
ATP_FLAG VARCHAR2(1 BYTE),
BOM_ITEM_TYPE NUMBER,
PICK_COMPONENTS_FLAG VARCHAR2(1 BYTE),
REPLENISH_TO_ORDER_FLAG VARCHAR2(1 BYTE),
SHIPPABLE_ITEM_FLAG VARCHAR2(1 BYTE),
CUSTOMER_ORDER_FLAG VARCHAR2(1 BYTE),
INTERNAL_ORDER_FLAG VARCHAR2(1 BYTE),
CUSTOMER_ORDER_ENABLED_FLAG VARCHAR2(1 BYTE),
INTERNAL_ORDER_ENABLED_FLAG VARCHAR2(1 BYTE),
SO_TRANSACTIONS_FLAG VARCHAR2(1 BYTE),
MTL_TRANSACTIONS_ENABLED_FLAG VARCHAR2(1 BYTE),
STOCK_ENABLED_FLAG VARCHAR2(1 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
ASSEMBLY_ITEM_ID NUMBER,
ALTERNATE_BOM_DESIGNATOR VARCHAR2(10 BYTE),
CONFIGURATOR_FLAG VARCHAR2(1 BYTE),
PRICE_LIST_ID NUMBER,
ROUNDING_FACTOR NUMBER,
PRICING_CONTEXT VARCHAR2(30 BYTE),
PRICING_ATTRIBUTE1 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE2 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE3 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE4 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE5 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE6 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE7 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE8 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE9 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE10 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE11 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE12 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE13 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE14 VARCHAR2(150 BYTE),
PRICING_ATTRIBUTE15 VARCHAR2(150 BYTE),
COMPONENT_CODE VARCHAR2(1000 BYTE),
LOOP_FLAG NUMBER,
INVENTORY_ASSET_FLAG NUMBER,
PLANNING_FACTOR NUMBER,
OPERATION_SEQ_NUM NUMBER,
PARENT_BOM_ITEM_TYPE NUMBER,
WIP_SUPPLY_TYPE NUMBER,
ITEM_NUM NUMBER,
EFFECTIVITY_DATE DATE,
DISABLE_DATE DATE,
IMPLEMENTATION_DATE DATE,
SUPPLY_SUBINVENTORY VARCHAR2(10 BYTE),
SUPPLY_LOCATOR_ID NUMBER,
COMPONENT_REMARKS VARCHAR2(240 BYTE),
CHANGE_NOTICE VARCHAR2(10 BYTE),
OPERATION_LEAD_TIME_PERCENT NUMBER,
REXPLODE_FLAG NUMBER,
COMMON_BILL_SEQUENCE_ID NUMBER,
COMMON_ORGANIZATION_ID NUMBER,
PRIMARY_PATH_FLAG NUMBER,
AUTO_REQUEST_MATERIAL VARCHAR2(1 BYTE),
EXPLOSION_TYPE VARCHAR2(20 BYTE),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
COMP_BILL_SEQ_ID NUMBER,
COMP_COMMON_BILL_SEQ_ID NUMBER,
NUM_COL1 NUMBER,
NUM_COL2 NUMBER,
NUM_COL3 NUMBER,
DATE_COL1 DATE,
DATE_COL2 DATE,
DATE_COL3 DATE,
CHAR_COL1 VARCHAR2(80 BYTE),
CHAR_COL2 VARCHAR2(80 BYTE),
CHAR_COL3 VARCHAR2(80 BYTE),
INCLUDE_IN_COST_ROLLUP NUMBER,
PARENT_SORT_ORDER VARCHAR2(2000 BYTE),
STRUCTURE_TYPE_ID NUMBER,
OBJ_NAME VARCHAR2(30 BYTE),
PK1_VALUE VARCHAR2(240 BYTE),
PK2_VALUE VARCHAR2(240 BYTE),
PK3_VALUE VARCHAR2(240 BYTE),
PK4_VALUE VARCHAR2(240 BYTE),
PK5_VALUE VARCHAR2(240 BYTE),
HGRID_FLAG VARCHAR2(240 BYTE),
OPERATION_OFFSET NUMBER,
CURRENT_REVISION VARCHAR2(3 BYTE),
LOCATOR VARCHAR2(40 BYTE),
SUGGESTED_VENDOR_NAME VARCHAR2(240 BYTE),
UNIT_PRICE NUMBER,
VENDOR_ID NUMBER,
FROM_END_ITEM_UNIT_NUMBER VARCHAR2(30 BYTE),
TO_END_ITEM_UNIT_NUMBER VARCHAR2(30 BYTE),
FROM_END_ITEM_REV_ID NUMBER,
FROM_END_ITEM_MINOR_REV_ID NUMBER,
TO_END_ITEM_REV_ID NUMBER,
TO_END_ITEM_MINOR_REV_ID NUMBER,
NEW_COMPONENT_CODE VARCHAR2(4000 BYTE),
FROM_BILL_REVISION_ID NUMBER,
TO_BILL_REVISION_ID NUMBER,
REVISION_ID NUMBER,
REVISION_LABEL VARCHAR2(260 BYTE),
EFFECTIVITY_CONTROL NUMBER,
ACCESS_FLAG VARCHAR2(1 BYTE),
ENG_ITEM_FLAG VARCHAR2(1 BYTE),
ASSEMBLY_TYPE NUMBER,
FROM_OBJECT_REVISION_ID NUMBER,
FROM_MINOR_REVISION_ID NUMBER,
TO_OBJECT_REVISION_ID NUMBER,
TO_MINOR_REVISION_ID NUMBER,
OBJECT_REVISION_ID NUMBER,
MINOR_REVISION_ID NUMBER,
MINOR_REVISION_CODE VARCHAR2(30 BYTE),
COMPONENT_ITEM_REVISION_ID NUMBER,
COMPONENT_MINOR_REVISION_ID NUMBER,
BOM_IMPLEMENTATION_DATE DATE,
GTIN_NUMBER VARCHAR2(25 BYTE),
GTIN_DESCRIPTION VARCHAR2(240 BYTE),
TRADE_ITEM_DESCRIPTOR VARCHAR2(35 BYTE),
TRADE_ITEM_DESCRIPTOR_DESC VARCHAR2(80 BYTE),
GTIN_PUBLICATION_STATUS VARCHAR2(1 BYTE),
TOP_GTIN_NUMBER VARCHAR2(25 BYTE),
TOP_GTIN_DESCRIPTION VARCHAR2(240 BYTE),
TOP_TRADE_ITEM_DESCRIPTOR VARCHAR2(35 BYTE),
PARENT_GTIN_NUMBER VARCHAR2(25 BYTE),
PARENT_GTIN_DESCRIPTION VARCHAR2(240 BYTE),
PARENT_TRADE_ITEM_DESCRIPTOR VARCHAR2(35 BYTE),
QUANTITY_OF_CHILDREN NUMBER,
TOTAL_QTY_AT_NEXT_LEVEL NUMBER,
CHANGE_ID NUMBER,
ACD_TYPE NUMBER,
QUANTITY_RELATED NUMBER,
CHANGE_POLICY_VALUE NUMBER,
EXPLODED_DATE DATE,
EXPLODED_UNIT_NUMBER VARCHAR2(30 BYTE),
EXPLODED_END_ITEM_REV NUMBER,
EXPLODED_OPTION NUMBER,
BASIS_TYPE NUMBER,
ITEM_CODE VARCHAR2(240 BYTE),
ITEM_DESC VARCHAR2(2000 BYTE)
);
=========================================================================
Query to get BOM Explosion from TEMP TABLE
=========================================================================
SELECT xxleg.top_item_id,plan_level,LPAD (' ', 2 * (LEVEL - 1)) || TO_CHAR (msi.CONCATENATED_SEGMENTS) bom_item_code,
msi.DESCRIPTION,
xxleg.item_num,
xxleg.operation_seq_num, xxleg.component_quantity-- bet.uom
,UPPER (bic.inventory_item_status_code) status
FROM bom_explosion_temp xxleg,bom_inventory_components_v bic, mtl_system_items_kfv msi
WHERE /*xxleg.top_item_id = :sample_bom_item_id
AND */xxleg.organization_id = :inv_org_id
AND xxleg.component_item_id = bic.component_item_id (+)
AND xxleg.bill_sequence_id = bic.bill_sequence_id (+)
and xxleg.COMPONENT_ITEM_ID=msi.INVENTORY_ITEM_ID
and xxleg.ORGANIZATION_ID=msi.ORGANIZATION_ID
START WITH xxleg.assembly_item_id IS NULL
CONNECT BY PRIOR xxleg.component_item_id = xxleg.assembly_item_id;
Order by xxleg.top_item_id
--xxleg.plan_level,item_num,operation_seq_num;
***********************************************************************************
#Oracle Apps #EBS #BOM #Bill Of Material #WIP #BOM Explode, Explosion #Intended Bill of Material
Some Important BOM Table:-
- BOM_BILL_OF_MATERIALS
- BOM_RESOURCES
- BOM_DEPARTMENTS
- BOM_OPERATIONAL_ROUTINGS
- BOM_OPERATION_SEQUENCES
- BOM_OPERATION_SEQUENCES
- BOM_INVENTORY_COMPONENTS
- BOM_STANDARD_OPERATIONS
Saturday, 6 May 2023
Tuesday, 2 May 2023
Query : user Password
--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/
--Query to execute
SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = ':USER_NAME';
Subscribe to:
Posts (Atom)