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

To query Receipt accounting entries - II

SELECT acr.receipt_number, ada.amount_dr, ada.amount_cr,
ada.acctd_amount_dr,ada.acctd_amount_cr
FROM ar.ar_distributions_all ada,
ar.ar_misc_cash_distributions_all mcd,
ar.ar_cash_receipts_all acr
WHERE ada.source_table = 'MCD'
AND ada.source_id = mcd.misc_cash_distribution_id
AND mcd.cash_receipt_id = acr.cash_receipt_id
AND mcd.set_of_books_id = '&sob'

To query Receipt accounting entries - I

SELECT amount_dr, amount_cr, acctd_amount_dr, acctd_amount_cr,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT
FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc
WHERE source_table = 'CRH'
AND EXISTS (
SELECT 'T'
FROM ar.ar_cash_receipt_history_all a, ar.ar_cash_receipts_all b
WHERE a.cash_receipt_id = b.cash_receipt_id
AND source_id = cash_receipt_history_id
AND b.org_id = '&org_id'
AND b.org_id = a.org_id
AND b.receipt_number LIKE '%&receipt_number%')
AND ad.code_combination_id = gcc.code_combination_id
UNION ALL
SELECT amount_dr, amount_cr, acctd_amount_dr, acctd_amount_cr,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT
FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc
WHERE source_table = 'RA'
AND EXISTS (
SELECT 'T'
FROM ar.ar_receivable_applications_all a,
ar.ar_cash_receipts_all b
WHERE a.cash_receipt_id = b.cash_receipt_id
AND a.receivable_application_id = source_id
AND b.org_id = '&org_id'
AND b.org_id = a.org_id
AND b.receipt_number LIKE '%&receipt_number%')
AND ad.code_combination_id = gcc.code_combination_id

To validate whether transaction's REC is equal to its REV plus TAX or not

SELECT c.trx_number, c.complete_flag, c.customer_trx_id,
a.customer_trx_line_id,
(SELECT payment_schedule_id
FROM ar.ar_payment_schedules_all d
WHERE a.customer_trx_id = d.customer_trx_id) payment_schedule_id,
(SELECT receivable_application_id
FROM ar.ar_receivable_applications_all e
WHERE a.customer_trx_id =
e.customer_trx_id)
receivable_application_id
FROM ar.ra_customer_trx_lines_all a,
ar.ra_customer_trx_lines_all b,
ar.ra_customer_trx_all c
WHERE c.set_of_books_id = '&sob'
AND a.customer_trx_id = c.customer_trx_id
AND a.set_of_books_id = '&sob'
AND a.set_of_books_id = b.set_of_books_id
AND a.customer_trx_line_id = b.link_to_cust_trx_line_id
AND a.revenue_amount <> b.taxable_amount
AND EXISTS (
SELECT 'T'
FROM ar.ra_cust_trx_types_all rctt
WHERE rctt.post_to_gl = 'Y'
AND rctt.set_of_books_id = c.set_of_books_id
AND rctt.cust_trx_type_id = c.cust_trx_type_id
AND rctt.end_date IS NULL)
AND EXISTS (
SELECT 'T'
FROM ar.ra_cust_trx_line_gl_dist_all rctlg
WHERE rctlg.set_of_books_id = c.set_of_books_id
AND rctlg.customer_trx_id = c.customer_trx_id
AND rctlg.gl_date >= SYSDATE - 31)

To retrieve transactions with a certain amount open(during charges amount)

SELECT pse.customer_id, hza.account_number, hza.account_name,
pse.trx_number, pse.trx_date, pse.amount_due_original,
pse.amount_due_remaining
FROM apps.ar_payment_schedules_all pse, apps.hz_cust_accounts hza
WHERE 1 = 1
AND pse.customer_id = hza.cust_account_id
to_char(xmv.customer_id) als type
and hza.CUST_ACCOUNT_ID > -1
AND pse.status = 'OP'
AND pse.CLASS IN ('INV', 'CM')
HAVING SUM (pse.amount_due_remaining) =4.50 --- this must be your condition
GROUP BY pse.customer_id,
hza.account_number,
hza.account_name,
pse.trx_number,
pse.trx_date,
pse.amount_due_original,
pse.amount_due_remaining

Retrieve Incomplete invoices before month end

SELECT /*+RULE */
hca.account_number acct_nbr
, ctx.trx_number trx_nbr
, TO_CHAR(ctx.trx_date,’DD-MM-YYYY’) trx_date
, ctt.NAME tran_type
, ctx.customer_trx_id
, ctx.bill_to_customer_id
, ctx.cust_trx_type_id
FROM apps.ra_cust_trx_types_all ctt
, apps.hz_cust_accounts hca
, apps.ra_customer_trx_all ctx
WHERE NVL(ctx.complete_flag,’N') = ‘N’
AND ctx.bill_to_customer_id = hca.cust_account_id
AND ctx.cust_trx_type_id = ctt.cust_trx_type_id

To get the list of Custom Alerts

SELECT aa.alert_name,
aa.creation_date,
aa.last_update_date,
aa.alert_condition_type,
aa.enabled_flag,
aa.start_date_active,
aa.end_date_active,
aa.description,
aa.date_last_checked,
aa.maintain_history_days,
aa.table_name,
aa.sql_statement_text,
fa.application_name
FROM alr_alerts aa, fnd_application_tl fa
WHERE aa.alert_name LIKE 'XXA%'
AND aa.application_id = fa.application_id