eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OE_ORDER_EXTN_IMPORT_PVT

Source


1 PACKAGE  BODY OE_ORDER_EXTN_IMPORT_PVT as
2 /* $Header: OEXVIMEB.pls 120.0.12020000.4 2013/02/12 10:33:40 aparava noship $ */
3 
4 PROCEDURE IMPORT_ORDER_EXTN
5                 (ERRBUF         OUT NOCOPY  VARCHAR2
6                 ,RETCODE        OUT NOCOPY  NUMBER
7 		,p_data_set_id  IN          NUMBER
8                 )
9 IS
10 l_return_status           VARCHAR2(30000);
11 l_err_text                VARCHAR2(30000);
12 l_err_code                NUMBER;
13 l_msg_count               NUMBER;
14 l_rel_sql                 VARCHAR2(100);
15 l_data_set_id             NUMBER      := p_data_set_id;
16 l_header_id 	          NUMBER;
17 l_line_id	          NUMBER;
18 l_header_count	          NUMBER;
19 l_line_count	          NUMBER;
20 l_customer_key_profile    VARCHAR2(1) := 'N';
21 l_org_id                  NUMBER;
22 
23 CURSOR l_header_cur IS
24 SELECT header_id,
25        order_source_id,
26        orig_sys_document_ref,
27        sold_to_org_id,
28        org_id
29 FROM OE_HEADERS_EXT_IFACE_ALL
30     WHERE PROCESS_STATUS=1
31       AND DATA_SET_ID=l_data_set_id;
32 
33 CURSOR l_line_cur IS
34 SELECT line_id,
35        order_source_id,
36        orig_sys_document_ref,
37        orig_sys_line_ref,
38        orig_sys_shipment_ref,
39        sold_to_org_id,
40        org_id
41 FROM OE_LINES_EXT_IFACE_ALL
42     WHERE PROCESS_STATUS=1
43       AND DATA_SET_ID=l_data_set_id;
44 
45 
46 BEGIN
47 
48     FND_FILE.PUT_LINE(  FND_FILE.LOG,'Import_Order_Extn: BEGIN');
49     FND_FILE.PUT_LINE(  FND_FILE.LOG,'Data Set ID: ' ||p_data_set_id);
50 
51     l_rel_sql 		  := 'SELECT ''ADMIN_DEFINED'' FROM DUAL';
52 
53  IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
54       fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
55       l_customer_key_profile := nvl(l_customer_key_profile, 'N');
56       FND_FILE.PUT_LINE( FND_FILE.LOG, 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
57 
58       fnd_profile.get('DEFAULT_ORG_ID', l_org_id);
59       FND_FILE.PUT_LINE( FND_FILE.LOG, 'DEFAULT ORG ID = '||l_org_id ) ;
60       IF l_org_id IS NULL THEN
61         FND_FILE.PUT_LINE( FND_FILE.LOG, 'Unable to derive org_id from MO: Default Operating Unit. Please set the profile value.');
62         RAISE FND_API.G_EXC_ERROR;
63       END IF;
64 
65  END IF;
66 
67  FND_FILE.PUT_LINE( FND_FILE.LOG, 'Deriving Header_id from OE_ORDER_HEADERS_ALL...');
68  FOR i IN l_header_cur
69   LOOP
70    IF i.header_id IS NULL THEN
71      BEGIN
72       SELECT header_id INTO l_header_id
73            FROM oe_order_headers_all
74           WHERE order_source_id       = i.order_source_id
75             AND orig_sys_document_ref = i.orig_sys_document_ref
76             AND org_id = Nvl(i.org_id,l_org_id)
77             AND (sold_to_org_id is NULL OR decode(l_customer_key_profile, 'Y',nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
78                 = decode(l_customer_key_profile, 'Y',nvl(i.sold_to_org_id, FND_API.G_MISS_NUM), 1));
79       FND_FILE.PUT_LINE( FND_FILE.LOG, 'l_header_id : ' || l_header_id);
80      EXCEPTION
81         WHEN NO_DATA_FOUND THEN
82         l_header_id :=NULL;
83          FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to derive header_id for order_source_id :'||i.order_source_id
84                                           ||',' || 'orig_sys_document_ref : ' ||i.orig_sys_document_ref);
85      END;
86 
87     IF l_header_id IS NOT NULL THEN
88        UPDATE OE_HEADERS_EXT_IFACE_ALL
89                  SET PROCESS_STATUS = 2, HEADER_ID= l_header_id
90                  WHERE data_set_id  =  l_data_set_id
91                  AND PROCESS_STATUS = 1
92                  AND order_source_id       = i.order_source_id
93                  AND orig_sys_document_ref = i.orig_sys_document_ref
94                  AND (sold_to_org_id is NULL OR decode(l_customer_key_profile, 'Y',nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
95                      = decode(l_customer_key_profile, 'Y',nvl(i.sold_to_org_id, FND_API.G_MISS_NUM), 1));
96 
97     END IF;
98    ELSE
99       UPDATE OE_HEADERS_EXT_IFACE_ALL
100                  SET PROCESS_STATUS = 2
101                  WHERE data_set_id  =  l_data_set_id
102                  AND header_id = i.header_id
103                  AND PROCESS_STATUS = 1;
104    END IF;
105 
106   END LOOP ;
107 
108   BEGIN
109     SELECT COUNT(*) INTO l_header_count
110      FROM OE_HEADERS_EXT_IFACE_ALL
111       WHERE PROCESS_STATUS=2
112         AND HEADER_ID IS NOT NULL;
113   EXCEPTION
114       WHEN NO_DATA_FOUND THEN
115        l_header_count := NULL;
116   END;
117 
118   IF l_header_count = 0 THEN
119    FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO records in OE_HEADERS_EXT_IFACE_ALL to process');
120   END IF;
121 
122   IF l_header_count > 0 THEN
123   FND_FILE.PUT_LINE( FND_FILE.LOG, 'Calling EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data to import OE_HEADERS_EXT_IFACE_ALL data');
124 
125   EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data
126    (    p_api_version                  => 1.0
127       , p_application_id               => 660
128       , p_attr_group_type              => 'OE_HEADER_ATTRIBUTES_EXT'
129       , p_object_name                  => 'OE_ORDER_HEADERS_ALL'
130       , p_hz_party_id                  => 'OPERATIONS:5877'
131       , p_interface_table_name         => 'OE_HEADERS_EXT_IFACE_ALL'
132       , p_data_set_id                  => l_data_set_id
133        ,p_related_class_codes_query    => l_rel_sql
134       , p_init_fnd_msg_list            => 'F'
135       , p_log_errors                   => 'T'
136       , p_add_errors_to_fnd_stack      => 'T'
137       , p_commit                       => 'F'
138       , p_validate                     => true
139       , p_do_dml                       => true
140       , x_return_status                => l_return_status
141       , x_errorcode                    => l_err_code
142       , x_msg_count                    => l_msg_count
143       , x_msg_data                     => l_err_text
144         );
145 
146         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_return_status after importing OE_HEADERS_EXT_IFACE_ALL : '||l_return_status);
147         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_err_code : '||l_err_code);
148         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_err_text : '||l_err_text);
149         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_msg_count : '||l_msg_count);
150 
151         IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
152             retcode := 0;
153             FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Sucessfully imported OE_HEADERS_EXT_IFACE_ALL data');
154         ELSIF  l_return_status = 'W' THEN
155             retcode := 1;
156             FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Warning while importing OE_HEADERS_EXT_IFACE_ALL');
157         ELSE
158             retcode := 2;
159             FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error reported while importing OE_HEADERS_EXT_IFACE_ALL. Please fix the errors in this log file and re-run the concurrent program.');
160         END IF;
161 
162 
163         ERROR_HANDLER.Log_Error(
164             p_write_err_to_inttable         => 'Y'
165            ,p_write_err_to_conclog          => 'Y'
166            ,p_write_err_to_debugfile        => 'N'
167            );
168 
169          IF l_return_status = FND_API.G_RET_STS_SUCCESS OR l_return_status = 'W' THEN
170             DELETE FROM OE_HEADERS_EXT_IFACE_ALL
171                 WHERE data_set_id  = l_data_set_id
172                   AND PROCESS_STATUS = 2;
173             COMMIT;
174          END IF;
175   END IF;
176 
177  FND_FILE.PUT_LINE( FND_FILE.LOG, 'Deriving Line_id from OE_ORDER_LINES_ALL...');
178  FOR i IN l_line_cur
179   LOOP
180    IF i.line_id IS NULL THEN
181      BEGIN
182       SELECT line_id INTO l_line_id
183               FROM oe_order_lines_all
184              WHERE  order_source_id   = i.order_source_id
185                AND orig_sys_document_ref = i.orig_sys_document_ref
186 	       AND orig_sys_line_ref  = i.orig_sys_line_ref
187                AND NVL(orig_sys_shipment_ref,FND_API.G_MISS_CHAR) = NVL(i.orig_sys_shipment_ref,FND_API.G_MISS_CHAR)
188                AND org_id = Nvl(i.org_id,l_org_id)
189                AND (sold_to_org_id is NULL OR decode(l_customer_key_profile, 'Y', nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
190                    = decode(l_customer_key_profile, 'Y', nvl(i.sold_to_org_id, FND_API.G_MISS_NUM), 1));
191 
192      EXCEPTION
193         WHEN NO_DATA_FOUND THEN
194         l_line_id :=NULL;
195         FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unable to derive line_id for order_source_id :'||i.order_source_id
196                                           ||',' || 'orig_sys_document_ref : ' ||i.orig_sys_document_ref
197                                           ||',' || 'orig_sys_line_ref : ' ||i.orig_sys_line_ref
198                                           ||',' || 'orig_sys_shipment_ref : ' ||i.orig_sys_shipment_ref);
199      END;
200 
201     IF l_line_id IS NOT NULL THEN
202        UPDATE OE_LINES_EXT_IFACE_ALL
203                  SET PROCESS_STATUS = 2, LINE_ID= l_line_id
204                  WHERE data_set_id  = l_data_set_id
205                  AND PROCESS_STATUS = 1
206                  AND order_source_id   = i.order_source_id
207 	         AND orig_sys_line_ref  = i.orig_sys_line_ref
208                  AND NVL(orig_sys_shipment_ref,FND_API.G_MISS_CHAR) = NVL(i.orig_sys_shipment_ref,FND_API.G_MISS_CHAR)
209                  AND (sold_to_org_id is NULL OR decode(l_customer_key_profile, 'Y', nvl(sold_to_org_id, FND_API.G_MISS_NUM), 1)
210                      = decode(l_customer_key_profile, 'Y', nvl(i.sold_to_org_id, FND_API.G_MISS_NUM), 1));
211     END IF;
212    ELSE
213       UPDATE OE_LINES_EXT_IFACE_ALL
214                  SET PROCESS_STATUS = 2
215                  WHERE data_set_id  =  l_data_set_id
216                  AND line_id = i.line_id
217                  AND PROCESS_STATUS = 1;
218    END IF;
219 
220   END LOOP ;
221 
222  BEGIN
223     SELECT COUNT(*) INTO l_line_count
224      FROM OE_LINES_EXT_IFACE_ALL
225       WHERE PROCESS_STATUS=2
226         AND LINE_ID IS NOT NULL;
227   EXCEPTION
228       WHEN NO_DATA_FOUND THEN
229        l_line_count := NULL;
230  END;
231   IF l_line_count = 0 THEN
232    FND_FILE.PUT_LINE( FND_FILE.LOG, 'NO records in OE_LINES_EXT_IFACE_ALL to process');
233   END IF;
234 
235   IF l_line_count > 0 THEN
236 
237   FND_FILE.PUT_LINE( FND_FILE.LOG, 'Calling EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data to import OE_LINES_EXT_IFACE_ALL data');
238 
239   EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data
240    (    p_api_version                  => 1.0
241       , p_application_id               => 660
242       , p_attr_group_type              => 'OE_LINE_ATTRIBUTES_EXT'
243       , p_object_name                  => 'OE_ORDER_LINES_ALL'
244       , p_hz_party_id                  => 'OPERATIONS:5877'
245       , p_interface_table_name         => 'OE_LINES_EXT_IFACE_ALL'
246       , p_data_set_id                  => l_data_set_id
247        ,p_related_class_codes_query    => l_rel_sql
248       , p_init_fnd_msg_list            => 'F'
249       , p_log_errors                   => 'T'
250       , p_add_errors_to_fnd_stack      => 'T'
251       , p_commit                       => 'F'
252       , p_validate                     => true
253       , p_do_dml                       => true
254       , x_return_status                => l_return_status
255       , x_errorcode                    => l_err_code
256       , x_msg_count                    => l_msg_count
257       , x_msg_data                     => l_err_text
258         );
259 
260         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_return_status after importing OE_LINES_EXT_IFACE_ALL : '||l_return_status);
261         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_err_code : '||l_err_code);
262         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_err_text : '||l_err_text);
263         FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_msg_count : '||l_msg_count);
264 
265         IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
266            retcode := 0;
267            FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Sucessfully imported OE_LINES_EXT_IFACE_ALL data');
268         ELSIF  l_return_status = 'W' THEN
269            retcode := 1;
270            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Warning while importing OE_LINES_EXT_IFACE_ALL');
271         ELSE
272            retcode := 2;
273            FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error reported while importing OE_LINES_EXT_IFACE_ALL. Please fix the errors in this log file and re-run the concurrent program.');
274         END IF;
275 
276 
277         ERROR_HANDLER.Log_Error(
278             p_write_err_to_inttable         => 'Y'
279            ,p_write_err_to_conclog          => 'Y'
280            ,p_write_err_to_debugfile        => 'N'
281            );
282 
283          IF l_return_status = FND_API.G_RET_STS_SUCCESS OR l_return_status = 'W' THEN
284               DELETE FROM OE_LINES_EXT_IFACE_ALL
285                   WHERE data_set_id  = l_data_set_id
286                     AND PROCESS_STATUS = 2;
287               COMMIT;
288          END IF;
289    END IF;
290           -- All the errors will be mtl_interface_errors  with process_status <> 2 and the transaction_id will be marked..
291           -- we can get the details of the error by joining with transcation_id
292 EXCEPTION
293   WHEN FND_API.G_EXC_ERROR THEN
294 	retcode := 2;
295 	errbuf := 'Please fix the error in the log file';
296     FND_FILE.PUT_LINE(FND_FILE.LOG,	'Please fix the errors in this log file and re-run the concurrent program.');
297 
298   WHEN OTHERS THEN
299 	   retcode := 2;
300 	   errbuf := sqlerrm;
301      FND_FILE.PUT_LINE(FND_FILE.LOG, 'An Exception has occured. Returning with ERROR :'||sqlerrm);
302 END;
303 END OE_ORDER_EXTN_IMPORT_PVT;