异常：当采购单价与接收、入库单价不一致时的解决办法 Po price is NOT equal to RCV price and MMT price

The issue occurs twice again, the problem should be a bug[  patch 5256307 ].

 

 

Solution:

-----------------------------------------------------------------------------

 

Step1>update po's unit price to the price that it has been used by receiving;

 

udate po_lines_all
set unit_price = 766.99
where po_line_id = 413817;


update po_line_locations_all
set price_override = 766.99
where po_line_id = 413817;

 

Step2>return po line;

 

Step3>update po's unit price to right price;

 

It has been corrected on Purchase Order Form or updated by sql;

 

update po_lines_all
set unit_price = 655.4615
where po_line_id = 413817;

update po_line_locations_all
set price_override = 655.4615
where po_line_id = 413817;

 

Step4>re-receive;

 

----------------------------------------------------------------------------

 

 

 

The following sql will be used to check this issue;

============================================================================

select ph.org_id,
       ph.segment1 po_number,
       pl.line_num,
       msi.segment1 item_number,
       rt.PRIMARY_QUANTITY quantity_received,
       pd.recovery_rate,
       pl.unit_price po_unit_price,
       rt.po_unit_price RCV_po_unit_price,
       ph.currency_code PO_currency_code,
       rt.currency_code RCV_currency_code,
       (select pla1.unit_price
          from po.po_lines_archive_all pla1
         where pl.po_line_id = pla1.po_line_id
           and pla1.latest_external_flag <> 'Y'
           and pla1.last_update_date in
               (select max(pla.last_update_date) last_update_date_max
                  from po.po_lines_archive_all pla
                 where pla1.po_line_id = pla.po_line_id
                   and pla.latest_external_flag <> 'Y')) PO_last_original_unit_price,
       (select pla1.unit_price
          from po.po_lines_archive_all pla1
         where pl.po_line_id = pla1.po_line_id
           and pla1.latest_external_flag = 'Y') po_now_unit_price,
       pv.vendor_name,
       pv.segment1 vendor_number
  from po.po_headers_all        ph,
       po.po_lines_all          pl,
       po.rcv_transactions      rt,
       po.po_line_locations_all pll,
       po.po_distributions_all  pd,
       inv.mtl_system_items_b   msi,
       po.po_vendors            pv
 where ph.org_id = 211
   and ph.po_header_id = pl.po_header_id
   and ph.po_header_id = rt.po_header_id
   and rt.po_line_id = pl.po_line_id
   and pl.po_line_id = pll.po_line_id
   and pll.line_location_id = pd.line_location_id
   and rt.source_document_code = 'PO'
   and rt.destination_type_code = 'RECEIVING'

   and rt.organization_id = msi.organization_id
   and pl.item_id = msi.inventory_item_id
   and pv.vendor_id = ph.vendor_id
   and rt.po_unit_price <> pl.unit_price
   and rt.transaction_date >= sysdate - 30
   and not exists
 (select 'T'
          from po.rcv_transactions rt1
         where rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code
           and rt.transaction_id = rt1.parent_transaction_id)
   and not exists
 (select 'T'
          from po.rcv_transactions rt1
         where rt1.transaction_id = rt.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)
 order by rt.transaction_date