1 PACKAGE BODY WIP_LOT_NUMBER_DEFAULT AS
2 /* $Header: wiplndfb.pls 120.3 2006/07/20 00:42:33 rekannan noship $ */
3
4 FUNCTION Lot_Number(
5 P_Item_Id IN NUMBER,
6 P_Organization_Id IN NUMBER,
7 P_Lot_Number IN VARCHAR2,
8 P_Job_Name IN VARCHAR2,
9 P_Default_Flag IN NUMBER) return VARCHAR2 IS
10 -- Fixed bug 5201815
11 -- Made this API as autonomous to commit the update to mtl_system_items
12
13 Pragma AUTONOMOUS_TRANSACTION;
14
15 x_lot_control_code NUMBER;
16 x_item_prefix VARCHAR2(100);
17 x_start_auto_lot_number VARCHAR2(100);
18 x_lot_number_default_type NUMBER;
19 x_lot_number_generation NUMBER;
20 x_org_prefix VARCHAR2(100);
21 x_lot_number_zero_padding NUMBER;
22 x_lot_number_length NUMBER;
23 lot_no VARCHAR2(500);
24 lot_length NUMBER;
25 /* ER 4378835: Increased length of new_auto_lot_number from 30 to 80 to support OPM Lot-model changes */
26 new_auto_lot_number varchar2(80); /** Bug 2923750 **/
27
28 CURSOR clot IS
29 SELECT
30 MSI.LOT_CONTROL_CODE,
31 MSI.AUTO_LOT_ALPHA_PREFIX,
32 MSI.START_AUTO_LOT_NUMBER,
33 WP.LOT_NUMBER_DEFAULT_TYPE,
34 MP.LOT_NUMBER_GENERATION,
35 MP.AUTO_LOT_ALPHA_PREFIX,
36 MP.LOT_NUMBER_ZERO_PADDING,
37 MP.LOT_NUMBER_LENGTH
38 FROM
39 MTL_SYSTEM_ITEMS MSI,
40 WIP_PARAMETERS WP,
41 MTL_PARAMETERS MP
42 WHERE MP.ORGANIZATION_ID = P_Organization_Id
43 AND WP.ORGANIZATION_ID = P_Organization_Id
44 AND MSI.ORGANIZATION_ID = P_Organization_Id
45 AND MSI.INVENTORY_ITEM_ID = P_Item_Id;
46
47 BEGIN
48
49 -- For nonstandard job through mass load, it is possible that the
50 -- Item_Id is NULL
51
52 IF P_Item_Id IS NULL THEN
53 return(NULL);
54 END IF;
55
56 OPEN clot;
57 FETCH clot INTO x_lot_control_code,
58 x_item_prefix,
59 x_start_auto_lot_number,
60 x_lot_number_default_type,
61 x_lot_number_generation,
62 x_org_prefix,
63 x_lot_number_zero_padding,
64 x_lot_number_length;
65 CLOSE clot;
66
67 lot_no := NULL;
68
69 -- Not under lot control
70 IF x_lot_control_code = WIP_CONSTANTS.NO_LOT THEN
71 return(NULL);
72
73 -- If the lot_number is already manually set then just use it
74 ELSIF P_Lot_Number IS NOT NULL OR P_Default_Flag <> WIP_CONSTANTS.YES THEN
75 return(P_Lot_Number);
76
77 -- If WIP Parameter is set to no default
78 ELSIF x_lot_number_default_type = WIP_CONSTANTS.NO_DEFAULT THEN
79 return(NULL);
80
81 -- If WIP Parameter is set to based on Job Name
82 ELSIF x_lot_number_default_type = WIP_CONSTANTS.DEFAULT_JOB THEN
83 return(P_Job_Name);
84
85 -- Based on Inventory Rules
86 ELSE
87 IF x_lot_number_generation = WIP_CONSTANTS.ORG_LEVEL THEN
88 IF x_lot_number_zero_padding = WIP_CONSTANTS.YES THEN
89 SELECT x_org_prefix ||
90 LPAD(to_char(MTL_LOT_NUMERIC_SUFFIX_S.nextval),
91 x_lot_number_length -
92 nvl(lengthb(x_org_prefix),0),'0')
93 INTO lot_no
94 FROM DUAL;
95 ELSE
96 SELECT x_org_prefix ||
97 to_char(MTL_LOT_NUMERIC_SUFFIX_S.nextval),
98 NVL(lengthb(x_org_prefix),0)
99 + NVL(lengthb(to_char(
100 MTL_LOT_NUMERIC_SUFFIX_S.currval)),0)
101 INTO lot_no, lot_length
102 FROM DUAL;
103 END IF;
104 ELSIF x_lot_number_generation = WIP_CONSTANTS.ITEM_LEVEL THEN
105 IF x_lot_number_zero_padding = WIP_CONSTANTS.YES THEN
106 lot_no := x_item_prefix || lpad(x_start_auto_lot_number,
107 x_lot_number_length - nvl(lengthb(x_item_prefix),0),'0');
108 ELSE
109 lot_no := x_item_prefix || x_start_auto_lot_number;
110 lot_length := lengthb(x_item_prefix)
111 + lengthb(x_start_auto_lot_number);
112 END IF;
113
114 /** Fix for bug 2923750 -- modification starts **/
115 new_auto_lot_number := to_char(to_number(x_start_auto_lot_number) + 1);
116 /* Fix for bug 4768625. We should not do zero padding when we update
117 MSI.START_AUTO_LOT_NUMBER.
118 IF x_lot_number_zero_padding = WIP_CONSTANTS.YES THEN
119 new_auto_lot_number := lpad(new_auto_lot_number,
120 x_lot_number_length - nvl(lengthb(x_item_prefix),0)
121 ,'0');
122 ELSE
123 IF lengthb(ltrim(rtrim(x_start_auto_lot_number))) <>
124 lengthb(to_char(to_number(ltrim(rtrim(x_start_auto_lot_number))))) THEN
125 new_auto_lot_number := lpad(new_auto_lot_number,
126 lengthb(x_start_auto_lot_number)
127 ,'0');
128 END IF;
129 END IF;
130 */
131 -- End fix for bug 4768625
132
133 /** Fix for bug 2923750 -- modification ends **/
134
135 UPDATE MTL_SYSTEM_ITEMS
136 SET Start_Auto_Lot_Number =
137 decode(Start_Auto_Lot_Number,NULL,NULL,
138 new_auto_lot_number), /** Bug 2923750 **/
139 Last_Update_Date =SYSDATE,
140 Last_Updated_By = FND_GLOBAL.USER_ID,
141 Last_Update_Login = FND_GLOBAL.LOGIN_ID
142 WHERE Organization_Id = P_Organization_Id
143 AND Inventory_Item_Id = P_Item_Id;
144
145 END IF;
146 commit; -- Fixed bug 5201815
147 IF lot_length > x_lot_number_length
148 AND x_lot_number_zero_padding <> WIP_CONSTANTS.YES THEN
149 return(NULL);
150 ELSE
151 return(lot_no);
152 END IF;
153
154 END IF;
155
156 END Lot_Number;
157
158 END WIP_LOT_NUMBER_DEFAULT;
[Home] [Help]