采办，接收数据收集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 = 854: 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 = 857: ap_invoice_distributions_all - SQLselect 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 = 858: 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 = 859: 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 = 8510: 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 = 8511: 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 = 8520: mtl_material_transactions - SQLselect 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 = 8521: mtl_transaction_types - SQLselect 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 = 8523: 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 = 8524: 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 = 8525: 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 = 8526: rcv_parameters - SQLselect 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 = 8527: 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 = 8528: 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 = 8529: 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 = 8531: 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 = 8532: mtl_transaction_lots_interface - SQLselect 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 = 8534: 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 = 8535: 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 = 8536: 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'