Query : Sales Order Details After Pick Release

SELECT ooh.order_number,
ool.line_number,
wnd.delivery_id,
mtrh.request_number
FROM oe_order_headers ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
mtl_txn_request_lines mtrl,
mtl_txn_reuest_headers mtrh
WHERE ooh.order_number = --Order Number Input Parameter
AND ool.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id

Read More

Query : That list of all employees with their positions, organizations and usernames.

SELECT papf.full_name,
fu.user_name,
hapf.NAME Position,
haou.NAME Organzation,
asg.primary_flag AS "Primary Position",
papf.person_id
FROM fnd_user fu,
per_all_people_f papf,
per_all_assignments_f asg,
hr_all_positions_f hapf,
hr_all_organization_units haou
WHERE papf.person_id = asg.person_id(+)
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.position_id = hapf.position_id(+)
AND fu.employee_id(+) = papf.person_id
AND haou.organization_id = asg.organization_id
AND (
upper(pos.NAME) LIKE upper('%&Name%') OR
upper(us.user_name) LIKE upper('%&Name%') OR
upper(ppl.full_name) LIKE upper('%&Name%')
)
ORDER BY hapf.NAME

Read More

Query : To extract Unposted Journals in the Set Of Book

select
glsob.name "gl book",
gljb.name "batch name" ,
gljh.name "journal name",
gljh.period_name ,
decode (gljh.status ,'u','unposted',gljh.status) status,
gljh.je_category category ,
gljh.je_source source ,
gljh.currency_code currency ,
gljh.running_total_dr "entered amount dr" ,
gljh.running_total_accounted_dr "accounted amount dr" ,
gljh.running_total_cr "entered amount cr",
gljh.running_total_accounted_cr "accounted amount cr"
from
gl_je_headers gljh,
gl_je_batches gljb ,
gl_sets_of_books glsob
where
gljb.je_batch_id = gljh.je_batch_id and
gljh.set_of_books_id = glsob.set_of_books_id and
gljh.status <> 'P'
order by glsob.name, gljh.period_name

Read More

Query : To get the fiscal year name

select calendar_type,
description,
period_suffix_type,
number_per_fiscal_year,
fiscal_year_name
from fa_calendar_types
where calendar_type='&calendertype';

Read More

Query : To get the calendar information

select calendar_type,
start_date,
end_date,
period_num,
period_name
from fa_calendar_periods
where calendar_type='&calendertype'
order by 2;

Read More

Query : To get the info from deprn_periods

select book_type_code,
period_name,
period_counter,
period_num,
fiscal_year,
period_open_date,
period_close_date,
calendar_period_open_date,
calendar_period_close_date
from fa_deprn_periods
where book_type_code='&book'
order by 3

Read More

Query : To get the book control info

select fab.book_type_code,
fab.deprn_calendar,
fab.prorate_calendar,
fab.fiscal_year_name,
fab.deprn_status,
fab.current_fiscal_year
from fa_book_controls fab
where fab.book_type_code='&book'
and fab.book_type_code
IN (select book_type_code
from fa_deprn_periods
where period_close_date IS NULL
);

Read More