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

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

INFO: Oracle Workflow APIs

Details for some of Oracle Workflow APIs are...

1 WF_ENGINE
2 WF_CORE
3 WF_PURGE
4 WF_DIRECTORY
5 WF_PREF
6 WF_MONITOR
7 Oracle Workflow Views
8 WF_QUEUE
9 FND_DOCUMENT_MANAGEMENT
10 WF_NOTIFICATIONS

Read More

Query : To get the Statement of expenses incurred in particular gl entry account

select gl.chart_of_accounts_id,
gl.account_type,
gl.segment12 account_dep,
gl.segment14 company,
gl.segment16 department,
bl.currency_code,
bl.period_net_dr ,
bl.period_net_cr ,
substr(a.description,1,50) descr,
he.period_name
from gl_code_combinations gl ,
gl_balances bl,
fnd_flex_value_sets c ,
fnd_flex_values b ,
fnd_flex_values_tl a,
gl_je_headers he
where gl.code_combination_id =bl.code_combination_id
and gl.chart_of_accounts_id=xxx -- add coaid
and b.flex_value_set_id = c.flex_value_set_id
and a.flex_value_id = b.flex_value_id
and he.set_of_books_id=bl.set_of_books_id
and a.language = 'US'
and c.flex_value_set_name like 'Operations Account'
and b.flex_value= gl.segment12;

Read More

Query : INTRANSIT REPORT

select t.invoiced_flag,
t.actual_cost ,
t.transaction_date ,
t.transaction_quantity,
t.inventory_item_id ,
t.subinventory_code
from mtl_material_transactions t,
mtl_onhand_quantities o
where t.subinventory_code = 'INTRANSIT'
and t.inventory_item_id not in o.inventory_item_id

Read More

INFO: AIM Documents for Techies & Techno Functional

MD050 - Module Design - By Functional Consultants

MD070 - Technical Document Design - By Technical Consultants

MD020 - Testing Document Design - By Functional Consultants

MD0120 - Migration/ User Training - By Technical Consultants

CV040 - Conversion of Functional Document - By Functional Consultants

CV060 - Conversion of Technical Document - By Technical Consultants

Read More

Query : Asset migration

SELECT

FA.ASSET_NUMBER,

FA.TAG_NUMBER,

FA.DESCRIPTION,

FA.SERIAL_NUMBER,

FA.MODEL_NUMBER,

FB.BOOK_TYPE_CODE,

FB.DATE_PLACED_IN_SERVICE,
FAI.FIXED_ASSETS_COST,
FA.CURRENT_UNITS,
FA.CURRENT_UNITS,
FAI.PAYABLES_CODE_COMBINATION_ID
FDH.LOCATION_ID,
FDH.ASSIGNED_TO,
'MIGRATION',
FAI.INVOICE_NUMBER,
FAI.PO_VENDOR_ID,
FAI.PO_NUMBER,
FA.DEPRECIATE_FLAG,
FA.ASSET_TYPE,
FDS.DEPRN_RESERVE,
FDS.YTD_DEPRN,
FB.SALVAGE_VALUE
FROM
FA_ADDITIONS FA, FA_BOOKS FB, FA_ASSET_INVOICES FAI,
FA_DISTRIBUTION_HISTORY,FA_DEPRN_SUMMARY FDS
WHERE
FA.ASSET_ID = FB. ASSET_ID AND
FA.ASSET_ID = FB ASSET_ID AND
FA.ASSET_ID =FAI.ASSET_ID AND
FA.ASSET_ID = FDH. ASSET_ID AND
FB.BOOK_TYPE_CODE = FAI. BOOK_TYPE_CODE AND
FB.BOOK_TYPE_CODE = FDH. BOOK_TYPE_CODE

Read More

Query :

