IN VS EXIST IN ORACLE

1.
To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.
2.
The IN and EXISTS are logically the same. The IN clause compares values returned by the subquery and filters out rows in the outer query; the EXISTS clause compares values and filters out rows inside the subquery. In the case of NULL values, the resulting set of rows is the same.

Cursors in oracles

declare
cursor testv is
select code_combination_id,account_type
from gl_code_combinations;
Type rec_gl_periods is Record(ccid gl_code_combinations.code_combination_id%Type,
descr gl_code_combinations.account_type%Type
);
recs rec_gl_periods;
testrow testv%rowtype;
begin
open testv;
loop
fetch testv into testrow;
exit when testv%notfound;
message (testrow.code_combination_id);
message(testrow.account_type);

end loop;
null;
end;

Attaching reports with Forms

DECLARE
PL_ID PARAMLIST;
BEGIN

PL_ID := GET_PARAMETER_LIST ('report_plist');
IF NOT ID_NULL(PL_ID) THEN
-- this is just to make sure it clean
DESTROY_PARAMETER_LIST ('report_plist');
END IF;

PL_ID := CREATE_PARAMETER_LIST('report_plist');
Add_Parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'SCREEN');
--Add_Parameter(pl_id,'DESNAME',TEXT_PARAMETER,'printer name');
--Add_Parameter(pl_id,'ORIENTATION',TEXT_PARAMETER,'LANDSCAPE');
--add_parameter (pl_id, 'BATCHID',text_parameter,'124');
--add_parameter (pl_id, 'COMPNAME' , text_parameter ,'Company Name' );
Add_Parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');
RUN_PRODUCT (REPORTS,'c:\repo\manualque.rdf',SYNCHRONOUS,RUNTIME,FILESYSTEM, pl_id,NULL);
DESTROY_PARAMETER_LIST ('report_plist');
END;

Ledger's Opening Balancs

Select period_name,
period_year,
period_num,
to_char(start_date,'yyyy/mm/dd') ||' 00:00:00'
into lv_period_name,
ln_period_year,
ln_period_num,
lv_start_date
from gl_periods
where upper(period_set_name) = 'Dec-95'
and adjustment_period_flag <> 'Y'
and :p_date_from between (to_char(start_date,'yyyy/mm/dd') ||' 00:00:00') and (to_char(end_date,'yyyy/mm/dd') ||' 00:00:00') ;

--- Getting Op Bal for the period

Select sum(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0))
into ln_month_op_bal
from gl_Balances gb,
gl_code_combinations gcc
where gb.period_name = lv_period_name
and gb.code_combination_id = gcc.code_combination_id
and gb.code_combination_id = :ccid
and gb.Ledger_id = :p_sob
and gb.currency_code = 'PKR'
and gb.actual_fLag = 'A' ;


Select nvl(sum(nvl(jgl.accounted_dr,0) - nvl(jgl.accounted_cr,0)),0)
into ln_amt
from gl_je_headers jgh ,
gl_je_lines jgl
where
jgh.Ledger_id = :p_sob
and jgh.Ledger_id =jgl.ledger_id
and jgh.status = 'P'
and jgh.actual_flag = 'A'
and jgh.je_header_id = jgl.je_header_id
and jgl.code_combination_id = :ccid
and ((to_char(jgh.default_effective_date,'yyyy/mm/dd') ||' 00:00:00') >= lv_start_date
and (to_char(jgh.default_effective_date,'yyyy/mm/dd') ||' 00:00:00') < :p_date_from) ;

return nvl(ln_amt,0) + nvl(ln_month_op_bal,0) ;

Exception
when no_data_found then
return 0 ;
when too_many_rows then
return 0 ;
when others then
return 0 ;

Registering New Forms in Oracle Apps

Follow the instructions in give link

http://www.exforsys.com/tutorials/oracle-apps/registering-new-forms-in-oracle-apps-11i.html

Oracle Apps HRMS Payroll's Important Joins

Important joins in Payroll.

select *
from pay_consolidation_sets pcs,pay_all_payrolls_f papf,pay_payroll_actions ppa
,pay_element_sets pes
,hr_assignment_sets has
where pcs.CONSOLIDATION_SET_ID = papf.CONSOLIDATION_SET_ID
and ppa.CONSOLIDATION_SET_ID = pcs.CONSOLIDATION_SET_ID
and pes.ELEMENT_SET_ID = ppa.ELEMENT_SET_ID
and has.ASSIGNMENT_SET_ID = ppa.ASSIGNMENT_SET_ID

Oracle Apps HRMS Employee Salary History

Employee's salary History with reasons.

SELECT pro.ROWID, pro.pay_proposal_id, pro.business_group_id,
pro.assignment_id, pro.performance_review_id, prv.review_date,
hl2.meaning, pro.proposed_salary, TO_NUMBER (pro.proposed_salary),
pro.change_date, pro.proposal_reason, hl1.meaning,
pro.last_change_date, pro.next_sal_review_date, pro.forced_ranking,
pro.attribute_category, pro.attribute1, pro.attribute2,
pro.multiple_components,
pro.approved, pro.comments, pro.object_version_number,
pro.last_update_date, pro.last_updated_by, pro.last_update_login,
pro.created_by, pro.creation_date
FROM hr_lookups hl1,
hr_lookups hl2,
per_performance_reviews prv,
per_pay_proposals pro
WHERE pro.performance_review_id = prv.performance_review_id(+)
AND hl2.lookup_code(+) = prv.performance_rating
AND hl2.lookup_type(+) = 'PERFORMANCE_RATING'
AND hl1.lookup_code(+) = pro.proposal_reason
AND hl1.lookup_type(+) = 'PROPOSAL_REASON'