ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle ebs table

oracle ebs table

原创 Linux操作系统 作者:lmainxie 时间:2009-01-05 15:02:01 0 删除 编辑

--INV
10100
select
select * from mtl_transaction_accounts
SELECT mmt.costed_flag,mmt.cost_update_id,mmt.transaction_date,mmt.organization_id,
mmt.last_update_date,mmt.error_code,mmt.transaction_quantity,mmt.transaction_cost,mmt.actual_cost,mmt.transaction_type_id
  FROM MTL_MATERIAL_TRANSACTIONS MMT where Transaction_id=22040
  select * from mtl_transaction_types where transaction_type_id=24
  select mmt.organization_id from Mtl_Material_Transactions mmt where mmt.creation_date>sysdate-1
  select * from mtl_transaction_accounts
  select * from MTL_MATERIAL_TRANSACTIONS
 
  SELECT * FROM GL_PERIOD_STATUSES_V  WHERE APPLICATION_ID=101 AND SET_OF_BOOKS_ID=1005
  SELECT * FROM GL_SETS_OF_BOOKS
update MTL_MATERIAL_TRANSACTIONS MMT set costed_flag='N',error_code=null where (mmt.costed_flag='E')
update MTL_MATERIAL_TRANSACTIONS MMT set costed_flag='N',
error_code=null where ( mmt.costed_flag is null)
select * from gl_interface where user_je_category_name='Payments'
select * from MTL_MATERIAL_TRANSACTIONS
select * from mtl_transaction_accounts
select mmtp.transa from mtl_material_transactions_temp mmtp
SELECT cid.line_type_name line_type,
       CASE
         WHEN cid.base_transaction_value > 0 THEN
          cid.base_transaction_value
       END cr,
       CASE
         WHEN cid.base_transaction_value < 0 THEN
          -cid.base_transaction_value
       END dr,
       --cid.primary_quantity,
       --cid.primary_uom,
       --cid.basis_type_name,
       cid.rate_or_amount rate
  FROM cst_inv_distribution_v cid
WHERE organization_id = 104
   AND transaction_id = 4855
   select * FROM FND_LOOKUP_TYPES_VL
   select * from cst_inv_distribution_v
   select * from MTL_TRANSACTION_ACCOUNTS
--GL
select * from gl_interface where reference30='CASH CLEARING' reference25='13-NOV-20083'
select * from gl_interface_control
select * from gl_daily_conversion_types
select * from AP_CHECKS_V


SELECT mtt.costed_flag FROM mtl_material_transactions mtt
select * from mtl_system_items where segment1='971901000002'
--PO
select * from PO_POSITION_CONTROLS FOR UPDATE
SELECT hpf.organization_id FROM HR_POSITIONS_F hpf for update
select * from mtl_parameters
select * from HR_ORGANIZATION_UNITS
select * from po_releases_all
update po_releases_all pra set pra.authorization_status='APPROVED'
select * from rcv_transactions_interface
select transferred_to_oe_flag,p.* from po_requisition_headers_all p for update--oe
1000004
select * from po_requisition_headers_all where segment1='1000032' for update
select * from po_requisition_lines_all prla where prla.requisition_header_id=183 for update
select * from po_
select * from po_agents
select * from po_acceptances
select * from rcv_parameters
select * from po_requisition_suppliers
select * from po_line_locations_all
select * from po_reqexpress_headers_all
select * from po_reqexpress_lines_all
select * from PO_HEADERS_RFQQT_v
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE LINE_LOCATION_ID=2 FOR UPDATE
SELECT pha.type_lookup_code FROM PO_HEADERS_ALL pha
select * from rcv_shipment_headers、
select * from Po_Distributions_All
select * from rcv_shipment_lines for update
select * from rcv_transactions_interface for update
select * from rcv_transactions
UPDATE po_headers_all pha SET pha.approved_flag='Y',pha.approved_date=sysdate 
WHERE PHA.PO_HEADER_ID=142
select * from PO_VENDOR_LIST_HEADERS--Supply list
select * from PO_ASL_SUPPLIERS_V  --Approved Supplier list

