[Home] [Help]
PACKAGE BODY: APPS.GMDQC0_WF_P
Source
1 PACKAGE BODY gmdqc0_wf_p AS
2 /* $Header: GMDQC0B.pls 115.4 2002/04/18 14:58:20 pkm ship $ */
3 PROCEDURE init_wf (
4 -- procedure to initialize and run Workflow
5 -- called via trigger on IC_TRAN_CMP,IC_TRAN_PND
6
7 p_trans_id IN VARCHAR2,
8 p_orgn_code IN ic_tran_pnd.orgn_code%TYPE ,
9 p_whse_code IN ic_tran_pnd.whse_code%TYPE ,
10 p_item_id IN ic_tran_pnd.item_id%TYPE ,
11 p_doc_type IN ic_tran_pnd.doc_type%TYPE ,
12 p_doc_id IN ic_tran_pnd.doc_id%TYPE,
13 p_lot_id IN ic_tran_pnd.lot_id%TYPE,
14 p_trans_qty IN NUMBER
15
16 )
17
18 IS
19
20 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE := 'GMDQC0';
21 l_itemkey WF_ITEMS.ITEM_KEY%TYPE := p_trans_id;
22
23 /* make sure that process runs with background engine
24 to prevent SAVEPOINT/ROLLBACK error (see Workflow FAQ)
25 the value to use for this is -1 */
26
27 l_run_wf_in_background CONSTANT WF_ENGINE.THRESHOLD%TYPE := -1;
28
29
30 l_item_no ic_item_mst.item_no%TYPE ;
31 l_item_desc1 ic_item_mst.item_desc1%TYPE ;
32 l_dualum_ind ic_item_mst.dualum_ind%TYPE ;
33 l_item_um ic_item_mst.item_um%TYPE ;
34 l_item_um2 ic_item_mst.item_um2%TYPE ;
35 l_trans_qty NUMBER;
36 l_WorkflowProcess VARCHAR2(30) := 'GMDQC0_PROCESS';
37 l_count NUMBER;
38 BEGIN
39 /* Check for Specifications */
40 SELECT NVL(count(*),0) into l_count
41 FROM QC_SPEC_MST
42 WHERE item_id = p_item_id;
43
44 IF l_count > 0 THEN
45 /* create the process */
46
47 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype, itemkey => l_itemkey, process => l_WorkflowProcess);
48
49 /* make sure that process runs with background engine */
50 WF_ENGINE.THRESHOLD := l_run_wf_in_background ;
51
52 /* set the item attributes */
53 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
54 aname => 'TRANS_ID',
55 avalue => p_trans_id);
56
57 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
58 aname => 'ORGN_CODE',
59 avalue => p_orgn_code);
60
61 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
62 aname => 'WHSE_CODE',
63 avalue => p_whse_code);
64 WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype,itemkey => l_itemkey,
65 aname => 'ITEM_ID',
66 avalue => p_item_id);
67
68 WF_ENGINE.SETITEMATTRNUMBER (itemtype => l_itemtype,itemkey => l_itemkey,
69 aname => 'LOT_ID',
70 avalue => p_lot_id);
71
72 WF_ENGINE.SETITEMATTRTEXT (itemtype => l_itemtype,itemkey => l_itemkey,
73 aname => 'DOC_TYPE',
74 avalue => p_doc_type);
75
76 WF_ENGINE.SETITEMATTRNUMBER (itemtype => l_itemtype,itemkey => l_itemkey,
77 aname => 'DOC_ID',
78 avalue => p_doc_id);
79
80 WF_ENGINE.SETITEMATTRNUMBER (itemtype => l_itemtype,itemkey => l_itemkey,
81 aname => 'TRANS_QTY',
82 avalue => p_trans_qty);
83
84 /* start the Workflow process */
85
86 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_itemkey);
87
88 END IF;
89 EXCEPTION
90 WHEN OTHERS THEN
91 WF_CORE.CONTEXT ('gmdqc0_wf_p','init_wf',l_itemtype,l_itemkey,p_trans_id );
92 raise;
93 END init_wf;
94
95 /* ######################################################################## */
96
97 PROCEDURE select_role(
98 /* procedure to set the required attributes and find the role associated.
99 input/output parameters conform to WF standard (see WF FAQ) */
100 p_itemtype IN VARCHAR2,
101 p_itemkey IN VARCHAR2,
102 p_actid IN NUMBER,
103 p_funcmode IN VARCHAR2,
104 p_resultout OUT VARCHAR2
105 )
106 IS
107 l_item_id ic_item_mst.item_id%TYPE:=wf_engine.getitemattrtext (p_itemtype, p_itemkey, 'ITEM_ID');
108 l_item_no ic_item_mst.item_no%TYPE;
109 l_whse_item_no Varchar2(32);
110 l_whse_item_id ic_item_mst.item_id%TYPE;
111 l_item_desc1 ic_item_mst.item_desc1%TYPE;
112 l_item_um ic_item_mst.item_um%TYPE;
113 l_item_um2 ic_item_mst.item_um2%TYPE;
114 l_lot_id ic_lots_mst.lot_id%TYPE:=wf_engine.getitemattrtext (p_itemtype, p_itemkey,'LOT_ID');
115 l_orgn_code sy_orgn_mst.orgn_code%TYPE:=wf_engine.getitemattrtext (p_itemtype, p_itemkey,'ORGN_CODE');
116 l_orgn_name sy_orgn_mst.orgn_name%TYPE;
117 l_whse_code ic_whse_mst.whse_code%TYPE:=wf_engine.getitemattrtext (p_itemtype, p_itemkey,'WHSE_CODE');
118 l_whse_name ic_whse_mst.whse_name%TYPE;
119 l_lot_no ic_lots_mst.lot_no%TYPE ;
120 l_sublot_no ic_lots_mst.sublot_no%TYPE ;
121 l_doc_type sy_docs_mst.doc_type%TYPE:=wf_engine.getitemattrtext (p_itemtype, p_itemkey,'DOC_TYPE');
122 l_doc_desc sy_docs_mst.doc_desc%TYPE;
123 l_role_name wf_roles.name%TYPE;
124 l_role_display_name wf_roles.display_name%TYPE;
125 l_email_Address varchar2(2000);
126 l_notification_Preference varchar2(2000);
127 l_language varchar2(2000);
128 l_territory varchar2(2000);
129 l_sample_form VARCHAR2(100);
130 l_datastring VARCHAR2(2000);
131 l_delimiter VARCHAR2(1);
132 NO_ROLE_ERROR Exception;
133 /* Declaring the Workflow Parameters to be passed to generic Role association */
134 l_wf_item_type VARCHAR2(8):='GMDQC0';
135 l_process_name VARCHAR2(80):='GMDQC0_PROCESS';
136 l_activity_name VARCHAR2(100):='SELECT_ROLE';
137 /* Begin Bug#2250274 Praveen Reddy */
138 /* Added variables l_doc_id and l_doc_no*/
139 l_doc_id ic_tran_pnd.doc_id%TYPE:=wf_engine.getitemattrtext (p_itemtype, p_itemkey,'DOC_ID');
140 l_doc_no VARCHAR2(40);
141 /* End Bug#2250274*/
142 BEGIN
143 IF (p_funcmode = 'RUN') THEN
144
145 /* Selecting item details for the item on which the transaction has occured */
146 SELECT item_no,item_desc1,item_um,item_um2,whse_item_id
147 INTO l_item_no,l_item_desc1,l_item_um,l_item_um2,l_whse_item_id
148 FROM ic_item_mst
149 WHERE item_id = l_item_id;
150
151 /* Select warehouse item details for the given item */
152 SELECT item_no
153 INTO l_whse_item_no
154 FROM ic_item_mst
155 WHERE item_id = l_whse_item_id;
156
157 /* Selecting lot details for the transaction occured */
158 IF l_lot_id <> 0 THEN
159 SELECT lot_no,sublot_no
160 INTO l_lot_no,l_sublot_no
161 FROM ic_lots_mst
162 WHERE lot_id = l_lot_id;
163 ELSE
164 l_lot_no:=NULL;
165 l_sublot_no:=NULL;
166 END IF;
167 /* Selecting Organization details from the organization mst */
168 SELECT orgn_name
172
169 INTO l_orgn_name
170 FROM sy_orgn_mst
171 WHERE orgn_code = l_orgn_code;
173 /* Selecting Warehouse details from the Warehouse master mst */
174 SELECT whse_name
175 INTO l_whse_name
176 FROM ic_whse_mst
177 WHERE whse_code = l_whse_code;
178
179 /* Selecting Document details from Document Master */
180 SELECT doc_desc
181 INTO l_doc_desc
182 FROM sy_docs_mst
183 WHERE doc_type = l_doc_type;
184
185 /* Begin Bug#2250274 Praveen Reddy*/
186 /* Added code to fetch the doc_no depending upon doc type and doc_id*/
187 IF l_doc_type in ('PORC') THEN
188 SELECT receipt_num
189 INTO l_doc_no
190 FROM rcv_shipment_headers
191 WHERE shipment_header_id = l_doc_id;
192 ELSIF l_doc_type in ('RECV') THEN
193 SELECT recv_no
194 INTO l_doc_no
195 FROM po_recv_hdr
196 WHERE recv_id = l_doc_id;
197 ELSIF l_doc_type in ('PROD') THEN
198 SELECT batch_no
199 INTO l_doc_no
200 FROM pm_btch_hdr
201 WHERE batch_id = l_doc_id;
202 ELSIF l_doc_type in ('PICY','PIPH','ADJI','ADJR','CREI','CRER','TRNI','TRNR') then
203 SELECT journal_no
204 INTO l_doc_no
205 FROM ic_jrnl_mst
206 WHERE journal_id = l_doc_id;
207 END IF;
208 /* End Bug#2250274*/
209
210 /* Set the SAMPLE form to be entered */
211 /* Bug fix B2314407 . Added PORC transaction to the if Condition */
212 IF l_doc_type in ('PORD','RECV','PORC') THEN
213 l_sample_form:='QCSMPED1_F:ITEM_NO="'||l_item_no||'" WF="YES"';
214 ELSIF l_doc_type = 'PROD' THEN
215 l_sample_form:='QCSMPED3_F:ITEM_NO="'||l_item_no||'" WF="YES"';
216 ELSE
217 l_sample_form:='QCSMPED2_F:ITEM_NO="'||
218 l_item_no||'" WHSE_CODE="'||l_whse_code||'" LOT_NO="'||
219 l_lot_no||'" SUBLOT_NO="'||l_sublot_no||'" WF="YES"';
220 END IF;
221
222 /* Setting all the required attributes */
223 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
224 itemkey => p_itemkey,
225 aname => 'SAMPLE_FORM',
226 avalue => l_sample_form);
227
228 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
229 itemkey => p_itemkey,
230 aname => 'ITEM_NO',
231 avalue => l_item_no);
232
233 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
234 itemkey => p_itemkey,
235 aname => 'ITEM_DESC1',
236 avalue => l_item_desc1);
237
238 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
239 itemkey => p_itemkey,
240 aname => 'ITEM_UM',
241 avalue => l_item_um);
242
243 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
244 itemkey => p_itemkey,
245 aname => 'ITEM_UM2',
246 avalue => l_item_um2);
247 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
248 itemkey => p_itemkey,
249 aname => 'LOT_NO',
250 avalue => l_lot_no);
251
252 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
253 itemkey => p_itemkey,
254 aname => 'SUBLOT_NO',
255 avalue => l_sublot_no);
256 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
257 itemkey => p_itemkey,
258 aname => 'ORGN_NAME',
259 avalue => l_orgn_name);
260
261 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
262 itemkey => p_itemkey,
263 aname => 'WHSE_NAME',
264 avalue => l_whse_name);
265
266 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
267 itemkey => p_itemkey,
268 aname => 'DOC_DESC',
269 avalue => l_doc_desc);
270 /* Begin Bug#2250274 Praveen Reddy*/
271 /* Added call to set the doc_no value into the workflow tables */
272 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
273 itemkey => p_itemkey,
274 aname => 'DOC_NO',
275 avalue => l_doc_no);
276 /* End Bug#2250274 */
277
278 BEGIN
279 /* Get the Delimiter from the Profile Value */
280 IF (FND_PROFILE.DEFINED ('SY$WF_DELIMITER')) THEN
281 l_delimiter := FND_PROFILE.VALUE ('SY$WF_DELIMITER');
282 ELSE
283 RAISE NO_ROLE_ERROR;
284 END IF;
285 IF l_delimiter is NULL THEN
286 RAISE NO_ROLE_ERROR;
287 END IF;
288 /* Constructing the Data String to Find the Values */
289 l_datastring:='ORGN_CODE='||l_orgn_code||l_delimiter||
290 'WHSE_CODE='||l_whse_code||l_delimiter||
294 gma_wfstd_p.get_role(l_wf_item_type,l_process_name,l_activity_name,l_datastring,l_role_name);
291 'WHSE_ITEM_NO='||l_whse_item_no||l_delimiter||
292 'ITEM_NO=' ||l_item_no;
293 /* Getting the Role from generic Role Association Package */
295 IF l_role_name in ('NOROLE','ERROR') THEN
296 RAISE NO_ROLE_ERROR;
297 ELSE
298 p_resultout := 'COMPLETE:ROLE_EXIST';
299 END IF;
300 EXCEPTION
301 WHEN NO_ROLE_ERROR THEN
302 p_resultout := 'COMPLETE:SELERR';
303 return;
304 END;
305
306 WF_DIRECTORY.GETROLEINFO(l_role_name,l_role_display_name,l_email_Address,
307 l_notification_Preference,l_language,l_territory);
308 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
309 itemkey => p_itemkey,
310 aname => 'ROLE_NAME',
311 avalue => l_role_name);
312
313 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype,
314 itemkey => p_itemkey,
315 aname => 'ROLE_DISPLAY_NAME',
316 avalue => l_role_display_name);
317 END IF;
318 EXCEPTION
319 WHEN OTHERS THEN
320 WF_CORE.CONTEXT ('gmdqc0_wf_p','select_role',p_itemtype,p_itemkey,l_role_name);
321 raise;
322 END select_role;
323 END gmdqc0_wf_p;