1 PACKAGE BODY INV_INVTOPKL_XMLP_PKG AS
2 /* $Header: INVTOPKLB.pls 120.4.12020000.2 2012/07/09 09:07:59 ptkumar ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 C_DATE_FORMAT varchar2(20);
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 C_DATE_FORMAT := 'DD-MON-YYYY';
9 CP_DATE_REQD_LO := to_char(P_DATE_REQD_LO,C_DATE_FORMAT);
10 CP_DATE_REQD_HI := to_char(P_DATE_REQD_HI,C_DATE_FORMAT);
11 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
12 EXCEPTION
13 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
14 /*SRW.MESSAGE(1
15 ,'Before Report: Init')*/NULL;
16 END;
17 DECLARE
18 L_RETURN_STATUS VARCHAR2(1);
19 L_MSG_COUNT NUMBER;
20 L_MSG_DATA VARCHAR2(240);
21 BEGIN
22 IF WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS => L_RETURN_STATUS
23 ,X_MSG_COUNT => L_MSG_COUNT
24 ,X_MSG_DATA => L_MSG_DATA
25 ,P_ORGANIZATION_ID => P_ORG_ID) THEN
26 P_WMS_INSTALL := 'Y';
27 ELSE
28 P_WMS_INSTALL := 'N';
29 END IF;
30 END;
31 GET_CHART_OF_ACCOUNTS_ID;
32 BEGIN
33 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || TO_CHAR(P_ORG_ID) || '"')*/NULL;
34 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_SET_OF_BOOKS_ID' || '" FIELD="' || TO_CHAR(P_SET_OF_BOOKS_ID) || '"')*/NULL;
35 FND_PROFILE.PUT('MFG_ORGANIZATION_ID'
36 ,TO_CHAR(P_ORG_ID));
37 EXCEPTION
38 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
39 /*SRW.MESSAGE(1
40 ,'Before Report: Failed in setting org/ledger id profiles ')*/NULL;
41 RAISE;
42 END;
43 BEGIN
44 NULL;
45 EXCEPTION
46 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
47 /*SRW.MESSAGE(1
48 ,'Before Report: ItemFlex')*/NULL;
49 END;
50 BEGIN
51 /*SRW.REFERENCE(CP_CHART_OF_ACCOUNTS_NUM)*/NULL;
52 EXCEPTION
53 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
54 /*SRW.MESSAGE(1
55 ,'Before Report: AccountFlex')*/NULL;
56 END;
57 IF P_ALLOCATE_MOVE_ORDER = 'Y' THEN
58 INV_CALL_ALLOCATIONS_ENGINE;
59 END IF;
60 P_SOURCE_LOCATOR := INV_PROJECT.GET_LOCATOR(P_SOURCE_LOCATOR_ID
61 ,P_ORG_ID);
62 P_DEST_LOCATOR := INV_PROJECT.GET_LOCATOR(P_DEST_LOCATOR_ID
63 ,P_ORG_ID);
64 DECLARE
65 l_return boolean;
66 BEGIN
67 l_return := P_ORG_IDVALIDTRIGGER();
68 l_return := P_REQUESTED_BYVALIDTRIGGER();
69 l_return := P_MOVE_ORDER_TYPEVALIDTRIGGER();
70 l_return := P_PICK_SLIP_GROUP_RULE_IDVALID();
71 l_return := P_PRINT_OPTIONVALIDTRIGGER();
72 l_return := P_ALLOCATE_MOVE_ORDERVALIDTRIG();
73 l_return := P_PLAN_TASKSVALIDTRIGGER();
74 l_return := P_FREIGHT_CODEVALIDTRIGGER();
75 END;
76 RETURN (TRUE);
77 END BEFOREREPORT;
78 FUNCTION AFTERREPORT RETURN BOOLEAN IS
79 BEGIN
80 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
81 RETURN (TRUE);
82 END AFTERREPORT;
83 FUNCTION AFTERPFORM RETURN BOOLEAN IS
84 L_TEMP_WHERE VARCHAR2(200);
85 BEGIN
86 IF P_MOVE_ORDER_LOW IS NOT NULL THEN
87 L_TEMP_WHERE := ' AND MTRH.REQUEST_NUMBER >= :P_MOVE_ORDER_LOW';
88 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
89 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
90 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
91 END IF;
92 IF P_MOVE_ORDER_HIGH IS NOT NULL THEN
93 L_TEMP_WHERE := ' AND MTRH.REQUEST_NUMBER <= :P_MOVE_ORDER_HIGH';
94 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
95 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
96 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
97 END IF;
98 IF P_DATE_REQD_LO IS NOT NULL THEN
99 L_TEMP_WHERE := ' AND TO_CHAR(MTRL.Date_required,''YYYY-MM-DD'') >= TO_CHAR(:P_DATE_REQD_LO,''YYYY-MM-DD'')';
100 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
101 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
102 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
103 END IF;
104 IF P_DATE_REQD_HI IS NOT NULL THEN
105 L_TEMP_WHERE := ' AND TO_CHAR(MTRL.Date_required,''YYYY-MM-DD'') <= TO_CHAR(:P_DATE_REQD_HI,''YYYY-MM-DD'')';
106 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
107 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
108 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
109 END IF;
110 IF (P_SOURCE_SUBINV IS NOT NULL) THEN
111 P_WHERE_MMT := P_WHERE_MMT || ' AND MMT.SUBINVENTORY_CODE = :P_SOURCE_SUBINV';
112 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.SUBINVENTORY_CODE = :P_SOURCE_SUBINV';
113 P_WHERE_MTRL := P_WHERE_MTRL || ' AND MTRL.FROM_SUBINVENTORY_CODE = :P_SOURCE_SUBINV';
114 END IF;
115 IF (P_SOURCE_LOCATOR_ID IS NOT NULL) THEN
116 P_WHERE_MMT := P_WHERE_MMT || ' AND MMT.LOCATOR_ID = :P_SOURCE_LOCATOR_ID';
117 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.LOCATOR_ID = :P_SOURCE_LOCATOR_ID';
118 P_WHERE_MTRL := P_WHERE_MTRL || ' AND MTRL.FROM_LOCATOR_ID = :P_SOURCE_LOCATOR_ID';
119 END IF;
120 IF (P_DEST_SUBINV IS NOT NULL) THEN
121 P_WHERE_MMT := P_WHERE_MMT || ' AND MMT.TRANSFER_SUBINVENTORY = :P_DEST_SUBINV';
122 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.TRANSFER_SUBINVENTORY = :P_DEST_SUBINV';
123 P_WHERE_MTRL := P_WHERE_MTRL || ' AND MTRL.TO_SUBINVENTORY_CODE = :P_DEST_SUBINV';
124 END IF;
125 IF (P_DEST_LOCATOR_ID IS NOT NULL) THEN
126 P_WHERE_MMT := P_WHERE_MMT || ' AND MMT.TRANSFER_LOCATOR_ID = :P_DEST_LOCATOR_ID';
127 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.TRANSFER_TO_LOCATION = :P_DEST_LOCATOR_ID';
128 P_WHERE_MTRL := P_WHERE_MTRL || ' AND MTRL.TO_LOCATOR_ID = :P_DEST_LOCATOR_ID';
129 END IF;
130 IF P_REQUESTED_BY IS NOT NULL THEN
131 L_TEMP_WHERE := ' AND MTRL.CREATED_BY = :P_REQUESTED_BY';
132 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
133 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
134 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
135 END IF;
136 IF P_MOVE_ORDER_TYPE = 1 THEN
137 L_TEMP_WHERE := ' AND MTRH.MOVE_ORDER_TYPE = 3';
138 ELSIF P_MOVE_ORDER_TYPE = 2 THEN
139 L_TEMP_WHERE := ' AND MTRH.MOVE_ORDER_TYPE = 6';
140 ELSIF P_MOVE_ORDER_TYPE = 3 THEN
141 L_TEMP_WHERE := ' AND MTRH.MOVE_ORDER_TYPE = 5';
142 ELSIF P_MOVE_ORDER_TYPE = 4 THEN
143 L_TEMP_WHERE := ' AND MTRH.MOVE_ORDER_TYPE IN (1,2)';
144 ELSE
145 L_TEMP_WHERE := ' AND MTRH.MOVE_ORDER_TYPE IN (1,2,3,5,6)';
146 END IF;
147 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
148 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
149 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
150 IF P_PRINT_OPTION = '1' THEN
151 P_WHERE_MMT := P_WHERE_MMT || ' AND 1 = 2 ';
152 P_WHERE_MMTT := P_WHERE_MMTT || ' AND 1 = 2 ';
153 ELSIF P_PRINT_OPTION = '2' THEN
154 P_WHERE_MMT := P_WHERE_MMT || ' AND 1 = 2 ';
155 P_WHERE_MTRL := P_WHERE_MTRL || ' AND 1 = 2 ';
156 ELSIF P_PRINT_OPTION = '3' THEN
157 P_WHERE_MMT := P_WHERE_MMT || ' AND 1 = 2 ';
158 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.WMS_TASK_STATUS = 8 ';
159 P_WHERE_MTRL := P_WHERE_MTRL || ' AND 1 = 2 ';
160 ELSIF P_PRINT_OPTION = '4' THEN
161 P_WHERE_MMT := P_WHERE_MMT || ' AND 1 = 2 ';
162 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.WMS_TASK_STATUS <> 8 ';
163 P_WHERE_MTRL := P_WHERE_MTRL || ' AND 1 = 2 ';
164 ELSIF P_PRINT_OPTION = '5' THEN
165 P_WHERE_MMTT := P_WHERE_MMTT || ' AND 1 = 2 ';
166 P_WHERE_MTRL := P_WHERE_MTRL || ' AND 1 = 2 ';
167 ELSE
168 NULL;
169 END IF;
170 IF P_PICK_SLIP_NUMBER_LOW IS NOT NULL THEN
171 P_WHERE_MMT := P_WHERE_MMT || ' AND MMT.PICK_SLIP_NUMBER >= :P_PICK_SLIP_NUMBER_LOW';
172 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.PICK_SLIP_NUMBER >= :P_PICK_SLIP_NUMBER_LOW';
173 END IF;
174 IF P_PICK_SLIP_NUMBER_HIGH IS NOT NULL THEN
175 P_WHERE_MMT := P_WHERE_MMT || ' AND MMT.PICK_SLIP_NUMBER <= :P_PICK_SLIP_NUMBER_HIGH';
176 P_WHERE_MMTT := P_WHERE_MMTT || ' AND MMTT.PICK_SLIP_NUMBER <= :P_PICK_SLIP_NUMBER_HIGH';
177 END IF;
178 IF P_SALES_ORDER_LOW IS NOT NULL OR P_SALES_ORDER_HIGH IS NOT NULL OR P_CUSTOMER_ID IS NOT NULL OR P_FREIGHT_CODE IS NOT NULL THEN
179 P_FROM_MMT := P_FROM_MMT || ', WSH_DELIVERY_DETAILS WDD';
180 P_FROM_MMTT := P_FROM_MMTT || ', WSH_DELIVERY_DETAILS WDD';
181 P_FROM_MTRL := P_FROM_MTRL || ', WSH_DELIVERY_DETAILS WDD';
182 L_TEMP_WHERE := '';
183 IF P_SALES_ORDER_LOW IS NOT NULL THEN
184 L_TEMP_WHERE := L_TEMP_WHERE || ' AND WDD.SOURCE_HEADER_NUMBER >= :P_SALES_ORDER_LOW ';
185 END IF;
186 IF P_SALES_ORDER_HIGH IS NOT NULL THEN
187 L_TEMP_WHERE := L_TEMP_WHERE || ' AND WDD.SOURCE_HEADER_NUMBER <= :P_SALES_ORDER_HIGH ';
188 END IF;
189 IF P_CUSTOMER_ID IS NOT NULL THEN
190 L_TEMP_WHERE := L_TEMP_WHERE || ' AND WDD.CUSTOMER_ID = :P_CUSTOMER_ID ';
191 END IF;
192 IF P_FREIGHT_CODE IS NOT NULL THEN
193 L_TEMP_WHERE := L_TEMP_WHERE || ' WDD.SHIP_METHOD_CODE = :P_FREIGHT_CODE';
194 END IF;
195 L_TEMP_WHERE := L_TEMP_WHERE || ' AND MTRL.LINE_ID = WDD.MOVE_ORDER_LINE_ID';
196 P_WHERE_MMT := P_WHERE_MMT || L_TEMP_WHERE;
197 P_WHERE_MMTT := P_WHERE_MMTT || L_TEMP_WHERE;
198 P_WHERE_MTRL := P_WHERE_MTRL || L_TEMP_WHERE;
199 else
200 P_FROM_MMT := ' ';
201 P_FROM_MTRL := ' ';
202 P_FROM_MMTT := ' ';
203 END IF;
204 RETURN (TRUE);
205 END AFTERPFORM;
206 PROCEDURE GET_CHART_OF_ACCOUNTS_ID IS
207 L_ERRBUF VARCHAR2(132);
208 L_COAID NUMBER;
209 BEGIN
210 BEGIN
211 SELECT
212 SET_OF_BOOKS_ID
213 INTO P_SET_OF_BOOKS_ID
214 FROM
215 ORG_ORGANIZATION_DEFINITIONS
216 WHERE ORGANIZATION_ID = P_ORG_ID;
217 EXCEPTION
218 WHEN OTHERS THEN
219 /*SRW.MESSAGE(1
220 ,'Could not find the ledger id')*/NULL;
221 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
222 END;
223 BEGIN
224 SELECT
225 CHART_OF_ACCOUNTS_ID
226 INTO L_COAID
227 FROM
228 GL_SETS_OF_BOOKS
229 WHERE SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 /*SRW.MESSAGE(1
233 ,'No Data Found in Get COAI')*/NULL;
234 L_ERRBUF := GL_MESSAGE.GET_MESSAGE('GL_PLL_INVALID_SOB'
235 ,'Y'
236 ,'SOBID'
237 ,TO_CHAR(P_SET_OF_BOOKS_ID));
238 /*SRW.MESSAGE(1
239 ,L_ERRBUF)*/NULL;
240 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
241 WHEN OTHERS THEN
242 /*SRW.MESSAGE(1
243 ,'Others Exception in Get COAI')*/NULL;
244 L_ERRBUF := SQLERRM;
245 /*SRW.MESSAGE(1
246 ,L_ERRBUF)*/NULL;
247 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
248 END;
249 CP_CHART_OF_ACCOUNTS_NUM := L_COAID;
250 END GET_CHART_OF_ACCOUNTS_ID;
251 FUNCTION P_REQUESTED_BYVALIDTRIGGER RETURN BOOLEAN IS
252 BEGIN
253 IF P_REQUESTED_BY IS NOT NULL THEN
254 SELECT
255 USER_NAME
256 INTO P_REQUESTED_BY_NAME
257 FROM
258 FND_USER
259 WHERE USER_ID = P_REQUESTED_BY;
260 END IF;
261 RETURN (TRUE);
262 EXCEPTION
263 WHEN OTHERS THEN
264 RETURN NULL;
265 END P_REQUESTED_BYVALIDTRIGGER;
266 FUNCTION P_ORG_IDVALIDTRIGGER RETURN BOOLEAN IS
267 BEGIN
268 SELECT
269 ORG.ORGANIZATION_NAME
270 INTO P_ORG_NAME
271 FROM
272 ORG_ORGANIZATION_DEFINITIONS ORG
273 WHERE ORG.ORGANIZATION_ID = P_ORG_ID;
274 RETURN (TRUE);
275 EXCEPTION
276 WHEN OTHERS THEN
277 RETURN NULL;
278 END P_ORG_IDVALIDTRIGGER;
279 FUNCTION CF_PROJECT_NUMBERFORMULA(PROJECT_ID IN NUMBER) RETURN CHAR IS
280 L_PROJECT_NUMBER VARCHAR2(100);
281 L_PROJECT_ID NUMBER; -- Bug 13791981 added
282 BEGIN
283 L_PROJECT_ID := PROJECT_ID; -- Bug 13791981 added
284
285 IF L_PROJECT_ID IS NOT NULL THEN -- Bug 13791981 changed
286 BEGIN
287 SELECT
288 PROJECT_NUMBER
289 INTO L_PROJECT_NUMBER
290 FROM
291 PJM_PROJECTS_V PJMP
292 WHERE L_PROJECT_ID = PJMP.PROJECT_ID; -- Bug 13791981 changed
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 BEGIN
296 SELECT
297 PROJECT_NUMBER
298 INTO L_PROJECT_NUMBER
299 FROM
300 PA_PROJECTS_EXPEND_V PPEV
301 WHERE L_PROJECT_ID = PPEV.PROJECT_ID -- Bug 13791981 changed
302 AND ROWNUM = 1;
303 EXCEPTION
304 WHEN OTHERS THEN
305 L_PROJECT_NUMBER := NULL;
306 END;
307 WHEN OTHERS THEN
308 L_PROJECT_NUMBER := NULL;
309 END;
310 END IF;
311 RETURN (L_PROJECT_NUMBER);
312 EXCEPTION
313 WHEN OTHERS THEN
314 RETURN (NULL);
315 END CF_PROJECT_NUMBERFORMULA;
316 FUNCTION CF_TASK_NUMBERFORMULA(TASK_ID IN NUMBER
317 ,PROJECT_ID IN NUMBER) RETURN CHAR IS
318 L_TASK_NUMBER VARCHAR2(100);
319 L_TASK_ID NUMBER; -- Bug 13791981 added
320 L_PROJECT_ID NUMBER; -- Bug 13791981 added
321 BEGIN
322 -- Bug 13791981 added
323 L_TASK_ID := TASK_ID;
324 L_PROJECT_ID := PROJECT_ID;
325
326 IF L_TASK_ID IS NOT NULL THEN -- Bug 13791981 changed
327 BEGIN
328 SELECT
329 TASK_NUMBER
330 INTO L_TASK_NUMBER
331 FROM
332 PJM_TASKS_V PJMT
333 WHERE L_PROJECT_ID = PJMT.PROJECT_ID -- Bug 13791981 changed
334 AND L_TASK_ID = PJMT.TASK_ID; -- Bug 13791981 changed
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 BEGIN
338 SELECT
339 TASK_NUMBER
340 INTO L_TASK_NUMBER
341 FROM
342 PA_TASKS_EXPEND_V PTEV
343 WHERE L_PROJECT_ID = PTEV.PROJECT_ID -- Bug 13791981 changed
344 AND L_TASK_ID = PTEV.TASK_ID -- Bug 13791981 changed
345 AND ROWNUM = 1;
346 EXCEPTION
347 WHEN OTHERS THEN
348 L_TASK_NUMBER := NULL;
349 END;
350 WHEN OTHERS THEN
351 L_TASK_NUMBER := NULL;
352 END;
353 END IF;
354 RETURN (L_TASK_NUMBER);
355 EXCEPTION
356 WHEN OTHERS THEN
357 RETURN (NULL);
358 END CF_TASK_NUMBERFORMULA;
359 FUNCTION P_MOVE_ORDER_TYPEVALIDTRIGGER RETURN BOOLEAN IS
360 BEGIN
361 SELECT
362 MEANING
363 INTO P_MOVE_ORDER_TYPE_MEANING
364 FROM
365 MFG_LOOKUPS
366 WHERE LOOKUP_TYPE = 'INV_PICK_SLIP_MO_TYPES'
367 AND LOOKUP_CODE = P_MOVE_ORDER_TYPE;
368 RETURN (TRUE);
369 EXCEPTION
370 WHEN OTHERS THEN
371 RETURN TRUE;
372 END P_MOVE_ORDER_TYPEVALIDTRIGGER;
373 FUNCTION P_PICK_SLIP_GROUP_RULE_IDVALID RETURN BOOLEAN IS
374 BEGIN
375 IF P_PICK_SLIP_GROUP_RULE_ID IS NOT NULL THEN
376 SELECT
377 NAME
378 INTO P_PICK_SLIP_GROUP_RULE_NAME
379 FROM
380 WSH_PICK_GROUPING_RULES
381 WHERE PICK_GROUPING_RULE_ID = P_PICK_SLIP_GROUP_RULE_ID;
382 END IF;
383 RETURN (TRUE);
384 EXCEPTION
385 WHEN OTHERS THEN
386 RETURN TRUE;
387 END P_PICK_SLIP_GROUP_RULE_IDVALID;
388 FUNCTION P_PRINT_OPTIONVALIDTRIGGER RETURN BOOLEAN IS
389 L_PRINT_OPTION NUMBER;
390 BEGIN
391 IF P_PRINT_OPTION = '1' THEN
392 L_PRINT_OPTION := 1;
393 ELSIF P_PRINT_OPTION = '2' THEN
394 L_PRINT_OPTION := 2;
395 ELSIF P_PRINT_OPTION = '3' THEN
396 L_PRINT_OPTION := 3;
397 ELSIF P_PRINT_OPTION = '4' THEN
398 L_PRINT_OPTION := 4;
399 ELSIF P_PRINT_OPTION = '5' THEN
400 L_PRINT_OPTION := 5;
401 ELSE
402 L_PRINT_OPTION := 99;
403 END IF;
404 SELECT
405 MEANING
406 INTO P_PRINT_OPTION_MEANING
407 FROM
408 MFG_LOOKUPS
409 WHERE LOOKUP_TYPE = 'INV_PICK_SLIP_PRINT_OPTIONS'
410 AND LOOKUP_CODE = L_PRINT_OPTION;
411 RETURN (TRUE);
412 EXCEPTION
413 WHEN OTHERS THEN
414 RETURN TRUE;
415 END P_PRINT_OPTIONVALIDTRIGGER;
416 FUNCTION CF_WIP_INFOFORMULA(LINE_ID_P IN NUMBER
417 ,MOVE_ORDER_TYPE IN NUMBER
418 ,TRANSACTION_SOURCE_TYPE_ID IN NUMBER) RETURN NUMBER IS
419 CURSOR C_WIP_ENTITY_TYPE IS
420 SELECT
421 WE.ENTITY_TYPE
422 FROM
423 WIP_ENTITIES WE,
424 MTL_TXN_REQUEST_LINES MTRL
425 WHERE MTRL.LINE_ID = LINE_ID_P --bug12674388
426 AND WE.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID;
427 CURSOR C_DISCRETE_INFO IS
428 SELECT
429 WE.WIP_ENTITY_NAME JOB,
430 WL.LINE_CODE LINE,
431 WO.OPERATION_SEQ_NUM OPERATION,
432 BD.DEPARTMENT_CODE DEPARTMENT,
433 TO_CHAR(NULL) START_DATE
434 FROM
435 WIP_ENTITIES WE,
436 WIP_LINES WL,
437 BOM_DEPARTMENTS BD,
438 WIP_OPERATIONS WO,
439 WIP_DISCRETE_JOBS WDJ,
440 MTL_TXN_REQUEST_LINES MTRL
441 WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
442 AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
443 AND WDJ.LINE_ID = wl.line_id (+)
444 AND WDJ.ORGANIZATION_ID = wl.organization_id (+)
445 AND WO.DEPARTMENT_ID = bd.department_id (+)
446 AND MTRL.TXN_SOURCE_ID = wo.wip_entity_id (+)
447 AND MTRL.ORGANIZATION_ID = wo.organization_id (+)
448 AND MTRL.TXN_SOURCE_LINE_ID = wo.operation_seq_num (+)
449 AND WDJ.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID
450 AND WDJ.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
451 AND MTRL.LINE_ID = LINE_ID_P;
452 CURSOR C_REPETITIVE_INFO IS
453 SELECT
454 MSIK.CONCATENATED_SEGMENTS JOB,
455 WL.LINE_CODE LINE,
456 WO.OPERATION_SEQ_NUM OPERATION,
457 BD.DEPARTMENT_CODE DEPARTMENT,
458 TO_CHAR(WRS.FIRST_UNIT_START_DATE) START_DATE
459 FROM
460 MTL_SYSTEM_ITEMS_KFV MSIK,
461 WIP_ENTITIES WE,
462 WIP_LINES WL,
463 BOM_DEPARTMENTS BD,
464 WIP_OPERATIONS WO,
465 WIP_REPETITIVE_SCHEDULES WRS,
466 MTL_TXN_REQUEST_LINES MTRL
467 WHERE MSIK.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
468 AND MSIK.ORGANIZATION_ID = WE.ORGANIZATION_ID
469 AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
470 AND WE.ORGANIZATION_ID = WRS.ORGANIZATION_ID
471 AND WL.LINE_ID = WRS.LINE_ID
472 AND WL.ORGANIZATION_ID = WRS.ORGANIZATION_ID
473 AND bd.department_id (+) = WO.DEPARTMENT_ID
474 AND wo.wip_entity_id (+) = MTRL.TXN_SOURCE_ID
475 AND wo.operation_seq_num (+) = MTRL.TXN_SOURCE_LINE_ID
476 AND wo.organization_id (+) = MTRL.ORGANIZATION_ID
477 AND wo.repetitive_schedule_id (+) = MTRL.REFERENCE_ID
478 AND WRS.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID
479 AND WRS.REPETITIVE_SCHEDULE_ID = MTRL.REFERENCE_ID
480 AND WRS.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
481 AND MTRL.LINE_ID = LINE_ID_P;
482 CURSOR C_FLOW_INFO IS
483 SELECT
484 WE.WIP_ENTITY_NAME JOB,
485 WL.LINE_CODE LINE,
486 BOS2.OPERATION_SEQ_NUM OPERATION,
487 BD.DEPARTMENT_CODE DEPARTMENT,
488 TO_CHAR(NULL) START_DATE
489 FROM
490 WIP_ENTITIES WE,
491 WIP_LINES WL,
492 BOM_DEPARTMENTS BD,
493 BOM_OPERATION_SEQUENCES BOS2,
494 BOM_OPERATION_SEQUENCES BOS,
495 BOM_OPERATIONAL_ROUTINGS BOR,
496 WIP_FLOW_SCHEDULES WFS,
497 MTL_TXN_REQUEST_LINES MTRL
498 WHERE WE.WIP_ENTITY_ID = WFS.WIP_ENTITY_ID
499 AND WE.ORGANIZATION_ID = WFS.ORGANIZATION_ID
500 AND WL.LINE_ID = WFS.LINE_ID
501 AND WL.ORGANIZATION_ID = WFS.ORGANIZATION_ID
502 AND BD.DEPARTMENT_ID = BOS2.DEPARTMENT_ID
503 AND BOS2.OPERATION_SEQUENCE_ID = BOS.LINE_OP_SEQ_ID
504 AND BOS2.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
505 AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
506 AND BOS.OPERATION_TYPE = 1
507 AND ( BOR.ALTERNATE_ROUTING_DESIGNATOR = WFS.ALTERNATE_ROUTING_DESIGNATOR
508 OR ( WFS.ALTERNATE_ROUTING_DESIGNATOR IS NULL
509 AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL ) )
510 AND BOR.ASSEMBLY_ITEM_ID = WFS.PRIMARY_ITEM_ID
511 AND BOR.ORGANIZATION_ID = WFS.ORGANIZATION_ID
512 AND WFS.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID
513 AND WFS.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
514 AND MTRL.LINE_ID = LINE_ID_P;
515 L_WIP_INFO C_DISCRETE_INFO%ROWTYPE;
516 BEGIN
517 CP_WIP_ENTITY_TYPE := NULL;
518 IF (MOVE_ORDER_TYPE = 5 AND TRANSACTION_SOURCE_TYPE_ID in (5,13)) THEN
519 OPEN C_WIP_ENTITY_TYPE;
520 FETCH C_WIP_ENTITY_TYPE
521 INTO CP_WIP_ENTITY_TYPE;
522 CLOSE C_WIP_ENTITY_TYPE;
523 IF CP_WIP_ENTITY_TYPE in (1,5) THEN
524 OPEN C_DISCRETE_INFO;
525 FETCH C_DISCRETE_INFO
526 INTO L_WIP_INFO;
527 CLOSE C_DISCRETE_INFO;
528 ELSIF CP_WIP_ENTITY_TYPE = 2 THEN
529 OPEN C_REPETITIVE_INFO;
530 FETCH C_REPETITIVE_INFO
531 INTO L_WIP_INFO;
532 CLOSE C_REPETITIVE_INFO;
533 ELSIF CP_WIP_ENTITY_TYPE = 4 THEN
534 OPEN C_FLOW_INFO;
535 FETCH C_FLOW_INFO
536 INTO L_WIP_INFO;
537 CLOSE C_FLOW_INFO;
538 END IF;
539 END IF;
540 CP_WIP_JOB := L_WIP_INFO.JOB;
541 CP_WIP_LINE := L_WIP_INFO.LINE;
542 CP_WIP_OPERATION := L_WIP_INFO.OPERATION;
543 CP_WIP_DEPARTMENT := L_WIP_INFO.DEPARTMENT;
544 CP_WIP_START_DATE := L_WIP_INFO.START_DATE;
545 RETURN 1;
546 EXCEPTION
547 WHEN OTHERS THEN
548 RETURN -1;
549 END CF_WIP_INFOFORMULA;
550 FUNCTION CF_SO_INFOFORMULA(LINE_ID IN NUMBER
551 ,MOVE_ORDER_TYPE IN NUMBER) RETURN NUMBER IS
552 CURSOR C_SO_INFO IS
553 SELECT
554 WDD.SOURCE_HEADER_NUMBER SO_NUMBER,
555 OEL.LINE_NUMBER SO_LINE_NUMBER,
556 WND.NAME DELIVERY_NAME
557 FROM
558 OE_ORDER_LINES_ALL OEL,
559 WSH_DELIVERY_DETAILS WDD,
560 WSH_DELIVERY_ASSIGNMENTS WDA,
561 WSH_NEW_DELIVERIES WND
562 WHERE WDD.MOVE_ORDER_LINE_ID = CF_SO_INFOFORMULA.LINE_ID
563 AND WDD.SOURCE_LINE_ID = OEL.LINE_ID
564 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
565 AND WND.DELIVERY_ID (+) = WDA.DELIVERY_ID;
566 BEGIN
567 IF MOVE_ORDER_TYPE = 3 THEN
568 OPEN C_SO_INFO;
569 FETCH C_SO_INFO
570 INTO CP_SO_ORDER_NUMBER,CP_SO_LINE_NUMBER,CP_SO_DELIVERY_NAME;
571 CLOSE C_SO_INFO;
572 END IF;
573 RETURN 1;
574 EXCEPTION
575 WHEN OTHERS THEN
576 RETURN -1;
577 END CF_SO_INFOFORMULA;
578 PROCEDURE INV_CALL_ALLOCATIONS_ENGINE IS
579 L_DETAIL_STATUS VARCHAR2(10);
580 BEGIN
581 IF (P_MOVE_ORDER_TYPE IS NOT NULL AND P_ORG_ID IS NOT NULL) THEN
582 INV_PICK_SLIP_REPORT.RUN_DETAIL_ENGINE(L_DETAIL_STATUS
583 ,P_ORG_ID
584 ,P_MOVE_ORDER_TYPE
585 ,P_MOVE_ORDER_LOW
586 ,P_MOVE_ORDER_HIGH
587 ,P_SOURCE_SUBINV
588 ,P_SOURCE_LOCATOR_ID
589 ,P_DEST_SUBINV
590 ,P_DEST_LOCATOR_ID
591 ,P_SALES_ORDER_LOW
592 ,P_SALES_ORDER_HIGH
593 ,P_FREIGHT_CODE
594 ,P_CUSTOMER_ID
595 ,P_REQUESTED_BY
596 ,P_DATE_REQD_LO
597 ,P_DATE_REQD_HI
598 ,(P_PLAN_TASKS = 'Y')
599 ,P_PICK_SLIP_GROUP_RULE_ID
600 ,P_CONC_REQUEST_ID);
601 IF L_DETAIL_STATUS in ('E','U') THEN
602 /*SRW.MESSAGE(1
603 ,'Detail Engine Failed to release some/all lines')*/NULL;
604 END IF;
605 END IF;
606 END INV_CALL_ALLOCATIONS_ENGINE;
607 FUNCTION P_CUSTOMER_IDVALIDTRIGGER RETURN BOOLEAN IS
608 BEGIN
609 IF P_CUSTOMER_ID IS NOT NULL THEN
610 BEGIN
611 SELECT
612 PARTY.PARTY_NAME
613 INTO P_CUSTOMER_NAME
614 FROM
615 HZ_PARTIES PARTY,
616 HZ_CUST_ACCOUNTS CUST_ACCT
617 WHERE CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER_ID
618 AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID;
619 EXCEPTION
620 WHEN OTHERS THEN
621 NULL;
622 END;
623 END IF;
624 RETURN TRUE;
625 EXCEPTION
626 WHEN OTHERS THEN
627 RETURN TRUE;
628 END P_CUSTOMER_IDVALIDTRIGGER;
629 FUNCTION CF_TASK_STATUSFORMULA(TASK_STATUS IN NUMBER) RETURN CHAR IS
630 L_MEANING VARCHAR2(20);
631 BEGIN
632 IF TASK_STATUS IS NOT NULL AND TASK_STATUS <> 0 THEN
633 SELECT
634 MEANING
635 INTO L_MEANING
636 FROM
637 MFG_LOOKUPS
638 WHERE LOOKUP_TYPE = 'WMS_TASK_STATUS'
639 AND LOOKUP_CODE = TASK_STATUS;
640 ELSIF TASK_STATUS = 0 THEN
641 L_MEANING := P_CONST_UNALLOCATED;
642 END IF;
643 RETURN L_MEANING;
644 EXCEPTION
645 WHEN OTHERS THEN
646 RETURN NULL;
647 END CF_TASK_STATUSFORMULA;
648 FUNCTION CF_TASK_IDFORMULA(TASK_STATUS IN NUMBER
649 ,PARENT_LINE_ID IN NUMBER
650 ,TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
651 BEGIN
652 IF TASK_STATUS not in (0,6) AND P_WMS_INSTALL = 'Y' THEN
653 RETURN NVL(PARENT_LINE_ID
654 ,TRANSACTION_ID);
655 ELSE
656 RETURN NULL;
657 END IF;
658 END CF_TASK_IDFORMULA;
659 FUNCTION P_ALLOCATE_MOVE_ORDERVALIDTRIG RETURN BOOLEAN IS
660 BEGIN
661 IF P_ALLOCATE_MOVE_ORDER IS NOT NULL THEN
662 SELECT
663 MEANING
664 INTO P_ALLOCATE_MOVE_ORDER_MEANING
665 FROM
666 FND_LOOKUPS
667 WHERE LOOKUP_TYPE = 'YES_NO'
668 AND LOOKUP_CODE = P_ALLOCATE_MOVE_ORDER;
669 END IF;
670 RETURN TRUE;
671 EXCEPTION
672 WHEN OTHERS THEN
673 RETURN TRUE;
674 END P_ALLOCATE_MOVE_ORDERVALIDTRIG;
675 FUNCTION P_PLAN_TASKSVALIDTRIGGER RETURN BOOLEAN IS
676 BEGIN
677 IF P_PLAN_TASKS IS NOT NULL THEN
678 SELECT
679 MEANING
680 INTO P_PLAN_TASKS_MEANING
681 FROM
682 FND_LOOKUPS
683 WHERE LOOKUP_TYPE = 'YES_NO'
684 AND LOOKUP_CODE = P_PLAN_TASKS;
685 END IF;
686 RETURN TRUE;
687 EXCEPTION
688 WHEN OTHERS THEN
689 RETURN TRUE;
690 END P_PLAN_TASKSVALIDTRIGGER;
691 FUNCTION P_FREIGHT_CODEVALIDTRIGGER RETURN BOOLEAN IS
692 BEGIN
693 RETURN (TRUE);
694 END P_FREIGHT_CODEVALIDTRIGGER;
695 FUNCTION CF_SEC_QTYFORMULA(INVENTORY_ITEM_ID_1 IN NUMBER
696 ,SEC_TRANSACTION_QTY IN NUMBER) RETURN NUMBER IS
697 L_TRACK_IND VARCHAR2(20);
698 L_SEC_QTY NUMBER;
699 BEGIN
700 IF INVENTORY_ITEM_ID_1 IS NOT NULL THEN
701 SELECT
702 TRACKING_QUANTITY_IND
703 INTO L_TRACK_IND
704 FROM
705 MTL_SYSTEM_ITEMS_B
706 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
707 AND ORGANIZATION_ID = P_ORG_ID;
708 IF (L_TRACK_IND = 'PS') THEN
709 L_SEC_QTY := SEC_TRANSACTION_QTY;
710 ELSE
711 L_SEC_QTY := NULL;
712 END IF;
713 END IF;
714 RETURN (L_SEC_QTY);
715 END CF_SEC_QTYFORMULA;
716 FUNCTION CF_SEC_UOMFORMULA(INVENTORY_ITEM_ID_1 IN NUMBER
717 ,SEC_UOM IN VARCHAR2) RETURN CHAR IS
718 L_TRACK_IND VARCHAR2(20);
719 L_SEC_UOM VARCHAR2(10);
720 BEGIN
721 IF INVENTORY_ITEM_ID_1 IS NOT NULL THEN
722 SELECT
723 TRACKING_QUANTITY_IND
724 INTO L_TRACK_IND
725 FROM
726 MTL_SYSTEM_ITEMS_B
727 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
728 AND ORGANIZATION_ID = P_ORG_ID;
729 IF (L_TRACK_IND = 'PS') THEN
730 L_SEC_UOM := SEC_UOM;
731 ELSE
732 L_SEC_UOM := NULL;
733 END IF;
734 END IF;
735 RETURN (L_SEC_UOM);
736 END CF_SEC_UOMFORMULA;
737 FUNCTION CF_PARENT_SEC_UOMFORMULA(PARENT_ITEM_ID IN NUMBER
738 ,PARENT_SEC_TXN_UOM IN VARCHAR2) RETURN CHAR IS
739 L_TRACK_IND VARCHAR2(20);
740 L_PARENT_SEC_UOM NUMBER;
741 BEGIN
742 IF PARENT_ITEM_ID IS NULL THEN
743 RETURN ('');
744 ELSE
745 SELECT
746 TRACKING_QUANTITY_IND
747 INTO L_TRACK_IND
748 FROM
749 MTL_SYSTEM_ITEMS_B
750 WHERE INVENTORY_ITEM_ID = PARENT_ITEM_ID
751 AND ORGANIZATION_ID = P_ORG_ID;
752 IF (L_TRACK_IND = 'PS') THEN
753 L_PARENT_SEC_UOM := PARENT_SEC_TXN_UOM;
754 ELSE
755 L_PARENT_SEC_UOM := NULL;
756 END IF;
757 END IF;
758 RETURN (L_PARENT_SEC_UOM);
759 END CF_PARENT_SEC_UOMFORMULA;
760 FUNCTION CF_PARENT_SEC_QTYFORMULA(PARENT_ITEM_ID IN NUMBER
761 ,PARENT_SEC_TXN_QTY IN NUMBER) RETURN NUMBER IS
762 L_TRACK_IND VARCHAR2(20);
763 L_PARENT_SEC_QTY NUMBER;
764 BEGIN
765 IF PARENT_ITEM_ID IS NULL THEN
766 RETURN ('');
767 ELSE
768 SELECT
769 TRACKING_QUANTITY_IND
770 INTO L_TRACK_IND
771 FROM
772 MTL_SYSTEM_ITEMS_B
773 WHERE INVENTORY_ITEM_ID = PARENT_ITEM_ID
774 AND ORGANIZATION_ID = P_ORG_ID;
775 IF (L_TRACK_IND = 'PS') THEN
776 L_PARENT_SEC_QTY := PARENT_SEC_TXN_QTY;
777 ELSE
778 L_PARENT_SEC_QTY := NULL;
779 END IF;
780 END IF;
781 RETURN (L_PARENT_SEC_QTY);
782 END CF_PARENT_SEC_QTYFORMULA;
783 FUNCTION CP_SO_ORDER_NUMBER_P RETURN NUMBER IS
784 BEGIN
785 RETURN CP_SO_ORDER_NUMBER;
786 END CP_SO_ORDER_NUMBER_P;
787 FUNCTION CP_SO_LINE_NUMBER_P RETURN NUMBER IS
788 BEGIN
789 RETURN CP_SO_LINE_NUMBER;
790 END CP_SO_LINE_NUMBER_P;
791 FUNCTION CP_SO_DELIVERY_NAME_P RETURN NUMBER IS
792 BEGIN
793 RETURN CP_SO_DELIVERY_NAME;
794 END CP_SO_DELIVERY_NAME_P;
795 FUNCTION CP_WIP_JOB_P RETURN VARCHAR2 IS
796 BEGIN
797 RETURN CP_WIP_JOB;
798 END CP_WIP_JOB_P;
799 FUNCTION CP_WIP_DEPARTMENT_P RETURN VARCHAR2 IS
800 BEGIN
801 RETURN CP_WIP_DEPARTMENT;
802 END CP_WIP_DEPARTMENT_P;
803 FUNCTION CP_WIP_LINE_P RETURN VARCHAR2 IS
804 BEGIN
805 RETURN CP_WIP_LINE;
806 END CP_WIP_LINE_P;
807 FUNCTION CP_WIP_ENTITY_TYPE_P RETURN NUMBER IS
808 BEGIN
809 RETURN CP_WIP_ENTITY_TYPE;
810 END CP_WIP_ENTITY_TYPE_P;
811 FUNCTION CP_WIP_START_DATE_P RETURN VARCHAR2 IS
812 BEGIN
813 RETURN CP_WIP_START_DATE;
814 END CP_WIP_START_DATE_P;
815 FUNCTION CP_WIP_OPERATION_P RETURN VARCHAR2 IS
816 BEGIN
817 RETURN CP_WIP_OPERATION;
818 END CP_WIP_OPERATION_P;
819 FUNCTION CP_CHART_OF_ACCOUNTS_NUM_P RETURN NUMBER IS
820 BEGIN
821 RETURN CP_CHART_OF_ACCOUNTS_NUM;
822 END CP_CHART_OF_ACCOUNTS_NUM_P;
823 END INV_INVTOPKL_XMLP_PKG;
824
[Home] [Help]