select * from po_headers_all where segment1 in ('1000001','1000014')
 and type_lookup_code='STANDARD'
select * from MTL_CROSS_REFERENCE_TYPES
select * from mtl_cross_references
select * from po_lookup_codes where lookup_code='Price Type'
UPDATE po_line_locations_all  PLLA set plla.approved_flag='Y',plla.approved_date=sysdate
  WHERE PLLA.Po_Header_Id=281
  select pha.authorization_status,pha.approved_flag,pha.approved_date from po_headers_all pha where segment1='1000032' for update
 select * from xla_po_ael_sl_v
 select * from po_line_locations_all PLLA WHERE PLLA.Po_Header_Id=142
 update po_line_locations_all plla set plla.quantity_received=8,
 plla.quantity_accepted=8  WHERE PLLA.Po_Header_Id=142
 select * from po_headers_all pha  where segment1 in ('1000029','1000025')
 select * from po_line_locations_all PLLA WHERE PLLA.Po_Header_Id=281
 update po_headers_all pha set pha.approved_flag='Y',Pha.Approved_Date=sysdate,pha.authorization_status='APPROVED'
 where segment1 in ('1000029')
 SELECT DISTINCT LOOKUP_TYPE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='TAX_TYPE'
 SELECT * FROM FND_LOOKUP_TYPES WHERE LOOKUP_TYPE='TAX_TYPE'
 SELECT * FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE LIKE '%TAX TYPE%'
 SELECT * FROM FND_LOOKUP_VALUES WHERE LOOKUP_CODE='SALES'
UPDATE
---AP
SELECT * FROM AP_ACCOUNTING_EVENTS_ALL
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL
SELECT * FROM AP_AE_HEADERS_ALL--Transfer to Gl headers
select * from ap_ae_lines_all--Gl Account for ap table
SELECT * FROM AP_INVOICES_ALL--INVOICE
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL--DISTRIBUTION
SELECT * FROM ap_checks_all--payment
select * from ap_invoices_interface
select * from ap_invoice_lines_interface
select * from AP_INVOICE_PAYMENTS_ALL
select * from AP_PAYMENT_HISTORY_ALL
select * from ap_payme
--Standard Invoice
--Expense Report--Need run Report
--Recurring Invoice
--WIP
select * from wip_transaction_accounts
select * from wip_move_transactions where transaction_id=1034
SELECT * FROM WIP_ENTITIES
SELECT * FROM WIP_DISCRETE_JOBS
SELECT * FROM WIP_MOVE_TRANSACTION
select * FROM WIP_COST_TXN_INTERFACE
select * from WIP_TRANSACTIONS
select * from WIP_COST_TXN_INTERFACE
select * from wip_accounting_classes
select * from wip_transaction_accounts where transaction_id=2003

--OM
select otta.org_id,otta.* from oe_transaction_types_all otta for update
SELECT * FROM Mtl_Transaction_Types mtt1
select * from oe_order_headers_all where order_number=46
select NON_DELIVERY_INVOICE_SOURCE from oe_transaction_types_all
select * from mtl_parameters
select * from org_organization_definitions
select * from HR_ORGANIZATION_UNITS
select * from MTL_RESERVATIONS
select * from WSH_PICKING_BATCHES_V where order_number=46--OE release
select * from WSH_DELIVERABLES_V  for update
select * from wsh_delivery_details where delivery_detail_id=14009
select * from wsh_delivery_assignments where delivery_detail_id=14009
select * from OE_PAYMENTS
select * from  WSH_DLVB_DLVY_V where delivery_id=10010
select * from wsh_exceptions_v--??
SELECT OE_INTERFACED_FLAG,INV_INTERFACED_FLAG from wsh_delivery_details--??
select * from wsh_new_deliveries wnd where wnd.creation_date>sysdate-1
select * from wsh_trip_stops where creation_date>sysdate-1
select * from hr_locations_all
select * from wsh_trips
select * from oe_headers_iface_all ohia for update
select * from MTL_ONHAND_QUANTITIES
SELECT * FROM OE_SALES_CREDITS_V--SALES CREDITS

