-- FA Asset Register Query
SELECT fa.asset_number,
fa.asset_id,
REPLACE(fa.description,chr(10)) description,
fa.attribute1 sub_category1,
fa.attribute2 sub_category2,
fc.segment1 major_cat,
fc.segment2 minor_cat,
(select keys.segment1
from fa_asset_keywords keys
WHERE KEYS.code_combination_id = fa.asset_key_ccid
AND rownum = 1
) asset_key,
fdp.fiscal_year,
fdp.period_name,
fdp.period_num,
(SELECT loc.segment1||loc.segment2||loc.segment3
FROM fa_locations loc,
fa_distribution_history dist
WHERE dist.asset_id = fa.asset_id
AND dist.location_id = loc.location_id
AND rownum = 1 ) loc,
(SELECT first_name || ' ' || last_name
FROM per_all_people_f f, fa_distribution_history dist
WHERE dist.asset_id = fa.asset_id
AND dist.assigned_to = f.person_id
AND f.effective_end_date = '31-DEC-4712'
AND rownum = 1 ) employee_name,
fdd.book_type_code,
fb.date_placed_in_service date_in_use ,
add_months(fb.date_placed_in_service,fb.life_in_months) service_end_date ,
fdd.deprn_run_date,
fdd.COST Current_cost,
fdd.deprn_amount mtd_deprn,
fdd.ytd_deprn,
fdd.deprn_reserve acc_deprn,
fdd.COST - fdd.deprn_reserve - nvl( (SELECT sum(impairment_amount)
FROM apps.fa_impairments fi
WHERE fi.asset_id = fa.asset_id
AND impairment_date <= fdp.calendar_period_close_date
AND status='POSTED'), 0 ) Net_Book_Value,
(SELECT sum(impairment_amount)
FROM apps.fa_impairments fi
WHERE fi.asset_id = fa.asset_id
AND impairment_date <= fdp.calendar_period_close_date
AND status='POSTED') impairment_amount,
( SELECT max(vendor_name)
FROM FA_INVOICE_DETAILS_V
WHERE ASSET_ID = fa.ASSET_ID ) Supplier ,
(SELECT max(gc.segment3)
FROM fa_distribution_history dist,
gl_code_combinations gc
WHERE dist.code_combination_id = gc.code_combination_id
AND dist.asset_id = fa.asset_id
AND rownum = 1
) segment3,
(SELECT max(ffv.description)
FROM fa_distribution_history dist,
gl_code_combinations gc,
fnd_flex_values_vl ffv
WHERE dist.asset_id = fa.asset_id
AND dist.code_combination_id = gc.code_combination_id
AND gc.segment3 = ffv.flex_value
AND ffv.FLEX_VALUE_SET_ID = 1023631
AND ffv.ENABLED_FLAG = 'Y'
) segment3_desc,
( SELECT max(PO_NUMBER)
FROM FA_INVOICE_DETAILS_V
WHERE ASSET_ID = fa.ASSET_ID ) po_number,
( SELECT max(aia.DOC_SEQUENCE_VALUE)
FROM FA_INVOICE_DETAILS_V V,
AP_INVOICES_ALL aia
WHERE v.INVOICE_NUMBER = aia.invoice_num
AND V.ASSET_ID = fa.ASSET_ID) Voucher_num,
(SELECT max(INVOICE_NUMBER)
FROM FA_INVOICE_DETAILS_V v
WHERE v.ASSET_ID = fa.ASSET_ID ) INVOICE_NUMBER,
(SELECT MAX(aia.invoice_date)
FROM FA_INVOICE_DETAILS_V v,
ap_invoices_All aia
WHERE v.INVOICE_NUMBER = aia.invoice_num
AND v.ASSET_ID = fa.ASSET_ID) INVOICE_DATE,
(SELECT max(aia.invoice_amount)
FROM FA_INVOICE_DETAILS_V v,
AP_INVOICES_ALL aia
WHERE v.INVOICE_NUMBER = aia.invoice_num
AND v.ASSET_ID = fa.ASSET_ID ) INVOICE_AMOUNT,
(SELECT max(gc.segment4)
FROM fa_distribution_history dist,
gl_code_combinations gc
WHERE dist.code_combination_id = gc.code_combination_id
AND dist.asset_id = fa.asset_id
AND ROWNUM = 1
) Deprn_exp_acc ,
CASE WHEN (add_months(fb.date_placed_in_service,fb.life_in_months) > SYSDATE and add_months(fb.date_placed_in_service,fb.life_in_months) - sysdate > 360 ) THEN
Trunc(months_between(add_months(fb.date_placed_in_service,fb.life_in_months) , SYSDATE) /12)
ELSE 0
END remaing_life_years,
CASE WHEN (add_months(fb.date_placed_in_service,fb.life_in_months) > SYSDATE and add_months(fb.date_placed_in_service,fb.life_in_months) - sysdate > 30 ) THEN
ceil( mod(months_between(add_months(fb.date_placed_in_service,fb.life_in_months) , SYSDATE) , 12) )
else 0 end Remaing_life_Months
FROM fa_additions fa,
fa_books fb,
fa_categories fc ,
fa_deprn_periods fdp,
fa.fa_deprn_detail fdd
WHERE fa.asset_id = fdd.asset_id
-- AND fa.asset_id IN (490626,407294, 406514,490627,490629)
AND fa.asset_id = fb.asset_id
AND fa.asset_category_id = fc.category_id
AND fdp.period_counter = fdd.period_counter
AND fdp.book_type_code = fdd.book_type_code
AND fdd.book_type_code = 'ALDAR CORP BOOK'
AND fdp.period_counter BETWEEN 24265 AND 24276
ORDER BY fa.asset_number , fdp.period_num ;
-- Resp wise MO assignment Detail Query
SELECT
-- a.responsibility_id ,
-- a.application_id ,
responsibility_name ,
( SELECT NAME
from hr_operating_units
where organization_id = fnd_profile.value_specific('ORG_ID',1,a.responsibility_id, a.application_id) ) mo_operating_unit,
(Select SECURITY_PROFILE_NAME
from per_security_profiles s
Where S.SECURITY_PROFILE_ID = fnd_profile.value_specific('XLA_MO_SECURITY_PROFILE_LEVEL',1,A.responsibility_id,A.application_id)) mo_security_profile
FROM fnd_responsibility_vl A,
fnd_application_vl b
WHERE REPLACE(responsibility_name,'&','') in ( '','','' )
AND A.application_id = b.application_id ;
-- AP Invoice full Details query
SELECT pv.vendor_name,
pvsa.vendor_site_code,
aia.invoice_num,
-- aia.invoice_id,
aia.description,
initcap(aia.invoice_type_lookup_code) invoice_type,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'AP_WFAPPROVAL_STATUS'
AND lookup_code = aia.wfapproval_status) approval_status_meaning,
aia.invoice_date ,
(SELECT MAX(trunc(aida1.accounting_date))
FROM ap_invoice_distributions_all aida1
WHERE aida1.invoice_id = aia.invoice_id
AND aida1.LINE_TYPE_LOOKUP_CODE = 'ITEM'
) gl_date,
CASE
when aia.wfapproval_status = 'WFAPPROVED' then
(SELECT MAX(creation_date)
FROM ap_inv_aprvl_hist_all aiha
WHERE aiha.invoice_id = aia.invoice_id
AND aiha.org_id = aia.org_id
AND aiha.response IN ('APPROVED')
)
WHEN aia.wfapproval_status = 'MANUALLY APPROVED' THEN
(SELECT nvl(MAX(creation_date),aia.last_update_date)
FROM ap_inv_aprvl_hist_all aiha
WHERE aiha.invoice_id = aia.invoice_id
AND aiha.org_id = aia.org_id
AND aiha.response IN ('APPROVED','MANUALLY APPROVED')
)
WHEN aia.wfapproval_status IN ('REJECTED') THEN
( SELECT MAX(creation_date)
FROM ap_inv_aprvl_hist_all aiha
WHERE aiha.invoice_id = aia.invoice_id
AND aiha.org_id = aia.org_id
AND aiha.response IN ('REJECT')
)
ELSE
Null
End Approve_Date,
aia.invoice_received_date ,
aia.goods_received_date,
ps.due_date,
aia.doc_sequence_value voucher_num,
( SELECT Min(gcc.segment3)
FROM apps.ap_invoice_distributions_all aida ,
gl_code_combinations gcc
WHERE aida.org_id= aia.org_Id
AND aida.invoice_id = aia.invoice_id
AND aida.line_type_lookup_code = 'ITEM'
AND aida.DIST_code_combination_id = gcc.code_combination_id ) project_no,
(SELECT sum( aila.amount )
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'ITEM'
) invoice_amount,
(SELECT nvl(sum( aila.amount ),0)
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'PREPAY'
) prepay_amount,
(SELECT nvl(sum( aila.amount ) ,0)
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'TAX') tax_amount ,
(SELECT NVL(Sum(aipa.amount),0)
FROM ap_invoice_payments_all aipa,
ap_checks_all aca
WHERE aipa.invoice_id = aia.invoice_id
AND aipa.check_id = aca.check_id
and aca.status_lookup_code != 'VOIDED' ) amount_paid,
ps.amount_remaining outstanding
FROM ap_suppliers pv
, ap_supplier_sites_all pvsa
, ap_invoices_all aia
, ap_payment_schedules_all ps
WHERE aia.org_id = 1250
AND AIA.INVOICE_ID in ( select distinct AIDA.INVOICE_ID
from APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIDA.ORG_ID= 1250
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND TRUNC(AIDA.ACCOUNTING_DATE) BETWEEN '01-JUL-2010' AND '31-MAR-2023' )
AND aia.invoice_type_lookup_code = 'STANDARD'
AND aia.cancelled_date IS NULL
AND aia.vendor_id = pv.vendor_id
AND aia.vendor_site_id = pvsa.vendor_site_id
AND aia.invoice_id = ps.invoice_id
ORDER BY 7 ASC ;
-- incident status update detail
select msib.segment1 unit_code ,
inc.incident_number sr_number ,
aud.creation_date status_change_date,
( SELECT status.name
FROM cs_incident_statuses_tl status
WHERE status.incident_status_id = aud.OLD_INCIDENT_STATUS_ID
AND status.language = userenv('LANG')
) From_status ,
( SELECT status.name
FROM cs_incident_statuses_tl status
WHERE status.incident_status_id = aud.INCIDENT_STATUS_ID
and status.language = userenv('LANG')
) To_Status
FROM
cs_incidents_all_b inc,
mtl_system_items_b MSIB,
cs_incidents_audit_b aud
WHERE AUD.CREATION_DATE >= '05-MAR-2023'
and inc.incident_id = aud.incident_id
and inc.inventory_item_id = msib.inventory_item_id
AND aud.CHANGE_INCIDENT_STATUS_FLAG = 'Y'
and aud.old_incident_status_id is not null
AND aud.old_incident_status_id != aud.incident_status_id
and inc.incident_type_id = 17002
and aud.creation_date = (select max(aud1.creation_date)
from cs_incidents_audit_b aud1
where aud1.incident_id = inc.incident_id
and aud1.change_incident_status_flag = 'Y'
AND aud1.OLD_INCIDENT_STATUS_ID is not null )
ORDER BY inc.incident_number , AUD.CREATION_DATE
-- Incident detail query
SELECT
( SELECT ACCOUNT.account_number
FROM hz_cust_accounts account
WHERE ACCOUNT.cust_account_id = inc.account_id
) account_number,
inc.incident_id,
inc.incident_number SR_number,
inc.incident_date reported_Date,
( SELECT type.name
FROM cs_incident_types_tl type
WHERE type.incident_type_id = inc.incident_type_id
AND TYPE.language = userenv('LANG')
) SR_type,
( SELECT problem.meaning
FROM fnd_lookup_values problem
WHERE problem.lookup_code = inc.problem_code
AND problem.lookup_type = 'REQUEST_PROBLEM_CODE'
AND problem.language = userenv('LANG')
AND problem.view_application_id = 170
AND problem.security_group_id = fnd_global.lookup_security_group(problem.lookup_type
, problem.view_application_id)
) problem_code ,
( SELECT status.name
FROM cs_incident_statuses_tl status
WHERE status.incident_status_id = inc.incident_status_id
AND status.language = userenv('LANG')
) Current_SR_status,
aud.creation_date status_Change_Date ,
aud.creation_time status_change_time ,
(select nvl(ppf.full_name,rs.user_name)
from fnd_user rs,
per_all_people_F ppf
where rs.user_id = aud.last_updated_by
and rs.employee_id = ppf.person_id(+)
and sysdate between ppf.effective_Start_date(+) and ppf.effective_end_Date(+) ) status_changed_by ,
( SELECT status.name
FROM cs_incident_statuses_tl status
WHERE status.incident_status_id = aud.OLD_INCIDENT_STATUS_ID
AND status.language = userenv('LANG')
) From_status ,
( SELECT status.name
FROM cs_incident_statuses_tl status
WHERE status.incident_status_id = aud.INCIDENT_STATUS_ID
AND status.language = userenv('LANG')
) To_Status
FROM
cs_incidents_all_b inc,
CS_INCIDENTS_AUDIT_B AUD
WHERE inc.incident_date between '01-JUL-2022' and sysdate+1
AND inc.incident_id = aud.incident_id
AND aud.CHANGE_INCIDENT_STATUS_FLAG = 'Y'
AND aud.OLD_INCIDENT_STATUS_ID is not null
AND inc.incident_type_id = 15002
ORDER BY INC.INCIDENT_ID , AUD.CREATION_DATE
;
-- payroll element entry value query
select petf.element_name||' -- '|| peevf.screen_entry_value
from pay_element_types_f petf,
pay_element_links_f pelf,
pay_element_entries_f peef,
per_all_assignments_f paaf,
per_all_people_f papf,
pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE 1 = 1
and paaf.assignment_id = 10000
and pelf.element_type_id = petf.element_type_id
and petf.element_name = 'Allowance'
and peef.element_type_id = petf.element_type_id
and trunc (sysdate) between trunc (peef.effective_start_date) and trunc (peef.effective_end_date)
and trunc (sysdate) between trunc (petf.effective_start_date) and trunc (petf.effective_end_date)
and trunc (sysdate) between trunc (pelf.effective_start_date) and trunc (pelf.effective_end_date)
and paaf.assignment_id = peef.assignment_id
and trunc (sysdate) between trunc (paaf.effective_start_date) and trunc (paaf.effective_end_date)
and trunc (sysdate) between trunc (peevf.effective_start_date) and trunc (peevf.effective_end_date)
AND papf.person_id = paaf.person_id
and papf.current_emp_or_apl_flag = 'Y'
and trunc (sysdate) between trunc (papf.effective_start_date) and trunc (papf.effective_end_date)
and peevf.element_entry_id = peef.element_entry_id
AND pivf.input_value_id = peevf.input_value_id
and peevf.screen_entry_value is not null
;
-- AR Invoice Amount applied query
SELECT ACRA.CASH_RECEIPT_ID RECEIPT_ID,
RECEIPT_NUMBER,
RECEIPT_DATE ,
(SELECT STATUS
FROM AR_CASH_RECEIPT_HISTORY_ALL
WHERE CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
and CURRENT_RECORD_FLAG = 'Y' ) RECEIPT_STATUS ,
ARAA.APPLY_DATE,
--ARAA.APPLICATION_TYPE,
RCTA.CUSTOMER_TRX_ID Invoice_Id,
RCTA.TRX_NUMBER,
RCTA.TRX_DATE,
APSA.AMOUNT_DUE_ORIGINAL TRX_AMOUNT,
ARAA.AMOUNT_APPLIED RECEIPT_APPLIED,
APSA.AMOUNT_DUE_REMAINING REMAINING_AMOUNT
FROM AR_CASH_RECEIPTS_ALL ACRA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
RA_CUSTOMER_TRX_ALL RCTA,
AR_PAYMENT_SCHEDULES_ALL apsa
WHERE acra.RECEIPT_NUMBER = '83248324'
AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
AND ARAA.STATUS = 'APP'
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
and RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID ;
-- Get lookup value meaning
SELECT HR_GENERAL_UTILITIES.GET_LOOKUP_MEANING('XXPN_GLOBAL_DEBUG_OPTIONS','SMS_DEBUG_FLAG') SMS_DEBUG,
HR_GENERAL_UTILITIES.GET_LOOKUP_MEANING('XXPN_GLOBAL_DEBUG_OPTIONS','CONC_DEBUG_FLAG') conc_debug
from dual;
-- Receipt unapplied amount
(SELECT Sum( ACR.AMOUNT - ( NVL((SELECT sum(ARAA.AMOUNT_APPLIED)
FROM apps.AR_RECEIVABLE_APPLICATIONS_ALL ARAA
WHERE ARAA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARAA.DISPLAY = 'Y'
and ARAA.STATUS = 'APP'
AND ARAA.APPLICATION_TYPE = 'CASH'
),0) ) )
FROM APPS.AR_CASH_RECEIPTS_ALL ACR,
APPS.AR_CASH_RECEIPT_HISTORY_ALL ACRH
WHERE ACR.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
AND ACRH.CURRENT_RECORD_FLAG = 'Y'
AND ACRH.STATUS = 'CLEARED'
--AND trunc(acrh.trx_date) = trunc(sysdate) - 1
)
-- User assigned Resps Query
SELECT
USR.USER_ID,
usr.user_name,
resp.responsibility_name,
RESP.RESPONSIBILITY_ID ,
GRP.DESCRIPTION ,
grp.responsibility_application_id ,
grp.start_date assigned_date ,
GRP.END_DATE ASSIGNMENT_END_DATE,
GRP.LAST_UPDATED_BY
FROM fnd_user usr,
FND_USER_RESP_GROUPS_DIRECT grp ,
fnd_responsibility_vl resp
WHERE USR.user_id = grp.user_id
AND grp.responsibility_id = resp.responsibility_id
and grp.responsibility_application_id = resp.application_id
and USR.USER_NAME = 'TEST1'
and ( USR.END_DATE is null or USR.END_DATE >= TRUNC(sysdate) )
-- and ( RESP.END_DATE is not null )
and ( grp.end_date = '31-DEC-22' )
order by usr.user_name;
No comments:
Post a Comment