[Home] [Help]
PACKAGE BODY: APPS.CSICUMPI_PUB
Source
1 PACKAGE BODY CSICUMPI_PUB AS
2 /* $Header: CSICUMPB.pls 115.6 2002/11/12 00:12:24 rmamidip noship $ */
3
4 FUNCTION get_non_primary_party_list(p_account_number VARCHAR2,
5 p_org_id NUMBER) RETURN VARCHAR2 IS
6
7 CURSOR c1(l_account_number VARCHAR2) is
8 SELECT hzpty.party_name
9 FROM hz_cust_accounts hzacct,
10 hz_cust_account_roles hzrole,
11 hz_parties hzpty
12 WHERE hzacct.account_number = l_account_number
13 AND hzrole.cust_account_id = hzacct.cust_account_id
14 AND hzpty.party_id = hzrole.party_id;
15
16
17 party_list VARCHAR2(32767) := '';
18 current_party_name VARCHAR2(100) := '';
19 x_msg_data VARCHAR2(2000);
20 x_msg_count NUMBER;
21
22 BEGIN
23
24 FOR c1_rec in c1(p_account_number) LOOP
25 IF (party_list IS NULL) THEN
26 party_list := c1_rec.party_name;
27 ELSE
28 party_list := party_list || ', ' || c1_rec.party_name;
29 END IF;
30 END LOOP;
31 return party_list;
32
33 EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 return party_list;
36 WHEN OTHERS THEN
37 fnd_msg_pub.count_and_get(p_count => x_msg_count,
38 p_data => x_msg_data,
39 p_encoded => fnd_api.g_false);
40
41
42 return party_list;
43
44 END get_non_primary_party_list;
45
46 FUNCTION get_Root_information(p_customer_product_id NUMBER)
47 RETURN VARCHAR2 IS
48 l_Instance_Id NUMBER;
49 l_Product VARCHAR2(250);
50 l_Description VARCHAR2(250);
51 l_Serial VARCHAR2(30);
52 l_Lot VARCHAR2(30);
53 l_Reference VARCHAR2(30);
54 l_Result_String VARCHAR2(2000);
55 l_Token CONSTANT VARCHAR2(4) := '@$?!';
56 CURSOR Root_Cur (P_Object_ID IN NUMBER) IS
57 SELECT CIR.OBJECT_ID
58 FROM CSI_II_RELATIONSHIPS CIR
59 START WITH Object_Id = P_Object_ID
60 CONNECT BY PRIOR OBJECT_ID = SUBJECT_ID
61 ORDER BY OBJECT_ID;
62 CURSOR Instance_Cur (P_Instance_ID IN NUMBER) IS
63 SELECT CII.INSTANCE_NUMBER REFERENCE,
64 CII.SERIAL_NUMBER SERIAL,
65 CII.LOT_NUMBER LOT,
66 MSIK.CONCATENATED_SEGMENTS PRODUCT,
67 MSIK.DESCRIPTION DESCRIPTION
68 FROM CSI_ITEM_INSTANCES CII,
69 MTL_SYSTEM_ITEMS_KFV MSIK
70 WHERE MSIK.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
71 AND CII.INSTANCE_ID = P_INSTANCE_ID
72 AND MSIK.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID;
73
74 BEGIN
75 OPEN Root_Cur(P_Customer_Product_ID);
76 FETCH Root_Cur INTO l_Instance_Id;
77 CLOSE Root_Cur;
78 IF l_Instance_ID IS NULL
79 THEN l_Instance_Id := P_Customer_Product_ID;
80 END IF;
81 OPEN Instance_Cur(l_Instance_Id);
82 FETCH Instance_Cur INTO
83 l_reference,
84 l_serial,
85 l_lot,
86 l_Product,
87 l_Description;
88 IF (Instance_Cur%NOTFOUND) THEN
89 l_result_string := '';
90 ELSE
91 l_result_string := l_product||l_token ||
92 l_description|| l_token ||
93 l_reference || l_token ||
94 l_serial|| l_token ||
95 l_lot;
96
97 END IF;
98 CLOSE Instance_Cur;
99 return l_result_string;
100
101 END GET_Root_INFORMATION;
102
103 FUNCTION GET_Part_Information(p_customer_product_id NUMBER)
104 RETURN VARCHAR2 IS
105 CURSOR Part_Info_Cur IS
106 SELECT MSIK.Concatenated_Segments,
107 MSIK.DESCRIPTION,
108 CII.Instance_Number,
109 CII.Serial_Number,
110 CII.Lot_Number
111 FROM CSI_Item_Instances CII,
112 MTL_SYSTEM_ITEMS_KFV MSIK
113 WHERE CII.Instance_Id = P_Customer_Product_Id
114 AND CII.Inventory_Item_Id = MSIK.Inventory_Item_ID
115 AND CII.Inv_Master_Organization_Id = MSIK.Organization_Id;
116
117 l_Product VARCHAR2(250);
118 l_Description VARCHAR2(250);
119 l_Serial VARCHAR2(30);
120 l_Lot VARCHAR2(30);
121 l_Reference VARCHAR2(30);
122 l_Result_String VARCHAR2(2000);
123 l_Token CONSTANT VARCHAR2(4) := '@$?!';
124 BEGIN
125 OPEN Part_Info_Cur;
126 FETCH Part_Info_Cur INTO
127 l_Product,
128 l_Description,
129 l_Reference,
130 l_Serial,
131 l_Lot;
132 IF (Part_Info_Cur%NOTFOUND) THEN
133 l_result_string := '';
134 ELSE
135 l_result_string := l_product||l_token ||
136 l_description|| l_token ||
137 l_reference || l_token ||
138 l_serial|| l_token ||
139 l_lot;
140
141 END IF;
142 CLOSE Part_Info_Cur;
143 return l_result_string;
144
145 END Get_Part_Information;
146 FUNCTION GET_CHILDREN_FLAG(p_customer_product_id NUMBER)
147 RETURN VARCHAR2 IS
148
149 CURSOR c1 IS
150 SELECT Subject_ID
151 FROM CSI_II_RELATIONSHIPS
152 WHERE OBJECT_ID = P_Customer_Product_Id
153 AND ACTIVE_END_DATE IS NULL;
154
155 l_cp_id NUMBER;
156 l_value VARCHAR2(1);
157
158 BEGIN
159
160 OPEN c1;
161 FETCH c1 INTO l_cp_id;
162 IF (c1%NOTFOUND) THEN
163 l_value := 'N';
164 ELSE
165 l_value := 'Y';
166 END IF;
167 CLOSE c1;
168 RETURN l_value;
169
170 END GET_CHILDREN_FLAG;
171 /*
172 PROCEDURE Get_Configuration
173 (p_cp_id IN NUMBER,
174 p_config_type IN VARCHAR2 DEFAULT NULL,
175 p_as_of_date IN DATE DEFAULT sysdate,
176 x_config_tbl OUT NOCOPY Config_Tbl_Type,
177 x_config_tbl_count OUT NOCOPY NUMBER
178 ) IS
179 l_cp_id NUMBER;
180 l_config_type VARCHAR2(30);
181 l_as_of_date DATE;
182
183 l_config_tbl Config_Tbl_Type;
184 l_config_tbl_count NUMBER := 0;
185
186 CURSOR comp_csr IS
187 SELECT CII.Instance_Id,
188 CIIR.OBJECT_ID,
189 CIIR.RELATIONSHIP_TYPE_CODE,
190 CII.OWNER_PARTY_ACCOUNT_ID,
191 CII.INVENTORY_ITEM_ID,
192 CII.SERIAL_NUMBER,
193 CII.LOT_NUMBER
194 FROM CSI_ITEM_INSTANCES CII,
195 CSI_II_RELATIONSHIPS CIIR
196 WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
197 AND l_AS_OF_DATE BETWEEN NVL(CIIR.ACTIVE_START_DATE,l_As_Of_Date-1) AND NVL(CIIR.ACTIVE_END_DATE,l_As_Of_Date+1)
198 CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID;
199
200 l_counter NUMBER := 0;
201 l_reference_number NUMBER;
202 l_curr_serial_number VARCHAR2(30);
203
204 FUNCTION IsOrphan
205 (
206 p_parent_id IN NUMBER,
207 p_num_of_recs_to_srch IN NUMBER
208 ) RETURN BOOLEAN IS
209 l_c1 NUMBER;
210 l_parent_found BOOLEAN := FALSE;
211 l_parent_id NUMBER := p_parent_id;
212 l_num_of_recs_to_srch NUMBER := p_num_of_recs_to_srch;
213
214 BEGIN
215 IF l_num_of_recs_to_srch <= 0 THEN
216 RETURN(FALSE);
217 END IF;
218
219 FOR l_c1 IN 0..l_num_of_recs_to_srch LOOP
220 IF (l_parent_id = l_config_tbl(l_c1).config_cp_id) THEN
221 l_parent_found := TRUE;
222 EXIT;
223 END IF;
224 END LOOP;
225
226 RETURN(NOT(l_parent_found));
227
228 END IsOrphan;
229
230
231 BEGIN
232 l_cp_id := p_cp_id;
233 l_config_type := p_config_type;
234 l_as_of_date := NVL(p_as_of_date,sysdate);
235
236 FOR c1 IN comp_csr LOOP
237 IF NVL(l_config_type,NVL(c1.config_type,'X')) = NVL(c1.config_type,'X') THEN
238
239 IF IsOrphan(c1.config_parent_id,l_config_tbl_count) THEN
240
241 SELECT Instance_Number,
242 serial_number
243 INTO l_reference_number,
244 l_curr_serial_number
245 FROM csi_Item_Instances
246 WHERE Instance_id = c1.customer_product_id;
247
248 l_config_tbl.DELETE;
249 l_config_tbl_count := 0;
250 END IF;
251 l_config_tbl(l_counter).config_cp_id := c1.customer_product_id;
252 l_config_tbl(l_counter).config_parent_cp_id := c1.config_parent_id;
253 l_config_tbl(l_counter).config_type := c1.config_type;
254 l_config_tbl(l_counter).customer_id := c1.customer_id;
255 l_config_tbl(l_counter).inventory_item_id := c1.inventory_item_id;
256 l_config_tbl(l_counter).serial_number := c1.current_serial_number;
257 l_config_tbl(l_counter).lot_number := c1.lot_number;
258 l_config_tbl_count := l_config_tbl_count + 1;
259 l_counter := l_counter + 1;
260
261 ELSE
262
263 SELECT Instance_Number,
264 serial_number
265 INTO l_reference_number,
266 l_curr_serial_number
267 FROM csi_Item_Instances
268 WHERE Instance_id = c1.customer_product_id;
269
270 l_config_tbl.DELETE;
271 l_config_tbl_count := 0;
272 END IF;
273 END LOOP;
274
275 x_config_tbl := l_config_tbl;
276 x_config_tbl_count := l_config_tbl_count;
277
278 END Get_Configuration;
279 */
280 FUNCTION Get_Root_Id(P_Customer_Product_Id IN NUMBER)
281 RETURN NUMBER IS
282
283 CURSOR Root_Cur (P_Object_ID IN NUMBER) IS
284 SELECT CIR.OBJECT_ID
285 FROM CSI_II_RELATIONSHIPS CIR
286 START WITH Object_Id = P_Object_ID
287 CONNECT BY PRIOR OBJECT_ID = SUBJECT_ID
288 ORDER BY OBJECT_ID;
289 l_Root_Id NUMBER;
290
291 BEGIN
292 OPEN Root_Cur(P_Customer_Product_ID);
293 FETCH Root_Cur INTO l_root_Id;
294 CLOSE Root_Cur;
295 IF l_Root_Id IS NULL
296 THEN l_Root_Id := P_Customer_Product_ID;
297 END IF;
298 RETURN l_Root_ID;
299 EXCEPTION
300 WHEN OTHERS
301 THEN RETURN NULL;
302 END Get_RooT_Id;
303 END CSICUMPI_PUB;