tag:blogger.com,1999:blog-63286841681011769742024-03-14T00:14:53.615-07:00Apps QueryDevelopers in 11i/R12 EBS are in a race with the Universe to create better idiot-proof programs, while the Universe is trying to create better idiots. So far the Universe is winning.Unknownnoreply@blogger.comBlogger162125tag:blogger.com,1999:blog-6328684168101176974.post-91725108400920670442012-08-23T15:44:00.000-07:002012-08-23T15:44:00.832-07:00Query : AP to GL data transferSELECT 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.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-45019236036020499912011-08-23T15:27:00.000-07:002011-08-23T15:27:00.130-07:00Query :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,
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-77915531600256900372010-09-27T07:12:00.000-07:002010-09-27T07:12:24.930-07:00Query : Sales Order Details After Pick ReleaseSELECT 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 Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-46560972157392418042010-09-27T07:10:00.000-07:002010-09-27T07:10:19.977-07:00Query : 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(Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-68470308022701200962010-09-27T07:07:00.000-07:002010-09-27T07:07:56.316-07:00Query : To extract Unposted Journals in the Set Of Bookselect
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 amountUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-25624478859052557222010-09-27T06:42:00.000-07:002010-09-27T06:42:06.502-07:00Query : To get the fiscal year nameselect calendar_type,
description,
period_suffix_type,
number_per_fiscal_year,
fiscal_year_name
from fa_calendar_types
where calendar_type='&calendertype';Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-60026123044845129342010-09-27T06:40:00.000-07:002010-09-27T06:40:01.297-07:00Query : To get the calendar informationselect calendar_type,
start_date,
end_date,
period_num,
period_name
from fa_calendar_periods
where calendar_type='&calendertype'
order by 2;Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-64966057175438804602010-09-27T06:38:00.000-07:002010-09-27T06:38:29.001-07:00Query : To get the info from deprn_periodsselect 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 3Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-11549848333092136362010-09-27T06:36:00.000-07:002010-09-27T06:36:49.364-07:00Query : To get the book control infoselect 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
);Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-80326249631678673092010-08-28T03:15:00.000-07:002010-08-28T03:15:24.107-07:00INFO: Oracle Workflow APIsDetails 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_NOTIFICATIONSUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-29883203139895054262010-08-28T03:13:00.000-07:002010-08-28T03:13:10.835-07:00Query : To get the Statement of expenses incurred in particular gl entry accountselect 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 ,
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-88484019101271470822010-08-28T02:55:00.000-07:002010-08-28T02:55:25.755-07:00Query : INTRANSIT REPORTselect 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_idUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-21033333096239361632010-08-28T02:36:00.000-07:002010-08-28T02:36:23.263-07:00INFO: AIM Documents for Techies & Techno FunctionalMD050 - Module Design - By Functional ConsultantsMD070 - Technical Document Design - By Technical ConsultantsMD020 - Testing Document Design - By Functional ConsultantsMD0120 - Migration/ User Training - By Technical ConsultantsCV040 - Conversion of Functional Document - By Functional ConsultantsCV060 - Conversion of Technical Document - By Technical ConsultantsUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-73305498337202845622010-08-24T08:18:00.000-07:002010-08-28T02:17:59.921-07:00Query : Asset migrationSELECTFA.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,
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-23088917596129885932010-08-24T08:11:00.000-07:002010-08-24T08:11:08.132-07:00Query :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 Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-41304357875971783212010-08-24T07:55:00.001-07:002010-08-24T07:55:40.547-07:00Query : To get Prepayment Invoice StatusSELECT 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 Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6328684168101176974.post-53559424537445342282010-08-24T07:51:00.000-07:002010-08-24T07:51:46.301-07:00INFO: Accounting lines in FA via SLAAccounting 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.Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6328684168101176974.post-72615843623949699102010-08-24T07:49:00.000-07:002010-08-24T07:49:14.181-07:00Query : To get YTD DepreciationSELECT 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
,Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-46827239646580917312010-08-24T07:47:00.001-07:002010-08-24T07:47:34.130-07:00Query : Get the Inventory org Addressselect * 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;Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-16821332110740743642010-08-24T07:45:00.000-07:002010-08-24T07:45:41.086-07:00Query : To get the list of Custom AlertsSELECT 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_idUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-87150276667822739412010-08-23T17:48:00.000-07:002010-08-23T17:48:50.303-07:00Query : Retrieve Incomplete Invoices before month endSELECT /*+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
, Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-58564511845776717222010-08-23T17:45:00.000-07:002010-08-23T17:45:30.313-07:00Query : To retrieve transactions with a certain amount open (dunning 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 (Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-14136463289576152172010-08-23T15:23:00.000-07:002010-08-23T15:23:08.525-07:00Query : To validate whether a transaction's REC is equal to its REV plus TAX or notSELECT 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 =
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-32596124850831624732010-08-23T15:15:00.000-07:002010-08-23T15:15:57.141-07:00How : Amortized and Expensed AdjustmentsAmortized and Expensed Adjustments
In the period you add an asset or for CIP assets, changing financial information does not adjust depreciation, since no depreciation has been taken. If you change financial information after you have run depreciation, you must choose whether to expense or amortize the adjustment:
Expensed Adjustment
For expensed adjustments, Oracle Assets recalculates Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6328684168101176974.post-26251585126678469492010-08-23T15:13:00.001-07:002010-08-23T15:13:22.516-07:00Query : To query Receipt Accounting entries -IISELECT 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'Unknownnoreply@blogger.com0