Duplicate Vendor List

Duplicate Vendor List

select pv1.vendor_name,
pv2.vendor_name DUP_VENDOR_NAME,
pv1.segment1 VENDOR_ID,
pv2.segment1 DUP_VENDOR_ID,
pvsa1.vendor_site_code SITE_CODE,
pvsa2.vendor_site_code SITE_CODE,
pvsa1.address_line1,
pvsa2.address_line1 DUP_ADDRESS_LINE1,
pvsa1.zip
from po_vendors pv1,
po_vendors pv2,
po_vendor_sites_all pvsa1,
po_vendor_sites_all pvsa2
where pvsa1.vendor_site_id <> pvsa2.vendor_site_id
and substr(replace(pvsa1.address_line1, ' '),1,20) =
substr(replace(pvsa2.address_line1, ' '),1,20)
and pvsa1.zip = pvsa2.zip
and pv1.vendor_id = pvsa1.vendor_id
and pv2.vendor_id = pvsa2.vendor_id
and pv1.vendor_id <> pv2.vendor_id
and pvsa1.address_line1 <> 'YOUR ADDEDSS’
order by 1;

Query : To Check whether Periods of AP/AR/GL/FA/PO is closed?

Query : To Check whether Periods of AP/AR/GL/FA/PO is closed?

SELECT (SELECT sob.NAME
FROM gl.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",
a.period_name "Period_Name", a.period_num "Period_Num",
a.gl_status "GL_Status", b.po_status "PO_Status",
c.ap_status "AP_Status", d.ar_status "AR_Status",
e.fa_status "FA_Status"
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) gl_status,
set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) po_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ap_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ar_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, 'Open',
'Closed'
) fa_status,fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id=b.set_of_books_id(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1

Query : To get the calendar information

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;

QUERY FOR UNPOSTED JOURNALS IN R12

QUERY FOR UNPOSTED JOURNALS IN R12
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.LEDGER_id = glsob.set_of_books_id and
gljh.status <> 'P'
order by glsob.name, gljh.period_name

Query : To get the fiscal year name

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';

Query : Verify that Multi Org is Installed

Query : Verify that Multi Org is Installed

select multi_org_flag,release_name,
applications_system_name
from apps.fnd_product_groups;


If the value returned is 'Y' then a Multi Org environment exists;

If the value returned is 'N' then a Multi Org environment does not exist;

QUERY: Which Oracle Apps Version i am using

QUERY: Which Oracle Apps Version i am using

SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = USERENV('LANG')