How can I find the LOV query behind an Oracle Forms List of Values (LOV)?

Example: 

I want to find the Leaving reason LOV Query. 

Step1: 

    Go to Help --> About Oracle Application --> Find the Form Name

Step2: 

    Open the Leaving reason LOV as shown from the above screenshot. 

Step3:

    Run the Below Query:

        SELECT TO_CHAR(sq.sql_fulltext) AS perwstem_lov_query

FROM v$session ses, 

     v$sqlarea sq

WHERE ses.module LIKE '%PERWSTEM%' -- Form Name

 --AND ses.client_identifier = '&your_ebs_username'

  AND sq.sql_id = ses.prev_sql_id;

Result: 

  select meaning ,lookup_code from hr_leg_lookups where lookup_type = 'LEAV_REAS' and enabled_flag = 'Y' and :1 between nvl(start_date_active, :2) and nvl(end_date_active, :3) order by meaning

Password Protection in Oracle RTF File

Password Protection in Oracle RTF Report for PDF Output. 

Step: 1

    Add Password in the Select statement to pass the dynamic password to the document based on your requirement. Here i am giving example with Employee date of birth in DD-MON-YYYY format as dynamic password. 

Query : 

SELECT employee_number,

       full_name employee_name,

       to_char(date_of_birth, 'DD-MON-YYYY') pwd_date_of_bith

  FROM per_all_people_f

 WHERE sysdate BETWEEN effective_start_date AND

       nvl(effective_end_date, sysdate);

Step 2: 

    Develop report with Data model or RDF and generate the XML file. 

Ste 3: 

    With help of XML file, Develop a report in the as per the required format in RTF template. 

Step 4: 

    To enable the password, Open the RTF file

        Navigation: File --> Info --> Properties --> Advanced Properties -- Go to Custom and Add parameters and values

Password Protection: 

xdo-pdf-security - true

xdo-pdf-open-password - {/XX_EMP_DETAIL/LIST_G_EMPLOYEE_NUMBER/G_EMPLOYEE_NUMBER/DATE_OF_BIRTH}

DATE_OF_BIRTH is the field we are passing as dynamic password to the report. 

Add two parameter values and Save & Close and test.

Loans API

