1 Package GMD_COMMON_VAL AUTHID CURRENT_USER AS
2 /* $Header: GMDPCOMS.pls 120.7.12010000.3 2009/03/19 18:08:38 plowe ship $ */
3
4 /* Purpose: Validation functions and procedures used by more than one */
5 /* part of GMD (Routings, Ops, Formula, QC, Recipes, Lab) */
6 /* */
7 /* Some code common to more than one module can be found in GMA_VALID_GRP */
8 /* (ex: validate_um, validate_orgn_code, validate_type) */
9 /* */
10 /* check_from_date */
11 /* check_date */
12 /* check_date_range */
13 /* get_customer_id */
14 /* customer_exists */
15 /* check_project */
16 /* check_user_id */
17 /* action_code */
18 /* */
19 /* MODIFICATION HISTORY */
20 /* Person Date Comments */
21 /* --------- ------ ------------------------------------------ */
22 /* 14Nov2000 Created */
23 /* Subtypes */
24
25 /* ======== */
26
27 /* Constants */
28 /* ========= */
29 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_COMMON_VAL';
30 -- P_eff_max_date CONSTANT DATE
31 -- := TO_DATE(FND_PROFILE.VALUE('SY$EFF_MAX_DATE'), 'YYYY/MM/DD HH24:MI:SS');
32
33 -- P_eff_min_date CONSTANT DATE
34 -- := TO_DATE(FND_PROFILE.VALUE('SY$EFF_MIN_DATE'), 'YYYY/MM/DD HH24:MI:SS');
35 -- Bug #2425875 (JKB) Commented above.
36
37
38 /* Error Return Code Constants: */
39 /* =========================== */
40 GMD_MAX_DATE_ERR CONSTANT INTEGER := -6; --Date is greater than system max date.
41 GMD_MIN_DATE_ERR CONSTANT INTEGER := -7; --Date is less than system min date.
42 GMD_TO_FROM_DATE CONSTANT INTEGER := -8; --From date is greater than to date.
43 GMD_FROM_DATE_REQD CONSTANT INTEGER := -35; --From date is required.
44 FMVAL_CUSTID_ERR CONSTANT INTEGER := -92205;
45
46
47 /* define this record for calculating charges */
48 /*Bug 2365583 Added a new member def_charge to the record type */
49 /* Bug 5258672 Added Max_Capacity_In_Res_UOM */
50 TYPE CHARGE_REC IS RECORD (
51 RoutingStep_id NUMBER,
52 Max_Capacity NUMBER,
53 capacity_uom cr_rsrc_mst.capacity_um%TYPE ,
54 charge INTEGER,
55 def_charge VARCHAR2(1),
56 Max_Capacity_In_Res_UOM NUMBER
57 );
58
59 TYPE PROCESS_LOSS_REC IS RECORD (
60 qty NUMBER := 0 ,
61 Recipe_id NUMBER ,
62 Formula_id NUMBER ,
63 Routing_id NUMBER ,
64 Item_Id NUMBER ,
65 Orgn_Code VARCHAR2(4) ,
66 inventory_item_id NUMBER,
67 organization_id NUMBER,
68 Validity_Rule_Id NUMBER ,
69 UOM VARCHAR2(4)
70 );
71
72 /* Define a table type for charges */
73 TYPE charge_tbl IS TABLE OF CHARGE_REC
74 INDEX BY BINARY_INTEGER;
75 TYPE fm_rout_hdr_tbl IS TABLE OF fm_rout_hdr%ROWTYPE INDEX BY BINARY_INTEGER;
76 TYPE fm_rout_dtl_tbl IS TABLE OF fm_rout_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
77 TYPE fm_rout_dep_tbl IS TABLE OF fm_rout_dep%ROWTYPE INDEX BY BINARY_INTEGER;
78 TYPE operation_tbl IS TABLE OF gmd_operations%ROWTYPE INDEX BY BINARY_INTEGER;
79 TYPE oprn_actv_tbl IS TABLE OF gmd_operation_activities%ROWTYPE INDEX BY BINARY_INTEGER;
80 TYPE oprn_rsrc_tbl IS TABLE OF gmd_operation_resources%ROWTYPE INDEX BY BINARY_INTEGER;
81 /* Functions and Procedures */
85 pcalledby_form IN VARCHAR2) RETURN NUMBER;
82 /* ======================== */
83 FUNCTION check_from_date(pfrom_date IN DATE,
84
86 FUNCTION check_date(pdate IN DATE,
87 pcalledby_form IN VARCHAR2) RETURN NUMBER;
88 FUNCTION check_date_range(pfrom_date IN DATE,
89 pto_date IN DATE,
90 pcalledby_form IN VARCHAR2) RETURN NUMBER;
91 PROCEDURE get_customer_id(pcustomer_no IN VARCHAR2,
92 xcust_id OUT NOCOPY NUMBER,
93 xsite_id OUT NOCOPY NUMBER,
94 xorg_id OUT NOCOPY NUMBER,
95 xreturn_code OUT NOCOPY NUMBER);
96
97 PROCEDURE customer_exists
98 ( p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
100 p_commit IN VARCHAR2 := FND_API.G_FALSE,
101 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
102 p_customer_id IN NUMBER,
103 p_site_id IN NUMBER,
104 p_org_id IN NUMBER,
105 p_customer_no IN VARCHAR2,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2,
109 x_return_code OUT NOCOPY NUMBER,
110 x_customer_id OUT NOCOPY NUMBER);
111
112
113 /* check_user */
114 PROCEDURE check_user_id (p_api_version IN NUMBER,
115 p_init_msg_list IN VARCHAR2,
116 p_commit IN VARCHAR2,
117 p_validation_level IN NUMBER,
118 p_user_id IN NUMBER,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 x_return_code OUT NOCOPY NUMBER);
123
124
125 PROCEDURE action_code
126 ( p_api_version IN NUMBER,
127 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
128 p_commit IN VARCHAR2 := FND_API.G_FALSE,
129 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
130 p_action_code IN VARCHAR2,
131 x_return_status OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2,
134 x_return_code OUT NOCOPY NUMBER);
135
136 /* Added a few validation and fetch procedures */
137 /* Later on to be moved to its appropriate packages */
138 PROCEDURE Get_Status
139 ( Status_code IN GMD_STATUS.Status_code%TYPE ,
140 Meaning OUT NOCOPY GMD_STATUS.Meaning%TYPE ,
141 Description OUT NOCOPY GMD_STATUS.Description%TYPE ,
142 x_return_status OUT NOCOPY VARCHAR2
143 );
144
145 PROCEDURE Calculate_Process_loss
146 ( process_loss IN process_loss_rec ,
147 Entity_type IN VARCHAR2 ,
148 x_recipe_theo_loss OUT NOCOPY GMD_PROCESS_LOSS.process_loss%TYPE ,
149 x_process_loss OUT NOCOPY GMD_PROCESS_LOSS.process_loss%TYPE ,
150 x_return_status OUT NOCOPY VARCHAR2 ,
151 x_msg_count OUT NOCOPY NUMBER ,
152 x_msg_data OUT NOCOPY VARCHAR2
153 );
154
155 PROCEDURE Calculate_Total_Qty
156 ( formula_id IN GMD_RECIPES.Formula_id%TYPE ,
157 x_product_qty OUT NOCOPY NUMBER ,
158 x_ingredient_qty OUT NOCOPY NUMBER ,
159 x_uom IN OUT NOCOPY VARCHAR2 ,
160 x_return_status OUT NOCOPY VARCHAR2 ,
161 x_msg_count OUT NOCOPY NUMBER ,
162 x_msg_data OUT NOCOPY VARCHAR2 ,
163 p_scale_factor IN NUMBER DEFAULT NULL ,
164 p_primaries IN VARCHAR2 DEFAULT 'OUTPUTS'
165 );
166
167 FUNCTION Get_Routing_Scale_Factor(vRecipe_id IN NUMBER,
168 x_return_status OUT NOCOPY VARCHAR2,
169 vFormula_id IN NUMBER DEFAULT NULL,
170 vRouting_Id IN NUMBER DEFAULT NULL
171 ) RETURN NUMBER;
172
173 PROCEDURE Calculate_Charges
174 ( Batch_id IN NUMBER ,
175 Recipe_id IN NUMBER ,
176 Routing_id IN NUMBER ,
177 VR_qty IN NUMBER ,
178 Tolerance IN NUMBER ,
179 Orgn_id IN NUMBER ,
180 x_charge_tbl OUT NOCOPY charge_tbl ,
181 x_return_status OUT NOCOPY VARCHAR2
182 );
183
184
185 PROCEDURE Calculate_Step_Charges
186 ( P_recipe_id IN NUMBER ,
187 P_tolerance IN NUMBER ,
188 P_orgn_id IN NUMBER ,
189 P_step_tbl IN GMD_AUTO_STEP_CALC.step_rec_tbl,
190 x_charge_tbl OUT NOCOPY charge_tbl ,
191 x_return_status OUT NOCOPY VARCHAR2
192 );
193
194 FUNCTION UPDATE_ALLOWED(Entity VARCHAR2
195 ,Entity_id NUMBER
196 ,Update_Column_Name VARCHAR2 Default Null)
197 RETURN BOOLEAN;
198
199 FUNCTION VERSION_CONTROL_STATE(Entity VARCHAR2, Entity_id NUMBER)
200 RETURN VARCHAR2;
201
202
203 PROCEDURE Run_status_update( p_errbuf OUT NOCOPY VARCHAR2,
204 p_retcode OUT NOCOPY VARCHAR2,
205 pCalendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
206 pPeriod_code IN cm_cmpt_dtl.period_code%TYPE,
207 pCost_mthd_code IN cm_cmpt_dtl.cost_mthd_code%TYPE);
208
209 -- KSHUKLA added the procedure to check the formula, item access to the
210 -- Recipe override org , process loss, and validity rules organizations
211
212 PROCEDURE CHECK_FORMULA_ITEM_ACCESS(pFormula_id IN NUMBER,
213 pInventory_Item_ID IN NUMBER,
214 x_return_status OUT NOCOPY VARCHAR2,
215 pRevision IN VARCHAR2 DEFAULT NULL);
216
217 -- Kapil ME Auto-Prod Bug# 5716318
218 -- Added the Procedure.
219 PROCEDURE Calculate_Total_Product_Qty ( p_formula_id IN gmd_recipes.formula_id%TYPE,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2);
223
224 /*Added the following procedure in Bug No.7027512 */
225 PROCEDURE Run_status_update( p_errbuf OUT NOCOPY VARCHAR2,
226 p_retcode OUT NOCOPY VARCHAR2,
227 pLegal_entity_id IN number,
228 pCalendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
229 pPeriod_code IN cm_cmpt_dtl.period_code%TYPE,
230 pCost_type_id IN cm_cmpt_dtl.Cost_type_id%TYPE);
231
232 END; /* Package Specification GMD_COMMON_VAL*/
[Home] [Help]