[Home] [Help]
PACKAGE BODY: APPS.CSP_ITEM_PL_PARAMS_PVT
Source
1 PACKAGE BODY CSP_ITEM_PL_PARAMS_PVT AS
2 /* $Header: cspvpipb.pls 120.1 2006/01/20 13:55:29 phegde noship $ */
3
4 --
5 -- Purpose: Insert or update table mtl_item_pl_params based on some conditions
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- --------- ------ ------------------------------------------
10 -- phegde 01/05/06 created package
11
12
13 PROCEDURE merge_item_params
14 ( p_organization_id NUMBER
15 ,p_inventory_item_id NUMBER
16 ,p_excess_service_level NUMBER
17 ,p_repair_service_level NUMBER
18 ,p_newbuy_service_level NUMBER
19 ,p_excess_edq_factor NUMBER
20 ,p_repair_edq_factor NUMBER
21 ,p_newbuy_edq_factor NUMBER
22 ,p_excess_edq_multiple NUMBER
23 ,p_repair_edq_multiple NUMBER
24 ,p_newbuy_edq_multiple NUMBER
25 ) IS
26 l_excess_service_level NUMBER;
27 l_repair_service_level NUMBER;
28 l_newbuy_service_level NUMBER;
29 l_excess_edq_factor NUMBER;
30 l_repair_edq_Factor NUMBER;
31 l_newbuy_edq_Factor NUMBER;
32 l_edq_multiple NUMBER;
33 l_item_pl_params_s NUMBER;
34 l_count NUMBER;
35 l_item_excess_Service_level NUMBER;
36 l_item_repair_Service_level NUMBER;
37 l_item_newbuy_service_level NUMBER;
38 l_item_excess_edq_factor NUMBER;
39 l_item_repair_edq_factor NUMBER;
40 l_item_newbuy_edq_factor NUMBER;
41 l_item_excess_Edq_multiple NUMBER;
42 l_item_repair_edq_multiple NUMBER;
43 l_item_newbuy_edq_multiple NUMBER;
44 BEGIN
45 SELECT excess_service_level,
46 repair_service_level,
47 newbuy_service_level,
48 excess_edq_Factor,
49 repair_edq_Factor,
50 newbuy_edq_Factor,
51 edq_multiple
52 INTO l_excess_Service_level,
53 l_repair_Service_level,
54 l_newbuy_Service_level,
55 l_excess_edq_factor,
56 l_repair_edq_Factor,
57 l_newbuy_edq_Factor,
58 l_edq_multiple
59 FROM csp_planning_parameters cpp
60 WHERE organization_id = p_organization_id
61 and cpp.organization_type = 'W';
62
63
64 BEGIN
65 SELECT excess_service_level,
66 repair_service_level,
67 newbuy_service_level,
68 excess_edq_factor,
69 repair_edq_factor,
70 newbuy_edq_factor,
71 excess_edq_multiple,
72 repair_edq_multiple,
73 newbuy_edq_multiple
74 INTO l_item_excess_Service_level,
75 l_item_repair_Service_level,
76 l_item_newbuy_service_level,
77 l_item_excess_edq_factor,
78 l_item_repair_edq_factor,
79 l_item_newbuy_edq_factor,
80 l_item_excess_Edq_multiple,
81 l_item_repair_edq_multiple,
82 l_item_newbuy_edq_multiple
83 FROM csp_item_pl_params
84 WHERE organization_id = p_organization_id
85 AND inventory_item_id = p_inventory_item_id;
86
87 IF ((nvl(l_item_excess_Service_level, nvl(l_excess_service_level, 0)) <> nvl(p_excess_service_level, 0)) OR
88 (nvl(l_item_repair_Service_level, nvl(l_repair_service_level, 0)) <> nvl(p_repair_service_level, 0)) OR
89 (nvl(l_item_newbuy_service_level, nvl(l_newbuy_service_level, 0)) <> nvl(p_newbuy_service_level, 0)) OR
90 (nvl(l_item_excess_edq_factor, nvl(l_excess_edq_factor, 0)) <> nvl(p_excess_edq_factor, 0)) OR
91 (nvl(l_item_repair_edq_factor, nvl(l_repair_edq_Factor, 0)) <> nvl(p_repair_edq_factor, 0)) OR
92 (nvl(l_item_newbuy_edq_Factor, nvl(l_newbuy_edq_Factor,0)) <> nvl(p_newbuy_edq_factor,0)) OR
93 (nvl(l_item_excess_Edq_multiple, nvl(l_edq_multiple, 0)) <> nvl(p_excess_edq_multiple, 0)) OR
94 (nvl(l_item_repair_edq_multiple, nvl(l_edq_multiple, 0)) <> nvl(p_repair_edq_multiple, 0)) OR
95 (nvl(l_item_newbuy_edq_multiple, nvl(l_edq_multiple, 0)) <> nvl(p_newbuy_edq_multiple, 0))) THEN
96 UPDATE csp_item_pl_params
97 SET excess_service_level = decode(p_excess_service_level, nvl(l_item_excess_Service_level, l_excess_service_level), excess_service_level, p_excess_service_level),
98 repair_service_level = decode(p_repair_service_level, nvl(l_item_repair_Service_level, l_repair_service_level), repair_service_level, p_repair_service_level),
99 newbuy_service_level = decode(p_newbuy_service_level, nvl(l_item_newbuy_service_level, l_newbuy_service_level), newbuy_service_level, p_newbuy_service_level),
100 excess_edq_factor = decode(p_excess_edq_factor, nvl(l_item_excess_edq_factor, l_excess_edq_factor), excess_edq_factor, p_excess_edq_factor),
101 repair_edq_factor = decode(p_repair_edq_factor, nvl(l_item_repair_edq_factor, l_repair_edq_factor), repair_edq_factor, p_repair_edq_factor),
102 newbuy_edq_factor = decode(p_newbuy_edq_factor, nvl(l_item_newbuy_edq_factor, l_newbuy_edq_factor), newbuy_edq_factor, p_newbuy_edq_factor),
103 excess_edq_multiple = decode(p_excess_edq_multiple, nvl(l_item_excess_Edq_multiple, l_edq_multiple), excess_edq_multiple, p_excess_edq_multiple),
104 repair_edq_multiple = decode(p_repair_edq_multiple, nvl(l_item_repair_edq_multiple, l_edq_multiple), repair_edq_multiple, p_repair_edq_multiple),
105 newbuy_edq_multiple = decode(p_newbuy_edq_multiple, nvl(l_item_newbuy_edq_multiple, l_edq_multiple), newbuy_edq_multiple, p_newbuy_edq_multiple),
106 last_updated_by = fnd_global.user_id,
107 last_update_date = sysdate,
108 last_update_login = fnd_global.login_id
109 WHERE organization_id = p_organization_id
110 AND inventory_item_id = p_inventory_item_id;
111
112 csp_plan_details_pkg.reorders(p_organization_id,p_inventory_item_id);
113
114 END IF;
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 IF ((nvl(l_excess_service_level, 0) <> nvl(p_excess_service_level, 0)) OR
118 (nvl(l_repair_service_level, 0) <> nvl(p_repair_service_level, 0)) OR
119 (nvl(l_newbuy_service_level, 0) <> nvl(p_newbuy_service_level, 0)) OR
120 (nvl(l_excess_edq_factor, 0) <> nvl(p_excess_edq_factor, 0)) OR
121 (nvl(l_repair_edq_Factor, 0) <> nvl(p_repair_edq_factor, 0)) OR
122 (nvl(l_newbuy_edq_Factor,0) <> nvl(p_newbuy_edq_factor,0)) OR
123 (nvl(l_edq_multiple, 0) <> nvl(p_excess_edq_multiple, 0)) OR
124 (nvl(l_edq_multiple, 0) <> nvl(p_repair_edq_multiple, 0)) OR
125 (nvl(l_edq_multiple, 0) <> nvl(p_newbuy_edq_multiple, 0))) THEN
126 SELECT csp_item_pl_params_s1.nextval
127 INTO l_item_pl_params_s
128 FROM dual;
129
130 INSERT INTO csp_item_pl_params(ITEM_PL_PARAMS_ID,
131 ORGANIZATION_ID,
132 INVENTORY_ITEM_ID,
133 CREATED_BY,
134 CREATION_DATE,
135 LAST_UPDATED_BY,
136 LAST_UPDATE_DATE,
137 LAST_UPDATE_LOGIN,
138 EXCESS_SERVICE_LEVEL,
139 REPAIR_SERVICE_LEVEL,
140 NEWBUY_SERVICE_LEVEL,
141 EXCESS_EDQ_FACTOR,
142 REPAIR_EDQ_FACTOR,
143 NEWBUY_EDQ_FACTOR,
144 EXCESS_EDQ_MULTIPLE,
145 REPAIR_EDQ_MULTIPLE,
146 NEWBUY_EDQ_MULTIPLE)
147 SELECT
148 l_item_pl_params_s,
149 p_organization_id,
150 p_inventory_item_id,
151 fnd_global.user_id created_by,
152 sysdate creation_date,
153 fnd_global.user_id last_updated_by,
154 sysdate last_update_Date,
155 fnd_global.login_id last_update_login,
156 decode(nvl(p_excess_service_level, 0), nvl(cpp.excess_service_level, 0), null, p_excess_service_level) excess_Service_level,
157 decode(p_repair_service_level, cpp.repair_service_level, null, p_repair_service_level) repair_Service_level,
158 decode(p_newbuy_service_level, cpp.newbuy_service_level, null, p_newbuy_service_level) newbuy_service_level,
159 decode(p_excess_edq_factor, cpp.excess_edq_factor, null, p_excess_edq_Factor) excess_edq_Factor,
160 decode(p_repair_edq_factor, cpp.repair_edq_factor, null, p_repair_edq_Factor) repair_edq_Factor,
161 decode(p_newbuy_edq_factor, cpp.newbuy_edq_factor, null, p_newbuy_edq_Factor) newbuy_edq_Factor,
162 decode(p_excess_edq_multiple, cpp.edq_multiple, null, p_excess_edq_multiple) excess_Edq_multiple,
163 decode(p_repair_edq_multiple, cpp.edq_multiple, null, p_repair_edq_multiple) repair_edq_multiple,
164 decode(p_newbuy_edq_multiple, cpp.edq_multiple, null, p_newbuy_edq_multiple) newbuy_edq_multiple
165 FROM csp_planning_parameters cpp
166 WHERE cpp.organization_id = p_organization_id
167 AND cpp.organization_type = 'W';
168
169 csp_plan_details_pkg.reorders(p_organization_id,p_inventory_item_id);
170
171 END IF;
172
173 END;
174
175
176 /* MERGE INTO CSP_ITEM_PL_PARAMS cipp
177 USING CSP_PLANNING_PARAMETERS cpp
178 ON (cipp.organization_id = p_organization_id
179 AND cipp.inventory_item_id = p_inventory_item_id)
180 WHEN MATCHED THEN
181 UPDATE SET cipp.excess_service_level = decode(p_excess_service_level, cpp.excess_service_level, FND_API.G_MISS_NUM, p_excess_service_level),
182 cipp.repair_service_level = decode(p_repair_service_level, cpp.repair_service_level, FND_API.G_MISS_NUM, p_repair_service_level)
183 WHEN NOT MATCHED THEN
184
185 */
186 -- END IF;
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 null;
190 END;
191
192 END;