DBA Data[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;