I use the following queries very frequently on my use of Oracle E-Business suite 11i,
1. Identify Buyers without a Position assigned if using Position Hierarchy
SELECT paf.person_id, ppf.full_name
FROM per_all_assignments_f paf
,po_agents poa
,per_people_f ppf
,per_assignment_status_types past
WHERE trunc(sysdate) between TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)
AND paf.person_id = poa.agent_id
AND paf.person_id = ppf.person_id
AND TRUNC(sysdate) between TRUNC(ppf.effective_start_date) and TRUNC(ppf.effective_end_date)
AND paf.position_id is null
AND paf.job_id is not null
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.user_status = 'Active Assignment'
AND TRUNC(sysdate) between TRUNC(poa.start_date_active) AND TRUNC(NVL(poa.end_date_active, sysdate))
ORDER BY paf.person_id;
2. Retrive Timecard details with Resource, and Timecard Start time and End Time
SELECT DISTINCT PP.SEGMENT1 "Project Number", PT.TASK_NUMBER "Task number", HTA.ATTRIBUTE3, DAY.START_TIME, DET.MEASURE, DET.APPROVAL_STATUS, DET.COMMENT_TEXT
FROM
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID ,
PARENT_BUILDING_BLOCK_OVN ,
OBJECT_VERSION_NUMBER ,
MEASURE ,
RESOURCE_ID ,
APPROVAL_STATUS ,
COMMENT_TEXT, TRANSLATION_DISPLAY_KEY
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
) DET ,
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID ,
PARENT_BUILDING_BLOCK_OVN ,
OBJECT_VERSION_NUMBER ,
START_TIME ,
APPROVAL_STATUS, RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DAY'
) DAY ,
(SELECT TIME_BUILDING_BLOCK_ID,
START_TIME ,
APPROVAL_STATUS ,
OBJECT_VERSION_NUMBER ,
COMMENT_TEXT, RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
) TC
,HXC_TIME_ATTRIBUTE_USAGES HTAU
,HXC_TIME_ATTRIBUTES HTA
,PA_PROJECTS_ALL PP
,PA_TASKS PT
WHERE DAY.PARENT_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
AND DAY.PARENT_BUILDING_BLOCK_OVN = TC.OBJECT_VERSION_NUMBER
AND DET.PARENT_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
AND DET.PARENT_BUILDING_BLOCK_OVN = DAY.OBJECT_VERSION_NUMBER
AND TRUNC(DAY.START_TIME) BETWEEN &START_TIME AND &END_TIME
AND DAY.RESOURCE_ID = &resource_id
AND TC.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
)
AND DET.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID
)
AND DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
AND DET.OBJECT_VERSION_NUMBER = HTAU.TIME_BUILDING_BLOCK_OVN
AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
AND HTA.ATTRIBUTE1 = TO_CHAR(PP.PROJECT_ID)
AND HTA.ATTRIBUTE2 = TO_CHAR(PT.TASK_ID)
AND HTA.ATTRIBUTE5 = 'ST'
ORDER BY 4 ;
3. Retrive the Jobs logged by the user for a particulat Period
SELECT wpe.wip_entity_id, wpe.wip_entity_name, wpt.transaction_date, wpt.transaction_id
, hou.name "Organization"
,ppf.full_name ,pp.segment1, pt.task_number, wpt.transaction_quantity
,wpt.primary_quantity, wpt.actual_resource_rate
,ppf.employee_number, ppf.full_name, wpt.attribute_category, wpt.attribute15
FROM wip_transactions wpt
,wip_entities wpe
,pa_projects_all pp
,pa_tasks pt
,hr_all_organization_units hou
,per_all_people_f ppf
WHERE wpt.employee_id = &resource_id
AND TRUNC(wpt.transaction_date) between TRUNC(TO_DATE(&from_date,'DD-MON-RRRR'))
AND TRUNC(TO_DATE(&to_date,'DD-MON-RRRR'))
AND wpt.wip_entity_id = wpe.wip_entity_id
AND wpt.organization_id = hou.organization_id
AND ppf.person_id = wpt.employee_id
AND TRUNC(SYSDATE) between TRUNC(ppf.effective_start_date) and TRUNC(ppf.effective_end_date)
AND pp.project_id = wpt.project_id
AND pt.task_id = wpt.task_id
ORDER BY wpt.transaction_id;
4.Identify Timecards not transferred to Projects:
SELECT DISTINCT ppf.employee_number "Employee Number"
,ppf.full_name "Full name"
,day.start_time "Day Worked"
,det.measure "Hours"
,tc.time_building_block_id "Timecard id"
,det.comment_text "Comments"
FROM hxc_time_building_blocks TC
,hxc_time_building_blocks DAY
,hxc_time_building_blocks DET
,hxc_time_attribute_usages HTAU
,hxc_time_attributes HTA
,hxc_timecard_summary HTS
,per_all_people_f ppf
-- ,pa_projects_all pp
-- ,pa_tasks pt
WHERE tc.scope = 'TIMECARD'
AND tc.approval_status = 'SUBMITTED'
AND day.parent_building_block_id = tc.time_building_block_id
AND day.scope = 'DAY'
AND day.approval_status = 'SUBMITTED'
AND DET.parent_building_block_id = day.time_building_block_id
AND det.scope = 'DETAIL'
AND det.approval_status = 'SUBMITTED'
AND HTAU.time_building_block_id = tc.time_building_block_id
AND HTAU.time_building_block_ovn = tc.object_version_number
AND HTAU.time_attribute_id = hta.time_attribute_id
AND hta.attribute_category(+) = 'SECURITY'
AND hts.timecard_id = tc.time_building_block_id
AND hts.resource_id = ppf.person_id
AND TRUNC(SYSDATE) between TRUNC(NVL(ppf.effective_start_date, SYSDATE))
AND TRUNC(NVL(ppf.effective_end_date, SYSDATE))
AND TC.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
)
AND DET.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID
)
AND DET.PARENT_BUILDING_BLOCK_OVN =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DAY'
AND TIME_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM PA_EXPENDITURE_ITEMS_ALL
WHERE ORIG_TRANSACTION_REFERENCE = DET.TIME_BUILDING_BLOCK_ID||':'||DET.OBJECT_VERSION_NUMBER)
ORDER BY 1;
5. Concurrent Programs submitted today:
select user_concurrent_program_name, program, requestor, actual_start_date, actual_completion_date, completion_text, has_sub_request
,flv.meaning "Status", flv1.meaning "Phase"
from FND_CONC_REQ_SUMMARY_V fcrs
,fnd_lookup_values flv
,fnd_lookup_values flv1
where flv.lookup_type = 'CP_STATUS_CODE'
and flv.lookup_code = fcrs.status_code
and flv1.lookup_type = 'CP_PHASE_CODE'
and flv1.lookup_code = fcrs.phase_code
and trunc(actual_start_date) = trunc(sysdate)
order by actual_start_date asc;
6. Scheduled Concurrent Programs:
select fap.application_name "Application name"
,NVL(fcr.description, fcp.user_concurrent_program_name) "Program name"
,fcr.request_id,fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code "Resubmit Interval"
,fcr.resubmit_time "RESUBMIT_TIME"
,fcr.resubmitted "RESUBMITTED"
,flv.meaning "PHASE"
,flv1.meaning "STATUS"
,fcr.argument_text "ARGUMENTS"
,fcr.request_type
from apps.fnd_concurrent_requests fcr
,apps.fnd_lookup_values flv
,apps.fnd_lookup_values flv1
,apps.fnd_concurrent_programs_vl fcp
,apps.fnd_application_vl fap
where fcr.phase_code = flv.lookup_code
and flv.language = 'US'
and flv.lookup_type = 'CP_PHASE_CODE'
and fcr.status_code = flv1.lookup_code
and flv1.language = 'US'
and flv1.lookup_type = 'CP_STATUS_CODE'
and flv.view_application_id = 0
and flv1.view_application_id = 0
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.phase_code = 'P' -- 'Pending'
and fcr.status_code in ('I', 'Q')
and fap.application_id = fcr.program_application_id
order by request_id desc;
7. Release 11i Bank, Bank branches and Bank Accounts:
SELECT apba.org_id,hou.name,apbb.bank_name, apbb.bank_number, apbb.bank_num,apbb.institution_type
,apbb.bank_branch_name ,apba.bank_account_name, apba.bank_account_num, apba.set_of_books_id
,apbb.address_line1 ,apbb.address_line2 ,apbb.address_line3 ,apbb.city, apbb.state, apbb.zip, apbb.country, apbb.province
,apbb.end_date, apbb.active_date
,apba.currency_code,apba.iban_number, apba.inactive_date,apbu.start_date, apbu.end_date, apbu.primary_flag, apba.bank_branch_id
FROM ap_bank_account_uses_all apbu
,ap_bank_accounts_all apba
,ap_bank_branches apbb
,hr_all_organization_units hou
WHERE apbu.external_bank_account_id = apba.bank_account_id
AND apba.bank_branch_id = apbb.bank_branch_id
AND trunc(nvl(apba.inactive_date, sysdate+1)) > trunc(sysdate)
AND trunc(sysdate) between trunc(nvl(apbu.start_date, sysdate)) and trunc(nvl(apbu.end_date, sysdate))
AND trunc(nvl(apbb.end_date, sysdate+1)) > trunc(sysdate)
AND apbu.org_id = apba.org_id
AND hou.organization_id = apbu.org_id;
8. Release 11i Supplier, Sites and Bank Extract having invoices from the last 15 Months:
SELECT DISTINCT *
FROM (
SELECT POV.VENDOR_NAME
,POV.VENDOR_ID
,POV.VENDOR_NAME_ALT
,POV.SEGMENT1
,POV.ENABLED_FLAG
,POV.VENDOR_TYPE_LOOKUP_CODE
,POV.PAY_DATE_BASIS_LOOKUP_CODE
,POV.PAYMENT_PRIORITY
,POV.PAYMENT_METHOD_LOOKUP_CODE
,POV.TERMS_DATE_BASIS
,POV.QTY_RCV_TOLERANCE
,POV.QTY_RCV_EXCEPTION_CODE
,POV.ENFORCE_SHIP_TO_LOCATION_CODE
,POV.DAYS_EARLY_RECEIPT_ALLOWED
,POV.DAYS_LATE_RECEIPT_ALLOWED
,POV.RECEIPT_DAYS_EXCEPTION_CODE
,POV.RECEIVING_ROUTING_ID
,POV.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
,POV.ALLOW_UNORDERED_RECEIPTS_FLAG
,POV.VAT_REGISTRATION_NUM
,POV.BANK_CHARGE_BEARER
,POV.MATCH_OPTION
,NULL ECE_TP_LOCATION_CODE
,POV.ALLOW_AWT_FLAG
,POV.FEDERAL_REPORTABLE_FLAG
,POV.OFFSET_TAX_FLAG
,POVS.VENDOR_SITE_CODE
,POVS.VENDOR_SITE_CODE_ALT
,POVS.ADDRESS_LINE1
,POVS.ADDRESS_LINE2
,POVS.ADDRESS_LINE3
,NULL ADDRESS_LINE4
,POVS.CITY
,POVS.STATE
,POVS.ZIP
,POVS.PROVINCE
,POVS.COUNTRY
,POVS.AREA_CODE
,POVS.PHONE
,POVS.FAX_AREA_CODE
,POVS.FAX
,POVS.TELEX
,POVS.PURCHASING_SITE_FLAG
,POVS.PAY_SITE_FLAG
,POVS.PRIMARY_PAY_SITE_FLAG
,POVS.SHIP_TO_LOCATION_ID
,NULL "SHIP_TO_LOCATION_CODE"
,POVS.BILL_TO_LOCATION_ID
,NULL "BILL_TO_LOCATION_CODE"
,POVS.PAYMENT_METHOD_LOOKUP_CODE "SS_PAYMENT_METHOD_LOOKUP_CODE"
,POVS.TERMS_DATE_BASIS "SS_TERMS_DATE_BASIS"
,POVS.VAT_CODE
,POVS.PAY_GROUP_LOOKUP_CODE
,POVS.PAYMENT_PRIORITY "SS_PAYMENT_PRIORITY"
,POVS.TERMS_ID
,NULL "TERMS_NAME"
,POVS.INVOICE_AMOUNT_LIMIT
,POVS.PAY_DATE_BASIS_LOOKUP_CODE "SS_PAY_DATE_BASIS_LOOKUP_CODE"
,POVS.ALWAYS_TAKE_DISC_FLAG
,POVS.INVOICE_CURRENCY_CODE
,POVS.PAYMENT_CURRENCY_CODE
,POVS.HOLD_ALL_PAYMENTS_FLAG
,POVS.TAX_REPORTING_SITE_FLAG
,POVS.ORG_ID
,hou.name "OPERATING_UNIT_NAME"
,POVS.DEFAULT_PAY_SITE_ID
,POVS.MATCH_OPTION "SS_MATCH_OPTION"
,POVS.TOLERANCE_ID
,NULL "TOLERANCE_NAME"
,NULL "SERVICES_TOLERANCE_ID"
,NULL "SERVICES_TOLERANCE_NAME"
,NULL "PAYMENT_METHOD_CODE"
,POVS.SUPPLIER_NOTIF_METHOD
,POVS.EMAIL_ADDRESS
,POVS.REMITTANCE_EMAIL
,POVS.ALLOW_AWT_FLAG "SS_ALLOW_AWT_FLAG"
,POVS.AWT_GROUP_ID
,POVS.AUTO_TAX_CALC_FLAG
,POVS.VAT_REGISTRATION_NUM "SS_VAT_REGISTRATION_NUM"
,POVS.OFFSET_TAX_FLAG "SS_OFFSET_TAX_FLAG"
,POVS.AP_TAX_ROUNDING_RULE
,POVS.AMOUNT_INCLUDES_TAX_FLAG
,POVS.AUTO_TAX_CALC_OVERRIDE
,hou.attribute13 "ORG_COUNTRY"
,apbb.bank_name
,apbb.bank_number
,apbb.institution_type
,NULL "BANK_PARTY_ID"
,apbb.bank_branch_name "BRANCH_NAME"
,NULL "BRANCH_NUMBER"
,NULL "BRANCH_TYPE"
,NULL "BIC"
,NULL "BRANCH_CODE"
,apbb.address_line1 "SITE_ADDRESS_LINE1"
,apbb.address_line2 "SITE_ADDRESS_LINE2"
,apbb.address_line3 "SITE_ADDRESS_LINE3"
, NULL "SITE_ADDRESS_LINE4"
,apbb.city "SITE_CITY"
,apbb.state "SITE_STATE"
,apbb.county "SITE_COUNTY"
,apbb.zip "SITE_ZIP"
,apbb.province "SITE_PROVINCE"
,apba.bank_account_name "BANK_ACCOUNT_NAME"
,apba.bank_account_num "BANK_ACCOUNT_NUM"
,NULL "CHECK_DIGITS"
,apba.currency_code "CURRENCY"
,apba.iban_number "IBAN"
,NULL "SECONDARY_ACCOUNT_REFERENCE"
,NULL "PRIMARY_PAY_FLAG"
,apbu.primary_flag "PRIMARY ACCOUNT"
from apps.po_vendors pov
,apps.po_vendor_sites_all povs
,apps.hr_all_organization_units hou
,apps.ap_bank_account_uses_all apbu
,apps.ap_bank_accounts_all apba
,apps.ap_bank_branches apbb
where povs.org_id = hou.organization_id
and pov.vendor_id = povs.vendor_id
and pov.enabled_flag = 'Y'
and NVL(pov.one_time_flag, 'N') <> 'Y' -- with one time flag
and trunc( NVL(povs.inactive_date, sysdate)) >= trunc(sysdate)
and trunc(sysdate) between trunc(hou.date_from) and trunc(nvl(hou.date_to, sysdate))
and (povs.vendor_id, povs.vendor_site_id) in ( select distinct vendor_id, vendor_site_id --, vendor_site_id
from apps.ap_invoices_all
where trunc(creation_date) >= trunc(add_months(sysdate, -15)) ) -- Have invoices for the last 15 Months
and apbu.vendor_id = povs.vendor_id
and apbu.vendor_site_id = povs.vendor_site_id
and apbu.external_bank_account_id = apba.bank_account_id
and apba.bank_branch_id = apbb.bank_branch_id
and trunc(nvl(apba.inactive_date, sysdate+1)) > trunc(sysdate)
and trunc(sysdate) between trunc(nvl(apbu.start_date, sysdate)) and trunc(nvl(apbu.end_date, sysdate))
and trunc(nvl(apbb.end_date, sysdate+1)) > trunc(sysdate)
and apbu.org_id = apba.org_id
and hou.organization_id = apbu.org_id
UNION
SELECT POV.VENDOR_NAME
,POV.VENDOR_ID
,POV.VENDOR_NAME_ALT
,POV.SEGMENT1
,POV.ENABLED_FLAG
,POV.VENDOR_TYPE_LOOKUP_CODE
,POV.PAY_DATE_BASIS_LOOKUP_CODE
,POV.PAYMENT_PRIORITY
,POV.PAYMENT_METHOD_LOOKUP_CODE
,POV.TERMS_DATE_BASIS
,POV.QTY_RCV_TOLERANCE
,POV.QTY_RCV_EXCEPTION_CODE
,POV.ENFORCE_SHIP_TO_LOCATION_CODE
,POV.DAYS_EARLY_RECEIPT_ALLOWED
,POV.DAYS_LATE_RECEIPT_ALLOWED
,POV.RECEIPT_DAYS_EXCEPTION_CODE
,POV.RECEIVING_ROUTING_ID
,POV.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
,POV.ALLOW_UNORDERED_RECEIPTS_FLAG
,POV.VAT_REGISTRATION_NUM
,POV.BANK_CHARGE_BEARER
,POV.MATCH_OPTION
,NULL ECE_TP_LOCATION_CODE
,POV.ALLOW_AWT_FLAG
,POV.FEDERAL_REPORTABLE_FLAG
,POV.OFFSET_TAX_FLAG
,POVS.VENDOR_SITE_CODE
,POVS.VENDOR_SITE_CODE_ALT
,POVS.ADDRESS_LINE1
,POVS.ADDRESS_LINE2
,POVS.ADDRESS_LINE3
,NULL "ADDRESS_LINE4"
,POVS.CITY
,POVS.STATE
,POVS.ZIP
,POVS.PROVINCE
,POVS.COUNTRY
,POVS.AREA_CODE
,POVS.PHONE
,POVS.FAX_AREA_CODE
,POVS.FAX
,POVS.TELEX
,POVS.PURCHASING_SITE_FLAG
,POVS.PAY_SITE_FLAG
,POVS.PRIMARY_PAY_SITE_FLAG
,POVS.SHIP_TO_LOCATION_ID
,NULL "SHIP_TO_LOCATION_CODE"
,POVS.BILL_TO_LOCATION_ID
,NULL "BILL_TO_LOCATION_CODE"
,POVS.PAYMENT_METHOD_LOOKUP_CODE "SS_PAYMENT_METHOD_LOOKUP_CODE"
,POVS.TERMS_DATE_BASIS "SS_TERMS_DATE_BASIS"
,POVS.VAT_CODE
,POVS.PAY_GROUP_LOOKUP_CODE
,POVS.PAYMENT_PRIORITY "SS_PAYMENT_PRIORITY"
,POVS.TERMS_ID
,NULL "TERMS_NAME"
,POVS.INVOICE_AMOUNT_LIMIT
,POVS.PAY_DATE_BASIS_LOOKUP_CODE "SS_PAY_DATE_BASIS_LOOKUP_CODE"
,POVS.ALWAYS_TAKE_DISC_FLAG
,POVS.INVOICE_CURRENCY_CODE
,POVS.PAYMENT_CURRENCY_CODE
,POVS.HOLD_ALL_PAYMENTS_FLAG
,POVS.TAX_REPORTING_SITE_FLAG
,POVS.ORG_ID
,hou.name "OPERATING_UNIT_NAME"
,POVS.DEFAULT_PAY_SITE_ID
,POVS.MATCH_OPTION "SS_MATCH_OPTION"
,POVS.TOLERANCE_ID
,NULL "TOLERANCE_NAME"
,NULL "SERVICES_TOLERANCE_ID"
,NULL "SERVICES_TOLERANCE_NAME"
,NULL "PAYMENT_METHOD_CODE"
,POVS.SUPPLIER_NOTIF_METHOD
,POVS.EMAIL_ADDRESS
,POVS.REMITTANCE_EMAIL
,POVS.ALLOW_AWT_FLAG "SS_ALLOW_AWT_FLAG"
,POVS.AWT_GROUP_ID
,POVS.AUTO_TAX_CALC_FLAG
,POVS.VAT_REGISTRATION_NUM "SS_VAT_REGISTRATION_NUM"
,POVS.OFFSET_TAX_FLAG "SS_OFFSET_TAX_FLAG"
,POVS.AP_TAX_ROUNDING_RULE
,POVS.AMOUNT_INCLUDES_TAX_FLAG
,POVS.AUTO_TAX_CALC_OVERRIDE
,hou.attribute13 "ORG_COUNTRY"
,NULL "BANK_NAME"
,NULL "BANK_NUMBER"
,NULL "INSTITUTION_TYPE"
,NULL "BANK_PARTY_ID"
,NULL "BRANCH_NAME"
,NULL "BRANCH_NUMBER"
,NULL "BRANCH_TYPE"
,NULL "BIC"
,NULL "BRANCH_CODE"
,NULL "SITE_ADDRESS_LINE1"
,NULL "SITE_ADDRESS_LINE2"
,NULL "SITE_ADDRESS_LINE3"
,NULL "SITE_ADDRESS_LINE4"
,NULL "SITE_CITY"
,NULL "SITE_STATE"
,NULL "SITE_COUNTY"
,NULL "SITE_ZIP"
,NULL "SITE_PROVINCE"
,NULL "BANK_ACCOUNT_NAME"
,NULL "BANK_ACCOUNT_NUM"
,NULL "CHECK_DIGITS"
,NULL "CURRENCY"
,NULL "IBAN"
,NULL "SECONDARY_ACCOUNT_REFERENCE"
,NULL "PRIMARY_PAY_FLAG"
,NULL "PRIMARY ACCOUNT"
from apps.po_vendors pov
,apps.po_vendor_sites_all povs
,apps.hr_all_organization_units hou
where povs.org_id = hou.organization_id
and pov.vendor_id = povs.vendor_id
and pov.enabled_flag = 'Y'
and NVL(pov.one_time_flag, 'N') <> 'Y' -- With One Time Flag
and trunc( NVL(povs.inactive_date, sysdate)) >= trunc(sysdate)
and trunc(sysdate) between trunc(hou.date_from) and trunc(nvl(hou.date_to, sysdate))
and (povs.vendor_id, povs.vendor_site_id) in ( select distinct vendor_id, vendor_site_id --, vendor_site_id
from apps.ap_invoices_all
where trunc(creation_date) >= trunc(add_months(sysdate, -15)) ) -- Have invoices for the last 15 Months
and NOT EXISTS ( select 1
from apps.ap_bank_account_uses_all apbau1
,apps.ap_bank_accounts_all apb1
where apbau1.vendor_id = povs.vendor_id
and apbau1.vendor_site_id = povs.vendor_site_id
and apbau1.external_bank_account_id = apb1.bank_account_id
and apbau1.org_id = apb1.org_id
and apb1.org_id = povs.org_id
and trunc(sysdate) between trunc(nvl(apbau1.start_date, sysdate)) and trunc(nvl(apbau1.end_date, sysdate)))
)
order by segment1;
9. Entries Unposted to GL from SLA:
select gll.name, faa.application_name, faa.application_id, xah.*, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, xal.*
from apps.xla_ae_headers xah
,apps.xla_ae_lines xal
,apps.gl_ledgers gll
,apps.fnd_application_vl faa
,apps.gl_code_combinations gcc
where period_name = &Period_name
and application_id = &appl_id -- Applicationid
and xah.ae_header_id = xal.ae_header_id
and xah.ledger_id = gll.ledger_id
and xah.application_id = faa.application_id
and gll.ledger_id = &Ledgerid
and gl_transfer_status_code = 'N'
and gcc.code_combination_id = xal.code_combination_id
order by faa.application_id, xal.ae_header_id, xal.ae_line_num;
10. Tablespace usage details:
SELECT df.tablespace_name "Tablespace", totalusedspace "USED MB", (df.totalspace - tu.totalusedspace) "FREE MB"
,df.totalspace "TOTAL MB", ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "PCT FREE"
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
FROM dba_segments
GROUP BY tablespace_name) tu
WHERE df.tablespace_name = tu.tablespace_name;
Scheduled Concurrent Programs and their Timings:
SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
Then Substr(
fu.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
FROM fnd_concurrent_requests fcr
, fnd_user fu
, fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpt
, fnd_printer_styles_tl fpst
, fnd_conc_release_classes fcrc
, fnd_responsibility_tl frt
, fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
Order By Fu.Description, Fcr.Requested_Start_Date Asc;
AR Memo Lines List:
SELECT hou.name operating_unit,
t.NAME "Memo Line Name",
t.description,
b.line_type,
b.tax_code "Tax Classification Code",
b.tax_product_category "Tax Product Category",
b.uom_code "Unit of Measure",
gcc.concatenated_segments "Revenue Account",
b.start_date "Start date",
b.end_date "End Date"
FROM hr_operating_units hou
,ar_memo_lines_all_tl t
,ar_memo_lines_all_b b
,gl_code_combinations_kfv gcc
WHERE hou.organization_id = b.org_id
AND b.memo_line_id = t.memo_line_id
AND b.gl_id_rev = gcc.code_combination_id
ORDER BY hou.name, t.NAME;
Add list of Responsibilities and do a Password reset:
set serveroutput on
DECLARE
CURSOR c1 IS
select appl.application_short_name "APPLICATION_SHORT_NAME"
,resp.responsibility_key "RESPONSIBILITY_KEY"
,UPPER(fdg.data_group_name) "DATA_GROUP_NAME"
from fnd_responsibility_vl resp
,fnd_application_vl appl
,fnd_data_groups fdg
where resp.application_id = appl.application_id
and resp.data_group_id = fdg.data_group_id
and resp.responsibility_name in ('Application Developer',
'Functional Administrator',
'User Management',
'Workflow Administrator Web (New)',
'Application Diagnostics',
'System Administrator');
type l_rec is table of C1%ROWTYPE;
c boolean;
lc_rec l_rec;
j number := 0;
begin
c:=fnd_user_pkg.ChangePassword('USERNAME','Password');
if c = true then
dbms_output.put_line('success');
commit;
else
dbms_output.put_line('FAIL');
end if;
OPEN C1;
FETCH C1 BULK COLLECT INTO lc_rec;
CLOSE C1;
for i in 1..lc_rec.COUNT LOOP
fnd_user_pkg.addresp
( username => 'USERNAME',
resp_app => lc_rec(i).application_short_name,
resp_key => lc_rec(i).responsibility_key,
security_group => lc_rec(i).data_group_name,
description => NULL,
start_date => sysdate,
end_date => NULL
);
end loop;
commit;
dbms_output.put_line('Normal Completion');
exception
when others then
dbms_output.put_line('Exception:'||SQLERRM);
end
/
List of Locked Objects:
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14
-- Search for locked objects
-- To be executed under the SYSTEM account
-- Compatible with Oracle10.1.x and higher
select
distinct to_name object_locked
from
v$object_dependency
where
to_address in
(
select /*+ ordered */
w.kgllkhdl address
from
dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
)
/
Print This Post
