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;

APPS Shortcut Keys

Function	Hot Key
Clear Field	F5
Clear Form	F8
Clear Record	F6
Clear Block	F7
Commit / Save	Ctrl-S
Delete Record	Ctrl-↑
Down	↓
Duplicate Field	Shift-F5
Duplicate Record	Shift-F6
Edit	Ctrl-E
Enter Query	F11
Execute Query	Ctrl-F11
Exit	F4
Insert Record	Ctrl-↓
List of Values	Ctrl-L
Next Block	Shift-PgDn
Next Record	↓
Previous Block	Shift-PgUp
Previous Field	Shift-Tab
Previous Record	↑
Next Field	Tab
Print	Ctrl-P
Scroll Down	PgDn
Scroll Up	PgUp
Show Keys	Ctrl-K
Up	↑