[Home] [Help]
PACKAGE BODY: APPS.RLM_XML_API
Source
1 PACKAGE BODY RLM_XML_API as
2 /* $Header: RLMXMLPB.pls 120.8 2006/12/22 19:22:24 rlanka noship $*/
3
4 --
5 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
6 --
7 PROCEDURE ValidateScheduleType(x_SchedType IN VARCHAR2,
8 x_RetCode OUT NOCOPY NUMBER) IS
9 --
10 l_Timer NUMBER;
11 --
12 BEGIN
13 --
14 SELECT hsecs INTO l_Timer FROM v$timer;
15 --
16 IF (l_debug <> -1) THEN
17 rlm_core_sv.start_debug(x_SchedType || '-' || l_Timer);
18 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.ValidateScheduleType');
19 rlm_core_sv.dlog(C_DEBUG, 'x_SchedType', x_SchedType);
20 END IF;
21 --
22 IF (UPPER(x_SchedType) <> k_DEMAND) THEN
23 --
24 x_RetCode := k_ERROR;
25 --
26 ELSE
27 --
28 x_RetCode := k_SUCCESS;
29 --
30 END IF;
31 --
32 IF (l_debug <> -1) THEN
33 rlm_core_sv.dlog(C_DEBUG, 'x_RetCode', x_RetCode);
34 rlm_core_sv.dpop(C_SDEBUG);
35 END IF;
36 --
37 END ValidateScheduleType;
38
39
40 PROCEDURE SetSSSILineDetails(x_LineType IN VARCHAR2,
41 x_ReqdDt IN DATE,
42 x_RecdDt IN DATE,
43 x_ShipDt IN DATE,
44 x_ItemQty IN NUMBER,
45 x_RecdQty IN NUMBER,
46 x_ShipQty IN NUMBER,
47 x_DateType IN VARCHAR2,
48 x_ItemUOM IN VARCHAR2,
49 x_RecdUOM IN VARCHAR2,
50 x_ShipUOM IN VARCHAR2,
51 x_StartDt OUT NOCOPY DATE,
52 x_Qty OUT NOCOPY NUMBER,
53 x_Subtype OUT NOCOPY VARCHAR2,
54 x_DateCode OUT NOCOPY VARCHAR2,
55 x_QtyUOM OUT NOCOPY VARCHAR2,
56 x_ErrCode IN OUT NOCOPY NUMBER,
57 x_ErrMsg IN OUT NOCOPY VARCHAR2) IS
58
59 --
60 e_IncompleteData EXCEPTION;
61 e_UnknownData EXCEPTION;
62 --
63
64 BEGIN
65 --
66 IF (l_debug <> -1) THEN
67 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.SetSSSILineDetails');
68 rlm_core_sv.dlog(C_DEBUG, 'x_LineType', x_LineType);
69 rlm_core_sv.dlog(C_DEBUG, 'x_DateType', x_DateType);
70 rlm_core_sv.dlog(C_DEBUG, 'x_ReqdDt', to_char(x_ReqdDt, 'DD-MON-YYYY HH24:MI:SS'));
71 rlm_core_sv.dlog(C_DEBUG, 'x_RecdDt', to_char(x_RecdDt, 'DD-MON-YYYY HH24:MI:SS'));
72 rlm_core_sv.dlog(C_DEBUG, 'x_ShipDt', to_char(x_ShipDt, 'DD-MON-YYYY HH24:MI:SS'));
73 rlm_core_sv.dlog(C_DEBUG, 'x_RecdQty and UOM', x_RecdQty || '-' || x_RecdUOM);
74 rlm_core_sv.dlog(C_DEBUG, 'x_ShipQty and UOM', x_ShipQty || '-' || x_ShipUOM);
75 rlm_core_sv.dlog(C_DEBUG, 'x_ItemQty and UOM', x_ItemQty || '-' || x_ItemUOM);
76 END IF;
77 --
78 IF (UPPER(x_LineType) IN ('0', '1', '2') OR x_LineType is NULL) THEN
79 --
80 x_Subtype := '1';
81 x_DateCode := NVL(x_DateType, k_DELIVER);
82 x_StartDt := x_ReqdDt;
83 x_Qty := x_ItemQty;
84 x_QtyUOM := x_ItemUOM;
85 --
86 ELSIF (UPPER(x_LineType) = '4') THEN
87 --
88 IF (x_RecdDt IS NOT NULL) THEN
89 --
90 x_Subtype := k_RECEIPT;
91 x_DateCode := k_RECEIVED;
92 x_StartDt := x_RecdDt;
93 --
94 IF (x_RecdQty IS NOT NULL) THEN
95 --
96 x_Qty := x_RecdQty;
97 x_QtyUOM := x_RecdUOM;
98 --
99 END IF;
100 --
101 ELSIF (x_ShipDt IS NOT NULL) THEN
102 --
103 x_Subtype := k_SHIPMENT;
104 x_DateCode := k_SHIPPED;
105 x_StartDt := x_ShipDt;
106 --
107 IF (x_ShipQty IS NOT NULL) THEN
108 --
109 x_Qty := x_ShipQty;
110 x_QtyUOM := x_ShipUOM;
111 --
112 END IF;
113 --
114 ELSE
115 --
116 RAISE e_IncompleteData;
117 --
118 END IF;
119 --
120 ELSIF (x_LineType = '3') THEN
121 --
122 x_Subtype := k_FINISHED;
123 x_DateCode := k_FROMTO;
124 x_StartDt := x_ReqdDt;
125 x_Qty := x_ItemQty;
126 x_QtyUOM := x_ItemUOM;
127 --
128 ELSIF (x_LineType = '5') THEN
129 --
130 x_Subtype := k_AHDBHND;
131 x_DateCode := k_ASOF;
132 x_StartDt := x_ReqdDt;
133 x_Qty := x_ItemQty;
134 x_QtyUOM := x_ItemUOM;
135 --
136 ELSE
137 --
138 RAISE e_UnknownData;
139 --
140 END IF;
141 --
142 IF (l_debug <> -1) THEN
143 rlm_core_sv.dpop(C_SDEBUG);
144 END IF;
145 --
146 EXCEPTION
147 WHEN e_IncompleteData THEN
148 x_ErrCode := x_ErrCode + 1;
149 x_ErrMsg := x_ErrMsg || ' Required data not present on schedule';
150 IF (l_debug <> -1) THEN
151 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Required Data not on schedule');
152 rlm_core_sv.dpop(C_SDEBUG);
153 END IF;
154
155 WHEN e_UnknownData THEN
156 x_ErrCode := x_ErrCode + 1;
157 x_ErrMsg := x_ErrMsg || ' Unknown linetype ''' || x_LineType || '''';
158 IF (l_debug <> -1) THEN
159 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Unknown line type = ' || x_LineType);
160 rlm_core_sv.dpop(C_SDEBUG);
161 END IF;
162
163 WHEN OTHERS THEN
164 x_ErrCode := x_ErrCode + 1;
165 x_ErrMsg := x_ErrMsg || ' Unknown error in SetSSSILineDetails';
166 IF (l_debug <> -1) THEN
167 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Unknown error in SetSSSILineDetails');
168 rlm_core_sv.dpop(C_SDEBUG);
169 END IF;
170 raise ecx_utils.PROGRAM_EXIT;
171
172 END SetSSSILineDetails;
173
174
175 PROCEDURE SetSPSILineDetails(x_LineType IN VARCHAR2,
176 x_FromDt IN DATE,
177 x_ToDt IN DATE,
178 x_RecdDt IN DATE,
179 x_ShipDt IN DATE,
180 x_ItemQty IN NUMBER,
181 x_RecdQty IN NUMBER,
182 x_ShipQty IN NUMBER,
183 x_DateType IN VARCHAR2,
184 x_ItemUOM IN VARCHAR2,
185 x_RecdUOM IN VARCHAR2,
186 x_ShipUOM IN VARCHAR2,
187 x_BktType IN VARCHAR2,
188 x_StartDt OUT NOCOPY DATE,
189 x_EndDt OUT NOCOPY DATE,
190 x_Subtype OUT NOCOPY VARCHAR2,
191 x_Qty OUT NOCOPY NUMBER,
192 x_DateCode OUT NOCOPY VARCHAR2,
193 x_QtyUOM OUT NOCOPY VARCHAR2,
194 x_ErrCode IN OUT NOCOPY NUMBER,
195 x_ErrMsg IN OUT NOCOPY VARCHAR2) IS
196 --
197 e_UnknownData EXCEPTION;
198 e_IncompleteData EXCEPTION;
199 --
200 BEGIN
201 --
202 IF (l_debug <> -1) THEN
203 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.SetSPSILineDetails');
204 rlm_core_sv.dlog(C_DEBUG, 'x_LineType', x_LineType);
205 rlm_core_sv.dlog(C_DEBUG, 'x_DateType', x_DateType);
206 rlm_core_sv.dlog(C_DEBUG, 'x_BktType', x_BktType);
207 rlm_core_sv.dlog(C_DEBUG, 'x_FromDt', to_char(x_FromDt, 'DD-MON-YYYY HH24:MI:SS'));
208 rlm_core_sv.dlog(C_DEBUG, 'x_ToDt', to_char(x_ToDt, 'DD-MON-YYYY HH24:MI:SS'));
209 rlm_core_sv.dlog(C_DEBUG, 'x_RecdDt', to_char(x_RecdDt, 'DD-MON-YYYY HH24:MI:SS'));
210 rlm_core_sv.dlog(C_DEBUG, 'x_ShipDt', to_char(x_ShipDt, 'DD-MON-YYYY HH24:MI:SS'));
211 rlm_core_sv.dlog(C_DEBUG, 'x_RecdQty and UOM', x_RecdQty || '-' || x_RecdUOM);
212 rlm_core_sv.dlog(C_DEBUG, 'x_ShipQty and UOM', x_ShipQty || '-' || x_ShipUOM);
213 rlm_core_sv.dlog(C_DEBUG, 'x_ItemQty and UOM', x_ItemQty || '-' || x_ItemUOM);
214 END IF;
215
216 --
217 IF (UPPER(x_LineType) IN ('0', '1', '2') OR x_LineType IS NULL) THEN
218 --
219 x_Subtype := NVL(x_BktType, '1');
220 x_DateCode := NVL(x_DateType, k_DELIVER);
221 x_StartDt := NVL(x_FromDt, SYSDATE);
222 x_EndDt := x_ToDt;
223 x_Qty := x_ItemQty;
224 x_QtyUOM := x_ItemUOM;
225 --
226 ELSIF (UPPER(x_LineType) = '4') THEN
227 --
228 IF (x_RecdDt is NOT NULL) THEN
229 --
230 x_Subtype := k_RECEIPT;
231 x_DateCode := k_RECEIVED;
232 x_StartDt := x_RecdDt;
233 --
234 IF (x_RecdQty IS NOT NULL) THEN
235 --
236 x_Qty := x_RecdQty;
237 x_QtyUOM := x_RecdUOM;
238 --
239 END IF;
240 --
241 ELSIF (x_ShipDt is NOT NULL) THEN
242 --
243 x_Subtype := k_SHIPMENT;
244 x_DateCode := k_SHIPPED;
245 x_StartDt := x_ShipDt;
246 --
247 IF (x_ShipQty IS NOT NULL) THEN
248 --
249 x_Qty := x_ShipQty;
250 x_QtyUOM := x_ShipUOM;
251 --
252 END IF;
253 --
254 ELSE
255 --
256 RAISE e_IncompleteData;
257 --
258 END IF;
259 --
260 ELSIF (x_LineType = '3') THEN
261 --
262 x_SubType := NVL(x_BktType, k_FINISHED);
263 x_DateCode := k_FROMTO;
264 x_StartDt := x_FromDt;
265 x_EndDt := x_ToDt;
266 x_Qty := x_ItemQty;
267 x_QtyUOM := x_ItemUOM;
268 --
269 ELSIF (x_LineType = '5') THEN
270 --
271 x_SubType := NVL(x_BktType, k_AHDBHND);
272 x_DateCode := k_ASOF;
273 x_StartDt := x_FromDt;
274 x_EndDt := x_ToDt;
275 x_Qty := x_ItemQty;
276 x_QtyUOM := x_ItemUOM;
277 --
278 ELSE
279 --
280 RAISE e_UnknownData;
281 --
282 END IF;
283 --
284 IF (l_debug <> -1) THEN
285 rlm_core_sv.dpop(C_SDEBUG);
286 END IF;
287 --
288 EXCEPTION
289 WHEN e_IncompleteData THEN
290 x_ErrCode := x_ErrCode + 1;
291 x_ErrMsg := x_ErrMsg || ' Required data not on schedule';
292 IF (l_debug <> -1) THEN
293 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Required Data not present on schedule');
294 rlm_core_sv.dpop(C_SDEBUG);
295 END IF;
296
297 WHEN e_UnknownData THEN
298 x_ErrCode := x_ErrCode + 1;
299 x_ErrMsg := x_ErrMsg || ' Unknown linetype ''' || x_LineType || '''';
300 IF (l_debug <> -1) THEN
301 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Unknown linetype = '|| x_LineType);
302 rlm_core_sv.dpop(C_SDEBUG);
303 END IF;
304
305 WHEN OTHERS THEN
306 x_ErrCode := x_ErrCode + 1;
307 x_ErrMsg := x_ErrMsg || ' Unknown error in SetSPSILineDetails';
308 IF (l_debug <> -1) THEN
309 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Unknown error in SetSPSILineDetails');
310 rlm_core_sv.dpop(C_SDEBUG);
311 END IF;
312 raise ecx_utils.PROGRAM_EXIT;
313
314 END SetSPSILineDetails;
315
316
317
318 PROCEDURE SetScheduleItemNum(x_HeaderID IN NUMBER,
319 x_ErrCode IN OUT NOCOPY NUMBER,
320 x_ErrMsg IN OUT NOCOPY VARCHAR2) IS
321 --
322 x_GroupRef t_Cursor_ref;
323 x_GroupRec t_ItemAttribsRec;
324 x_SchedItemNum NUMBER := 1;
325 --
326 BEGIN
327 --
328 IF (l_debug <> -1) THEN
329 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.SetScheduleItemNum');
330 END IF;
331 --
332 g_SchedItemTab.DELETE;
333 --
334 InitializeSchedItemTab(x_HeaderID);
335 --
336 PrintSchedItemTab;
337 --
338 IF (l_debug <> -1) THEN
339 rlm_core_sv.dlog(C_DEBUG, '# of rows in g_SchedItemTab ', g_SchedItemTab.COUNT);
340 END IF;
341 --
342 FOR i IN 1..g_SchedItemTab.COUNT LOOP
343 --
344 UPDATE rlm_interface_lines_all
345 SET schedule_item_num = x_SchedItemNum
346 WHERE header_id = x_HeaderID AND
347 NVL(cust_ship_from_org_ext, k_VNULL) = NVL(g_SchedItemTab(i).ship_from_ext, k_VNULL) AND
348 NVL(cust_ship_to_ext, k_VNULL) = NVL(g_SchedItemTab(i).ship_to_ext, k_VNULL) AND
349 NVL(cust_bill_to_ext, k_VNULL) = NVL(g_SchedItemTab(i).bill_to_ext, k_VNULL) AND
350 NVL(customer_item_ext, k_VNULL) = NVL(g_SchedItemTab(i).cust_item_ext, k_VNULL) AND
351 NVL(item_description_ext, k_VNULL) = NVL(g_SchedItemTab(i).item_desc_ext, k_VNULL) AND
352 NVL(customer_dock_code, k_VNULL) = NVL(g_SchedItemTab(i).cust_dock_code, k_VNULL) AND
353 NVL(hazard_code_ext, k_VNULL) = NVL(g_SchedItemTab(i).hazrd_code_ext, k_VNULL) AND
354 NVL(customer_item_revision, k_VNULL) = NVL(g_SchedItemTab(i).cust_item_rev, k_VNULL) AND
355 NVL(item_note_text, k_VNULL) = NVL(g_SchedItemTab(i).item_note_text, k_VNULL) AND
356 NVL(cust_po_number, k_VNULL) = NVL(g_SchedItemTab(i).cust_po_num, k_VNULL) AND
357 NVL(cust_po_line_num, k_VNULL) = NVL(g_SchedItemTab(i).cust_po_linnum, k_VNULL) AND
358 NVL(cust_po_release_num, k_VNULL) = NVL(g_SchedItemTab(i).cust_po_relnum, k_VNULL) AND
359 NVL(cust_po_date, k_DNULL) = NVL(g_SchedItemTab(i).cust_po_date, k_DNULL) AND
360 NVL(commodity_ext, k_VNULL) = NVL(g_SchedItemTab(i).commodity_ext, k_VNULL) AND
361 NVL(supplier_item_ext, k_VNULL) = NVL(g_SchedItemTab(i).sup_item_ext, k_VNULL);
362
363 --4316744: Timezone uptake in RLM.
364 UPDATE rlm_interface_headers_all
365 SET sched_horizon_start_date = TRUNC(sched_horizon_start_date),
366 sched_horizon_end_date = TRUNC(sched_horizon_end_date) + 0.99999
367 WHERE header_id = x_HeaderID;
368 --
369 x_SchedItemNum := x_SchedItemNum + 1;
370 --
371 IF (l_debug <> -1) THEN
372 rlm_core_sv.dlog(C_DEBUG, '# of rows updated', SQL%ROWCOUNT);
373 END IF;
374 --
375 END LOOP;
376 --
377 IF (l_debug <> -1) THEN
378 rlm_core_sv.dpop(C_SDEBUG);
379 END IF;
380 --
381 EXCEPTION
382 --
383 WHEN OTHERS THEN
384 --
385 x_ErrCode := x_ErrCode + 1;
386 x_ErrMsg := x_ErrMsg || ' Unknown error in RLM_XML_API.SetScheduleItemNum';
387 IF (l_debug <> -1) THEN
388 rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Unknown error in SetScheduleItemNum');
389 rlm_core_sv.dpop(C_SDEBUG);
390 END IF;
391 --
392 END SetScheduleItemNum;
393
394
395 PROCEDURE PrintSchedItemTab IS
396 --
397 BEGIN
398 --
399 IF (l_debug <> -1) THEN
400 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.PrintSchedItemTab');
401 END IF;
402 --
403 FOR i in 1..g_SchedItemTab.COUNT LOOP
404 --
405 IF (l_debug <> -1) THEN
406 rlm_core_sv.dlog(C_DEBUG, '--------------------------------------------');
407 rlm_core_sv.dlog(C_DEBUG, 'Index', i);
408 rlm_core_sv.dlog(C_DEBUG,'Ship From', g_SchedItemTab(i).ship_from_ext);
409 rlm_core_sv.dlog(C_DEBUG,'Ship To', g_SchedItemTab(i).ship_to_ext);
410 rlm_core_sv.dlog(C_DEBUG,'Bill To', g_SchedItemTab(i).bill_to_ext);
411 rlm_core_sv.dlog(C_DEBUG,'Cust Item', g_SchedItemTab(i).cust_item_ext);
412 rlm_core_sv.dlog(C_DEBUG,'Item Desc', g_SchedItemTab(i).item_desc_ext);
413 rlm_core_sv.dlog(C_DEBUG,'Dock Code', g_SchedItemTab(i).cust_dock_code);
414 rlm_core_sv.dlog(C_DEBUG,'Haz Code', g_SchedItemTab(i).hazrd_code_ext);
415 rlm_core_sv.dlog(C_DEBUG,'Item Rev', g_SchedItemTab(i).cust_item_rev);
416 rlm_core_sv.dlog(C_DEBUG,'Item Note', g_SchedItemTab(i).item_note_text);
417 rlm_core_sv.dlog(C_DEBUG,'PO Num', g_SchedItemTab(i).cust_po_num);
418 rlm_core_sv.dlog(C_DEBUG,'PO Line Num', g_SchedItemTab(i).cust_po_linnum);
419 rlm_core_sv.dlog(C_DEBUG,'PO Rel Num', g_SchedItemTab(i).cust_po_relnum);
420 rlm_core_sv.dlog(C_DEBUG,'PO Date', g_SchedItemTab(i).cust_po_date);
421 rlm_core_sv.dlog(C_DEBUG,'Sup Item', g_SchedItemTab(i).sup_item_ext);
422 END IF;
423 --
424 END LOOP;
425 --
426 IF (l_debug <> -1) THEN
427 rlm_core_sv.dpop(C_SDEBUG);
428 END IF;
429 --
430 END PrintSchedItemTab;
431
432
433 PROCEDURE InitializeSchedItemTab (x_HeaderID IN NUMBER) IS
434 --
435 CURSOR c_ItemAttribs IS
436 SELECT cust_ship_from_org_ext,
437 cust_ship_to_ext,
438 cust_bill_to_ext,
439 customer_item_ext,
440 item_description_ext,
441 customer_dock_code,
442 hazard_code_ext,
443 customer_item_revision,
444 item_note_text,
445 cust_po_number,
446 cust_po_line_num,
447 cust_po_release_num,
448 cust_po_date,
449 commodity_ext,
450 supplier_item_ext
451 FROM rlm_interface_lines_all
452 WHERE header_id = x_HeaderID;
453 --
454 c_ItemAttribsRec t_ItemAttribsRec;
455 --
456 BEGIN
457 --
458 IF (l_debug <> -1) THEN
459 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.InitializeSchedItemTab');
460 END IF;
461 --
462 OPEN c_ItemAttribs;
463 FETCH c_ItemAttribs INTO c_ItemAttribsRec;
464 --
465 WHILE c_ItemAttribs%FOUND LOOP
466 --
467 IF NOT IsDuplicate(c_ItemAttribsRec) THEN
468 --
469 InsertItemAttribRec(c_ItemAttribsRec);
470 --
471 END IF;
472 --
473 FETCH c_ItemAttribs INTO c_ItemAttribsRec;
474 --
475 END LOOP;
476 --
477 CLOSE c_ItemAttribs; --bug 4570658
478
479 IF (l_debug <> -1) THEN
480 rlm_core_sv.dpop(C_SDEBUG);
481 END IF;
482 --
483 END InitializeSchedItemTab;
484
485
486
487 FUNCTION IsDuplicate(x_ItemAttribsRec IN t_ItemAttribsRec) RETURN BOOLEAN IS
488 --
489 b_Match BOOLEAN;
490 e_NoMatch EXCEPTION;
491 --
492 BEGIN
493 --
494 IF (l_debug <> -1) THEN
495 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.IsDuplicate');
496 END IF;
497 --
498 /*
499 IF (l_debug <> -1) THEN
500 rlm_core_sv.dlog(C_DEBUG, 'Printing attribs rec');
501 rlm_core_sv.dlog(C_DEBUG,'Ship From', x_ItemAttribsRec.ship_from_ext);
502 rlm_core_sv.dlog(C_DEBUG,'Ship To', x_ItemAttribsRec.ship_to_ext);
503 rlm_core_sv.dlog(C_DEBUG,'Bill To', x_ItemAttribsRec.bill_to_ext);
504 rlm_core_sv.dlog(C_DEBUG,'Cust Item', x_ItemAttribsRec.cust_item_ext);
505 rlm_core_sv.dlog(C_DEBUG,'Item Desc', x_ItemAttribsRec.item_desc_ext);
506 rlm_core_sv.dlog(C_DEBUG,'Dock Code', x_ItemAttribsRec.cust_dock_code);
507 rlm_core_sv.dlog(C_DEBUG,'Haz Code', x_ItemAttribsRec.hazrd_code_ext);
508 rlm_core_sv.dlog(C_DEBUG,'Item Rev', x_ItemAttribsRec.cust_item_rev);
509 rlm_core_sv.dlog(C_DEBUG,'Item Note', x_ItemAttribsRec.item_note_text);
510 rlm_core_sv.dlog(C_DEBUG,'PO Num', x_ItemAttribsRec.cust_po_num);
511 rlm_core_sv.dlog(C_DEBUG,'PO Line Num', x_ItemAttribsRec.cust_po_linnum);
512 rlm_core_sv.dlog(C_DEBUG,'PO Rel Num', x_ItemAttribsRec.cust_po_relnum);
513 rlm_core_sv.dlog(C_DEBUG,'PO Date', x_ItemAttribsRec.cust_po_date);
514 rlm_core_sv.dlog(C_DEBUG,'Sup Item', x_ItemAttribsRec.sup_item_ext);
515 END IF;
516 */
517
518 b_Match := FALSE;
519 --
520 IF (g_SchedItemTab.COUNT = 0) THEN
521 --
522 IF (l_debug <> -1) THEN
523 rlm_core_sv.dlog(C_DEBUG, '# of rows in g_SchedItemTab', g_SchedItemTab.COUNT);
524 rlm_core_sv.dpop(C_SDEBUG);
525 END IF;
526 RETURN (FALSE);
527 --
528 END IF;
529 --
530 FOR i IN 1..g_SchedItemTab.COUNT LOOP
531 --
532 BEGIN
533 --
534 IF (NVL(g_SchedItemTab(i).ship_from_ext, k_VNULL) <> NVL(x_ItemAttribsRec.ship_from_ext, k_VNULL)) THEN
535 IF (l_debug <> -1) THEN
536 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','SF did not match with entry ' || i);
537 END IF;
538 RAISE e_NoMatch;
539 END IF;
540
541 IF (NVL(g_SchedItemTab(i).ship_to_ext, k_VNULL) <> NVL(x_ItemAttribsRec.ship_to_ext, k_VNULL)) THEN
542 IF (l_debug <> -1) THEN
543 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','ST did not match with entry ' || i);
544 END IF;
545 RAISE e_NoMatch;
546 END IF;
547
548 IF (NVL(g_SchedItemTab(i).bill_to_ext, k_VNULL) <> NVL(x_ItemAttribsRec.bill_to_ext, k_VNULL)) THEN
549 IF (l_debug <> -1) THEN
550 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','BT did not match');
551 END IF;
552 RAISE e_NoMatch;
553 END IF;
554
555 IF (NVL(g_SchedItemTab(i).cust_item_ext, k_VNULL) <> NVL(x_ItemAttribsRec.cust_item_ext, k_VNULL)) THEN
556 IF (l_debug <> -1) THEN
557 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','CI did not match with entry ' || i);
558 END IF;
559 RAISE e_NoMatch;
560 END IF;
561
562 IF (NVL(g_SchedItemTab(i).item_desc_ext, k_VNULL) <> NVL(x_ItemAttribsRec.item_desc_ext, k_VNULL)) THEN
563 IF (l_debug <> -1) THEN
564 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','Desc did not match with entry ' || i);
565 END IF;
566 RAISE e_NoMatch;
567 END IF;
568
569 IF (NVL(g_SchedItemTab(i).cust_dock_code, k_VNULL) <> NVL(x_ItemAttribsRec.cust_dock_code, k_VNULL)) THEN
570 IF (l_debug <> -1) THEN
571 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','Dock did not match with entry ' || i);
572 END IF;
573 RAISE e_NoMatch;
574 END IF;
575
576 IF (NVL(g_SchedItemTab(i).hazrd_code_ext, k_VNULL) <> NVL(x_ItemAttribsRec.hazrd_code_ext, k_VNULL)) THEN
577 IF (l_debug <> -1) THEN
578 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','Haz did not match with entry ' || i);
579 END IF;
580 RAISE e_NoMatch;
581 END IF;
582
583 IF (NVL(g_SchedItemTab(i).cust_item_rev, k_VNULL) <> NVL(x_ItemAttribsRec.cust_item_rev, k_VNULL)) THEN
584 IF (l_debug <> -1) THEN
585 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','Item rev did not match with entry ' || i);
586 END IF;
587 RAISE e_NoMatch;
588 END IF;
589
590 IF (NVL(g_SchedItemTab(i).item_note_text, k_VNULL) <> NVL(x_ItemAttribsRec.item_note_text, k_VNULL)) THEN
591 IF (l_debug <> -1) THEN
592 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','Note did not match with entry ' || i);
593 END IF;
594 RAISE e_NoMatch;
595 END IF;
596
597 IF (NVL(g_SchedItemTab(i).cust_po_num, k_VNULL) <> NVL(x_ItemAttribsRec.cust_po_num, k_VNULL)) THEN
598 IF (l_debug <> -1) THEN
599 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','PO Num did not match with entry ' || i);
600 END IF;
601 RAISE e_NoMatch;
602 END IF;
603
604 IF (NVL(g_SchedItemTab(i).cust_po_linnum, k_VNULL) <> NVL(x_ItemAttribsRec.cust_po_linnum, k_VNULL)) THEN
605 IF (l_debug <> -1) THEN
606 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','POLnum did not match with entry ' || i);
607 END IF;
608 RAISE e_NoMatch;
609 END IF;
610
611 IF (NVL(g_SchedItemTab(i).cust_po_relnum, k_VNULL) <> NVL(x_ItemAttribsRec.cust_po_relnum, k_VNULL)) THEN
612 IF (l_debug <> -1) THEN
613 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','PORel did not match with entry ' || i);
614 END IF;
615 RAISE e_NoMatch;
616 END IF;
617
618 IF (NVL(g_SchedItemTab(i).commodity_ext, k_VNULL) <> NVL(x_ItemAttribsRec.commodity_ext, k_VNULL)) THEN
619 IF (l_debug <> -1) THEN
620 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','commodity did not match with entry ' || i);
621 END IF;
622 RAISE e_NoMatch;
623 END IF;
624
625 IF (NVL(g_SchedItemTab(i).sup_item_ext, k_VNULL) <> NVL(x_ItemAttribsRec.sup_item_ext, k_VNULL)) THEN
626 IF (l_debug <> -1) THEN
627 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','Sup item did not match with entry ' || i);
628 END IF;
629 RAISE e_NoMatch;
630 END IF;
631
632 IF (NVL(g_SchedItemTab(i).cust_po_date, k_DNULL) <> NVL(x_ItemAttribsRec.cust_po_date, k_DNULL)) THEN
633 IF (l_debug <> -1) THEN
634 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','PO Date did not match with entry ' || i);
635 END IF;
636 RAISE e_NoMatch;
637 END IF;
638
639 b_Match := TRUE;
640 IF (l_debug <> -1) THEN
641 rlm_core_sv.dlog(C_DEBUG,'RLM_MSG','All attributes matched with entry ' || i);
642 END IF;
643 EXIT;
644
645 EXCEPTION
646 --
647 WHEN e_NoMatch THEN
648 NULL;
649 --
650 END;
651 --
652 END LOOP;
653 --
654 IF (l_debug <> -1) THEN
655 rlm_core_sv.dlog(C_DEBUG, 'b_Match', b_Match);
656 rlm_core_sv.dpop(C_SDEBUG);
657 END IF;
658 RETURN (b_Match);
659 --
660 END IsDuplicate;
661
662
663 PROCEDURE InsertItemAttribRec(x_ItemAttribsRec IN t_ItemAttribsRec) IS
664 --
665 v_Index NUMBER;
666 --
667 BEGIN
668 --
669 IF (l_debug <> -1) THEN
670 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.InsertItemAttribs');
671 END IF;
672 --
673 v_Index := g_SchedItemTab.COUNT;
674 --
675 g_SchedItemTab(v_Index+1).ship_from_ext := x_ItemAttribsRec.ship_from_ext;
676 g_SchedItemTab(v_Index+1).ship_to_ext := x_ItemAttribsRec.ship_to_ext;
677 g_SchedItemTab(v_Index+1).bill_to_ext := x_ItemAttribsRec.bill_to_ext;
678 g_SchedItemTab(v_Index+1).cust_item_ext := x_ItemAttribsRec.cust_item_ext;
679 g_SchedItemTab(v_Index+1).item_desc_ext := x_ItemAttribsRec.item_desc_ext;
680 g_SchedItemTab(v_Index+1).cust_dock_code := x_ItemAttribsRec.cust_dock_code;
681 g_SchedItemTab(v_Index+1).hazrd_code_ext := x_ItemAttribsRec.hazrd_code_ext;
682 g_SchedItemTab(v_Index+1).cust_item_rev := x_ItemAttribsRec.cust_item_rev;
683 g_SchedItemTab(v_Index+1).item_note_text := x_ItemAttribsRec.item_note_text;
684 g_SchedItemTab(v_Index+1).cust_po_num := x_ItemAttribsRec.cust_po_num;
685 g_SchedItemTab(v_Index+1).cust_po_linnum := x_ItemAttribsRec.cust_po_linnum;
686 g_SchedItemTab(v_Index+1).cust_po_relnum := x_ItemAttribsRec.cust_po_relnum;
687 g_SchedItemTab(v_Index+1).cust_po_date := x_ItemAttribsRec.cust_po_date;
688 g_SchedItemTab(v_Index+1).commodity_ext := x_ItemAttribsRec.commodity_ext;
689 g_SchedItemTab(v_Index+1).sup_item_ext := x_ItemAttribsRec.sup_item_ext;
690 --
691 IF (l_debug <> -1) THEN
692 rlm_core_sv.dpop(C_SDEBUG);
693 END IF;
694 --
695 END InsertItemAttribRec;
696
697
698 PROCEDURE UpdateLineNumbers(x_HeaderID IN NUMBER,
699 x_ErrCode IN OUT NOCOPY NUMBER,
700 x_ErrMsg IN OUT NOCOPY VARCHAR2) IS
701 --
702 v_linenumber NUMBER := 1;
703 v_lineid NUMBER;
704 --
705 CURSOR c_Lines IS
706 SELECT line_id
707 FROM rlm_interface_lines_all
708 WHERE header_id = x_HeaderID
709 ORDER BY schedule_item_num;
710 --
711 BEGIN
712 --
713 IF (l_debug <> -1) THEN
714 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.UpdateLineNumbers');
715 rlm_core_sv.dlog(C_DEBUG, 'x_HeaderID', x_HeaderID);
716 END IF;
717 --
718 OPEN c_Lines;
719 FETCH c_Lines INTO v_lineid;
720 --
721 WHILE c_Lines%FOUND LOOP
722 --
723 UPDATE rlm_interface_lines_all
724 SET line_number = v_linenumber
725 WHERE line_id = v_lineid;
726 --
727 v_linenumber := v_linenumber + 1;
728 --
729 FETCH c_Lines INTO v_lineid;
730 --
731 END LOOP;
732 CLOSE c_lines; --bug 4570658
733 --
734 IF (l_debug <> -1) THEN
735 rlm_core_sv.dpop(C_SDEBUG);
736 rlm_core_sv.stop_debug;
737 END IF;
738 --
739 EXCEPTION
740 --
741 WHEN OTHERS THEN
742 --
743 x_ErrCode := x_ErrCode + 1;
744 x_ErrMsg := x_ErrMsg || ' Unknown error in UpdateLineNumbers';
745 IF (l_debug <> -1) THEN
746 rlm_core_sv.dlog(C_DEBUG,'RLM_ERROR', 'Unknown Error in UpdateLineNumbers');
747 rlm_core_sv.dpop(C_SDEBUG);
748 rlm_core_sv.stop_debug;
749 END IF;
750 --
751 END UpdateLineNumbers;
752
753
754 PROCEDURE FlexBktAssignment(x_header_id IN NUMBER,
755 x_ErrCode IN OUT NOCOPY NUMBER,
756 x_ErrMsg IN OUT NOCOPY VARCHAR2)
757 IS
758 --
759 v_lineID NUMBER;
760 v_line_ID NUMBER;
761 v_flexbkt VARCHAR2(30);
762 v_flexbktcode VARCHAR2(30);
763 v_start_date DATE;
764 v_end_date DATE;
765 --
766 CURSOR c_Line
767 IS
768 SELECT line_id, flex_bkt_code
769 FROM rlm_interface_lines_all
770 WHERE header_id = x_header_id
771 AND flex_bkt_flag is null;
772 --
773 CURSOR c_Bkt
774 IS
775 SELECT line_id, flex_bkt_code, start_date_time, end_date_time
776 FROM rlm_interface_lines_all
777 WHERE header_id = x_header_id
778 AND flex_bkt_flag = 'Y';
779 --
780 BEGIN
781 --
782 IF (l_debug <> -1) THEN
783 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.FlexBktAssignment');
784 rlm_core_sv.dlog(C_DEBUG, 'x_HeaderID', x_header_id);
785 END IF;
786 --
787 OPEN c_Line;
788 FETCH c_Line INTO v_lineID, v_flexbkt;
789 --
790 WHILE c_Line%FOUND LOOP
791 --
792 OPEN c_Bkt;
793 FETCH c_Bkt INTO v_line_ID, v_flexbktcode, v_start_date, v_end_date;
794 --
795 IF (l_debug <> -1) THEN
796 rlm_core_sv.dlog(C_DEBUG, 'BKTYPE', v_flexbkt);
797 END IF;
798 --
799 WHILE c_Bkt%FOUND LOOP
800 --
801 IF (l_debug <> -1) THEN
802 rlm_core_sv.dlog(C_DEBUG, 'FLEXBKTID', v_flexbktcode);
803 END IF;
804 --
805 IF v_flexbktcode = v_flexbkt THEN
806 --
807 IF (l_debug <> -1) THEN
808 rlm_core_sv.dlog(C_DEBUG, 'Match between FLEXBKTID and BKTYPE for line', v_lineID);
809 END IF;
810 --
811 UPDATE rlm_interface_lines_all
812 SET start_date_time = v_start_date,
813 end_date_time = v_end_date
814 WHERE line_id = v_lineID;
815 --
816 IF v_flexbkt NOT IN ('1', '2', '4', '5', k_RECEIPT, k_SHIPMENT) THEN
817 --
818 IF (l_debug <> -1) THEN
819 rlm_core_sv.dlog(C_DEBUG, 'v_flexbkt', v_flexbkt);
820 rlm_core_sv.dlog(C_DEBUG, 'Replacing ' || v_flexbkt || ' with FLEXIBLE');
821 END IF;
822 --
823 UPDATE rlm_interface_lines_all
824 SET item_detail_subtype = '3'
825 WHERE line_id = v_lineID;
826 --
827 END IF;
828 --
829 EXIT;
830 --
831 END IF;
832 --
833 FETCH c_Bkt INTO v_line_ID, v_flexbktcode, v_start_date, v_end_date;
834 --
835 END LOOP;
836 --
837 CLOSE c_Bkt;
838 --
839 FETCH c_Line INTO v_lineID, v_flexbkt;
840 --
841 END LOOP;
842 --
843 CLOSE c_Line;
844 --
845 DELETE FROM rlm_interface_lines_all
846 WHERE header_id = x_header_id
847 AND flex_bkt_flag = 'Y';
848 --
849 IF (l_debug <> -1) THEN
850 rlm_core_sv.dpop(C_SDEBUG);
851 END IF;
852 --
853 EXCEPTION
854 --
855 WHEN OTHERS THEN
856 --
857 x_ErrCode := x_ErrCode + 1;
858 x_ErrMsg := x_ErrMsg || ' Unknown error in RLM_XML_API.FlexBktAssignment';
859 IF (l_debug <> -1) THEN
860 rlm_core_sv.dlog(C_DEBUG,'RLM_ERROR', 'Unknown Error in FlexBktAssignment');
861 rlm_core_sv.dpop(C_SDEBUG);
862 END IF;
863
864 END FlexBktAssignment;
865
866
867 --MOAC changes: Added the following procedure
868
869 Procedure GetDefaultOU(x_default_ou IN OUT NOCOPY NUMBER,
870 x_ErrCode IN OUT NOCOPY NUMBER,
871 x_ErrMsg IN OUT NOCOPY VARCHAR2) IS
872 --
873 l_default_org_id NUMBER;
874 --
875 BEGIN
876 --
877 IF (l_debug <> -1) THEN
878 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.GetDefaultOU');
879 END IF;
880 --
881 FND_PROFILE.GET('DEFAULT_ORG_ID', l_default_org_id);
882 --
883 IF l_default_org_id IS NULL THEN
884 X_ErrCode := x_ErrCode+1;
885 X_ErrMsg := 'Cannot process schedule because the Default Operating Unit has not been defined';
886 X_default_ou := NULL;
887 ELSE
888 x_default_ou := l_default_org_id;
889 END IF;
890 --
891 IF (l_debug <> -1) THEN
892 rlm_core_sv.dlog(C_DEBUG, 'Default Org ID', x_default_ou);
893 rlm_core_sv.dpop(C_SDEBUG);
894 END IF;
895 --
896 EXCEPTION
897 WHEN OTHERS THEN
898 X_ErrCode := x_ErrCode + 1;
899 X_ErrMsg := 'Unknown error when determing Default Operating Unit';
900 --
901 IF (l_debug <> -1) THEN
902 Rlm_core_sv.dlog(C_DEBUG, 'RLM_ERROR', 'Unspecified error when Deriving OU');
903 Rlm_core_sv.dpop(C_SDEBUG);
904 END IF;
905 END GetDefaultOU;
906
907
908
909 FUNCTION DeriveExtProcessID(p_msgStd IN VARCHAR2,
910 p_txnType IN VARCHAR2,
911 p_txnSubtype IN VARCHAR2) RETURN NUMBER IS
912 --
913 v_StdID NUMBER;
914 v_ExtProcessID NUMBER;
915 v_Direction VARCHAR2(20) := 'IN';
916 v_stdType VARCHAR2(30) := 'XML';
917 --
918 BEGIN
919 --
920 IF (l_debug <> -1) THEN
921 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.DeriveExtProcessID');
922 rlm_core_sv.dlog(C_DEBUG, 'Msg Std', p_msgStd);
923 rlm_core_sv.dlog(C_DEBUG, 'Txn Type', p_txnType);
924 rlm_core_sv.dlog(C_DEBUG, 'Txn Subtype', p_txnSubtype);
925 rlm_core_sv.dlog(C_DEBUG, 'Direction', v_Direction);
926 rlm_core_sv.dlog(C_DEBUG, 'Std Type', v_stdType);
927 END IF;
928 --
929 SELECT standard_id
930 INTO v_StdID
931 FROM ecx_standards_b
932 WHERE standard_code = p_msgStd
933 AND standard_type = v_stdType;
934 --
935 IF (l_debug <> -1) THEN
936 rlm_core_sv.dlog(C_DEBUG, 'Standard ID', v_StdID);
937 END IF;
938 --
939 SELECT ext_process_id
940 INTO v_ExtProcessID
941 FROM ecx_ext_processes
942 WHERE standard_id = v_StdID
943 AND ext_type = p_txnType
944 AND ext_subtype = p_txnSubtype
945 AND direction = v_Direction;
946 --
947 IF (l_debug <> -1) THEN
948 rlm_core_sv.dlog(C_DEBUG, 'Ext Process ID', v_ExtProcessID);
949 rlm_core_sv.dpop(C_SDEBUG);
950 END IF;
951 --
952 RETURN v_ExtProcessID;
953 --
954 EXCEPTION
955 --
956 WHEN OTHERS THEN
957 --
958 IF (l_debug <> -1) THEN
959 rlm_core_sv.dlog(C_DEBUG, 'Unexpected error', SUBSTRB(SQLERRM, 1, 200));
960 rlm_core_sv.dpop(C_SDEBUG);
961 END IF;
962 --
963 RAISE;
964 --
965 END DeriveExtProcessID;
966
967
968
969 PROCEDURE DeriveCustomerId(x_internalcontrolNum IN NUMBER,
970 x_SourceTPLocCode OUT NOCOPY VARCHAR2,
971 x_CustomerId OUT NOCOPY NUMBER,
972 x_ErrCode IN OUT NOCOPY NUMBER,
973 x_ErrMsg IN OUT NOCOPY VARCHAR2) IS
974 --
975 v_msgType VARCHAR2(100);
976 v_msgStd VARCHAR2(100);
977 v_TxnType VARCHAR2(100);
978 v_TxnsubType VARCHAR2(100);
979 v_DocNum VARCHAR2(256);
980 v_PartyId VARCHAR2(256);
981 v_SourceTPLocCode VARCHAR2(256);
982 v_protocolType VARCHAR2(500);
983 v_protocolAdd VARCHAR2(2000);
984 v_userName VARCHAR2(500);
985 v_Passwd VARCHAR2(500);
986 v_attrib1 VARCHAR2(500);
987 v_attrib2 VARCHAR2(500);
988 v_attrib3 VARCHAR2(500);
989 v_attrib4 VARCHAR2(500);
990 v_attrib5 VARCHAR2(500);
991 v_ErrCode VARCHAR2(100);
992 v_ErrMsg VARCHAR2(100);
993 --
994 v_tpheaderID NUMBER;
995 v_CustacctSiteId NUMBER;
996 v_CustAccountId NUMBER;
997 v_Party_Id NUMBER;
998 v_PartySiteId NUMBER;
999 v_ExtProcessID NUMBER;
1000 --
1001 BEGIN
1002 --
1003 IF (l_debug <> -1) THEN
1004 rlm_core_sv.dpush(C_SDEBUG, 'RLM_XML_API.DeriveCustomerId');
1005 rlm_core_sv.dlog(C_DEBUG, 'Internal Control Num', x_internalControlNum);
1006 END IF;
1007 --
1008 ECX_TRADING_PARTNER_PVT.getEnvelopeInformation
1009 (
1010 i_internal_control_number => x_internalControlNum,
1011 i_message_type => v_msgtype,
1012 i_message_standard => v_msgStd,
1013 i_transaction_type => v_Txntype,
1014 i_transaction_subtype => v_txnSubtype,
1015 i_document_number => v_docnum,
1016 i_party_id => v_partyId,
1017 i_party_site_id => v_SourceTPLocCode,
1018 i_protocol_type => v_protocolType,
1019 i_protocol_address => v_protocolAdd,
1020 i_username => v_Username,
1021 i_password => v_Passwd,
1022 i_attribute1 => v_attrib1,
1023 i_attribute2 => v_attrib2,
1024 i_attribute3 => v_attrib3,
1025 i_attribute4 => v_attrib4,
1026 i_attribute5 => v_attrib5,
1027 retcode => v_ErrCode,
1028 retmsg => v_ErrMsg
1029 );
1030 --
1031 IF (l_debug <> -1) THEN
1032 rlm_core_sv.dlog(C_DEBUG, 'Party ID', v_partyId);
1033 rlm_core_sv.dlog(C_DEBUG, 'Source TP Location Code', v_SourceTPLocCode);
1034 rlm_core_sv.dlog(C_DEBUG, 'Protocal Type', v_protocolType);
1035 rlm_core_sv.dlog(C_DEBUG, 'Protocol Add', v_ProtocolAdd);
1036 rlm_core_sv.dlog(C_DEBUG, 'User name', v_Username);
1037 rlm_core_Sv.dlog(C_DEBUG, 'Password', v_Passwd);
1038 rlm_core_sv.dlog(C_DEBUG, 'Attrib1', v_attrib1);
1039 rlm_core_sv.dlog(C_DEBUG, 'Attrib2', v_attrib2);
1040 rlm_core_sv.dlog(C_DEBUG, 'Attrib3', v_attrib3);
1041 rlm_core_sv.dlog(C_DEBUG, 'Attrib4', v_attrib4);
1042 rlm_core_sv.dlog(C_DEBUG, 'Attrib5', v_attrib5);
1043 rlm_core_sv.dlog(C_DEBUG, 'Error Code', v_ErrCode);
1044 rlm_core_sv.dlog(C_DEBUG, 'Error Msg', v_Errmsg);
1045 rlm_core_sv.dlog(C_DEBUG, 'Txn Type', v_Txntype);
1046 rlm_core_sv.dlog(C_DEBUG, 'Txn Subtype', v_txnSubtype);
1047 rlm_core_sv.dlog(C_DEBUG, 'Msg Type', v_msgtype);
1048 rlm_core_sv.dlog(C_DEBUG, 'Doc Number', v_docnum);
1049 rlm_core_sv.dlog(C_DEBUG, 'Msg Std', v_msgStd);
1050 END IF;
1051 --
1052 x_SourceTPLocCode := v_SourceTPLocCode;
1053 v_ExtProcessID := DeriveExtProcessID(v_msgStd, v_Txntype, v_txnSubtype);
1054 --
1055 IF (l_debug <> -1) THEN
1056 rlm_core_sv.dlog(C_DEBUG, 'Ext Process ID', v_ExtProcessID);
1057 END IF;
1058 --
1059 SELECT party_site_id, party_id
1060 INTO v_PartySiteID, v_party_id
1061 FROM ecx_tp_headers
1062 WHERE tp_header_id IN (SELECT DISTINCT tp_header_id
1063 FROM ecx_tp_details
1064 WHERE source_tp_location_code = v_SourceTPLocCode
1065 AND ext_process_id = v_ExtProcessID);
1066 --
1067 IF (l_debug <> -1) THEN
1068 rlm_core_sv.dlog(C_DEBUG, 'Party Site ID', v_partySiteID);
1069 rlm_core_sv.dlog(C_DEBUG, 'Party ID', v_party_ID);
1070 END IF;
1071 --
1072 SELECT cust_acct_site_id, cust_account_id
1073 INTO v_CustacctSiteId, v_CustAccountId
1074 FROM hz_cust_acct_sites_all
1075 WHERE party_site_id = v_PartySiteID
1076 AND ece_tp_location_code = v_SourceTPLocCode;
1077 --
1078 x_CustomerId := v_CustAccountId;
1079 --
1080 IF (l_debug <> -1) THEN
1081 rlm_core_sv.dlog(C_DEBUG, 'Customer Site ID', v_CustacctSiteId);
1082 rlm_core_sv.dlog(C_DEBUG, 'Customer ID', v_CustAccountId);
1083 rlm_core_sv.dpop(C_SDEBUG);
1084 END IF;
1085 --
1086 EXCEPTION
1087 --
1088 WHEN OTHERS THEN
1089 --
1090 x_SourceTPLocCode := NULL;
1091 x_ErrCode := x_ErrCode + 1;
1092 x_ErrMsg := x_ErrMsg || 'error in RLM_XML_API.DeriveCustomerId' || SUBSTRB(SQLERRM, 1, 200);
1093 --
1094 IF (l_debug <> -1) THEN
1095 rlm_core_sv.dlog(C_DEBUG,'RLM_ERROR', 'Unknown Error in RLM_XML_API.DeriveCustomerId');
1096 rlm_core_sv.dlog(C_DEBUG, 'Exception when others',
1097 SUBSTRB(SQLERRM, 1, 200));
1098 rlm_core_sv.dpop(C_SDEBUG);
1099 END IF;
1100 --
1101 END DeriveCustomerId;
1102
1103 END RLM_XML_API;