DECLARE
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_loan_id NUMBER;
l_loan_number VARCHAR2(60);
l_index number := 0;
l_indexNo number := 1;
l_msg varchar2(4000) := null;
L_RESOURCE_ID  NUMBER:=NULL;
l_LOAN_DTL_REC LNS_LOAN_PUB.Loan_Details_Rec_Type;
L_LOAN_LINE_LIST   LNS_LOAN_PUB.Loan_Lines_List_Type;
BEGIN
Fnd_global.apps_initialize(1011864, 25323, 206);
MO_GLOBAL.INIT('LNS');
MO_GLOBAL.set_policy_context('S',204);
l_LOAN_DTL_REC.product_id:=10011;
l_LOAN_DTL_REC.LOAN_NUMBER :='api001';
l_LOAN_DTL_REC.LOAN_DESCRIPTION :='API Created direct ';
-- set LOAN_ASSIGNED_TO
select resource_id into L_RESOURCE_ID
--l_LOAN_DTL_REC.LOAN_ASSIGNED_TO
from jtf_rs_resource_extns
where UPPER(SOURCE_FIRST_NAME) = upper('tunde')
and category = 'EMPLOYEE'
and rownum <2;
l_LOAN_DTL_REC.LOAN_ASSIGNED_TO:=L_RESOURCE_ID ;
l_LOAN_DTL_REC.legal_entity_id := null;
l_LOAN_DTL_REC.primary_borrower_party_id := 1006;
l_LOAN_DTL_REC.CUST_ACCOUNT_ID := 1006;
l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID := 1826;
l_LOAN_DTL_REC.contact_rel_party_id := null;
l_LOAN_DTL_REC.requested_amount :=10000;
l_LOAN_DTL_REC.LOAN_PURPOSE_CODE :=NULL;
l_LOAN_DTL_REC.credit_review_flag := null;
l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE := null;
l_LOAN_DTL_REC.EXCHANGE_RATE := null;
l_LOAN_DTL_REC.EXCHANGE_DATE := sysdate;
l_LOAN_DTL_REC.LOAN_APPLICATION_DATE := sysdate;
l_LOAN_DTL_REC.loan_start_date := to_date('01-01-2009','dd-mm-yyyy');
l_LOAN_DTL_REC.open_loan_start_date := null;
l_LOAN_DTL_REC.open_to_term_event := null;
-- set trx_type_id
select unique cust_trx_type_id
into l_LOAN_DTL_REC.trx_type_id
from ra_cust_trx_types_all
--where name = 'Loan Principal' and org_id = 204;\
where name ='Invoice'; --and org_id =82;
l_LOAN_DTL_REC.LOAN_TERM := null;
--l_LOAN_DTL_REC.COLLATERAL_PERCENT := null;
l_LOAN_DTL_REC.LOAN_TERM_PERIOD := null;
l_LOAN_DTL_REC.balloon_payment_type := null;
l_LOAN_DTL_REC.balloon_payment_amount := null;
l_LOAN_DTL_REC.balloon_term := null;
l_LOAN_DTL_REC.LOAN_SUBTYPE := null;
--l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := null;
--l_LOAN_DTL_REC.FORGIVENESS_FLAG := null;
l_LOAN_DTL_REC.INDEX_RATE_ID := null;
--l_LOAN_DTL_REC.DAY_COUNT_METHOD := null;
l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY := null;
l_LOAN_DTL_REC.open_loan_term_period := null;
l_LOAN_DTL_REC.term_index_date:=sysdate;
l_LOAN_DTL_REC.open_index_date:=sysdate;
l_LOAN_DTL_REC.term_index_rate:=null;
l_LOAN_DTL_REC.term_spread:=null;
l_LOAN_DTL_REC.open_index_rate:=null;
l_LOAN_DTL_REC.open_spread:=null;
LNS_LOAN_PUB.CREATE_LOAN(
P_API_VERSION => 1.0,
P_COMMIT => 'F',
P_Loan_Details_Rec => l_LOAN_DTL_REC,
P_Loan_Lines_Rec   => L_LOAN_LINE_LIST,--LNS_LOAN_PUB.Loan_Lines_List_Type,
P_Application_id => 206,
P_Created_by_module => 'API',
X_LOAN_ID => l_loan_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count
--X_MSG_DATA => l_msg_data
);
dbms_output.put_line ( 'l_return_status ' || l_return_status);
dbms_output.put_line ( 'l_msg_count ' || l_msg_count);
IF l_return_status <> 'S' THEN
dbms_output.put_line('Errors:');
while (l_indexNo <= l_msg_Count ) loop
fnd_msg_pub.get(l_indexNo, 'F', l_msg, l_index);
dbms_output.put_line(l_msg);
l_indexNo := l_indexNo + 1;
End Loop;
ELSE
commit;
dbms_output.put_line('Success!') ;
dbms_output.put_line('New loan_id: ' || l_loan_id);
dbms_output.put_line('New loan number: ' ||
l_LOAN_DTL_REC.loan_number);
END IF;
END;

Delete Duplicate Records

delete
from transactions
where contribution_type_id =1
and action_id =1
and added_Date ='31-mar-2013'
and description  like '%Members Saving Regular%'
and rowid not in (
select max(rowid) from transactions
where contribution_type_id =1
and action_id =1
and added_Date ='31-mar-2013'
and description  like '%Members Saving Regular%'
group by member_id)

Employee Details Query

