[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_DATA_PUB
Source
1 PACKAGE BODY GMD_RECIPE_DATA_PUB AS
2 /* $Header: GMDPRDTB.pls 120.1 2005/07/21 02:49:36 kkillams noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'gmd_recipe_data_pub';
5
6 PROCEDURE get_recipe_data
7 ( p_api_version IN NUMBER ,
8 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
9 p_recipe_id IN NUMBER ,
10 p_organization_id IN NUMBER DEFAULT NULL ,
11 p_date IN DATE DEFAULT NULL , --Bug 4479101
12 x_return_status OUT NOCOPY VARCHAR2 ,
13 x_msg_count OUT NOCOPY NUMBER ,
14 x_msg_data OUT NOCOPY VARCHAR2 ,
15 x_return_code OUT NOCOPY NUMBER ,
16 x_recipe_rout_tbl OUT NOCOPY gmd_recipe_fetch_pub.recipe_rout_tbl ,
17 x_recipe_rout_matl_tbl OUT NOCOPY gmd_recipe_fetch_pub.recipe_rout_matl_tbl ,
18 x_recipe_step_out OUT NOCOPY gmd_recipe_fetch_pub.recipe_step_tbl ,
19 x_routing_depd_tbl OUT NOCOPY gmd_recipe_fetch_pub.routing_depd_tbl ,
20 x_oprn_act_out OUT NOCOPY gmd_recipe_fetch_pub.oprn_act_tbl ,
21 x_oprn_resc_rec OUT NOCOPY gmd_recipe_fetch_pub.oprn_resc_tbl ,
22 x_formula_header_rec OUT NOCOPY fm_form_mst%ROWTYPE ,
23 x_formula_dtl_tbl OUT NOCOPY gmdfmval_pub.formula_detail_tbl ,
24 x_recp_resc_proc_param_tbl OUT NOCOPY gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
25 )
26 IS
27
28
29 /* local Variable*/
30 p_return_status VARCHAR2(100) := FND_API.G_RET_STS_SUCCESS;
31 lrecord_type gmdfmval_pub.formula_info_in;
32 x_recipe_id gmd_recipes.recipe_id%TYPE ;
33 x_routing_id fm_rout_hdr.routing_id%TYPE ;
34 l_return_status VARCHAR2(1);
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(240);
37 l_return_code NUMBER;
38
39
40 BEGIN
41
42 IF p_recipe_id IS NOT NULL THEN
43 gmd_recipe_val.recipe_exists(p_api_version,p_init_msg_list,NULL,NULL,p_recipe_id, NULL,NULL,
44 x_return_status,x_msg_count, x_msg_data, x_return_code, x_recipe_id);
45 IF x_return_status <> p_return_status THEN
46 x_return_code := 2;
47 -- x_return_status := 2;
48 Else
49 lrecord_type.recipe_id := p_recipe_id;
50
51 gmdfmval_pub.get_element(pElement_name => 'RECIPE'
52 ,pRecord_in => lrecord_type
53 ,pDate => p_date --Bug 4479101
54 ,xFormulaHeader_rec => x_formula_header_rec
55 ,xFormulaDetail_tbl => x_formula_dtl_tbl
56 ,xReturn => x_return_status);
57 IF x_return_status <>p_return_status THEN
58 x_return_code := 3;
59 -- x_return_status := 3;
60 Else
61 gmd_recipe_fetch_pub.get_rout_hdr(p_api_version,p_init_msg_list, p_recipe_id,
62 x_return_status, x_msg_count, x_msg_data,
63 x_return_code, x_recipe_rout_tbl);
64
65 IF x_return_status <>p_return_status THEN
66 x_return_code := 4 ;
67 -- x_return_status := 4;
68 ELSE
69 gmd_recipe_fetch_pub.get_step_depd_details( p_api_version,p_init_msg_list, p_recipe_id,
70 x_return_status, x_msg_count, x_msg_data,
71 x_return_code, x_routing_depd_tbl);
72
73 IF x_return_status = 'U' THEN
74 x_return_code := 8 ;
75 ELSE
76 gmd_recipe_fetch_pub.get_rout_material( p_api_version,p_init_msg_list, p_recipe_id, x_return_status, x_msg_count, x_msg_data,
77 x_return_code, x_recipe_rout_matl_tbl);
78
79
80 gmd_recipe_fetch_pub.get_recipe_step_details(p_api_version,p_init_msg_list, p_recipe_id,p_organization_id,
81 x_return_status, x_msg_count, x_msg_data,
82 x_return_code, x_recipe_step_out);
83 IF x_return_status <>p_return_status THEN
84 x_return_code := 5;
85 -- x_return_status := 5;
86 ELSE
87 gmd_recipe_fetch_pub.get_oprn_act_detl(p_api_version,p_init_msg_list, p_recipe_id,p_organization_id,
88 x_return_status, x_msg_count, x_msg_data,
89 x_return_code, x_oprn_act_out);
90 IF x_return_status <>p_return_status THEN
91 x_return_code := 6;
92 -- x_return_status := 6;
93 ELSE
94 gmd_recipe_fetch_pub.get_oprn_resc_detl( p_api_version,p_init_msg_list, p_recipe_id,p_organization_id,
95 x_return_status, x_msg_count, x_msg_data,
96 x_return_code, x_oprn_resc_rec);
97 IF x_return_status <>p_return_status THEN
98
99 x_return_code := 7;
100 -- x_return_status := 7;
101 ELSE
102 gmd_recipe_fetch_pub.get_recipe_process_param_detl(p_api_version => p_api_version,
103 p_init_msg_list => p_init_msg_list,
104 p_recipe_id => p_recipe_id,
105 p_organization_id => p_organization_id,
106 x_return_status => x_return_status,
107 x_msg_count => x_msg_count,
108 x_msg_data => x_msg_data,
109 x_recp_resc_proc_param_tbl => x_recp_resc_proc_param_tbl);
110 IF x_return_status <> p_return_status THEN
111 x_return_code := 9;
112 END IF;
113 END IF;
114
115 END IF;
116 END IF;
117 END IF;
118 END IF;
119 END IF;
120
121 END IF;
122
123 ElSE
124 x_return_code := 1;
125 -- x_return_status := '1';
126
127 END IF;
128
129
130 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
131 x_return_code := 0;
132 -- x_return_status := 0;
133 END IF;
134
135 EXCEPTION
136 WHEN FND_API.G_EXC_ERROR THEN
137 X_return_code := SQLCODE;
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
140
141 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
142 X_return_code := SQLCODE;
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
145
146 WHEN OTHERS THEN
147 X_return_code := SQLCODE;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
150
151 END ;
152
153 END GMD_RECIPE_DATA_PUB ;