[Home] [Help]
PACKAGE BODY: APPS.GMI_ALLOCATION_RULES_PVT
Source
1 PACKAGE BODY GMI_ALLOCATION_RULES_PVT AS
2 /* $Header: GMIVALRB.pls 120.0 2005/05/25 15:42:07 appldev noship $ */
3
4 /* Global variables */
5 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_ALLOCATION_RULES_PVT';
6
7
8 /* +=========================================================================+
9 | PROCEDURE NAME |
10 | Get_Allocation_Parms |
11 | |
12 | USAGE |
13 | Used to retrieve the allocation rules |
14 | |
15 | DESCRIPTION |
16 | This procedure is used to retrieve all details from op_alot_prm |
17 | |
18 | PARAMETERS |
19 | p_co_code IN VARCHAR2(4) - Customer Company Code |
20 | p_cust_no IN VARCHAR2(32) - Customer Number |
21 | p_alloc_class IN VARCHAR2(8) - Allocation Class |
22 | x_op_alot_prm OUT NOCOPY RECORD - Record containing op_alot_prm |
23 | |
24 | HISTORY |
25 | 15-DEC-1999 K.Y.Hunt Created
26 +=========================================================================+
27 */
28
29 -- HW BUG#:2643440, removed intitalization of G_MISS_XXX
30 -- from p_co_code,p_cust_no, p_alloc_class,p_of_cust_id,
31 -- p_ship_to_org_id,p_org_id.
32 PROCEDURE GET_ALLOCATION_PARMS
33 ( p_co_code IN OP_CUST_MST.CO_CODE%TYPE default NULL
34 , p_cust_no IN OP_CUST_MST.CUST_NO%TYPE default NULL
35 , p_alloc_class IN IC_ITEM_MST.ALLOC_CLASS%TYPE default NULL
36 , p_of_cust_id IN NUMBER default NULL
37 , p_ship_to_org_id IN NUMBER default NULL
38 , p_org_id IN NUMBER default NULL
39 , x_return_status OUT NOCOPY VARCHAR2
40 , x_op_alot_prm OUT NOCOPY op_alot_prm%ROWTYPE
41 , x_msg_count OUT NOCOPY NUMBER
42 , x_msg_data OUT NOCOPY VARCHAR2
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ALLOCATION_PARMS';
46 l_cust_id OP_CUST_MST.CUST_ID%TYPE;
47 l_msg_count NUMBER :=0;
48 l_msg_data VARCHAR2(2000);
49 l_return_status VARCHAR2(1);
50 l_op_alot_prm OP_ALOT_PRM%ROWTYPE;
51
52 CURSOR op_cust_mst_c1 is
53 SELECT cust_id
54 FROM op_cust_mst
55 WHERE co_code = p_co_code and cust_no = p_cust_no;
56
57 --BUG 1655007 - Ensure delete_marked rows are ignored
58 --===================================================
59 /*CURSOR op_alot_prm_c1 is
60 SELECT *
61 FROM op_alot_prm
62 WHERE cust_id=l_cust_id and alloc_class = p_alloc_class and delete_mark=0;
63
64 CURSOR op_alot_prm_c2 is
65 SELECT *
66 FROM op_alot_prm
67 WHERE cust_id IS NULL and alloc_class = p_alloc_class and delete_mark=0;
68 */
69 /* bug 2245351, use generic of_cust_id, and mtl_org_id for the record
70 cust_id is null is still supported
71 sold_to_org_id would be the same value as cust_id
72 */
73 CURSOR op_alot_prm_c1 is
74 SELECT *
75 FROM op_alot_prm
76 WHERE sold_to_org_id = p_of_cust_id
77 --and ship_to_org_id = p_ship_to_org_id
78 and org_id = p_org_id
79 and alloc_class = p_alloc_class
80 and delete_mark=0;
81 CURSOR op_alot_prm_c2 is
82 SELECT *
83 FROM op_alot_prm
84 WHERE sold_to_org_id IS NULL
85 and org_id is null
86 and alloc_class = p_alloc_class
87 and delete_mark=0;
88
89 BEGIN
90
91 /* OPEN op_cust_mst_c1;
92 FETCH op_cust_mst_c1 INTO l_cust_id;
93
94 IF (op_cust_mst_c1%NOTFOUND)
95 THEN
96 CLOSE op_cust_mst_c1;
97 FND_MESSAGE.SET_NAME('GML','OP_API_INVALID_CUSTOMER');
98 FND_MESSAGE.SET_TOKEN('CUST_NO',p_cust_no);
99 FND_MSG_PUB.Add;
100 RAISE FND_API.G_EXC_ERROR;
101 ELSE
102 CLOSE op_cust_mst_c1;
103 END IF;
104 */
105 /*Attempt to locate allocation parms specific to this customer
106 =============================================================*/
107 GMI_reservation_Util.PrintLn('p_org_id '||p_org_id);
108 GMI_reservation_Util.PrintLn('p_of_cust_id '||p_of_cust_id);
109 GMI_reservation_Util.PrintLn('p_ship_to_org_id '||p_ship_to_org_id);
110 GMI_reservation_Util.PrintLn('p_alloc_class '||p_alloc_class);
111 OPEN op_alot_prm_c1;
112 FETCH op_alot_prm_c1 INTO x_op_alot_prm;
113
114 /* No allocation rule located specific to our customer so look for a
115 set of rules global across ALL customers
116 ==================================================================*/
117 IF (op_alot_prm_c1%NOTFOUND)
118 THEN
119 CLOSE op_alot_prm_c1;
120 OPEN op_alot_prm_c2;
121 /* dbms_output.put_line('generic fetch'); */
122 FETCH op_alot_prm_c2 INTO x_op_alot_prm;
123 IF (op_alot_prm_c2%NOTFOUND)
124 THEN
125 /* No rules defined on the database so set them from profile values */
126 /* =================================================================*/
127 GMI_ALLOCATION_RULES_PVT.GET_DEFAULT_PARMS
128 (x_op_alot_prm => l_op_alot_prm);
129 x_op_alot_prm := l_op_alot_prm;
130 GMI_reservation_Util.PrintLn('no rules in op_alot_prm, use default ');
131 ELSE
132 GMI_reservation_Util.PrintLn('op_alot_prm is found for all cust ');
133 END IF;
134 CLOSE op_alot_prm_c2;
135 ELSE
136 GMI_reservation_Util.PrintLn('op_alot_prm is found for cust '||p_of_cust_id);
137 CLOSE op_alot_prm_c1;
138 END IF;
139
140 /* EXCEPTION HANDLING
141 ====================*/
142
143 EXCEPTION
144 WHEN FND_API.G_EXC_ERROR THEN
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
147 , p_count => x_msg_count
148 , p_data => x_msg_data
149 );
150
151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
154 , p_count => x_msg_count
155 , p_data => x_msg_data
156 );
157 WHEN OTHERS THEN
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159
160 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
161 , l_api_name
162 );
163
164 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
165 , p_count => x_msg_count
166 , p_data => x_msg_data
167 );
168
169 END Get_allocation_parms;
170 /* +=========================================================================+
171 | PROCEDURE NAME |
172 | Get_Default_Parms |
173 | |
174 | DESCRIPTION |
175 | Used to set default allocation rules based on system profile values |
176 | |
177 | PARAMETERS |
178 | x_op_alot_prm OUT NOCOPY RECORD - Record containing allocation rules |
179 | |
180 | HISTORY |
181 | 15-DEC-1999 K.Y.Hunt Created |
182 +=========================================================================+
183 */
184 PROCEDURE GET_DEFAULT_PARMS
185 ( x_op_alot_prm OUT NOCOPY op_alot_prm%ROWTYPE)
186 IS
187 l_api_name CONSTANT VARCHAR2 (30) := 'GET_DEFAULT_PARMS';
188
189 BEGIN
190 oe_debug_pub.add('No specific allocation rules defined so use defaults',1);
191 x_op_alot_prm.alloc_method := FND_PROFILE.VALUE('IC$ALLOC_METHOD');
192 x_op_alot_prm.shelf_days := FND_PROFILE.VALUE('IC$SHELF_DAYS');
193 x_op_alot_prm.alloc_horizon := FND_PROFILE.VALUE('IC$ALLOC_HORIZON');
194 x_op_alot_prm.alloc_type := FND_PROFILE.VALUE('IC$ALLOC_TYPE');
195 x_op_alot_prm.lot_qty := FND_PROFILE.VALUE('IC$LOT_QTY');
196 x_op_alot_prm.partial_ind := FND_PROFILE.VALUE('OP$PARTIAL_ALLOC');
197 x_op_alot_prm.prefqc_grade := NULL;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 RAISE;
202 END Get_default_parms;
203
204 END GMI_ALLOCATION_RULES_PVT;