InterCompany中的Price list

      这几周由于工作的需要一直在研究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是否为上面查询出的结果。