Few Important Query

Get Calendar Information

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

Get Depreciation Period :

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

How to check AP ,GL, AR periods are 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

AP to GL Transfer

SELECT details."Vendor_Num", details."Vendor_Name",
details."Prepayment_Num", details."Prepayment_Currency",
details."Prepayment_Amount" "Prepayment_Amount",
SUM (details."Apply_Amount") "Apply_Amount_Sum"
FROM (SELECT pv.segment1 "Vendor_Num", pv.vendor_name "Vendor_Name",
ai1.invoice_num "Prepayment_Num",
ai1.invoice_currency_code "Prepayment_Currency",
ai2.invoice_num "Invoice_Num",
ai2.invoice_currency_code "Invoice_Currency",
aid1.amount "Prepayment_Amount",
NVL (aid2.amount, 0) "Apply_Amount"
FROM ap.ap_invoices_all ai1,
ap.ap_invoices_all ai2,
ap.ap_invoice_distributions_all aid1,
ap.ap_invoice_distributions_all aid2,
po.po_vendors pv
WHERE ai1.set_of_books_id = &sob
AND ai1.invoice_id = aid1.invoice_id
AND ai2.invoice_id(+) = aid2.invoice_id
AND aid1.invoice_distribution_id = aid2.prepay_distribution_id(+)
AND TRUNC (ai1.invoice_date) > TRUNC (SYSDATE - 3650)
AND ai1.invoice_type_lookup_code = 'PREPAYMENT'
AND ai1.cancelled_date IS NULL
-- AND ai1.invoice_num = '071230'
AND aid1.amount + NVL (aid2.amount, 0) <> 0
AND aid1.amount <> NVL (aid2.amount, 0)
AND ai1.vendor_id = pv.vendor_id) details
HAVING (details."Prepayment_Amount" + SUM (details."Apply_Amount") <> 0)
AND (details."Prepayment_Amount" <> SUM (details."Apply_Amount"))
GROUP BY details."Prepayment_Num",
details."Prepayment_Currency",
details."Prepayment_Amount",
details."Vendor_Num",
details."Vendor_Name"

Find Supplier Site Information :

select
pov.vendor_name Supplier,
povs.vendor_site_id,
povs.vendor_site_code Site,
povs.address_line1 A1ddress,
povs.address_line2 A2ddress,
povs.address_line3 A3ddress,
povs.city||', '||
povs.state||' '||
povs.zip A4ddress,
povs.ship_to_location_id,
povs.bill_to_location_id,
povs.ship_via_lookup_code,
povs.freight_terms_lookup_code,
povs.fob_lookup_code
from po_vendors pov,
po_vendor_sites povs
where pov.vendor_id=601
and pov.vendor_id=povs.vendor_id
order by 1

Prepayment AP invoice Details 

SELECT pv.VENDOR_NAME,
ai.invoice_num,
NVL (
DECODE (
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
1,
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
NULL
),
'N'
)
AS PP_F -- Y is Fully Applied, N is Partially or Not Applied
FROM ap_invoice_distributions_all aid, ap_invoices_all ai, po_vendors pv
WHERE aid.invoice_id = ai.INVOICE_ID
AND pv.VENDOR_ID = ai.VENDOR_ID
AND aid.line_type_lookup_code = 'ITEM'
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.INVOICE_ID = :P_INVOICE_ID
AND NVL (reversal_flag, 'N') <> 'Y'
GROUP BY pv.vendor_name, ai.invoice_num
HAVING NVL (
DECODE (
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
1,
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
NULL
),
'N'
) <> 'Y';

Supplier Bank Account Information

SELECT DISTINCT abau.bank_account_uses_id bank_account_uses_id,
abau.end_date end_date,
abau.external_bank_account_id external_bank_account_id,
abau.primary_flag primary_flag,
abau.start_date start_date,
abau.vendor_id vendor_id, 
abau.vendor_site_id vendor_site_id,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.bank_account_type bank_account_type,
aba.account_type account_type,
aba.currency_code currency_code,
aba.description bank_account_description,
aba.check_digits check_digits,
aba.multi_currency_flag multi_currency_flag,
abb.bank_name bank_name, 
abb.bank_name_alt bank_name_alt,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name,
abb.bank_branch_name_alt bank_branch_name_alt,
abb.bank_num bank_num, abb.institution_type institution_type,
abb.bank_branch_type bank_branch_type,
pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pv.vendor_type_lookup_code vendor_type_lookup_code,
pvs.vendor_site_code vendor_site_code,
pvs.address_line1,
pvs.address_line2,
pvs.city,
pvs.zip,
abb.end_date branch_end_date,
aba.inactive_date acct_inactive_date, 
aba.org_id
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)