ITPub博客

首页 > 数据库 > Oracle > InterCompany中的Price list

InterCompany中的Price list

原创 Oracle 作者:longwansheng 时间:2011-11-25 20:08:18 0 删除 编辑
InterCompany中的Price list
http://luntan.erp100.com/forum.php?mod=viewthread&tid=104188&fromuid=42955[@more@]发表于 2011/10/17 23:57:55 |只看该作者 |倒序浏览
这几周由于工作的需要一直在研究Intercompany有关的流程,发现里面经常出现选错Price list而导致运行Create Intercompany AR Invoice出现“APP-INV-05706 returned from QP API calling static price Cannot Retrieve Transfer Price for the item Return Warning from inarcil Cannot create invoice line”错误。下面是参照metalink上的一些资料获得的一些心得。
如果在运行Create Intercompany AR Invoice出现错误,打开其的log文件,查找到自己的item,里面应该有很多资料供后面的查找问题。如transaction id, shipping org id, selling org id, item id之类的。
然后将以上资料放入下面的sql语句中,以查找当前transaction所用的price list。
1.Query1:
select inventory_item_id, transaction_uom from
mtl_material_transactions where transaction_id = <你的transaction id>;
以上输出如下
inventory_item_id transaction_uom
31461 EA

2. Query2:
select ra.customer_name, ra.customer_id,mip.ship_organization_id, mip.sell_organization_id
from mtl_intercompany_parameters mip, ra_customers ra, mtl_material_transactions mmt
where mip.customer_id = ra.customer_id
and mmt.inventory_item_id = <上面得item id>
and mmt.transaction_id = <你的transaction id>;
以上将输出如下信息
ra.customer_name
ra.customer_id
mip.ship_organization_id
mip.sell_organization_id

3.Query3:
SELECT nvl(RSU.price_list_id,-1), nvl(RC.price_list_id, -1)
FROM mtl_intercompany_parameters MIP, ra_site_uses_all RSU, ra_customers RC
WHERE MIP.sell_organization_id = &org_id
AND MIP.ship_organization_id = &org_id
AND RSU.site_use_id = MIP.customer_site_id
AND RSU.org_id = MIP.ship_organization_id
AND RC.customer_id = MIP.customer_id;

4.Query4:
SELECT nvl(RSU.price_list_id, nvl(RC.price_list_id, -1))
FROM mtl_intercompany_parameters MIP, ra_site_uses_all RSU, ra_customers RC
WHERE MIP.sell_organization_id = &org_id
AND MIP.ship_organization_id = &org_id
AND RSU.site_use_id = MIP.customer_site_id
AND RSU.org_id = MIP.ship_organization_id
AND RC.customer_id = MIP.customer_id;

5.Query5:
select inventory_item_id, transaction_uom from
mtl_material_transactions where transaction_id = &trx_id;

6.Query6:
select /*+ ORDERED USE_NL(spll spl) index(qpa qp_pricing_attributes_n5) */
to_char(SPLL.operand) ,SPL.currency_code
from qp_pricing_attributes qpa,
qp_list_lines spll ,
qp_list_headers_b spl
where qpa.list_header_id=&pricelistidfromabove
and qpa.product_attribute_context= 'ITEM'
and qpa.product_attribute='PRICING_ATTRIBUTE1'
and qpa.product_attr_value= to_char(&itemId)
and qpa.product_uom_code='&primaryUOMcode'
and qpa.qualification_ind = 4
and qpa.excluder_flag = 'N'
and qpa.pricing_phase_id=1
and spll.list_line_id=qpa.list_line_id
and spl.list_header_id = qpa.list_header_id
and sysdate between nvl(SPLL.start_date_active,(sysdate -1))
and nvl(SPLL.end_date_active+0.99999,(sysdate +1))
and rownum =1;

7.Query7:
select concatenated_segments from mtl_system_items_kfv
where organization_id = &org_id and inventory_item_id=&itemId;

8.Query8:
select name from qp_list_headers_tl where list_header_id = &header_id;

记下Price list,然后到Order Management->Customer--> Trading Community->Trading Community->Customers->Standard,使用shipping OU的地址(注意不是selling OU),查看bill to site中的price list是否为上面查询出的结果。

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

全部评论

注册时间:2007-12-12

  • 博文量
    162
  • 访问量
    742185