SELECT PAPF.EMPLOYEE_NUMBER
, PAPF.FIRST_NAME
, PAPF.LAST_NAME
, PAPF.EMAIL_ADDRESS
, PAAF.SUPERVISOR_ID
, PAPF1.EMPLOYEE_NUMBER SUPV_EMPL_NUMBER
, PAPF1.FIRST_NAME SUPV_F_NAME
, PAPF1.LAST_NAME SUPV_L_NAME
, HLOC.LOCATION_CODE LOCATION
, HAOU.NAME ORG_NAME
, PP.NAME POSITION
, PG.NAME GRADE
, PJ.NAME MANAGERIAL_LEVEL
, PAPF.START_DATE
, PAPF.SEX GENDER
, PAPF.DATE_OF_BIRTH BIRTH_DATE
--, PAPF.COUNTRY_OF_BIRTH
, FT.TERRITORY_SHORT_NAME NATIONALITY
, HL.MEANING MARITAL_STATUS
FROM PER_ALL_PEOPLE_F PAPF
, PER_ALL_PEOPLE_F PAPF1
, PER_ALL_ASSIGNMENTS_F PAAF
, PER_POSITIONS PP
, PER_JOBS PJ
, PER_GRADES PG
, FND_TERRITORIES_TL FT
, HR_LOOKUPS HL
, HR_LOCATIONS HLOC
, HR_ALL_ORGANIZATION_UNITS HAOU
WHERE SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PRIMARY_FLAG = 'Y'
AND PAPF.PERSON_TYPE_ID IN (1120, 1121)
AND PAAF.POSITION_ID = PP.POSITION_ID(+)
AND PAAF.JOB_ID = PJ.JOB_ID(+)
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAPF.COUNTRY_OF_BIRTH = FT.TERRITORY_CODE(+)
AND HL.LOOKUP_TYPE(+) = 'MAR_STATUS'
AND PAPF.MARITAL_STATUS = HL.LOOKUP_CODE(+)
AND PAAF.SUPERVISOR_ID = PAPF1.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE(+) AND PAPF1.EFFECTIVE_END_DATE(+)
AND PAAF.LOCATION_ID = HLOC.LOCATION_ID(+)
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAPF.EMPLOYEE_NUMBER <> '1'
AND PAPF.EMPLOYEE_NUMBER NOT LIKE 'EDR%'

Employee Cost Centre Details Query

SELECT pcak.segment2 cost_centre,ffvt.DESCRIPTION
FROM pay_cost_allocation_keyflex pcak
,fnd_flex_values ffv
,fnd_flex_values_tl ffvt
WHERE pcak.cost_allocation_keyflex_id =
(SELECT pcaf.cost_allocation_keyflex_id
FROM pay_cost_allocations_f pcaf
WHERE pcaf.assignment_id =
(SELECT paaf.assignment_id
FROM per_all_assignments_f paaf
WHERE paaf.person_id =
(SELECT papf.person_id
FROM per_all_people_f papf
WHERE papf.employee_number = '1001'
AND papf.effective_end_date > SYSDATE)
AND paaf.effective_end_date > SYSDATE)
AND pcaf.effective_end_date > SYSDATE)
AND pcak.end_date_active IS NULL
and ffv.FLEX_VALUE = pcak.segment2
and ffvt.FLEX_VALUE_ID = ffv.FLEX_VALUE_ID

Loans API