Order Status:
Entered
Booked
wsh_delivery_details
2008-9-16 9:15:36
2008-9-16 9:27:14
2008-9-16 12:41:00
2008-9-5 13:50:35
2008-9-12 16:37:35
select * from oe_headers_iface_all
select * from FND_TABLES where table_name='OE_ORDER_HEADERS_ALL'
SELECT * FROM ALL_TABLES where WNER='ONT'and table_name like '%INTERFACE%'
'OE_ORDER_HEADERS_ALL'
Canceld
Order-level sales credit total () must equal 100%.
Salesperson is required on a booked order.
Payment Term is required on a booked order.

OE_ORDER_HEADERS_ALL1311

102/
141
122
--
AP-transfer to GL
Payable transfer to gl

--apps
select * from all_tables where wner='AP'


--bom

--AR
select * from RA_GROUP_BYS --Auto Invoice Group Rule
select * from RA_CUSTOMER_TRX_ALL
    CUSTOMER_TRX_ID 发票编号
    BILL_TO_SITE_USE_ID 客户收单方编号
    PRIMARY_SALES_ID销售员
REFERENCE是Oracle提供的外部编号输入框,但是由于版本问题和长度(<=30),不建议用户使用,如果要使用外部编号,请使用说明性弹性域

select * from RA_CUSTOMER_TRX_LINES_ALL
    LINE_ID            行号
    INVENTORY_ITEM_ID 可以为空,比如非物料的服务,只在DESCRIPTION中出现 /税行
    DESCRIPTION     
    QUANTITY_INVOICE   开票数量
    LINE_TYPE          行类型 (一般/税)
    EXTEND_PRICE       本行金额
注意:税行是隐藏行,所以至少会有两行

收款情况
select * from AR_CASH_RECEIPTS_ALL(还包含了非收款信息)
    CASH_RECEIPT_ID 内部code
    RECEIPT_NUMBER   收款号
    RECEIPT_DATE     收款日期
    AMOUNT           总额
    RECEIPT_TYPE     现金/杂项 Cash/Misc
    FUNCTIONAL_AMOUNT 本位币计量金额
   UI上为RECEIPTS
   核销关系不是一一对应,也不是一次核销100%,UI上右下方的Application 按钮
   AR_RECEIVABLE_APPLICATIONS_ALL
    APPLIED_CUSTOMER_TRX_ID   发票编号
    APPLIED_CUSTOMER_TRX_LINE_ID   发票行编号

    STATUS APP表示核销 /UNAPP表示未核销
        AMOUNT_APPLIED                 匹配金额
    注意:红冲收款报表时间跨月的问题;必须联查 AR_CASH_RECEIPTS_ALL和   AR_CASH_RECEIPT_HISTORY_ALL
--cost
select * from CST_COST_TYPES
 select * from mtl_system_items_b where segment1='890709000001'
 select * from cst_item_cost_details where inventory_item_id=3001
 select * from cst_item_costs where inventory_item_id=3001
 select * from cst_item_costs where inventory_item_id=3001 and cost_type_id=1 for update
 select * from mtl_material_transactions
 SELECT * FROM mtl_parameters
select * from FND_CONC_REQ_SUMMARY_V  vnm where vnm.USER_CONCURRENT_PROGRAM_NAME='Pick Selection List Generation'
select * from fnd_tables where wner='OE'
SELECT * FROM ALL_TABLES where wner='OE' AND TABLE_NAME LIKE '%INTERFACE%'
SELECT * FROM SO_LINE_DETAILS_INTERFACE
select * from hz_locations
SELECT * FROM OE_HEADERS_
select * from all_tables where  wner='ONT' ORDER BY TABLE_NAME
 TABLE_NAME ='OE_ORDER_HEADERS_ALL' wner='ONT' ORDER BY TABLE_NAME
