Thursday, February 24, 2011

WSH_DELIVERY_DETAILS.RELEASED_STATUS ( Pick Release Status)



Below post will give you the details about the pick release status
Instance: 11i or R12
Table: WSH_DELIVERY_DETAILS
Column: RELEASED_STATUS
Possible Values:
B: Backordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory
Delivery line statuses in detail
 Not Applicable (Code X)  
The delivery line can be invoiced but non-shippable, for example, a service line or a warranty line.
Not Ready for Release (Code N)
 The delivery line is not eligible for pick release. This happens when the order line is manually imported into Oracle Shipping Execution using the Import Delivery Line concurrent process or the corresponding order line has not reached the Awaiting Shipping workflow activity.
Ready for Release (Code R)
  The delivery line is eligible for pick release.  Occurs when the order line has reached the Awaiting Shipping workflow activity (it is booked, scheduled, and in Oracle Shipping Execution).
Submitted to Warehouse (Code S)
Pick release has processed the delivery line and has:
1.       Created move order headers and lines.
2.       Found available quantity and created inventory allocations.
3.       Not pick confirmed. If you are using auto-pick confirm, it changes release status to Staged. If you are not using auto-pick confirm and want to progress the delivery lines, navigate to Oracle Inventory Move Order Transaction window and perform manual pick confirm.
Staged (Code Y)
 The delivery line is pick confirmed; inventory is transferred from storage sub-inventory to staging sub-inventory.  It remains staged until ship confirm.
 Backordered (Code B)
Some of the circumstances that can causes this status are listed below
Ø  Pick release has processed the delivery line and cannot find the entire quantity.  This typically occurs when the Oracle Inventory indicates that there is not enough material (either because there is not enough material or because the inventory balance is incorrect).  
Ø  At ship confirm, you: Enter Shipped Quantity that is less than Original Requested Quantity Backorder the entire delivery quantity transfer a reservation to cycle count.
Ø  This typically occurs when the material that you want to ship:
1.       Has become unavailable, for example, damaged, between picking and shipping.
2.       Is available and you backorder material for specific business reasons. For example, all available material has been allocated to a specific customer when you find out additional supply for other orders will be delayed.
  Shipped (Code C)
  The delivery line’s delivery is ship confirmed and posted as in-transit, OM Interface and Inventory Interface have processed, and the trip is closed.
  Cancelled (Code D)
  The order line that the delivery line supports is cancelled.

Thursday, February 24, 2011 by Team search · 12

REF Cursors in Oracle PLSQL - An Overview with Examples


The Primary purpose of this post is to provide fair idea on the advanced concepts in PL/SQL like REF Cursor. We have given a try and hope to be useful for my audiences.

REF CURSOR
          A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result.
          To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, and then declare cursor variables of that type.

Syntax of the REF Cursor

Define a REF Cursor TYPE:
   TYPE ref_type_name IS REF CURSOR
    [RETURN {
             cursor_name%ROWTYPE           
            |ref_cursor_name%ROWTYPE
            |record_name%TYPE
            |record_type_name
            |db_table_name%ROWTYPE
            }
    ];


RETURN
specifies the data type of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.
Ø  cursor_name
An explicit cursor previously declared within the current scope.
Ø  ref_cursor_name
An ref cursor previously declared within the current scope.
Ø  record_name
A user-defined record previously declared within the current scope.
Ø  record_type_name
A user-defined record type that was defined using the data type specifies RECORD.
Ø  db_table_name
A database table or view, which must be accessible when the declaration is elaborated.
Ø  %ROWTYPE
A record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
Ø  %TYPE
Provides the datatype of a previously declared user-defined record.
Ø  type_name
A user-defined cursor variable type that was defined as a REF CURSOR.

Cursor_variable_declaration:
    cursor_variable_name ref_type_name;
OPEN a REF cursor...
OPEN cursor_variable_name
 FOR select_statement;

/*To be sure it's not open already:*/
IF NOT cursor_variable_name%ISOPEN THEN
   OPEN cursor_variable_name FOR select_statement;
END IF;

Types of REF CURSOR
Strongly Typed: A REF CURSOR that specifies a specific return type
DECLARE
TYPE    EmpCurTyp IS REF CURSOR
RETURN  emp%ROWTYPE; -- strongly typed ref cursor
cursor1 EmpCurTyp;
BEGIN
NULL;
END;
Weakly Typed:  A REF CURSOR that does not specify the return type  
DECLARE
TYPE    EmpCurTyp IS REF CURSOR  -- Weakly typed ref cursor
cursor1 EmpCurTyp;
BEGIN
NULL;
END;

Three statements to control a cursor variable
·      OPEN-FOR
·      FETCH
·      CLOSE

  1. OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
  2. PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement.
Simple Example:
DECLARE
 TYPE    EmpCurTyp IS REF CURSOR
 RETURN  emp%ROWTYPE; -- strong cursor
 emp1    EmpCurTyp;

 PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
  person emp%ROWTYPE;
 BEGIN
     DBMS_OUTPUT.PUT_LINE('-----');
     DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
 LOOP
     FETCH emp_cv INTO person;
     EXIT WHEN emp_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('Name = ' || person.ENAME ||' ' || person.JOB);
 END LOOP;
 END;

BEGIN
   OPEN  emp1
   FOR   SELECT *
         FROM emp
         WHERE ROWNUM < 11;
         process_emp_cv(emp1);
   CLOSE emp1;
   OPEN  emp1
   FOR   SELECT *
         FROM emp
         WHERE ENAME LIKE 'R%';
         process_emp_cv(emp1);
   CLOSE emp1;
END;

Difference between REF CURSOR and CURSOR with Example:
DECLARE
   TYPE rc IS REF CURSOR;
   CURSOR c IS SELECT * FROM dual;
   l_cursor rc;
BEGIN
       IF   (to_char(SYSDATE,'dd') = 30) THEN
            OPEN l_cursor FOR SELECT * FROM emp;
                 
      ELSIF (to_char(SYSDATE,'dd') = 29) THEN
             OPEN l_cursor FOR SELECT * FROM dept;
                   
      ELSE
             OPEN l_cursor FOR SELECT * FROM dual;
      END IF;
      OPEN c;
         -----
             /* some manipulation here */
         -----
      CLOSE c;
END;
Comparisons
1.       Cursor C will always be select * from dual.   The ref cursor can be anything.
2.       Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).
3.       Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

Usage Restrictions
The following are restrictions on cursor variable usage.
1.       Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.
2.       Null cannot be assigned to a cursor variable.
3.       The value of a cursor variable cannot be stored in a database column.
4.       Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.

 

 That's it.. Happy Sharing!!!

by Team search · 3

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.