[Home] [Help]
PACKAGE BODY: APPS.OEXBMCBK
Source
1 PACKAGE BODY OEXBMCBK AS
2 /* $Header: OEXBMCBB.pls 115.1 99/07/16 08:11:35 porting shi $ */
3
4 PROCEDURE get_config_delivery
5 (
6 link_mode IN NUMBER,
7 dem_src_header IN NUMBER,
8 dem_src_line IN NUMBER,
9 dem_src_type IN NUMBER,
10 config_item_id IN NUMBER,
11 dem_src_delivery IN OUT NUMBER,
12 msg_text IN OUT VARCHAR2,
13 completion_status IN OUT NUMBER
14 )
15 IS
16 parameter_error EXCEPTION;
17 header_id NUMBER := 0;
18 user_id NUMBER := 0;
19 stmt_number NUMBER := 0;
20 BEGIN
21
22 completion_status := 0; -- success
23 msg_text := 'OEXBMCBK:' || 'success';
24
25 IF (dem_src_header = -1) OR
26 (dem_src_line = -1) OR
27 (dem_src_type = -1) OR
28 (config_item_id = -1) THEN
29 RAISE parameter_error;
30 END IF;
31
32 -- Get the header_id from mtl_sales_orders using the ccid (dem_src_header)
33
34 stmt_number := 1;
35
36 SELECT OEORD.HEADER_ID
37 INTO header_id
38 FROM MTL_SALES_ORDERS MTLSO,
39 SO_ORDER_TYPES_ALL OETYP,
40 SO_HEADERS_ALL OEORD
41 WHERE MTLSO.SALES_ORDER_ID = dem_src_header
42 AND OETYP.NAME = MTLSO.SEGMENT2
43 AND OEORD.ORDER_NUMBER = TO_NUMBER( MTLSO.SEGMENT1 )
44 AND OEORD.ORDER_TYPE_ID = OETYP.ORDER_TYPE_ID;
45
46 IF (link_mode = 1) THEN
47
48 -- Update the MFG Release column for this configuration to 'ATO Item Created'
49 -- when linking the configuration item
50
51 stmt_number := 2;
52
53 UPDATE SO_LINES_ALL
54 SET S27 = 23
55 WHERE HEADER_ID = header_id
56 AND (LINE_ID = dem_src_line
57 OR ATO_LINE_ID = dem_src_line)
58 AND ATO_FLAG = 'Y';
59
60 ELSIF (link_mode = 2) THEN
61
62 -- Update the MFG Release column for this configuration to 'Released'
63 -- when delinking the configuration item
64
65 stmt_number := 3;
66
67 UPDATE SO_LINES_ALL
68 SET S27 = 4
69 WHERE HEADER_ID = header_id
70 AND (LINE_ID = dem_src_line
71 OR ATO_LINE_ID = dem_src_line)
72 AND ATO_FLAG = 'Y'
73 AND NOT EXISTS
74 (SELECT 'Released Details'
75 FROM SO_LINE_DETAILS
76 WHERE LINE_ID IN
77 (SELECT LINE_ID
78 FROM SO_LINES_ALL
79 WHERE LINE_ID = dem_src_line)
80 AND INVENTORY_ITEM_ID = config_item_id
81 AND RELEASED_FLAG = 'Y');
82
83 END IF;
84
85 -- Get the next delivery to be used in line_details and mtl_demand
86 -- if linking the configuration item
87
88 IF (link_mode = 1) THEN
89
90 stmt_number := 4;
91
92 SELECT SO_DELIVERIES_S.NEXTVAL
93 INTO dem_src_delivery
94 FROM DUAL;
95
96 END IF;
97
98 IF (link_mode = 1) THEN
99
100 -- Insert the line detail for the configuration item with the same
101 -- information as the link_model line detail when linking configuration item
102
103 stmt_number := 5;
104
105 INSERT INTO SO_LINE_DETAILS
106 (LINE_ID,
107 LINE_DETAIL_ID,
108 DELIVERY,
109 CREATED_BY,
110 CREATION_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_DATE,
113 COMPONENT_CODE,
114 COMPONENT_RATIO,
115 COMPONENT_SEQUENCE_ID,
116 CONFIGURATION_ITEM_FLAG,
117 CUSTOMER_REQUESTED_LOT_FLAG,
118 DEMAND_CLASS_CODE,
119 INCLUDED_ITEM_FLAG,
120 INVENTORY_ITEM_ID,
121 INVENTORY_LOCATION_ID,
122 LATEST_ACCEPTABLE_DATE,
123 LOT_NUMBER,
124 QUANTITY,
125 RELEASED_FLAG,
126 REQUIRED_FOR_REVENUE_FLAG,
127 SHIPPABLE_FLAG,
128 TRANSACTABLE_FLAG,
129 RESERVABLE_FLAG,
130 REVISION,
131 SCHEDULE_DATE,
132 SCHEDULE_STATUS_CODE,
133 SUBINVENTORY,
134 UNIT_CODE,
135 WAREHOUSE_ID,
136 CUSTOMER_ITEM_ID,
137 DPW_ASSIGNED_FLAG,
138 WIP_COMPLETED_QUANTITY,
139 WIP_RESERVED_QUANTITY,
140 CONTEXT,
141 ATTRIBUTE1,
142 ATTRIBUTE2,
143 ATTRIBUTE3,
144 ATTRIBUTE4,
145 ATTRIBUTE5,
146 ATTRIBUTE6,
147 ATTRIBUTE7,
148 ATTRIBUTE8,
149 ATTRIBUTE9,
150 ATTRIBUTE10,
151 ATTRIBUTE11,
152 ATTRIBUTE12,
153 ATTRIBUTE13,
154 ATTRIBUTE14,
155 ATTRIBUTE15)
156 SELECT LD.LINE_ID,
157 SO_LINE_DETAILS_S.NEXTVAL,
158 dem_src_delivery,
159 user_id,
160 SYSDATE,
161 user_id,
162 SYSDATE,
163 NVL(LD.COMPONENT_CODE, LD.INVENTORY_ITEM_ID)
164 || '-' || TO_CHAR(config_item_id),
165 LD.COMPONENT_RATIO,
166 NULL,
167 'Y',
168 LD.CUSTOMER_REQUESTED_LOT_FLAG,
169 LD.DEMAND_CLASS_CODE,
170 'N',
171 config_item_id,
172 LD.INVENTORY_LOCATION_ID,
173 LD.LATEST_ACCEPTABLE_DATE,
174 LD.LOT_NUMBER,
175 LD.QUANTITY,
176 'N',
177 LD.REQUIRED_FOR_REVENUE_FLAG,
178 'Y',
179 'Y',
180 'Y',
181 LD.REVISION,
182 LD.SCHEDULE_DATE,
183 LD.SCHEDULE_STATUS_CODE,
184 LD.SUBINVENTORY,
185 LD.UNIT_CODE,
186 LD.WAREHOUSE_ID,
187 LD.CUSTOMER_ITEM_ID,
188 LD.DPW_ASSIGNED_FLAG,
189 NULL,
190 NULL,
191 LD.CONTEXT,
192 LD.ATTRIBUTE1,
193 LD.ATTRIBUTE2,
194 LD.ATTRIBUTE3,
195 LD.ATTRIBUTE4,
196 LD.ATTRIBUTE5,
197 LD.ATTRIBUTE6,
198 LD.ATTRIBUTE7,
199 LD.ATTRIBUTE8,
200 LD.ATTRIBUTE9,
201 LD.ATTRIBUTE10,
202 LD.ATTRIBUTE11,
203 LD.ATTRIBUTE12,
207 FROM SO_LINE_DETAILS LD
204 LD.ATTRIBUTE13,
205 LD.ATTRIBUTE14,
206 LD.ATTRIBUTE15
208 WHERE LD.LINE_ID = dem_src_line
209 AND LD.INCLUDED_ITEM_FLAG = 'N'
210 AND ROWNUM = 1;
211
212 ELSIF (link_mode = 2) THEN
213
214 -- Delete the configuration item detail from so_line_details when the
215 -- configuration item is delinked in mtl_demand
216
217 stmt_number := 6;
218
219 DELETE FROM SO_LINE_DETAILS
220 WHERE LINE_ID = dem_src_line
221 AND INVENTORY_ITEM_ID = config_item_id;
222
223 END IF;
224
225 EXCEPTION
226 WHEN parameter_error THEN
227 completion_status := -1;
228 msg_text := 'OEXBMCBK:' || 'verify parameters';
229 WHEN OTHERS THEN
230 completion_status := SQLCODE;
231 msg_text := 'OEXBMCBK:(' || TO_CHAR(stmt_number) ||
232 '):' || SUBSTR(SQLERRM, 1, 70);
233 END;
234
235 END OEXBMCBK;