select * from fnd_tables where table_name='WSH_TRIPS'
select * from fnd_columns where table_id=71273
SELECT * FROM WSH_DELIVERIES_INTERFACE
 wsh_trips
 select 5000/12 from dual
 select * from mtl_onhand_quantities moq where moq.SUBINVENTORY_CODE='GPI_Stage'

select mmt.transaction_type_id,mmt.transaction_quantity
 from mtl_material_transactions mmt where mmt.subinventory_code='G'
 
 161, 141
 select * from mtl_parameters where organization_id=141
 select mmt.transaction_type_id,mmt.transaction_quantity,mmt.creation_date,mmt.subinventory_code
 from mtl_material_transactions mmt where mmt.subinventory_code='GPI_Stage'
 select segment1 from mtl_system_items where inventory_item_id=4003
 select * from mtl_transaction_types where transaction_type_id=52
 select * from gl_interface
 select * from gl_balances
 
 select * from GL_JE_HEADERS


INV库存
organization 两个含义:
1. 经营单位,A/B/C分公司,A下面有A1,A2等工厂,主题目标是为了独立核算此组织
ORG,ORG_ID;
2. 库存组织,例如制造商的仓库,例如A1,A2等工厂
Organization_id;

HR_ORGANIZATION_UNITS -
Org_organization_definitions
Mtl_subinventory_ 库存组织单位
MTL_PARAMETERS -库存组织参数(没有用ID,直接用name)
MTL_SYSTEM_ITEMS_b -物料信息(同上,应用了库存组织name)
MTL_SECONDARY_INVENTORIES -子库存组织 -
MTL_ITEM_LOCATTIONS -货位 - SUBINVENTROY_CODE

Mtl_Material_Transactions - (库存)物料事物表
成本 mtl_transaction_accounts
transaction_cost是事物成本;
ACTUAL_COST是通过成本算法计算出来的实际成本,主计量单位

现有量
汇总历史记录(正负合计)
Mtl_Material_Transactions
MTL_ONHAND_QUANTITIES现有量表,组织/子库存/货位/物品 summary可能按照挑库先进先出统计,如果设置了"不允许负库存",这样就不可能出现负数

PO
请购单头表
Po_Requisition_Headers_all
行表
Po_Requisition_lines_all
采购订单
PO_HEADER_ALL
PO_LINES_ALL
采购接收-退货/组织间转移/正常状态 都需要使用这个模块
RCV_TRANSACTIONS
1. 接收100单位货物,放入“待质检”货位
2. 接受/拒绝
3. 库存/退回
有三个不同的状态!例如:接收100个,80个接受入库,20个退回,那么有80个接受事务/20个退回事物
select TRANSACTION_TYPE,DESTINATION_TYPE_CODE from RCV_TRANSACTIONS
可以看出以下阶段:
A1.RECEIVE – RECEIVING
A2.ACCEPT – RECEIVING
A3.DELIERY – INVETORY(影响库存现有量)
A4.Return--inventory
po_headers_all和po_lines_all中,只是形態為type_lookup_code='QUOTATION'!!!!
select * from po_headers_all where type_lookup_code='QUOTATION'
如果按照正常模式,最后会触发产生MTL_MATERIAL_TRANSACTIONS

销售订单
OE_ORDER_headers_all
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID 就是客户层
SHIP_FROM_ORG_ID
SHIP_TO_ORG_ID 就是客户收货层
INVOICE_TO_ORG_ID 就是客户收单层
DELIVER_TO_ORG_ID
和客户结构有关
客户 RA_customers
客户Address Ra_Addresses
Address 货品抵达 site RA_SITE_USES_ALL
Address 发票抵达 site
OE_ORDER_LINEs_all
-Journal
select * from gl_budget_versions
select * from gl_je_categories_tl
select * from gl_je_sources
Journal Import
select * from gl_import_references
select * from gl_periods
GL凭证
select * from gl_je_batches
凭证日期:                DEFAULT_EFFECTIVE_DATE
会计期间:                DEFAULT_PERIOD_NAME
原币种凭证批借贷方汇总: RUNNING_TOTAL_DR/CR 比如美元
本位币凭证批借贷方汇总: RUNNING_TOTAL_ACCOUNTED_DR/CR

