處理時間：每月最後一週，即可開始檢查有沒有異常的庫存交易需要處理
建議參考文件：Oracle Resolving Period Close Inventory Pending Transaction

How to review pending transaction by inventory accounting period ?

Responsibility : Inventory super user
Navigator : Accounting Close Cycle > Inventory Accounting Periods

==> Choose the organization you want to view.

image

image 

You can use below sql to view each pending item ...
------------------------- Resolution Required -------------------------
Unprocessed Material

SELECT   mmtt.transaction_temp_id,mmtt.organization_id
       , mp.organization_code inv_org
       , TRUNC (mmtt.transaction_date) trx_date
       , msib.segment1 item
       , (SELECT license_plate_number
            FROM wms_license_plate_numbers
           WHERE lpn_id = mmtt.lpn_id) lpn
       , (SELECT license_plate_number
            FROM wms_license_plate_numbers
           WHERE lpn_id = mmtt.transfer_lpn_id) transfer_lpn
       , mmtt.primary_quantity
       , mmtt.subinventory_code
       , mmtt.transfer_subinventory
       , mmtt.error_explanation
       , mmtt.ERROR_CODE
       , mmtt.source_line_id
       , mmtt.rcv_transaction_id
       , mts.description source_type
       , mm.transaction_type_name
       , mmtt.process_flag
       ,mmtt.transaction_source_type_id
    FROM mtl_material_transactions_temp mmtt
       , mtl_parameters mp
       , mtl_system_items_b msib
       , mtl_txn_source_types mts
       , mtl_transaction_types mm
   WHERE mp.organization_id = mmtt.organization_id
     AND msib.inventory_item_id = mmtt.inventory_item_id
     AND msib.organization_id = mmtt.organization_id
     AND mts.transaction_source_type_id = mmtt.transaction_source_type_id
     AND mmtt.transaction_type_id = mm.transaction_type_id
     AND mp.attribute6 = 'Y'
     AND mp.organization_id IN (91,92, 93,94,101,102,325,374)
     and (mts.description ='Inventory'
     or mmtt.process_flag ='E' )
ORDER BY 3;

 

  • Uncosted Material (只需處裡cost_flag=E 的)
SELECT (SELECT organization_code
          FROM mtl_parameters
         WHERE organization_id = mmt.organization_id)
          org,
       mmt.organization_id,
       transaction_id,
       transaction_type_id,
       transaction_quantity,
       transaction_date,
       transaction_group_id,
       ERROR_CODE,
       error_explanation
  FROM mtl_material_transactions mmt
WHERE (costed_flag = 'N' OR costed_flag = 'E')
   AND organization_id IN (91, 92, 93, 94, 101, 102, 325, 374);


Pending WIP costing

目前無SQL
  • Uncosted WSM
目前無SQL
  • Pending WSM interface
目前無SQL

 

  • ------------------------- Resolution Recommendend -------------------------
    Pending Receiving

SELECT rti.to_organization_id,
       (SELECT segment1
          FROM mtl_system_items_b
         WHERE inventory_item_id = rti.item_id AND organization_id = 100)
          segment1,
       rti.packing_slip,
       rti.interface_transaction_id
  FROM rcv_transactions_interface rti
WHERE rti.to_organization_id IN (91, 92, 93, 94, 101, 102, 325, 374)
   AND destination_type_code = 'INVENTORY';
Note :  有資料不代表異常，有可能是User還沒有做完交易。

Pending Material SELECT mti.organization_id,
       mp.organization_code inv_org,
       TRUNC (mti.transaction_date) trx_date,
       mti.item_segment1 item,
       (SELECT license_plate_number
          FROM wms_license_plate_numbers
         WHERE lpn_id = mti.lpn_id)
          lpn,
       (SELECT license_plate_number
          FROM wms_license_plate_numbers
         WHERE lpn_id = mti.transfer_lpn_id)
          transfer_lpn,
       mti.primary_quantity,
       mti.subinventory_code,
       (SELECT    segment1
               || '-'
               || segment2
               || '-'
               || segment3
               || '-'
               || segment4
          FROM mtl_item_locations
         WHERE inventory_location_id = mti.locator_id)
          LOCATOR,
       mti.transfer_subinventory,
       (SELECT    segment1
               || '-'
               || segment2
               || '-'
               || segment3
               || '-'
               || segment4
          FROM mtl_item_locations
         WHERE inventory_location_id = mti.transfer_locator)
          transfer_locator,
       mti.error_explanation,
       mti.ERROR_CODE
  FROM mtl_transactions_interface mti, mtl_parameters mp
WHERE mti.process_flag = 3 AND mp.organization_id = mti.organization_id --  AND mp.attribute6 = 'Y'
       AND mp.organization_id IN (91, 92, 93, 94, 101, 102, 325, 374);

  • Pending Shop Floor Move 
    • 無

 

  • ------------------------- Unprocessed Shipping Transactions -------------------------
    Pending Transactions
    • SELECT   mp.organization_code inv_org
           , trunc(x.initial_pickup_date) shipped_date
           , x.NAME delivery_no
           , x.status_code
           , wl.meaning
           , 'Pending Trip-Stop' error_status
        FROM wsh_new_deliveries x
           , mtl_parameters mp
           , wsh_lookups wl
       WHERE mp.organization_id = x.organization_id
         AND mp.attribute6 = 'Y'
         AND wl.lookup_type = 'DELIVERY_STATUS'
         AND wl.lookup_code = x.status_code
         AND x.status_code = 'CL'
         AND EXISTS (
                SELECT wdl.delivery_id
                  FROM wsh_delivery_legs wdl
                     , wsh_trip_stops wts
                 WHERE wts.pending_interface_flag = 'Y'
                   AND wdl.pick_up_stop_id = wts.stop_id
                   AND x.delivery_id = wdl.delivery_id
                   AND wts.stop_location_id = x.initial_pickup_location_id)
         AND NVL (x.shipment_direction, 'O') IN ('O', 'IO')
    UNION ALL
    SELECT   mp.organization_code inv_org
           , trunc(x.initial_pickup_date) shipped_date
           , x.NAME delivery_no
           , x.status_code
           , wl.meaning
           , 'Not Closed' error_status
        FROM wsh_new_deliveries x
           , mtl_parameters mp
           , wsh_lookups wl
       WHERE mp.organization_id = x.organization_id
         AND mp.attribute6 = 'Y'
         AND wl.lookup_type = 'DELIVERY_STATUS'
         AND wl.lookup_code = x.status_code
         AND x.status_code = 'CO';
arrow
arrow
    全站熱搜

    Somebaby 發表在 痞客邦 留言(0) 人氣()