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

To get Payment 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';

FA 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

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

AIM Documents for 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

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