Query : AP to GL data 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"

Read More

Query :

SELECT round (SUM (cpc.accounted_value), 2) accounted_value,
round (SUM (cpc.accounted_onhand_value), 2) accounted_onhand_value,
round (SUM (cpc.rollback_onhand_value), 2) rollback_onhand_value,
round (SUM (cpc.onhand_value_discrepancy), 2) discrepancy,
msi.segment1, cpc.organization_id,oap.period_name
FROM inv.org_acct_periods oap,
bom.cst_period_close_summary cpc,
inv.mtl_system_items_b msi
WHERE oap.organization_id = cpc.organization_id
AND oap.acct_period_id = cpc.acct_period_id
AND oap.period_name = 'NOV-08'
AND msi.organization_id = &org_id
AND msi.inventory_item_id = cpc.inventory_item_id
HAVING TRUNC (SUM (cpc.onhand_value_discrepancy), 2) > 1
OR TRUNC (SUM (cpc.onhand_value_discrepancy), 2) < -1
GROUP BY msi.segment1, cpc.organization_id,oap.period_name

Read More

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