1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Listing all Count Dates from the MTL_CYCLE_COUNT_ENTRIES_V table

Discussion in 'SQL PL/SQL' started by RJAYE, Oct 13, 2009.

  1. RJAYE

    RJAYE Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    I am fairly new working with PL/SQL and I am trying to write a script that will display all cycle count dates.

    The columns available in the MTL_CYCLE_COUNT_ENTRIES_V table for counts are COUNT_QUANTITY_FIRST, COUNT_QUANTITY_PRIOR, and COUNT_QUANTITY_CURRENT. (So, there are only 3 columns that display the count dates.)

    But, there is another column NUMBER_OF_COUNTS, and I have some records with more than 4 counts in this field -- and I do not know how to display the dates that these counts were posted. (If there are only 3 columns to display count dates but the number of counts exceeds 3 -- how can I extract the 4th, 5th, 6th etc. counts and display the dates that they were counted?

    Thanks in advance...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    MTL_CYCLE_COUNT_ENTRIES_V is a view, not a table, and is displaying aggregates for the base table data. Find the definition of this view then query the base table for the information you seek.
     
  3. RJAYE

    RJAYE Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    The table: INV.MTL_CYCLE_COUNT_ENTRIES has the same issue with only 3 columns to list counts: (COUNT_QUANTITY_FIRST, COUNT_QUANTITY_CURRENT, COUNT_QUANTITY_PRIOR).
    How can I identify cycle counts for items that have been posted 3 or more times?

    Thank you,
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Again, look at the source for the view; there must be some statement in that query which calculates the post count. Find that and you should have a starting point for finding the information you seek.