select SUM( NVL(ds.deprn_reserve,0) * ds.UNITS_ASSIGNED/ad.CURRENT_UNITS) as deprn_reserve
From (select a.asset_id,
a.tag_number,
a.current_units+nvl(b.units,0) as current_units,
a.asset_category_id
from apps.fa_additions a,
(select asset_id,sum(abs(transaction_units)) as units
from fa.fa_distribution_history
where asset_id in (select asset_id
from fa.fa_retirements
where to_char(date_retired,'mon-yy') = 'dec-03')
and (retirement_id is not null and date_ineffective is not null)
group by asset_id
) b
where a.asset_id = b.asset_id(+)
) ad,
apps.fa_books bk,
apps.fa_categories_b cat,
fa.FA_BOOK_CONTROLS fbc,
fa.fa_locations loc,
hr.per_all_people_f hr,
(Select ds2.asset_id, fdh.UNITS_ASSIGNED, cc.SEGMENT2,cc.SEGMENT3,
fdh.LOCATION_ID,fdh.ASSIGNED_TO,
ds2.deprn_reserve as deprn_reserve,
decode(dp2.fiscal_year,TO_NUMBER(TO_CHAR(to_date(upper('JAN-07'),'MON-RR'),'YYYY')),
ds2.ytd_deprn,Null) as ytd_deprn,
decode(dp2.period_name,upper('JAN-07'),ds2.deprn_amount,Null) as deprn_amount
from fa.fa_deprn_periods dp2,
fa.fa_deprn_summary ds2,
fa.FA_BOOK_CONTROLS fbc,
fa.FA_DISTRIBUTION_HISTORY fdh,
gl.GL_CODE_COMBINATIONS cc
where fbc.BOOK_CLASS = 'CORPORATE'
and dp2.book_type_code = fbc.book_type_code
and ds2.book_type_code = fbc.book_type_code
and fdh.book_type_code = fbc.book_type_code
and dp2.period_counter = ds2.period_counter
and ds2.asset_id = fdh.asset_id
and cc.CODE_COMBINATION_ID=fdh.CODE_COMBINATION_ID
and nvl(fdh.DATE_INEFFECTIVE,sysdate+1) > sysdate
and ds2.period_counter = ( Select Max(dss.period_counter)
from fa.fa_deprn_summary dss
where dss.book_type_code = ds2.book_type_code
and dss.asset_id = ds2.asset_id )
and dp2.book_type_code = fbc.BOOK_TYPE_CODE ) ds
where ad.ASSET_ID = bk.ASSET_ID
and fbc.BOOK_CLASS = 'CORPORATE'
and bk.book_type_code = fbc.book_type_code
and nvl(bk.DATE_INEFFECTIVE, sysdate+1)>sysdate
and ds.asset_id(+) = ad.asset_id
and ad.asset_category_id = cat.category_id
and ds.LOCATION_ID = loc.location_id
and loc.enabled_flag = 'Y'
and ds.assigned_to = hr.person_id(+)
and nvl(hr.EFFECTIVE_END_DATE,sysdate+1)>sysdate

Read More

Query : To get Prepayment Invoice Status

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

Read More

INFO: Accounting lines in FA via SLA

Accounting lines are now stored in the SLA Tables:
xla_events
xla_ae_headers
xla_ae_lines
xla_distribution_links
by linking to the event id in the FA_Transaction_Headers and
FA_Deprn_Summary tables. CCID no longer stored in the
FA_Adjustments table.

Read More

Query : To get YTD Depreciation

SELECT fbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.cost asset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM') accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD') ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN') dep_this_run
,gcc.segment2 department_no
,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian
,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number
,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number
,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number
,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number
,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type
,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROM apps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE 1=1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id=fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL

Read More

Query : Get the Inventory org Address

select * from hr_locations_v hlv,
hr_all_organization_units hou
WHERE hlv.inventory_organization_id = hou.organization_id
AND hlv.location_id = hou.location_id
AND hou.organization_id = :p_mfg_org;

Read More

Query : 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

Read More