Search This Blog

Friday, July 25, 2014

Understanding Cycle count and best practices

Best practices:
•    Oracle provides ABC analysis and “minimum per year” cycle counts based on the ABC value
•    The key here is “minimum per year” counts. The algorithm may cause an  item to be counted more than the number of counts required per year
•    Because the cycle count process is random, you may NOT get an evenly spaced occurrence of counts
•    You have no access to see when an item will be counted during the year
•    If a 4 count a year item is added during the middle of the year, it will still get counted at least 4 times that year
•    Counts are not always evenly spaced during the year
•    You can’t ever know when an item is to be counted
•    Oracle developed the Cycle Count process to pass compliance and adhere to best practices
•    Random  means Random. It doesn’t mean evenly divided. It doesn’t even mean logical.
•    You can defeat the “randomness” by using manual Cycle Counts for every item but then you are missing the point of Cycle Counting
•    Cycle counting is a measure of your inventory accuracy
•    If you manipulate the process and remove the randomness, you will manipulate the accuracy
•    Make sure you have Save output selected in the Request Set when you execute Perform full cycle count request set in the 3 requests in the set
•    Make sure you print to a valid printer
•    You may not want to display system quantity, it could influence a count entry

Some Insights on Cycle Count Formula:
The Oracle site, and some others have the actual formulas used, but then on top of that, there is some rounding formula used that does not seem to be published.
Logic for Cycle Count Auto Schedule (As given in metalink)
The first criteria of selecting the items to schedule, is whether these items are marked as a control group items or not and what is the criterion on which the Cycle count scheduler is being run (Include Control Group Items is checked or not). Based on this the scheduler will select the Control Group Items or Non Control Group Items to schedule counts.
The second criteria is Max items to Schedule, which is defined as:
MaxItemsToSchedule = (TotalItemsInClass * NWorkingDays) /ClassCountInterval + 1;
TotalItemsInClass = the number of items defined in the class
NWorkingDays = BeginDate - EndDate + 1 (this will be printed in the concurrent log file of the scheduler).
ClassCountInterval = NumWorkDaysThisYear / NumCountsPerYear (these will also be printed in the concurrent log file of the scheduler).
Once MaxItemsToSchedule is calculated, these number of items are picked randomly and will get scheduled and will be marked, so that these not picked in the scheduled run.

Now lets consider the following possible cases :
No. of items in class = 100
Counts per year for class = 1
No. of working days in the year = 200 (taken, for easy calculation)
Count Frequency = Daily

Now since the count frequency is 'Daily' you can run the scheduler on every working day.Using the above formula, Maximum Items to schedule = ((100*1*1)/200)+1 = 0+1 = 1 The above formula has integer division and so instead of 0.5+1 we have 0+1.
So, in this case all the 100 items in the class will be counted twice in a year. But, now if you change the count frequency from 'Daily' to 'Weekly' as shown in Case 2.

No. of items in class = 100
Counts per year for class = 1
No. of working days in the year = 200 (taken, for easy calculation)
Count Frequency = Weekly (say 5 work days in a week)
Maximum Items to schedule = ((100*1*5)/200)+1 = 3

Here, all the 100 items will be counted at least once, but some of the items may be counted twice since in our formula we are doing the ROUNDING off.In this case 80 items were counted only once and 20 items got counted twice.

If we do not do the rounding off (using integer division and adding 1), then in the above case we will have 2.5 items to schedule. Now, we can schedule either 2 or 3 items.So, if we schedule 2 items then all the 100 items will not be scheduled at least once (20 items will be left out). So we do the rounding off so that all the items are scheduled at least once.

You can see that by changing the count frequency from 'Daily' to 'Weekly' the number of items that are getting counted twice have been reduced from 100 as in Case 1, to 20 in Case 2. Since we are doing 'Rounding off' in the formula, some items of the class may be counted more than the number of counts specified.But, as I have mentioned before, Counts per year is the minimum guaranteed number of counts per year for all items in the class.

In cases where you have very few items and count frequency is 'Daily', the magnitude of the difference between 'actual number of counts' and 'counts per year specified' will be much higher. In these cases, by changing the count frequency from 'Daily' to 'weekly/period', will reduce the magnitude of difference. Also, we allow the user to change the 'Counts per year' at any point in time in the year. So, once this value is changed then all the items in the class are scheduled afresh,
irrespective of their scheduling before the change of value for counts per year.

The logic for Cycle counting is forward looking.
1. The counts per year for a class ensures that all items in that class are scheduled at least that many items in a year. That is, if the counts per year for a class is 10 and the class has 100 items, then each of the 100 items in the class are scheduled at least 10 times.
2. In cases where you have very few items in the class and have high count frequency (say Daily), there are chances that it will be counted more number of times, then specified in its class. However, in those cases you would ideally like to reduce the count frequency (say, from 'daily' to 'weekly').
3. The counts per year for a class can be changed by the user at a later point in time. Cycle counting does not take into account the number of counts that have already been done before the change of value for 'counts per year'. It now takes into account the new value of 'counts per year' for all the items in the class

Monday, April 28, 2014

Query to List Active Serial Number for an Items

Below query can be used to display active serial number for Item, Organization combination

SELECT msi.segment1 item_number
     , msn.serial_number
     , msn.current_status_name
     , msn.status_code
     , msn.current_subinventory_code
     , ml.concatenated_segments
FROM   mtl_serial_numbers_all_v msn
     , mtl_system_items_b msi
     , mtl_item_locations_kfv ml
     , mtl_parameters mp
