采购，接收数据收集SQL汇总（从订单->接收->INVOICE所有数据关联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'