Thursday, February 24, 2011
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
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
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.
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.
An explicit cursor previously declared within the current scope.
Ø ref_cursor_name
An ref cursor previously declared within the current scope.
An ref cursor previously declared within the current scope.
Ø record_name
A user-defined record previously declared within the current scope.
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.
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.
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.
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.
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
- 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.
- 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.
by Team search · 3
Subscribe to:
Posts (Atom)
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.