[Home] [Help]
PACKAGE BODY: APPS.OE_MESSAGE_PURGE_PVT
Source
1 PACKAGE BODY OE_MESSAGE_PURGE_PVT AS
2 /* $Header: OEXMPRGB.pls 120.4 2005/11/09 23:30:12 ssurapan noship $ */
3
4 /* ---------------------------------------------------------------
5 -- Start of Comments
6 -- API name OE_MESSAGE_PURGE_PVT
7 -- Type Private
8 -- Function
9 -- Pre-reqs
10 -- Parameters
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13 -- Notes
14 --
15 -- End of Comments
16 ------------------------------------------------------------------ */
17
18 /* -----------------------------------------------------------
19 Procedure: Purge
20 ----------------------------------------------------------- */
21 PROCEDURE PURGE(
22 errbuf OUT NOCOPY VARCHAR2
23
24 ,retcode OUT NOCOPY NUMBER
25
26 ,p_commit IN NUMBER DEFAULT 500
27 ,p_start_date IN VARCHAR2
28 ,p_end_date IN VARCHAR2
29 ,p_message_source IN VARCHAR2
30 ,p_customer_id_name IN NUMBER
31 ,p_customer_id_number IN NUMBER
32 ,p_order_type_id IN NUMBER
33 ,p_start_order_num IN NUMBER
34 ,p_end_order_num IN NUMBER
35 ,p_message_status_code IN VARCHAR2 DEFAULT NULL) IS
36
37 l_start DATE := null;
38 l_end DATE := null;
39
40 CURSOR c_messages IS
41 SELECT m.transaction_id,
42 m.rowid,
43 o.order_number,
44 o.order_type_id,
45 o.sold_to_org_id,
46 m.message_status_code
47 FROM oe_processing_msgs m, oe_order_headers_all o
48 WHERE m.header_id = o.header_id (+)
49 AND NVL(m.message_source_code, 'NULL') =
50 NVL(p_message_source, NVL(m.message_source_code, 'NULL'))
51 AND TRUNC(m.creation_date) BETWEEN NVL(l_start, TRUNC(m.creation_date))
52 AND NVL(l_end, TRUNC(m.creation_date));
53
54 l_cnt NUMBER := 0;
55 l_commit NUMBER;
56 l_customer NUMBER := NULL;
57 l_debug_file VARCHAR2(500);
58
59 --
60 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
61 --
62 BEGIN
63 --Bug#4220950
64 errbuf := '';
65 retcode := 0;
66
67 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Order Management Message Purge Concurrent Program');
68 FND_FILE.Put_Line(FND_FILE.OUTPUT, '');
69 l_debug_file := OE_DEBUG_PUB.set_debug_mode ('FILE');
70 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Debug File: ' || l_debug_file);
71 FND_FILE.Put_Line(FND_FILE.OUTPUT, '');
72
73 IF l_debug_level > 0 THEN
74 oe_debug_pub.add( 'ENTER MESSAGE PURGE' ) ;
75 END IF;
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'P_COMMIT:'||TO_CHAR ( P_COMMIT ) ||'.' ) ;
78 END IF;
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add( 'P_START_DATE:'||P_START_DATE||'.' ) ;
81 END IF;
82 IF l_debug_level > 0 THEN
83 oe_debug_pub.add( 'P_END_DATE:'||P_END_DATE||'.' ) ;
84 END IF;
85 IF l_debug_level > 0 THEN
86 oe_debug_pub.add( 'P_MESSAGE_SOURCE:'||P_MESSAGE_SOURCE||'.' ) ;
87 END IF;
88 IF l_debug_level > 0 THEN
89 oe_debug_pub.add( 'P_CUSTOMER_ID_NAME:'||P_CUSTOMER_ID_NAME||'.' ) ;
90 END IF;
91 IF l_debug_level > 0 THEN
92 oe_debug_pub.add( 'P_CUSTOMER_ID_NUMBER:'||P_CUSTOMER_ID_NUMBER||'.' ) ;
93 END IF;
94 IF l_debug_level > 0 THEN
95 oe_debug_pub.add( 'P_ORDER_TYPE_ID:'||TO_CHAR ( P_ORDER_TYPE_ID ) ||'.' ) ;
96 END IF;
97 IF l_debug_level > 0 THEN
98 oe_debug_pub.add( 'P_START_ORDER_NUM:'||TO_CHAR ( P_START_ORDER_NUM ) ||'.' ) ;
99 END IF;
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'P_END_ORDER_NUM:'||TO_CHAR ( P_END_ORDER_NUM ) ||'.' ) ;
102 END IF;
103
104 l_start := TRUNC(TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS'));
105 l_end := TRUNC(TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS'));
106 IF p_commit > 0 THEN
107 l_commit := p_commit;
108 ELSE l_commit := NULL;
109 END IF;
110
111 FOR c_msg IN c_messages LOOP
112 IF (p_customer_id_name IS NULL OR p_customer_id_name = c_msg.sold_to_org_id) AND
113 (p_customer_id_number IS NULL OR p_customer_id_number = c_msg.sold_to_org_id) AND
114 (p_order_type_id IS NULL OR p_order_type_id = c_msg.order_type_id) AND
115 (p_start_order_num IS NULL OR p_start_order_num <= c_msg.order_number) AND
116 (p_end_order_num IS NULL OR p_end_order_num >= c_msg.order_number) AND
117 (p_message_status_code IS NULL OR p_message_status_code = nvl(c_msg.message_status_code,'OPEN')) THEN
118 IF l_cnt >= l_commit THEN
119 COMMIT;
120 l_cnt := 0;
121 END IF;
122 DELETE oe_processing_msgs_tl
123 WHERE transaction_id = c_msg.transaction_id;
124 DELETE oe_processing_msgs
125 WHERE ROWID = c_msg.rowid;
126 l_cnt := l_cnt + 1;
127 END IF;
128 END LOOP;
129
130 COMMIT;
131
132 IF l_debug_level > 0 THEN
133 oe_debug_pub.add( 'EXIT MESSAGE PURGE' ) ;
134 END IF;
135 FND_FILE.Put_Line(FND_FILE.OUTPUT, '');
136 FND_FILE.Put_Line(FND_FILE.OUTPUT,'End of Message Purge Concurrent Program');
137
138
139 END Purge;
140
141 END OE_MESSAGE_PURGE_PVT;