gl_je_headers日记账头信息
批号:     JE_BATCH_ID
会计期间: PERIOD_NAME
币种:     CURRENCY_CODE
汇率类型: CURRENCY_CONVERSION_TYPE
汇率日期: CURRENCY_CONVERSION_DATE
帐套:     SET_OF_BOOKS_ID 参考 GL_SETS_OF_BOOKS
凭证类型: JE_CATEGORY 参考 GL_JE_SOURCES
凭证来源: JE_SOURCE
Other - Recurring Formula Listing--Check Recurring
gl_je_lines日记账体信息
CODE_COMBINATION_ID 科目组合编号

GL_BALANCES 总帐余额
PERIOD_NET_DR/CR 净值
BEGIN_BALANCE_DR/CR 期初额
select * from GL_BALANCES where code_combination_id=1009
select * from gl_code_combinations  where code_combination_id=1009
select 5667.76-2365.6 from dual
AR应收发票
RA_CUSTOMER_TRX_ALL
    CUSTOMER_TRX_ID 发票编号
    BILL_TO_SITE_USE_ID 客户收单方编号
    PRIMARY_SALES_ID销售员
REFERENCE是Oracle提供的外部编号输入框,但是由于版本问题和长度(<=30),不建议用户使用,如果要使用外部编号,请使用说明性弹性域

select * from RA_CUSTOMER_TRX_LINES_ALL
    LINE_ID            行号
    INVENTORY_ITEM_ID 可以为空,比如非物料的服务,只在DESCRIPTION中出现 /税行
    DESCRIPTION     
    QUANTITY_INVOICE   开票数量
    LINE_TYPE          行类型 (一般/税)
    EXTEND_PRICE       本行金额
注意:税行是隐藏行,所以至少会有两行

收款情况
AR_CASH_RECEIPTS_ALL(还包含了非收款信息)
    CASH_RECEIPT_ID 内部code
    RECEIPT_NUMBER   收款号
    RECEIPT_DATE     收款日期
    AMOUNT           总额
    RECEIPT_TYPE     现金/杂项 Cash/Misc
    FUNCTIONAL_AMOUNT 本位币计量金额
   UI上为RECEIPTS
   核销关系不是一一对应,也不是一次核销100%,UI上右下方的Application 按钮
   AR_RECEIVABLE_APPLICATIONS_ALL
    APPLIED_CUSTOMER_TRX_ID   发票编号
    APPLIED_CUSTOMER_TRX_LINE_ID   发票行编号

    STATUS APP表示核销 /UNAPP表示未核销
        AMOUNT_APPLIED                 匹配金额
    注意:红冲收款报表时间跨月的问题;必须联查 AR_CASH_RECEIPTS_ALL和   AR_CASH_RECEIPT_HISTORY_ALL

AP
select * from AP_AE_LINES_ALL t

select * from AP_INVOICE_DISTRIBUTIONS_all t

select * from AP_INVOICE_PAYMENTS_ALL t
应付帐款(是我方人员按照供应商提供的纸张发票信息录入)UI 上的invoice
AP_INVOICES_ALL

实际付款PAYMENT
AP_CHECKS_ALL

核销关系 同AR,右下方的Payment 按钮
AP_INVOICE_PAYMENTS_ALL客户余额表,情况比较复杂:比如两个用户合并,应收应付差额,预付款

资产信息FA_ADDITIONS
名称
编号
分类
数量

资产类别
FA_CATEGORIES

