[Home] [Help]
PACKAGE BODY: APPS.GMD_VAL_DATA_PUB
Source
1 PACKAGE BODY GMD_VAL_DATA_PUB AS
2 /* $Header: GMDPVRDB.pls 120.2 2008/02/01 10:23:12 kannavar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'gmd_val_data_pub';
5 PROCEDURE get_val_data
6 ( p_api_version IN NUMBER ,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
8 p_object_type IN VARCHAR2 ,
9 p_recipe_no IN VARCHAR2 := NULL ,
10 p_recipe_version IN NUMBER := NULL ,
11 p_recipe_id IN NUMBER := NULL ,
12 p_total_input IN NUMBER := NULL ,
13 p_total_output IN NUMBER := NULL ,
14 p_formula_id IN NUMBER := NULL ,
15 p_item_id IN NUMBER := NULL ,
16 p_revision IN VARCHAR2 := NULL ,
17 p_item_no IN VARCHAR2 := NULL ,
18 p_product_qty IN NUMBER := NULL ,
19 p_uom IN VARCHAR2 := NULL ,
20 p_recipe_use IN VARCHAR2 := NULL ,
21 p_orgn_code IN VARCHAR2 := NULL ,
22 p_organization_id IN NUMBER := NULL ,
23 p_least_cost_validity IN VARCHAR2 := 'F' ,
24 p_start_date IN DATE := NULL ,
25 p_end_date IN DATE := NULL ,
26 p_status_type IN VARCHAR2 := NULL ,
27 p_validity_rule_id IN NUMBER := NULL ,
28 x_return_status OUT NOCOPY VARCHAR2 ,
29 x_msg_count OUT NOCOPY NUMBER ,
30 x_msg_data OUT NOCOPY VARCHAR2 ,
31 x_return_code OUT NOCOPY NUMBER ,
32 X_recipe_validity_out OUT NOCOPY GMD_VALIDITY_RULES.recipe_validity_tbl)
33 IS
34
35
36 /* local Variable*/
37 p_return_status VARCHAR2(100) := FND_API.G_RET_STS_SUCCESS;
38 -- lrecord_type gmdfmval_pub.formula_info_in;
39 -- x_recipe_id gmd_recipes.recipe_id%type ;
40 -- x_routing_id fm_rout_hdr.routing_id%type ;
41 l_return_status varchar2(1);
42 l_msg_count number;
43 l_msg_data varchar2(240);
44 l_return_code number;
45 l_recipe_validity_tab GMD_VALIDITY_RULES.recipe_validity_tbl;
46 j number default 0;
47
48
49 BEGIN
50 IF p_object_type = 'P' then
51
52 GMD_VALIDITY_RULES.get_validity_rules(p_api_version,p_init_msg_list,p_recipe_no,p_recipe_version ,
53 p_recipe_id,p_total_input, p_total_output,p_formula_id,p_item_id,p_revision,
54 p_item_no,p_product_qty,p_uom,null,p_orgn_code,p_organization_id, p_least_cost_validity, p_start_date,
55 p_end_date,p_status_type,p_validity_rule_id, x_return_status,x_msg_count, x_msg_data,
56 x_return_code, X_recipe_validity_out);
57
58 Elsif p_object_type = 'F' then
59 GMD_VALIDITY_RULES.get_validity_rules(p_api_version,p_init_msg_list,p_recipe_no,p_recipe_version ,
60 p_recipe_id,p_total_input, p_total_output,p_formula_id,p_item_id,p_revision,
61 p_item_no,p_product_qty,p_uom,1,p_orgn_code,p_organization_id, p_least_cost_validity, p_start_date,
62 p_end_date,p_status_type,p_validity_rule_id, x_return_status,x_msg_count, x_msg_data,
63 x_return_code, X_recipe_validity_out);
64
65
66
67
68 Elsif p_object_type = 'L' then
69 GMD_VALIDITY_RULES.get_validity_rules(p_api_version,p_init_msg_list,p_recipe_no,p_recipe_version ,
70 p_recipe_id,p_total_input, p_total_output,p_formula_id,p_item_id,p_revision,
71 p_item_no,p_product_qty,p_uom,null,p_orgn_code, p_organization_id, p_least_cost_validity, p_start_date,
72 p_end_date,p_status_type,p_validity_rule_id, x_return_status,x_msg_count, x_msg_data,
73 x_return_code, X_recipe_validity_out);
74
75 GMD_VALIDITY_RULES.get_validity_rules(p_api_version,p_init_msg_list,p_recipe_no,p_recipe_version ,
76 p_recipe_id,p_total_input, p_total_output,p_formula_id,p_item_id,p_revision,
77 p_item_no,p_product_qty,p_uom,NULL,p_orgn_code,p_organization_id, p_least_cost_validity, p_start_date,
78 p_end_date,'400',p_validity_rule_id, x_return_status,x_msg_count, x_msg_data,
79 x_return_code, l_recipe_validity_tab);
80 j :=X_recipe_validity_out.count;
81 FOR i IN 1..l_recipe_validity_tab.count
82 LOOP
83 x_recipe_validity_out(j+i) := l_recipe_validity_tab(i);
84 END LOOP;
85
86
87 GMD_VALIDITY_RULES.get_validity_rules(p_api_version,p_init_msg_list,p_recipe_no,p_recipe_version ,
88 p_recipe_id,p_total_input, p_total_output,p_formula_id,p_item_id,p_revision,
89 p_item_no,p_product_qty,p_uom,NULL,p_orgn_code, p_organization_id, p_least_cost_validity, p_start_date,
90 p_end_date,'500',p_validity_rule_id, x_return_status,x_msg_count, x_msg_data,
91 x_return_code, l_recipe_validity_tab);
92 j :=X_recipe_validity_out.count;
93 FOR i IN 1..l_recipe_validity_tab.count
94 LOOP
95 x_recipe_validity_out(j+i) := l_recipe_validity_tab(i);
96 END LOOP;
97
98
99 GMD_VALIDITY_RULES.get_validity_rules(p_api_version,p_init_msg_list,p_recipe_no,p_recipe_version ,
100 p_recipe_id,p_total_input, p_total_output,p_formula_id,p_item_id,p_revision,
101 p_item_no,p_product_qty,p_uom,null,p_orgn_code,p_organization_id, p_least_cost_validity, p_start_date,
102 p_end_date,'600',p_validity_rule_id, x_return_status,x_msg_count, x_msg_data,
103 x_return_code, l_recipe_validity_tab);
104 j :=X_recipe_validity_out.count;
105 FOR i IN 1..l_recipe_validity_tab.count
106 LOOP
107 x_recipe_validity_out(j+i) := l_recipe_validity_tab(i);
108 END LOOP;
109
110 /* Bug No.6788488 - Start */
111 BEGIN
112 DELETE FROM GMD_VAL_RULE_GTMP;
113 IF x_recipe_validity_out.count > 0 THEN
114 FOR i in 1..x_recipe_validity_out.count LOOP
115 INSERT INTO GMD_VAL_RULE_GTMP(
116 recipe_validity_rule_id, recipe_id , orgn_code , recipe_use ,
117 preference , start_date , end_date , min_qty ,
118 max_qty , std_qty , inv_min_qty , inv_max_qty ,
119 text_code , attribute_category , attribute1 , attribute2 ,
120 attribute3 , attribute4 , attribute5 , attribute6 ,
121 attribute7 , attribute8 , attribute9 , attribute10 ,
122 attribute11 , attribute12 , attribute13 , attribute14 ,
123 attribute15 , attribute16 , attribute17 , attribute18 ,
124 attribute19 , attribute20 , attribute21 , attribute22 ,
125 attribute23 , attribute24 , attribute25 , attribute26 ,
126 attribute27 , attribute28 , attribute29 , attribute30 ,
127 created_by , creation_date , last_updated_by , last_update_date ,
128 last_update_login , validity_rule_status , planned_process_loss , organization_id ,
129 inventory_item_id , revision , detail_uom , unit_cost ,
130 total_cost , delete_mark)
131 VALUES
132 (
133 x_recipe_validity_out(i).recipe_validity_rule_id, x_recipe_validity_out(i).recipe_id ,
134 x_recipe_validity_out(i).orgn_code , x_recipe_validity_out(i).recipe_use ,
135 x_recipe_validity_out(i).preference , x_recipe_validity_out(i).start_date ,
136 x_recipe_validity_out(i).end_date , x_recipe_validity_out(i).min_qty ,
137 x_recipe_validity_out(i).max_qty , x_recipe_validity_out(i).std_qty ,
138 x_recipe_validity_out(i).inv_min_qty , x_recipe_validity_out(i).inv_max_qty ,
139 x_recipe_validity_out(i).text_code , x_recipe_validity_out(i).attribute_category ,
140 x_recipe_validity_out(i).attribute1 , x_recipe_validity_out(i).attribute2 ,
141 x_recipe_validity_out(i).attribute3 , x_recipe_validity_out(i).attribute4 ,
142 x_recipe_validity_out(i).attribute5 , x_recipe_validity_out(i).attribute6 ,
143 x_recipe_validity_out(i).attribute7 , x_recipe_validity_out(i).attribute8 ,
144 x_recipe_validity_out(i).attribute9 , x_recipe_validity_out(i).attribute10 ,
145 x_recipe_validity_out(i).attribute11 , x_recipe_validity_out(i).attribute12 ,
146 x_recipe_validity_out(i).attribute13 , x_recipe_validity_out(i).attribute14 ,
147 x_recipe_validity_out(i).attribute15 , x_recipe_validity_out(i).attribute16 ,
148 x_recipe_validity_out(i).attribute17 , x_recipe_validity_out(i).attribute18 ,
149 x_recipe_validity_out(i).attribute19 , x_recipe_validity_out(i).attribute20 ,
150 x_recipe_validity_out(i).attribute21 , x_recipe_validity_out(i).attribute22 ,
151 x_recipe_validity_out(i).attribute23 , x_recipe_validity_out(i).attribute24 ,
152 x_recipe_validity_out(i).attribute25 , x_recipe_validity_out(i).attribute26 ,
153 x_recipe_validity_out(i).attribute27 , x_recipe_validity_out(i).attribute28 ,
154 x_recipe_validity_out(i).attribute29 , x_recipe_validity_out(i).attribute30 ,
155 x_recipe_validity_out(i).created_by , x_recipe_validity_out(i).creation_date ,
156 x_recipe_validity_out(i).last_updated_by , x_recipe_validity_out(i).last_update_date ,
157 x_recipe_validity_out(i).last_update_login , x_recipe_validity_out(i).validity_rule_status ,
158 x_recipe_validity_out(i).planned_process_loss , x_recipe_validity_out(i).organization_id ,
159 x_recipe_validity_out(i).inventory_item_id , x_recipe_validity_out(i).revision ,
160 x_recipe_validity_out(i).detail_uom , x_recipe_validity_out(i).unit_cost ,
161 x_recipe_validity_out(i).total_cost , 0);
162 END LOOP;
163 END IF;
164 EXCEPTION
165 WHEN OTHERS THEN
166 X_return_code := SQLCODE;
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
169 END;
170 /* Bug No.6788488 - End */
171
172 ELSIF p_object_type = 'G' then
173 -- BEGIN BUG#2436355 RajaSekhar
174 -- Added call to newly created procedure which gets all validity rules for the
175 -- recipe_id/item_id
176 GMD_VALIDITY_RULES.get_all_validity_rules(p_api_version,p_init_msg_list, p_recipe_id,p_item_id,p_revision,p_least_cost_validity,
177 x_return_status,x_msg_count, x_msg_data, x_return_code,
178 X_recipe_validity_out);
179 -- Code which calls get_validity_rules multiple times with different statuses is removed.
180 -- END BUG#2436355
181
182 END IF;
183
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 X_return_code := SQLCODE;
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
189
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 X_return_code := SQLCODE;
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
194
195 WHEN OTHERS THEN
196 X_return_code := SQLCODE;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
199
200 END ;
201
202
203 END GMD_VAL_DATA_PUB ;