WHERE  msi.inventory_item_id = msn.inventory_item_id
AND    mp.organization_code = msn.organization_code
AND    ml.inventory_location_id = msn.current_locator_id
AND    msn.current_status = 3
AND    mp.organization_id = msi.organization_id
AND    mp.organization_code =  :org_code
AND    msi.segment1 = :item

Tuesday, January 28, 2014

Query to display BOL, MBOL, Trip details for a Sales Order

SELECT   ooh.order_number
       , wnd.NAME delivery_name
       , wt.NAME trip_name
       , ool.line_number
       , ool.ordered_item
       , ool.flow_status_code
       , DECODE (wdd.released_status
               , 'R', 'Ready For Release'
               , 'B', 'Back Ordered'
               , 'S', 'Released To Warehouse'
               , 'D', 'Cancelled'
               , 'N', 'Not Ready For Release'
               , 'Y', 'Staged or Pick Confirmed'
               , 'C', 'Interfaced/Shipped'
               , 'I', 'Interfaced/Shipped'
               , 'O', 'Not Shipped'
                ) delivery_status
       , rct.trx_number invoice_number
       , wdd.released_status
       , ood.organization_name || ' (' || ood.organization_code || ')' ship_from_org
       , hp_carrier.party_name carrier_name
       , wdi.sequence_number bol_number
       , wds.departure_net_weight ship_weight
       , wds.actual_departure_date ship_date
       , ooh.cust_po_number
       , SUBSTR (hp.party_name, 1, 30) || ' ' || 
       SUBSTR (hl_ship.address1, 1, 36) || ' ' || 
       SUBSTR (hl_ship.address2, 1, 36) || ' ' || 
       SUBSTR (, 1, 30) || ' ' || 
       SUBSTR (hl_ship.province , 1 , 2 ) || ' ' || 
       SUBSTR (hl_ship.postal_code , 1 , 8 ) ship_to_address
FROM     apps.hr_locations hl
       , org_organization_definitions ood
       , apps.oe_order_headers_all ooh
       , oe_order_lines_all ool
       , apps.hz_locations hl_ship
       , apps.hz_parties hp
       , apps.hz_party_sites hps
       , apps.hz_cust_acct_sites_all hcas
       , apps.hz_cust_site_uses_all hcsu
       , apps.hz_party_sites hps_bill
       , apps.hz_cust_acct_sites_all hcas_bill
       , apps.hz_cust_site_uses_all hcsu_bill
       , apps.wsh_delivery_details wdd
       , apps.wsh_new_deliveries wnd
       , apps.wsh_delivery_assignments wda
       , apps.wsh_trips wt
       , apps.wsh_delivery_legs wdl
       , apps.wsh_trip_stops wds
       , apps.wsh_document_instances wdi
       , apps.hz_parties hp_carrier
       , ra_customer_trx_all rct
WHERE    1 = 1
AND      ooh.header_id = ool.header_id
AND      ood.organization_id = ool.ship_from_org_id
AND      ooh.ship_from_org_id = hl.inventory_organization_id
AND      hl_ship.location_id = hps.location_id
AND      hp.party_id = hps.party_id
AND      hps.party_site_id = hcas.party_site_id
AND      hps_bill.party_site_id = hcas_bill.party_site_id
AND      hps_bill.party_id = hp.party_id
AND      hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND      hcsu.site_use_id = ooh.ship_to_org_id
AND      hcas_bill.cust_acct_site_id = hcsu_bill.cust_acct_site_id
AND      hcsu_bill.site_use_id = ooh.invoice_to_org_id
AND      ooh.header_id = wdd.source_header_id(+)
AND      wda.delivery_detail_id(+) = wdd.delivery_detail_id
AND      wda.delivery_id = wnd.delivery_id(+)
AND      ool.line_id = wdd.source_line_id
AND      wt.trip_id(+) = wds.trip_id
AND      wds.stop_id(+) = wdl.pick_up_stop_id
AND      wdl.delivery_id(+) = wnd.delivery_id
AND      hp_carrier.party_id(+) = wt.carrier_id
AND      wdi.entity_id(+) = wdl.delivery_leg_id
AND      wdi.entity_name(+) = 'WSH_DELIVERY_LEGS'
AND      TO_CHAR (ooh.order_number) = rct.ct_reference(+)
AND      TO_CHAR (ooh.order_number) = rct.interface_header_attribute1(+)
AND      interface_header_context(+) = 'ORDER ENTRY'
AND      ooh.order_number = :order_number
ORDER BY ool.flow_status_code
       , ooh.order_number
       , ool.line_number

Tuesday, January 7, 2014

Accounting Entries in Procure to Pay (P2P) cycle

A quick review on accounting entries in Procure to Pay cycle.

    Purchase Requisition creation: No entry
    Purchase Order creation: No entry
    Inventory Receipt:
    Inventory A/c…………….Debit
    AP Accrual A/C………Credit(This A/c We are giving in Financial Option)
    At the time of Matching the Invoice with Purchase Order
    AP Accrual A/c………….Debit
    Supplier A/c…………..Credit
    At the time of making payment to supplier
    Supplier A/C…………… Debit
    Bank A/c…………….Credit

Accounting Entries for Order to Cash Cycle (O2C)

A quick summary of accounting entries in Order to Cash cycle

    Sales order creation – No entries
    Pick release:
    Inventory Stage A/c…………………Debit
    Inventory Finished goods a/c……..Credit
    Ship confirm:
    Cogs A/c ……………………………Debit
    Inventory Organization a/c………Credit
    Receviable A/c………………………Debit
    Revenue A/c………………………Credit
    Tax ………………..…………………Credit
    Cash A/c Dr…………………………Debit
    Receivable A/c……………………….Credit

Copyright (c) All rights reserved. Presented by Suresh Vaishya