资产帐簿
FA_BOOK_CONTROLS 和会计帐簿有什么关系?
FA_BOOKS
UI中的Inquiry
Mothed是折旧方法(直线法/产量法)

FA_DISTRIBUTION_HISTORY分配assignment,给什么部门使用多少
LOCATION_ID 部门 联查FA_LOCATIONS

折旧信息(分摊方法)
FA_DEPRN_DETAIL
period_counter 折旧期间编号

折旧事务(新增、重建、转移、报废)
FA_TRANSACTION_HEADERS

select fnd_profile.VALUE('ORG_ID') FROM DUAL
select * from hr_operating_units hou where hou.organization_id=204
--fnd
select * from fnd_application   
select * from fnd_application_tl where application_id=101
select * from fnd_application_vl where application_id = 101
----值集
select * from fnd_flex_value_sets   
select * from fnd_flex_values       
select * from fnd_flex_values_vl
----弹性域
select * from fnd_id_flexs         
select * from fnd_id_flex_structures where id_flex_code='GL#'
select * from fnd_id_flex_segments where id_flex_code='GL#' and id_flex_num=50671

select * from fnd_profile_options_vl
select * from fnd_concurrent_programs    程序表
select * from fnd_concurrent_requests    请求表
select * from fnd_concurrent_processes   进程表

--inv
select * from org_organization_definitions   库存组织
select * from mtl_parameters                 组织参数
select * from mtl_system_items_b where inventory_item_id = 171 and organization_id=204 物料表
select * from mtl_secondary_inventories    子库存
select * from mtl_item_locations           货位
select * from mtl_lot_numbers              批次
select * from mtl_onhand_quantities        现有量表
select * from mtl_serial_numbers           序列
select * from mtl_material_transactions    物料事务记录
select * from mtl_transaction_accounts     会计分录
select * from mtl_transaction_types        事务类型
select * from mtl_txn_source_types         事务来源类型
select * from mfg_lookups ml where ml.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'

--po
select * from po_requisition_headers_all   请求头
select * from po_requisition_lines_all     请求行
select * from po_headers_all               订单头
select * from po_lines_all                 订单行
select * from po_line_locations_all       
select * from po_distributions_all          分配
select * from po_releases_all               发送
select * from rcv_shipment_headers          采购接收头
select * from rcv_shipment_lines            采购接收行
select * from rcv_transactions              接收事务处理
select * from po_agents                    
select * from po_vendors                    订单
select * from po_vendor_sites_all
select * from po_headers_interface          Interface-run Import po
select * from po_lines_interface
select * from po_og
Invoice Price Variance Report      ---POXRCIPV
--oe
Credit Exposure Report
select * from ra_customers                  客户
select * from ra_addresses_all              地址
select * from ra_site_uses_all              用户

select * from oe_order_headers_all          销售头
select * from oe_order_lines_all            销售行


select * from mtl_item_re                   pick release/扣库存
select * from wsh_new_deliveries            发送
select * from wsh_delivery_details
select * from wsh_delivery_assignments
select 5000/12 from dual
--gl
select * from gl_sets_of_books     总帐
select * from gl_code_combinations gcc where gcc.summary_flag='Y'   科目组合
select * from gl_balances          科目余额
select * from gl_je_batches        凭证批
select * from gl_je_headers        凭证头
select * from gl_je_lines          凭证行
select * from gl_je_categories     凭证分类
select * from gl_je_sources        凭证来源
select * from gl_summary_templates 科目汇总模板
select * from gl_account_hierarchies 科目汇总模板层次
SELECT * FROM gl_interface gi
SELECT * FROM CST_INV_DISTRIBUTION_V
--ar
deposit -保证金
Guarantee-抵押品
select * from XLA_AEL_SL_V
select * from ar_batches_all                 事务处理批
select * from ra_customer_trx_all   where TRX_NUMBER='10100'          发票头 10100/1300
select * from ra_customer_trx_lines_all  where customer_trx_id=1500    发票行 1300 1301
select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id=1500   发票分配
select * from ar_cash_receipts_all 1100           收款
select * from ar_receivable_applications_all  where cash_receipt_id=1100 核销--applied_customer_trx_id=1300--Can show the Ar Invoice
select * from ar_receivable_applications_all where applied_customer_trx_id=1500
select * from ar_payment_schedules_all       付款计划
select * from ar_adjustments_all              发票调整
select * from ar_distributions_all   where source_id=1100        会计分录