DECLARE
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_loan_id NUMBER;
l_loan_number VARCHAR2(60);
l_index number := 0;
l_indexNo number := 1;
l_msg varchar2(4000) := null;
L_RESOURCE_ID  NUMBER:=NULL;
l_LOAN_DTL_REC LNS_LOAN_PUB.Loan_Details_Rec_Type;
L_LOAN_LINE_LIST   LNS_LOAN_PUB.Loan_Lines_List_Type;
BEGIN
Fnd_global.apps_initialize(1011864, 25323, 206);
MO_GLOBAL.INIT('LNS');
MO_GLOBAL.set_policy_context('S',204);
l_LOAN_DTL_REC.product_id:=10011;
l_LOAN_DTL_REC.LOAN_NUMBER :='api001';
l_LOAN_DTL_REC.LOAN_DESCRIPTION :='API Created direct ';
-- set LOAN_ASSIGNED_TO
select resource_id into L_RESOURCE_ID
--l_LOAN_DTL_REC.LOAN_ASSIGNED_TO
from jtf_rs_resource_extns
where UPPER(SOURCE_FIRST_NAME) = upper('tunde')
and category = 'EMPLOYEE'
and rownum <2;
l_LOAN_DTL_REC.LOAN_ASSIGNED_TO:=L_RESOURCE_ID ;
l_LOAN_DTL_REC.legal_entity_id := null;
l_LOAN_DTL_REC.primary_borrower_party_id := 1006;
l_LOAN_DTL_REC.CUST_ACCOUNT_ID := 1006;
l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID := 1826;
l_LOAN_DTL_REC.contact_rel_party_id := null;
l_LOAN_DTL_REC.requested_amount :=10000;
l_LOAN_DTL_REC.LOAN_PURPOSE_CODE :=NULL;
l_LOAN_DTL_REC.credit_review_flag := null;
l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE := null;
l_LOAN_DTL_REC.EXCHANGE_RATE := null;
l_LOAN_DTL_REC.EXCHANGE_DATE := sysdate;
l_LOAN_DTL_REC.LOAN_APPLICATION_DATE := sysdate;
l_LOAN_DTL_REC.loan_start_date := to_date('01-01-2009','dd-mm-yyyy');
l_LOAN_DTL_REC.open_loan_start_date := null;
l_LOAN_DTL_REC.open_to_term_event := null;
-- set trx_type_id
select unique cust_trx_type_id
into l_LOAN_DTL_REC.trx_type_id
from ra_cust_trx_types_all
--where name = 'Loan Principal' and org_id = 204;\
where name ='Invoice'; --and org_id =82;
l_LOAN_DTL_REC.LOAN_TERM := null;
--l_LOAN_DTL_REC.COLLATERAL_PERCENT := null;
l_LOAN_DTL_REC.LOAN_TERM_PERIOD := null;
l_LOAN_DTL_REC.balloon_payment_type := null;
l_LOAN_DTL_REC.balloon_payment_amount := null;
l_LOAN_DTL_REC.balloon_term := null;
l_LOAN_DTL_REC.LOAN_SUBTYPE := null;
--l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := null;
--l_LOAN_DTL_REC.FORGIVENESS_FLAG := null;
l_LOAN_DTL_REC.INDEX_RATE_ID := null;
--l_LOAN_DTL_REC.DAY_COUNT_METHOD := null;
l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY := null;
l_LOAN_DTL_REC.open_loan_term_period := null;
l_LOAN_DTL_REC.term_index_date:=sysdate;
l_LOAN_DTL_REC.open_index_date:=sysdate;
l_LOAN_DTL_REC.term_index_rate:=null;
l_LOAN_DTL_REC.term_spread:=null;
l_LOAN_DTL_REC.open_index_rate:=null;
l_LOAN_DTL_REC.open_spread:=null;
LNS_LOAN_PUB.CREATE_LOAN(
P_API_VERSION => 1.0,
P_COMMIT => 'F',
P_Loan_Details_Rec => l_LOAN_DTL_REC,
P_Loan_Lines_Rec   => L_LOAN_LINE_LIST,--LNS_LOAN_PUB.Loan_Lines_List_Type,
P_Application_id => 206,
P_Created_by_module => 'API',
X_LOAN_ID => l_loan_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count
--X_MSG_DATA => l_msg_data
);
dbms_output.put_line ( 'l_return_status ' || l_return_status);
dbms_output.put_line ( 'l_msg_count ' || l_msg_count);
IF l_return_status <> 'S' THEN
dbms_output.put_line('Errors:');
while (l_indexNo <= l_msg_Count ) loop
fnd_msg_pub.get(l_indexNo, 'F', l_msg, l_index);
dbms_output.put_line(l_msg);
l_indexNo := l_indexNo + 1;
End Loop;
ELSE
commit;
dbms_output.put_line('Success!') ;
dbms_output.put_line('New loan_id: ' || l_loan_id);
dbms_output.put_line('New loan number: ' ||
l_LOAN_DTL_REC.loan_number);
END IF;
END;