1 PACKAGE BODY OE_EXPORT_COMPLIANCE_WF as
2 /* $Header: OEXWECSB.pls 120.3 2007/11/13 04:27:58 prpathak ship $ */
3
4 PROCEDURE ECS_Request(
5 itemtype in varchar2,
6 itemkey in varchar2,
7 actid in number,
8 funcmode in varchar2,
9 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
10 IS
11 l_header_id NUMBER;
12 l_return_status VARCHAR2(30);
13 l_msg_count NUMBER;
14 l_msg_data VARCHAR2(2000);
15 l_line_rec OE_Order_PUB.Line_Rec_Type;
16 l_old_line_rec OE_Order_PUB.Line_Rec_Type;
17 p_line_rec OE_Order_PUB.Line_Rec_Type;
18 l_call_appl_id NUMBER;
19 l_org_id NUMBER;
20 l_organization_id NUMBER;
21 l_result_out VARCHAR2(100);
22 l_top_model_line_id NUMBER;
23 l_dummy VARCHAR2(2);
24
25 --
26 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
27 --
28 BEGIN
29
30 --
31 -- RUN mode - normal process execution
32 --
33 if (funcmode = 'RUN') then
34
35 OE_MSG_PUB.set_msg_context(
36 p_entity_code => 'LINE'
37 ,p_entity_id => to_number(itemkey)
38 ,p_line_id => to_number(itemkey));
39
40 IF l_debug_level > 0 THEN
41 oe_debug_pub.add( 'WITHIN DENIED PARTY WORKFLOW COVER ' ) ;
42 END IF;
43 IF l_debug_level > 0 THEN
44 oe_debug_pub.add( 'ITEM KEY IS ' || ITEMKEY ) ;
45 END IF;
46 OE_STANDARD_WF.Set_Msg_Context(actid);
47
48 SAVEPOINT Before_Lock;
49
50 select top_model_line_id into l_top_model_line_id
51 from oe_order_lines_all where
52 line_id = to_number(itemkey);
53
54 -- bug 4503620
55 BEGIN
56 IF l_top_model_line_id is not null then
57 select '1' into l_dummy
58 from oe_order_lines_all
59 where line_id = l_top_model_line_id
60 for update; --commented for bug 6415831 -- nowait;
61 END IF;
62 EXCEPTION
63 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
64 IF l_debug_level > 0 THEN
65 oe_debug_pub.add('OEXWECSB.pls: unable to lock the line:'||l_top_model_line_id,1);
66 END IF;
67 resultout := 'COMPLETE:INCOMPLETE';
68 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
69 fnd_message.set_name('ONT', 'OE_LINE_LOCKED');
70 OE_MSG_PUB.Add;
71 END IF;
72 OE_STANDARD_WF.Save_Messages;
73 OE_STANDARD_WF.Clear_Msg_Context;
74 return;
75 END; -- bug 4503620 ends
76
77 OE_Line_Util.Lock_Row
78 (p_line_id => to_number(itemkey),
79 p_x_line_rec => l_line_rec,
80 x_return_status => l_return_status);
81
82 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
83 IF l_debug_level > 0 THEN
84 oe_debug_pub.add( 'LOCK ROW RETURNED WITH ERROR' , 1 ) ;
85 END IF;
86 resultout := 'COMPLETE:INCOMPLETE';
87 OE_STANDARD_WF.Save_Messages;
88 OE_STANDARD_WF.Clear_Msg_Context;
89 return;
90 END IF;
91
92 OE_MSG_PUB.set_msg_context(
93 p_entity_code => 'LINE'
94 ,p_entity_id => l_line_rec.line_id
95 ,p_header_id => l_line_rec.header_id
96 ,p_line_id => l_line_rec.line_id
97 ,p_order_source_id => l_line_rec.order_source_id
98 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
99 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
100 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
101 ,p_change_sequence => l_line_rec.change_sequence
102 ,p_source_document_type_id => l_line_rec.source_document_type_id
103 ,p_source_document_id => l_line_rec.source_document_id
104 ,p_source_document_line_id => l_line_rec.source_document_line_id );
105
106 /* Here we call the procedure to populate the data into the interface tables.
107 After populating the interface tables we need to check invoke the Clearcross
108 Adapter which processes the data in the interface table.
109 If there is no data in the Response table then it means that the
110 Clearcross Adapter has not got the result.
111 This may be due to any reason like:
112 (a) Technicall Error:
113 This may be due to any technical error which has come up and this may include
114 problems like Network Error,Server not responding and so on.
115 (b) Functional Error:
116 This may be due to error in the Clearcross adapter.
117
118 */
119
120 -- This is the procedure which calls the Procedure for populating
121 -- the data into the Transaction tables.
122
123 -- This is the procedure which calls the Procedure for populating the data
124 -- into the Transaction tables.
125
126 ONT_ITM_PKG.Process_ITM_REQUEST(
127 p_line_rec => l_line_rec
128 ,x_return_status => l_return_status
129 ,x_result_out => l_result_out);
130
131
132 IF l_debug_level > 0 THEN
133 oe_debug_pub.add( 'AFTER PROCESSING THE REQUEST :'||L_RETURN_STATUS ) ;
134 END IF;
135
136
137 -- If error is generated by the Insert Procedure then we need to
138 -- capture the that error and do not allow the Workflow to process
139 -- further.
140 --
141 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
142 IF l_result_out = OE_GLOBALS.G_WFR_COMPLETE THEN
143 resultout := 'NOTIFIED';
144 OE_STANDARD_WF.Save_Messages;
145 OE_STANDARD_WF.Clear_Msg_Context;
146 RETURN;
147 END IF;
148 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
149 resultout := 'COMPLETE:INCOMPLETE';
150 OE_STANDARD_WF.Save_Messages;
151 OE_STANDARD_WF.Clear_Msg_Context;
152 RETURN;
153 ELSE
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157 RETURN;
158
159 END IF; -- End for 'RUN' mode
160
161 --
162 -- CANCEL mode - activity 'compensation'
163 --
164
165 -- This is an event point called with the effect of the activity
166 -- be undone, for example when a process is reset to an earlier point
167 -- due to a loop back.
168 --
169
170 if (funcmode = 'CANCEL') then
171
172 -- your cancel code goes here
173 null;
174
175 -- no result needed
176 resultout := 'COMPLETE';
177 return;
178 end if;
179
180
181 --
182 -- Other execution modes may be created in the future. Your
183 -- activity will indicate that it does not implement a mode
184 -- by returning null
185 --
186 -- resultout := '';
187 -- return;
188
189 exception
190 when others then
191 -- The line below records this function call in the error system
192 -- in the case of an exception.
193 wf_core.context('OE_EXPORT_COMPLIANCE_WF', 'ECS_Request',
194 itemtype, itemkey, to_char(actid), funcmode);
195 -- start data fix project
196 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
197 p_itemtype => itemtype,
198 p_itemkey => itemkey);
199 OE_STANDARD_WF.Save_Messages;
200 OE_STANDARD_WF.Clear_Msg_Context;
201 -- end data fix project
202 raise;
203 END ECS_Request;
204
205 END OE_EXPORT_COMPLIANCE_WF;