[Home] [Help]
PACKAGE: APPS.GMD_RECIPE_GENERATE
Source
1 PACKAGE GMD_RECIPE_GENERATE AUTHID CURRENT_USER AS
2 /*$Header: GMDARGES.pls 120.3.12020000.2 2012/09/29 09:33:37 yanpewan ship $*/
3
4 /* Global variables */
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_GENERATE';
6 g_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
7 g_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
8 g_recipe_id NUMBER(15);
9 g_orgn_code VARCHAR2(4);
10
11 TYPE FLEX IS RECORD (
12 ATTRIBUTE_CATEGORY VARCHAR2(30),
13 ATTRIBUTE1 VARCHAR2(240),
14 ATTRIBUTE2 VARCHAR2(240),
15 ATTRIBUTE3 VARCHAR2(240),
16 ATTRIBUTE4 VARCHAR2(240),
17 ATTRIBUTE5 VARCHAR2(240),
18 ATTRIBUTE6 VARCHAR2(240),
19 ATTRIBUTE7 VARCHAR2(240),
20 ATTRIBUTE8 VARCHAR2(240),
21 ATTRIBUTE9 VARCHAR2(240),
22 ATTRIBUTE10 VARCHAR2(240),
23 ATTRIBUTE11 VARCHAR2(240),
24 ATTRIBUTE12 VARCHAR2(240),
25 ATTRIBUTE13 VARCHAR2(240),
26 ATTRIBUTE14 VARCHAR2(240),
27 ATTRIBUTE15 VARCHAR2(240),
28 ATTRIBUTE16 VARCHAR2(240),
29 ATTRIBUTE17 VARCHAR2(240),
30 ATTRIBUTE18 VARCHAR2(240),
31 ATTRIBUTE19 VARCHAR2(240),
32 ATTRIBUTE20 VARCHAR2(240),
33 ATTRIBUTE21 VARCHAR2(240),
34 ATTRIBUTE22 VARCHAR2(240),
35 ATTRIBUTE23 VARCHAR2(240),
36 ATTRIBUTE24 VARCHAR2(240),
37 ATTRIBUTE25 VARCHAR2(240),
38 ATTRIBUTE26 VARCHAR2(240),
39 ATTRIBUTE27 VARCHAR2(240),
40 ATTRIBUTE28 VARCHAR2(240),
41 ATTRIBUTE29 VARCHAR2(240),
42 ATTRIBUTE30 VARCHAR2(240)
43 );
44
45
46 TYPE RECIPE_HDR IS RECORD (
47 RECIPE_ID NUMBER(15),
48 RECIPE_DESCRIPTION GMD_RECIPES_TL.RECIPE_DESCRIPTION%TYPE, -- Bug# 14480509
49 RECIPE_NO VARCHAR2(32),
50 RECIPE_VERSION NUMBER(5),
51 USER_ID FND_USER.user_id%TYPE,
52 USER_NAME FND_USER.user_name%TYPE,
53 OWNER_ORGN_CODE VARCHAR2(4),
54 CREATION_ORGN_CODE VARCHAR2(4),
55 FORMULA_ID FM_FORM_MST.formula_id%TYPE,
56 FORMULA_NO FM_FORM_MST.formula_no%TYPE,
57 FORMULA_VERS FM_FORM_MST.formula_vers%TYPE,
58 ROUTING_ID FM_ROUT_HDR.routing_id%TYPE,
59 ROUTING_NO FM_ROUT_HDR.routing_no%TYPE,
60 ROUTING_VERS FM_ROUT_HDR.routing_vers%TYPE,
61 PROJECT_ID NUMBER(15),
62 RECIPE_STATUS VARCHAR2(30),
63 PLANNED_PROCESS_LOSS NUMBER,
64 TEXT_CODE NUMBER(10),
65 DELETE_MARK NUMBER(5),
66 CREATION_DATE DATE,
67 CREATED_BY NUMBER(15),
68 LAST_UPDATED_BY NUMBER(15),
69 LAST_UPDATE_DATE DATE,
70 LAST_UPDATE_LOGIN NUMBER(15),
71 OWNER_ID NUMBER(15),
72 OWNER_LAB_TYPE VARCHAR2(4),
73 CALCULATE_STEP_QUANTITY NUMBER(5)
74 );
75
76 TYPE RECIPE_VR IS RECORD (
77 RECIPE_VALIDITY_RULE_ID NUMBER,
78 RECIPE_ID NUMBER,
79 RECIPE_NO VARCHAR2(32),
80 RECIPE_VERSION NUMBER,
81 USER_ID FND_USER.USER_ID%TYPE,
82 USER_NAME FND_USER.USER_NAME%TYPE,
83 ORGN_CODE VARCHAR2(4),
84 ITEM_ID NUMBER ,
85 ITEM_NO IC_ITEM_MST.ITEM_NO%TYPE,
86 RECIPE_USE VARCHAR2(30),
87 PREFERENCE NUMBER,
88 START_DATE DATE,
89 END_DATE DATE,
90 MIN_QTY NUMBER,
91 MAX_QTY NUMBER,
92 STD_QTY NUMBER,
93 ITEM_UM VARCHAR2(25),
94 INV_MIN_QTY NUMBER,
95 INV_MAX_QTY NUMBER,
96 CREATED_BY NUMBER,
97 CREATION_DATE DATE,
98 LAST_UPDATED_BY NUMBER,
99 LAST_UPDATE_DATE DATE,
100 LAST_UPDATE_LOGIN NUMBER,
101 DELETE_MARK NUMBER,
102 VALIDITY_RULE_STATUS VARCHAR2(30)
103 );
104
105 /* All table definitions */
106 TYPE recipe_tbl IS TABLE OF RECIPE_HDR
107 INDEX BY BINARY_INTEGER;
108 l_recipe_tbl GMD_RECIPE_HEADER.recipe_hdr;
109
110 TYPE recipe_flex IS TABLE OF FLEX
111 INDEX BY BINARY_INTEGER;
112 l_recipe_flex GMD_RECIPE_HEADER.flex;
113
114 TYPE recipe_vr_tbl IS TABLE OF RECIPE_VR
115 INDEX BY BINARY_INTEGER;
116 l_recipe_vr_tbl GMD_RECIPE_DETAIL.recipe_vr;
117
118 TYPE vr_flex IS TABLE OF FLEX
119 INDEX BY BINARY_INTEGER;
120 l_vr_flex GMD_RECIPE_DETAIL.flex;
121
122 TYPE status_rec_type IS RECORD(
123 ENTITY_STATUS gmd_parameters.recipe_status%TYPE,
124 DESCRIPTION gmd_status.description%TYPE,
125 STATUS_TYPE gmd_status.status_type%TYPE);
126
127 PROCEDURE recipe_generate(p_orgn_id IN NUMBER,
128 p_formula_id IN NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_recipe_no OUT NOCOPY VARCHAR2,
131 x_recipe_version OUT NOCOPY NUMBER,
132 p_commit IN VARCHAR2,
133 p_event_signed IN BOOLEAN DEFAULT FALSE,
134 p_routing_id IN NUMBER DEFAULT NULL,
135 p_enhanced_pi_ind IN VARCHAR2 DEFAULT NULL);
136
137 PROCEDURE recipe_generate(p_orgn_id IN NUMBER,
138 p_formula_id IN NUMBER,
139 x_return_status OUT NOCOPY VARCHAR2,
140 x_recipe_no OUT NOCOPY VARCHAR2,
141 x_recipe_version OUT NOCOPY NUMBER,
142 p_event_signed IN BOOLEAN DEFAULT FALSE,
143 -- Kapil ME GMO-LCF
144 p_routing_id IN NUMBER DEFAULT NULL,
145 p_enhanced_pi_ind IN VARCHAR2 DEFAULT NULL);
146 PROCEDURE create_validity_rule_set(p_recipe_id IN NUMBER,
147 p_recipe_no IN VARCHAR2,
148 p_recipe_version IN NUMBER,
149 p_formula_id IN NUMBER,
150 p_orgn_id IN NUMBER,
151 p_manage_validity_rules IN NUMBER,
152 p_recipe_use_prod IN NUMBER,
153 p_recipe_use_plan IN NUMBER,
154 p_recipe_use_cost IN NUMBER,
155 p_recipe_use_reg IN NUMBER,
156 p_recipe_use_tech IN NUMBER,
157 p_event_signed IN BOOLEAN,
158 x_return_status OUT NOCOPY VARCHAR2);
159
160 PROCEDURE create_validity_rule( p_recipe_id IN NUMBER,
161 p_recipe_no IN VARCHAR2,
162 p_recipe_version IN NUMBER,
163 p_formula_id IN NUMBER,
164 p_orgn_id IN NUMBER,
165 p_recipe_use IN NUMBER,
166 p_manage_validity_rules IN NUMBER,
167 x_end_status OUT NOCOPY VARCHAR2,
168 x_return_status OUT NOCOPY VARCHAR2,
169 p_event_signed IN BOOLEAN DEFAULT FALSE);
170
171
172 PROCEDURE create_recipe(p_formula_id IN NUMBER,
173 p_formula_status IN VARCHAR2,
174 p_orgn_id IN NUMBER,
175 x_end_status OUT NOCOPY VARCHAR2,
176 x_recipe_no OUT NOCOPY VARCHAR2,
177 x_recipe_version OUT NOCOPY NUMBER,
178 x_recipe_id OUT NOCOPY NUMBER,
179 x_return_status OUT NOCOPY VARCHAR2,
180 p_event_signed IN BOOLEAN DEFAULT FALSE,
181 -- Kapil GMO-LCF
182 p_routing_id IN NUMBER DEFAULT NULL,
183 p_enhanced_pi_ind IN VARCHAR2 DEFAULT NULL);
184
185 PROCEDURE calculate_date (p_start_date IN DATE,
186 p_num_days IN NUMBER,
187 x_end_date OUT NOCOPY DATE);
188
189 PROCEDURE manage_existing_validity(p_item_id IN NUMBER,
190 p_orgn_id IN NUMBER,
191 p_recipe_use IN NUMBER,
192 p_start_date IN DATE,
193 p_end_date IN DATE,
194 p_inv_min_qty IN NUMBER,
195 p_inv_max_qty IN NUMBER,
196 p_manage_validity_rules IN VARCHAR2);
197
198
199
200
201 FUNCTION Create_Validity RETURN BOOLEAN;
202
203 END GMD_RECIPE_GENERATE;