Need to find the last transaction of a module for License renewal purpose.
Inventory
The following SQL statement return the date and the organization name:
select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from mtl_material_transactions a, HR_ORGANIZATION_UNITS b where a.organization_id= b.organization_id and a.org_id in (&org_id is required)
GROUP BY b.name
)
GROUP BY name; |
iProcurement
The following SQL statement return the date and the organization name:
select max(last_update_date), name
from (
select max(a.last_update_date) last_update_date, b.name from po_requisition_headers_all a, HR_OPERATING_UNITS b
where a.org_id in (&org_id is required)
and apps_source_code = 'POR'
AND AUTHORIZATION_STATUS = 'APPROVED'
AND TYPE_LOOKUP_CODE = 'PURCHASE'
and a.org_id= b.organization_id
GROUP BY b.name
)
GROUP BY name;
|
Oracle Sourcing for Oracle Purchasing
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from PON_AUCTION_HEADERS_ALL a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and aution_status = 'ACTIVE' and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
Oracle Project Costing
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from PA_TRANSACTION_INTERFACE_ALL a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and aution_status = 'ACTIVE' and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
Oracle Cash Management
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from ar_cash_receipts_all a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
Oracle Accounts Payable
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from AP_INVOICES_ALL a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
Oracle Fixed Assets
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from FA_BOOK_CONTROLS a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
Oracle Order Management
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from oe_order_headers_all a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
Oracle iExpense
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from AP_EXPENSE_REPORT_HEADERS_ALL a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name;
|
Oracle Procurement
The following SQL statement return the date and the organization name:
select max(last_update_date), name from ( select max(a.last_update_date) last_update_date, b.name from PO_HEADERS_ALL a, HR_ORGANIZATION_UNITS b where a.org_id in (&org_id) and a.org_id= b.organization_id GROUP BY b.name ) GROUP BY name; |
The following SQL statement return the date and the organization name: 
The following SQL statement return the date and the organization name: 
The following SQL statement return the date and the organization name: 
The following SQL statement return the date and the organization name: 
No comments:
Post a Comment
comments are welcome