采办，接收数据收集SQL汇总（从订单->接收->INVOICE所有数据关联SQL）
采购，接收数据收集SQL汇总（从订单->接收->INVOICE所有数据关联SQL）
| INDEX OF QUERIES | |
|---|---|
| Source Document: Purchase Order: | |
| 1: po_headers_all (sql) | 2: po_lines_all (sql) |
| 3: po_line_locations_all (sql) | 4: po_distributions_all (sql) |
| Accounting Related Data: | |
| 5: Account Code Combinations - OU "XY_瑗垮煙浜氱敵" (sql) | 6: rcv_receiving_sub_ledger (sql) |
| Payables Data: | |
| 7: ap_invoice_distributions_all (sql) | 8: ap_invoices_all (sql) |
| 9: ap_invoice_lines_interface (sql) | 10: ap_invoices_interface (sql) |
| Shipment, Receiving, Supply Information: | |
| 11: rcv_shipment_headers (sql) | 12: rcv_shipment_lines (sql) |
| 13: rcv_transactions (sql) | 14: mtl_supply (sql) |
| 15: rcv_supply (sql) | 16: rcv_headers_interface (sql) |
| 17: rcv_transactions_interface (sql) | 18: po_interface_errors (sql) |
| Inventory Information: | |
| 19: mtl_system_items (sql) | 20: mtl_material_transactions (sql) |
| 21: mtl_transaction_types (sql) | 22: mtl_txn_request_lines (sql) |
| 23: mtl_material_transactions_temp (sql) | |
| Organizations Setup: | |
| 24: org_organization_definitions (sql) | 25: mtl_parameters (sql) |
| 26: rcv_parameters (sql) | 27: po_system_parameters_all (sql) |
| 28: financials_system_params_all (sql) | |
| Serial Lookups: | |
| 29: Serial Control (sql) | |
| Lot Data: | |
| 30: mtl_lot_numbers (sql) | 31: mtl_transaction_lot_numbers (sql) |
| 32: mtl_transaction_lots_interface (sql) | 33: mtl_transaction_lots_temp (sql) |
| 34: rcv_lots_supply (sql) | 35: rcv_lot_transactions (sql) |
| 36: rcv_lots_interface (sql) | |
| Lot Lookups: | |
| 37: Lot Control (sql) | 38: Lot Generation (sql) |
| 39: Lot Uniqueness (sql) | |
1: po_headers_all - SQL
select ph.*
from po_headers_all ph
where segment1 = '15100005'
and ph.org_id = 85 order by ph.org_id
2: po_lines_all - SQL
select pl.*
from po_lines_all pl ,
po_headers_all ph
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pl.po_header_id = ph.po_header_id
and ph.org_id = 85 order by pl.po_header_id , pl.po_line_id
3: po_line_locations_all - SQL
select pl.line_num , pl.item_id , msi.segment1 "Item Number (segment1) " , pll.*
from po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
mtl_system_items msi
where pl.po_header_id = ph.po_header_id
and pll.po_line_id = pl.po_line_id
and msi.organization_id = pll.ship_to_organization_id
and msi.inventory_item_id = pl.item_id
and ph.segment1 = '15100005'
and ph.org_id = 85
union all
select pl.line_num , pl.item_id , null , pll.*
from po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph
where pl.po_header_id = ph.po_header_id
and pll.po_line_id = pl.po_line_id
and pl.item_id is null
and ph.segment1 = '15100005'
and ph.org_id = 85
4: po_distributions_all - SQL
select pd.*
from po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
po_distributions_all pd
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and pll.line_location_id = pd.line_location_id
and ph.org_id = 85 order by pd.po_header_id , pd.po_line_id , pd.line_location_id , pd.po_distribution_id
5: Account Code Combinations - OU
/* Accounts for Op. Unit Id 85 = XY_瑗垮煙浜氱敵 */ select distinct 85 OU_id , gcc.CODE_COMBINATION_ID , gcc.segment1||'.'|| gcc.segment2||'.'|| gcc.segment3||'.'|| gcc.segment4||'.'|| gcc.segment5||'.'|| gcc.segment6||'.'|| gcc.segment7||'.'|| gcc.segment8 " Account Combination " , gcc.CHART_OF_ACCOUNTS_ID , gcc.ACCOUNT_TYPE , gcc.ENABLED_FLAG , gcc.SUMMARY_FLAG , gcc.DESCRIPTION , gcc.START_DATE_ACTIVE , gcc.END_DATE_ACTIVE , gcc.segment1 "PZ_COMPANY" , gcc.segment2 "PZ_DEPARTMENT" , gcc.segment3 "PZ_ACCOUNT" , gcc.segment4 "PZ_SUBACCOUNT" , gcc.segment5 "PZ_PRODUCT" , gcc.segment6 "PZ_PROJECT" , gcc.segment7 "PZ_PARTY" , gcc.segment8 "PZ_SPARE"
from gl_code_combinations gcc ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
po_distributions_all pd
where gcc.summary_flag = 'N'
and template_id is null
and chart_of_accounts_id = 101
and pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and pll.line_location_id = pd.line_location_id
and gcc.code_combination_id in (pd.accrual_account_id , pd.budget_account_id , pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id)
6: rcv_receiving_sub_ledger - SQL
select rrsl.*
from rcv_receiving_sub_ledger rrsl ,
rcv_transactions rt ,
po_headers_all ph
where rt.po_header_id = ph.po_header_id
and rrsl.rcv_transaction_id = rt.transaction_id
and ph.segment1 = '15100005'
and ph.org_id = 85
7: ap_invoice_distributions_all - SQL
select id.*
from ap_invoice_distributions_all id ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
po_distributions_all pd
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and pll.line_location_id = pd.line_location_id
and id.po_distribution_id = pd.po_distribution_id
and ph.org_id = 85
8: ap_invoices_all - SQL
select ai.*
from ap_invoices_all ai ,
ap_invoice_distributions_all id ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
po_distributions_all pd
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and pll.line_location_id = pd.line_location_id
and id.po_distribution_id = pd.po_distribution_id
and ai.invoice_id = id.invoice_id
and ph.org_id = 85
9: ap_invoice_lines_interface - SQL
select ili.*
from ap_invoice_lines_interface ili ,
po_headers_all ph
where ph.segment1 = '15100005'
and (ili.po_header_id = ph.po_header_id
or ili.po_number = '15100005' )
and ph.org_id = 85
10: ap_invoices_interface - SQL
select ihi.*
from ap_invoices_interface ihi ,
ap_invoice_lines_interface ili ,
po_headers_all ph
where ph.segment1 = '15100005'
and (ili.po_header_id = ph.po_header_id
or ili.po_number = '15100005' )
and ihi.invoice_id = ili.invoice_id
and ph.org_id = 85
11: rcv_shipment_headers - SQL
select distinct rsh.*
from po_headers_all ph ,
rcv_shipment_lines rsl ,
rcv_shipment_headers rsh
where ph.segment1 = '15100005'
and rsl.po_header_id = ph.po_header_id
and rsl.shipment_header_id = rsh.shipment_header_id
and ph.org_id = 85 order by rsh.shipment_header_id
12: rcv_shipment_lines - SQL
select rsl.*
from po_headers_all ph ,
rcv_shipment_lines rsl
where ph.segment1 = '15100005'
and rsl.po_header_id = ph.po_header_id
and ph.org_id = 85 order by rsl.po_header_id , rsl.po_release_id , rsl.po_line_id , rsl.po_line_location_id , rsl.po_distribution_id
13: rcv_transactions - SQL
select rt.*
from rcv_transactions rt ,
po_headers_all ph
where rt.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and ph.org_id = 85 order by rt.po_header_id , rt.po_release_id , rt.po_line_id , rt.po_line_location_id , rt.po_distribution_id , rt.transaction_id
14: mtl_supply - SQL
select ms.*
from mtl_supply ms ,
po_headers_all ph
where ms.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and ph.org_id = 85 order by ms.po_header_id , ms.po_release_id , ms.po_line_id , ms.po_line_location_id , ms.po_distribution_id
15: rcv_supply - SQL
select rs.*
from rcv_supply rs ,
po_headers_all ph
where rs.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and ph.org_id = 85 order by rs.po_header_id , rs.po_release_id , rs.po_line_id , rs.po_line_location_id , rs.po_distribution_id
16: rcv_headers_interface - SQL
select rhi.*
from rcv_headers_interface rhi
where exists (
select 1
from po_headers_all ph ,
rcv_shipment_lines rsl ,
rcv_shipment_headers rsh
where ph.segment1 = '15100005'
and ph.org_id = 85
and rsl.po_header_id = ph.po_header_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rsh.shipment_header_id = rhi.receipt_header_id)
or exists (
select 2
from rcv_transactions_interface rti
where nvl (rti.document_num , '-99999') = '15100005'
and rhi.header_interface_id = rti.header_interface_id)
or exists (
select 3
from rcv_transactions_interface rti ,
po_headers_all poh
where rti.po_header_id = poh.po_header_id
and rti.po_header_id is not null
and poh.segment1 = '15100005'
and rhi.header_interface_id = rti.header_interface_id)
17: rcv_transactions_interface - SQL
select distinct rti.*
from rcv_transactions_interface rti
where nvl(rti.document_num , '-99999') = '15100005'
or exists (
select 1
from po_headers_all ph
where ph.segment1 = '15100005'
and ph.org_id = 85
and rti.po_header_id = ph.po_header_id )
18: po_interface_errors - SQL
select distinct pie.*
from po_interface_errors pie ,
rcv_transactions_interface rti ,
rcv_headers_interface rhi ,
po_headers_all poh
where ((table_name = 'RCV_HEADERS_INTERFACE'
and rti.header_interface_id = rhi.header_interface_id
and pie.interface_header_id = rhi.header_interface_id
and (nvl (rti.po_header_id , -999) = poh.po_header_id
or nvl (rti.document_num , '-9999') = poh.segment1 ) )
or (table_name = 'RCV_TRANSACTIONS_INTERFACE'
and pie.interface_line_id = rti.interface_transaction_id
and (nvl (rti.po_header_id , -999) = poh.po_header_id
or nvl (rti.document_num , '-9999') = poh.segment1 ) ) )
and poh.segment1 = '15100005'
19: mtl_system_items - SQL
select distinct msi.*
from mtl_system_items msi ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and msi.inventory_item_id = pl.item_id
and msi.organization_id = pll.ship_to_organization_id
and ph.org_id = 85
20: mtl_material_transactions - SQL
select mmt.*
from mtl_material_transactions mmt ,
po_headers_all ph
where mmt.transaction_source_id = ph.po_header_id
and mmt.transaction_source_type_id = 1
and ph.segment1 = '15100005'
and ph.org_id = 85
21: mtl_transaction_types - SQL
select mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id , mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date
from mtl_transaction_types mtt
where exists (
select 1
from mtl_material_transactions mmt ,
po_headers_all ph
where mmt.transaction_source_id = ph.po_header_id
and mmt.transaction_source_type_id = 1
and ph.segment1 = '15100005'
and mtt.transaction_type_id = mmt.transaction_type_id
and ph.org_id = 85 )
or exists (
select 2
from mtl_material_transactions_temp mmtt ,
po_headers_all ph
where mmtt.transaction_source_id = ph.po_header_id
and ph.segment1 = '15100005'
and mmtt.transaction_type_id = mtt.transaction_type_id
and ph.org_id = 85 )
22: mtl_txn_request_lines - SQL
select distinct mol.*
from mtl_txn_request_lines mol ,
rcv_transactions rt ,
rcv_shipment_lines rsl ,
po_headers_all ph
where mol.reference_id = decode(mol.reference , 'SHIPMENT_LINE_ID' , rt.shipment_line_id , 'PO_LINE_LOCATION_ID' , rt.po_line_location_id , 'ORDER_LINE_ID' , rt.oe_order_line_id)
and rt.shipment_line_id = rsl.shipment_line_id
and mol.organization_id = rt.organization_id
and mol.inventory_item_id = rsl.item_id
and ph.segment1 = '15100005'
and rsl.po_header_id = ph.po_header_id
and ph.org_id = 85
23: mtl_material_transactions_temp - SQL
select mmtt.*
from mtl_material_transactions_temp mmtt ,
po_headers_all ph
where mmtt.transaction_source_id = ph.po_header_id
and ph.segment1 = '15100005'
and ph.org_id = 85
24: org_organization_definitions - SQL
select distinct ood.*
from org_organization_definitions ood ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
financials_system_params_all fsp
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and fsp.org_id = ph.org_id
and ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)
and ph.org_id = 85
25: mtl_parameters - SQL
select distinct mp.*
from mtl_parameters mp ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
financials_system_params_all fsp
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and fsp.org_id = ph.org_id
and mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id)
and ph.org_id = 85
26: rcv_parameters - SQL
select distinct rp.*
from rcv_parameters rp ,
po_line_locations_all pll ,
po_lines_all pl ,
po_headers_all ph ,
financials_system_params_all fsp
where pl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and pll.po_line_id = pl.po_line_id
and fsp.org_id = ph.org_id
and (rp.organization_id = fsp.inventory_organization_id
or rp.organization_id = pll.ship_to_organization_id)
and ph.org_id = 85
27: po_system_parameters_all - SQL
select psp.*
from po_system_parameters_all psp ,
po_headers_all ph
where psp.org_id = ph.org_id
and ph.segment1 = '15100005'
and ph.org_id = 85
28: financials_system_params_all - SQL
select fsp.*
from financials_system_params_all fsp ,
po_headers_all ph
where fsp.org_id = ph.org_id
and ph.segment1 = '15100005'
and ph.org_id = 85
29: Serial Control - SQL
select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active
from mfg_lookups
where lookup_type = 'MTL_SERIAL_NUMBER'
30: mtl_lot_numbers - SQL
select mln.*
from mtl_lot_numbers mln ,
mtl_transaction_lot_numbers mtln ,
po_headers_all ph ,
mtl_material_transactions mmt
where mmt.transaction_source_id = ph.po_header_id
and mmt.transaction_source_type_id = 1
and ph.segment1 = '15100005'
and mtln.transaction_id = mmt.transaction_id
and mtln.lot_number = mln.lot_number
and mtln.inventory_item_id = mln.inventory_item_id
and mtln.organization_id = mln.organization_id
and ph.org_id = 85
31: mtl_transaction_lot_numbers - SQL
select mtln.*
from mtl_transaction_lot_numbers mtln ,
po_headers_all ph ,
mtl_material_transactions mmt
where mmt.transaction_source_id = ph.po_header_id
and mmt.transaction_source_type_id = 1
and ph.segment1 = '15100005'
and mtln.transaction_id = mmt.transaction_id
and ph.org_id = 85
32: mtl_transaction_lots_interface - SQL
select mtli.*
from mtl_transaction_lots_interface mtli ,
rcv_transactions_interface rti
where (nvl(rti.document_num , '-99999') = '15100005'
or exists (
select 1
from po_headers_all ph
where ph.segment1 = '15100005'
and ph.org_id = 85
and rti.po_header_id = ph.po_header_id ) )
and mtli.product_transaction_id = RTI.interface_transaction_id
33: mtl_transaction_lots_temp - SQL
select mtlt.*
from mtl_transaction_lots_temp mtlt ,
mtl_material_transactions_temp mmtt ,
po_headers_all ph
where mmtt.transaction_source_id = ph.po_header_id
and ph.segment1 = '15100005'
and mmtt.transaction_source_type_id = 1
and mmtt.transaction_temp_id = mtlt.transaction_temp_id
and ph.org_id = 85
34: rcv_lots_supply - SQL
select rls.*
from rcv_lots_supply rls ,
rcv_shipment_lines rsl ,
po_headers_all ph
where rsl.shipment_line_id = rls.shipment_line_id
and ph.segment1 = '15100005'
and rsl.po_header_id = ph.po_header_id
and ph.org_id = 85
35: rcv_lot_transactions - SQL
select rlt.*
from rcv_lot_transactions rlt ,
rcv_shipment_lines rsl ,
po_headers_all ph
where rsl.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and rsl.shipment_line_id = rlt.shipment_line_id
and ph.org_id = 85
36: rcv_lots_interface - SQL
select rli.*
from rcv_lots_interface rli ,
rcv_transactions_interface rti
where rti.interface_transaction_id = rli.interface_transaction_id
and (exists (
select 1
from po_headers_all ph
where rti.po_header_id = ph.po_header_id
and ph.segment1 = '15100005'
and ph.org_id = 85 )
or (nvl(rti.document_num , '-99999') = '15100005' ) )
37: Lot Control - SQL
select lookup_code , meaning , enabled_flag , start_date_active , end_date_active
from mfg_lookups
where lookup_type = 'MTL_LOT_CONTROL'
38: Lot Generation - SQL
select lookup_code , meaning , enabled_flag , start_date_active , end_date_active
from mfg_lookups
where lookup_type = 'MTL_LOT_GENERATION'
39: Lot Uniqueness - SQL
select lookup_code , meaning , enabled_flag , start_date_active , end_date_active
from mfg_lookups
where lookup_type = 'MTL_LOT_UNIQUENESS'