SELECT hca.account_number customer_number,
       SUBSTR (hp.party_name,
               1,
               DECODE (INSTR (hp.party_name, '    '),
                       0, 50,
                       INSTR (hp.party_name, '    ')
                      )
              ) customer_name,
       rec.receipt_number, rec.amount receipt_amount, rec.receipt_date,
       rec.currency_code, rec.status, rt.trx_number trx_number,
       DECODE (interface_header_context,
               'ORDER ENTRY', rt.purchase_order,
               ''
              ) cust_po_number,
       rt.interface_header_attribute1 sales_order, app.apply_date,
       app.amount_applied applied_amount, bank.bank_account_name
  FROM ar.ar_cash_receipts_all rec,
       ar.ar_payment_schedules_all ps,
       ar.ar_receivable_applications_all app,
       ar.ra_customer_trx_all rt,
       ar.hz_cust_accounts hca,
       ar.hz_parties hp,
       ap.ap_bank_accounts_all bank
WHERE rec.cash_receipt_id = ps.cash_receipt_id
   AND ps.payment_schedule_id = app.payment_schedule_id(+)
   AND app.display(+) = 'Y'
   AND app.applied_customer_trx_id = rt.customer_trx_id(+)
   AND hp.party_id = hca.party_id
   AND hca.cust_account_id = ps.customer_id
   AND rec.status <> 'REV'
  -- AND rec.set_of_books_id = &sob
  -- AND rec.org_id = &org_id
   AND bank.bank_account_id = rec.remittance_bank_account_id
   AND hp.party_name LIKE '%&Customer_Name%'
   AND TO_CHAR (rec.receipt_date, 'YYYY-MM-DD') BETWEEN '&start_date'
                                                    AND '&end_date'

--ap
select * from ap_invoices_all               发票头
select * from ap_invoice_distributions_all 发票行
select * from ap_payment_schedules_all      付款计划
select * from ap_check_stocks_all           单据
select * from ap_checks_all                 付款
select * from ap_bank_branches              银行
select * from ap_bank_accounts_all          银行帐号
select * from ap_invoice_payments_all       核销
 


  

     

?2008 Baidu
Autoinvoice Import Program

select * from WIP_COST_TXN_INTERFACE_V
select *  from WIP_COST_TXN_INTERFACE

--反冲Journal
Select reversed_je_header_id From gl_je_headers h Where h.reversed_je_header_id
Is Not Null and ACTUAL_FLAG = 'A'


select * from RA_LINE_ORDER_bys
select * from SALES_ORDER_LINE
select * from mtl_system_items_b
select * from mtl_system_items_interface for update
select purchasing_org_id from po_ga_org_assignments
po_ga_org_assignments.purchasing_org_id


-------------- Can not find serial number--Group_mark_id


LOV 对应的 SQL 如下:
select serial_number
from mtl_serial_numbers
where inventory_item_id = :mtl_serial_reference.inventory_item_id
and current_organization_id = :mtl_serial_reference.org_id
and (group_mark_id is null or group_mark_id = -1)
and ((nvl(current_subinventory_code, '@@@') =
nvl(:mtl_serial_reference.subinventory_code, '@@@') and
nvl(current_locator_id, -1) =
nvl(:mtl_serial_reference.locator_id, -1) and
nvl(lot_number, '@@@') =
nvl(:mtl_

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10434717/viewspace-526752/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-29

  • 博文量
    23
  • 访问量
    49457