Monday, January 23, 2017

O2C Cycle tables


--Inventory
select *
from mtl_system_items_b msib
where msib.segment1='TEST-GJ1';

select *
from mtl_onhand_quantities_detail
where inventory_item_id=9727542;

select *
from mtl_onhand_quantities --view
where inventory_item_id=9727542;

--Order
select ooha.flow_status_code, ooha.*
from oe_order_headers_all ooha
where order_number=11067733;

select oola.flow_status_code, oola.*
from oe_order_lines_all oola
where header_id=40757700;

select wdd.released_status, wdd.*
from wsh_delivery_details wdd
where  source_header_id=40757700; --R(Ready to release), B(), C()

select wda.delivery_id, wda.*
from wsh_delivery_assignments wda
where delivery_detail_id=20924729;

select *
from wsh_new_deliveries
where delivery_id=8723686;

--
select *
from mtl_txn_request_headers
where 1=1
and request_number=23344489;
--and trunc(creation_date)=trunc(sysdate);

select *
from mtl_txn_request_lines
where header_id=23344490;

select *
from mtl_reservations
where inventory_item_id=9727542;

select *
from mtl_material_transactions
where inventory_item_id=9727542;

select *
from ra_interface_lines_all
where interface_line_attribute1='11067733';

--AR
select *
from ra_customer_trx_all
where trx_number='11071374';

select *
from ra_customer_trx_lines_all
where customer_trx_id=5888998;

select rcda.event_id, rcda.*
from ra_cust_trx_line_gl_dist_all rcda
where customer_trx_line_id=8918096;

--Accounting(transactions)
select *
from xla_events
where event_id=46887553;

select *
from xla_ae_headers
where event_id=46887553;

select *
from xla_ae_lines
where ae_header_id=23181811;

select  *
from gl_interface
where reference26=46887553;

--Ledger
select *
from gl_je_batches
where name='Receivables A 10207531 81076218';

select *
from gl_je_headers
where je_batch_id=14085655;

select *
from gl_je_lines
where je_header_id=24716354;

--Posting
select *
from gl_balances
where ledger_id=2022
and code_combination_id in (3006,
7000027,
7000032,
5854)
and period_name='Aug-16'
and trunc(last_update_date)=trunc(sysdate);

--Receipts
select *
from ar_cash_receipts_all acra
where receipt_number='RECEIPT_11067733';

select acrha.event_id, acrha.*
from ar_cash_receipt_history_all acrha
where cash_receipt_id=11822798;

select *
from ar_receivable_applications_all
where cash_receipt_id=11822798;

select *
from ar_payment_schedules_all
where cash_receipt_id=11822798;

--Accounting(Receipts)
select *
from xla_events
where event_id=46887554;

select *
from xla_ae_headers
where event_id=46887554;

select *
from xla_ae_lines
where ae_header_id=23181812;

select  *
from gl_interface
where reference26=46887554;

--Ledger
select *
from gl_je_batches
where name='Receivables A 10207532 81076345';

select *
from gl_je_headers
where je_batch_id=14085657;

select *
from gl_je_lines
where je_header_id=24716356;

--Posting
select *
from gl_balances
where ledger_id=2022
and code_combination_id in (3006,
5092)
and period_name='Aug-16'
and trunc(last_update_date)=trunc(sysdate);

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...