1 PACKAGE BODY WSMPCOGI AS
2 /* $Header: WSMCOGIB.pls 115.7 2002/11/14 23:02:39 zchen ship $ */
3 /*===========================================================================
4
5 FUNCTION NAME: get_alternate_designator
6
7 ===========================================================================*/
8
9 FUNCTION get_alternate_designator
10 (X_co_product_group_id NUMBER) return varchar2 is
11
12 x_alternate_designator VARCHAR2(10) := NULL;
13
14 BEGIN
15
16 SELECT bbom.alternate_bom_designator
17 INTO x_alternate_designator
18 FROM bom_bill_of_materials bbom,
19 bom_inventory_components bic,
20 wsm_co_products bcp
21 WHERE bcp.co_product_group_id = x_co_product_group_id
22 AND bcp.component_sequence_id = bic.component_sequence_id
23 AND bic.bill_sequence_id = bbom.bill_sequence_id
24 AND rownum = 1;
25
26 return(x_alternate_designator);
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 return(NULL);
31 RAISE;
32
33 END get_alternate_designator;
34
35 /*===========================================================================
36
37 PROCEDURE NAME: get_coprod_count
38
39 ===========================================================================*/
40
41 PROCEDURE get_coprod_count(x_co_product_group_id IN NUMBER,
42 x_count IN OUT NOCOPY NUMBER,
43 x_error_code IN OUT NOCOPY NUMBER,
44 x_error_msg IN OUT NOCOPY VARCHAR2)
45 IS
46
47 x_progress VARCHAR2(3) := NULL;
48
49 BEGIN
50
51 x_progress := '010';
52
53 SELECT count (*)
54 INTO x_count
55 FROM wsm_co_products
56 WHERE co_product_group_id = x_co_product_group_id
57 AND co_product_id IS NOT NULL;
58
59 x_error_code := 0;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 x_error_code := sqlcode;
64 x_error_msg := 'WSMPPCPD.get_coprod_count(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
65
66 END get_coprod_count;
67
68 /*===========================================================================
69
70 PROCEDURE NAME: get_bill_comp_sequence
71
72 ===========================================================================*/
73
74 PROCEDURE get_bill_comp_sequence(x_result IN OUT NOCOPY NUMBER,
75 x_error_code IN OUT NOCOPY NUMBER,
76 x_error_msg IN OUT NOCOPY VARCHAR2)
77 IS
78
79 x_progress VARCHAR2(3) := NULL;
80
81 BEGIN
82
83 x_progress := '010';
84
85 SELECT bom_inventory_components_s.nextval
86 INTO x_result
87 FROM sys.dual;
88
89 x_error_code := 0;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 x_error_code := sqlcode;
94 x_error_msg := 'WSMPPCPD.get_bill_comp_sequence(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
95
96 END get_bill_comp_sequence;
97
98 /*===========================================================================
99
100 FUNCTION NAME: get_component_sequence_id
101
102 ===========================================================================*/
103
104 FUNCTION Get_Component_Sequence_Id(p_component_item_id IN NUMBER,
105 p_operation_sequence_num IN VARCHAR2,
106 p_effectivity_date IN DATE,
107 p_bill_sequence_id IN NUMBER,
108 x_err_text OUT NOCOPY VARCHAR2 )
109 RETURN NUMBER
110 IS
111 l_id NUMBER;
112 ret_code NUMBER;
113 l_err_text VARCHAR2(2000);
114 BEGIN
115 -- commented out by Bala.
116 -- June 22nd, 2000.
117 /*
118 select component_sequence_id
119 into l_id
120 from bom_inventory_components
121 where bill_sequence_id = p_bill_sequence_id
122 and component_item_id = p_component_item_id
123 and operation_seq_num = p_operation_sequence_num
124 and effectivity_date = p_effectivity_date;
125 */
126
127 /*
128 ** Bill sequence Id passed is the primary bill sequence id
129 ** of the co-product(non_primary) which will have the
130 ** common bill sequence_id (which is the bill sequence Id
131 ** of the primary co-product ..Remember..Common bill) which
132 ** only will have the Inventory components.- Bala, June 22nd, 2000.
133 */
134 select bic.component_sequence_id
135 into l_id
136 from bom_inventory_components bic,
137 bom_bill_of_materials bom
138 where bom.bill_sequence_id = p_bill_sequence_id
139 and bic.bill_sequence_id = bom.common_bill_sequence_id
140 and bic.component_item_id = p_component_item_id
141 and bic.operation_seq_num = p_operation_sequence_num
142 and bic.effectivity_date = p_effectivity_date;
143
144 RETURN l_id;
145
146 EXCEPTION
147
148 WHEN OTHERS THEN
149 x_err_text := sqlerrm;
150 RETURN NULL;
151
152 END Get_Component_Sequence_Id;
153
154 /*===========================================================================
155 FUNCTION NAME: get_item_name
156
157 DESCRIPTION: This function does a id to value conversion and
158 returns the item name
159 ===========================================================================*/
160 FUNCTION Get_Item_Name (p_inventory_item_id IN NUMBER,
161 p_organization_id IN NUMBER,
162 x_error_code IN OUT NOCOPY NUMBER,
163 x_error_msg IN OUT NOCOPY VARCHAR2)
164 RETURN VARCHAR2 IS
165
166 l_item_name VARCHAR2(240);
167
168 BEGIN
169
170 SELECT concatenated_segments
171 INTO l_item_name
172 FROM mtl_system_items_kfv
173 WHERE inventory_item_id = p_inventory_item_id
174 AND organization_id = p_organization_id;
175
176 x_error_code := 0;
177 RETURN l_item_name;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 x_error_code := sqlcode;
182 x_error_msg := sqlerrm;
183 RETURN NULL;
184 END Get_Item_Name;
185
186 END;
[Home] [Help]