1 PACKAGE BODY INV_INVTOPKL_XMLP_PKG AS
2 /* $Header: INVTOPKLB.pls 120.3 2008/05/02 07:11:05 dwkrishn 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 BEGIN
282 IF PROJECT_ID IS NOT NULL THEN
283 BEGIN
284 SELECT
285 PROJECT_NUMBER
286 INTO L_PROJECT_NUMBER
287 FROM
288 PJM_PROJECTS_V PJMP
289 WHERE PROJECT_ID = PJMP.PROJECT_ID;
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 BEGIN
293 SELECT
294 PROJECT_NUMBER
295 INTO L_PROJECT_NUMBER
296 FROM
297 PA_PROJECTS_EXPEND_V PPEV
298 WHERE PROJECT_ID = PPEV.PROJECT_ID
299 AND ROWNUM = 1;
300 EXCEPTION
301 WHEN OTHERS THEN
302 L_PROJECT_NUMBER := NULL;
303 END;
304 WHEN OTHERS THEN
305 L_PROJECT_NUMBER := NULL;
306 END;
307 END IF;
308 RETURN (L_PROJECT_NUMBER);
309 EXCEPTION
310 WHEN OTHERS THEN
311 RETURN (NULL);
312 END CF_PROJECT_NUMBERFORMULA;
313 FUNCTION CF_TASK_NUMBERFORMULA(TASK_ID IN NUMBER
314 ,PROJECT_ID IN NUMBER) RETURN CHAR IS
315 L_TASK_NUMBER VARCHAR2(100);
316 BEGIN
317 IF TASK_ID IS NOT NULL THEN
318 BEGIN
319 SELECT
320 TASK_NUMBER
321 INTO L_TASK_NUMBER
322 FROM
323 PJM_TASKS_V PJMT
324 WHERE PROJECT_ID = PJMT.PROJECT_ID
325 AND TASK_ID = PJMT.TASK_ID;
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 BEGIN
329 SELECT
330 TASK_NUMBER
331 INTO L_TASK_NUMBER
332 FROM
333 PA_TASKS_EXPEND_V PTEV
334 WHERE PROJECT_ID = PTEV.PROJECT_ID
335 AND TASK_ID = PTEV.TASK_ID
336 AND ROWNUM = 1;
337 EXCEPTION
338 WHEN OTHERS THEN
339 L_TASK_NUMBER := NULL;
340 END;
341 WHEN OTHERS THEN
342 L_TASK_NUMBER := NULL;
343 END;
344 END IF;
345 RETURN (L_TASK_NUMBER);
346 EXCEPTION
347 WHEN OTHERS THEN
348 RETURN (NULL);
349 END CF_TASK_NUMBERFORMULA;
350 FUNCTION P_MOVE_ORDER_TYPEVALIDTRIGGER RETURN BOOLEAN IS
351 BEGIN
352 SELECT
353 MEANING
354 INTO P_MOVE_ORDER_TYPE_MEANING
355 FROM
356 MFG_LOOKUPS
357 WHERE LOOKUP_TYPE = 'INV_PICK_SLIP_MO_TYPES'
358 AND LOOKUP_CODE = P_MOVE_ORDER_TYPE;
359 RETURN (TRUE);
360 EXCEPTION
361 WHEN OTHERS THEN
362 RETURN TRUE;
363 END P_MOVE_ORDER_TYPEVALIDTRIGGER;
364 FUNCTION P_PICK_SLIP_GROUP_RULE_IDVALID RETURN BOOLEAN IS
365 BEGIN
366 IF P_PICK_SLIP_GROUP_RULE_ID IS NOT NULL THEN
367 SELECT
368 NAME
369 INTO P_PICK_SLIP_GROUP_RULE_NAME
370 FROM
371 WSH_PICK_GROUPING_RULES
372 WHERE PICK_GROUPING_RULE_ID = P_PICK_SLIP_GROUP_RULE_ID;
373 END IF;
374 RETURN (TRUE);
375 EXCEPTION
376 WHEN OTHERS THEN
377 RETURN TRUE;
378 END P_PICK_SLIP_GROUP_RULE_IDVALID;
379 FUNCTION P_PRINT_OPTIONVALIDTRIGGER RETURN BOOLEAN IS
380 L_PRINT_OPTION NUMBER;
381 BEGIN
382 IF P_PRINT_OPTION = '1' THEN
383 L_PRINT_OPTION := 1;
384 ELSIF P_PRINT_OPTION = '2' THEN
385 L_PRINT_OPTION := 2;
386 ELSIF P_PRINT_OPTION = '3' THEN
387 L_PRINT_OPTION := 3;
388 ELSIF P_PRINT_OPTION = '4' THEN
389 L_PRINT_OPTION := 4;
390 ELSIF P_PRINT_OPTION = '5' THEN
391 L_PRINT_OPTION := 5;
392 ELSE
393 L_PRINT_OPTION := 99;
394 END IF;
395 SELECT
396 MEANING
397 INTO P_PRINT_OPTION_MEANING
398 FROM
399 MFG_LOOKUPS
400 WHERE LOOKUP_TYPE = 'INV_PICK_SLIP_PRINT_OPTIONS'
401 AND LOOKUP_CODE = L_PRINT_OPTION;
402 RETURN (TRUE);
403 EXCEPTION
404 WHEN OTHERS THEN
405 RETURN TRUE;
406 END P_PRINT_OPTIONVALIDTRIGGER;
407 FUNCTION CF_WIP_INFOFORMULA(LINE_ID_P IN NUMBER
408 ,MOVE_ORDER_TYPE IN NUMBER
409 ,TRANSACTION_SOURCE_TYPE_ID IN NUMBER) RETURN NUMBER IS
410 CURSOR C_WIP_ENTITY_TYPE IS
411 SELECT
412 WE.ENTITY_TYPE
413 FROM
414 WIP_ENTITIES WE,
415 MTL_TXN_REQUEST_LINES MTRL
416 WHERE MTRL.LINE_ID = LINE_ID
417 AND WE.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID;
418 CURSOR C_DISCRETE_INFO IS
419 SELECT
420 WE.WIP_ENTITY_NAME JOB,
421 WL.LINE_CODE LINE,
422 WO.OPERATION_SEQ_NUM OPERATION,
423 BD.DEPARTMENT_CODE DEPARTMENT,
424 TO_CHAR(NULL) START_DATE
425 FROM
426 WIP_ENTITIES WE,
427 WIP_LINES WL,
428 BOM_DEPARTMENTS BD,
429 WIP_OPERATIONS WO,
430 WIP_DISCRETE_JOBS WDJ,
431 MTL_TXN_REQUEST_LINES MTRL
432 WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
433 AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
434 AND WDJ.LINE_ID = wl.line_id (+)
435 AND WDJ.ORGANIZATION_ID = wl.organization_id (+)
436 AND WO.DEPARTMENT_ID = bd.department_id (+)
437 AND MTRL.TXN_SOURCE_ID = wo.wip_entity_id (+)
438 AND MTRL.ORGANIZATION_ID = wo.organization_id (+)
439 AND MTRL.TXN_SOURCE_LINE_ID = wo.operation_seq_num (+)
440 AND WDJ.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID
441 AND WDJ.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
442 AND MTRL.LINE_ID = LINE_ID_P;
443 CURSOR C_REPETITIVE_INFO IS
444 SELECT
445 MSIK.CONCATENATED_SEGMENTS JOB,
446 WL.LINE_CODE LINE,
447 WO.OPERATION_SEQ_NUM OPERATION,
448 BD.DEPARTMENT_CODE DEPARTMENT,
449 TO_CHAR(WRS.FIRST_UNIT_START_DATE) START_DATE
450 FROM
451 MTL_SYSTEM_ITEMS_KFV MSIK,
452 WIP_ENTITIES WE,
453 WIP_LINES WL,
454 BOM_DEPARTMENTS BD,
455 WIP_OPERATIONS WO,
456 WIP_REPETITIVE_SCHEDULES WRS,
457 MTL_TXN_REQUEST_LINES MTRL
458 WHERE MSIK.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
459 AND MSIK.ORGANIZATION_ID = WE.ORGANIZATION_ID
460 AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
461 AND WE.ORGANIZATION_ID = WRS.ORGANIZATION_ID
462 AND WL.LINE_ID = WRS.LINE_ID
463 AND WL.ORGANIZATION_ID = WRS.ORGANIZATION_ID
464 AND bd.department_id (+) = WO.DEPARTMENT_ID
465 AND wo.wip_entity_id (+) = MTRL.TXN_SOURCE_ID
466 AND wo.operation_seq_num (+) = MTRL.TXN_SOURCE_LINE_ID
467 AND wo.organization_id (+) = MTRL.ORGANIZATION_ID
468 AND wo.repetitive_schedule_id (+) = MTRL.REFERENCE_ID
469 AND WRS.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID
470 AND WRS.REPETITIVE_SCHEDULE_ID = MTRL.REFERENCE_ID
471 AND WRS.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
472 AND MTRL.LINE_ID = LINE_ID_P;
473 CURSOR C_FLOW_INFO IS
474 SELECT
475 WE.WIP_ENTITY_NAME JOB,
476 WL.LINE_CODE LINE,
477 BOS2.OPERATION_SEQ_NUM OPERATION,
478 BD.DEPARTMENT_CODE DEPARTMENT,
479 TO_CHAR(NULL) START_DATE
480 FROM
481 WIP_ENTITIES WE,
482 WIP_LINES WL,
483 BOM_DEPARTMENTS BD,
484 BOM_OPERATION_SEQUENCES BOS2,
485 BOM_OPERATION_SEQUENCES BOS,
486 BOM_OPERATIONAL_ROUTINGS BOR,
487 WIP_FLOW_SCHEDULES WFS,
488 MTL_TXN_REQUEST_LINES MTRL
489 WHERE WE.WIP_ENTITY_ID = WFS.WIP_ENTITY_ID
490 AND WE.ORGANIZATION_ID = WFS.ORGANIZATION_ID
491 AND WL.LINE_ID = WFS.LINE_ID
492 AND WL.ORGANIZATION_ID = WFS.ORGANIZATION_ID
493 AND BD.DEPARTMENT_ID = BOS2.DEPARTMENT_ID
494 AND BOS2.OPERATION_SEQUENCE_ID = BOS.LINE_OP_SEQ_ID
495 AND BOS2.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
496 AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
497 AND BOS.OPERATION_TYPE = 1
498 AND ( BOR.ALTERNATE_ROUTING_DESIGNATOR = WFS.ALTERNATE_ROUTING_DESIGNATOR
499 OR ( WFS.ALTERNATE_ROUTING_DESIGNATOR IS NULL
500 AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL ) )
501 AND BOR.ASSEMBLY_ITEM_ID = WFS.PRIMARY_ITEM_ID
502 AND BOR.ORGANIZATION_ID = WFS.ORGANIZATION_ID
503 AND WFS.WIP_ENTITY_ID = MTRL.TXN_SOURCE_ID
504 AND WFS.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
505 AND MTRL.LINE_ID = LINE_ID_P;
506 L_WIP_INFO C_DISCRETE_INFO%ROWTYPE;
507 BEGIN
508 CP_WIP_ENTITY_TYPE := NULL;
509 IF (MOVE_ORDER_TYPE = 5 AND TRANSACTION_SOURCE_TYPE_ID in (5,13)) THEN
510 OPEN C_WIP_ENTITY_TYPE;
511 FETCH C_WIP_ENTITY_TYPE
512 INTO CP_WIP_ENTITY_TYPE;
513 CLOSE C_WIP_ENTITY_TYPE;
514 IF CP_WIP_ENTITY_TYPE in (1,5) THEN
515 OPEN C_DISCRETE_INFO;
516 FETCH C_DISCRETE_INFO
517 INTO L_WIP_INFO;
518 CLOSE C_DISCRETE_INFO;
519 ELSIF CP_WIP_ENTITY_TYPE = 2 THEN
520 OPEN C_REPETITIVE_INFO;
521 FETCH C_REPETITIVE_INFO
522 INTO L_WIP_INFO;
523 CLOSE C_REPETITIVE_INFO;
524 ELSIF CP_WIP_ENTITY_TYPE = 4 THEN
525 OPEN C_FLOW_INFO;
526 FETCH C_FLOW_INFO
527 INTO L_WIP_INFO;
528 CLOSE C_FLOW_INFO;
529 END IF;
530 END IF;
531 CP_WIP_JOB := L_WIP_INFO.JOB;
532 CP_WIP_LINE := L_WIP_INFO.LINE;
533 CP_WIP_OPERATION := L_WIP_INFO.OPERATION;
534 CP_WIP_DEPARTMENT := L_WIP_INFO.DEPARTMENT;
535 CP_WIP_START_DATE := L_WIP_INFO.START_DATE;
536 RETURN 1;
537 EXCEPTION
538 WHEN OTHERS THEN
539 RETURN -1;
540 END CF_WIP_INFOFORMULA;
541 FUNCTION CF_SO_INFOFORMULA(LINE_ID IN NUMBER
542 ,MOVE_ORDER_TYPE IN NUMBER) RETURN NUMBER IS
543 CURSOR C_SO_INFO IS
544 SELECT
545 WDD.SOURCE_HEADER_NUMBER SO_NUMBER,
546 OEL.LINE_NUMBER SO_LINE_NUMBER,
547 WND.NAME DELIVERY_NAME
548 FROM
549 OE_ORDER_LINES_ALL OEL,
550 WSH_DELIVERY_DETAILS WDD,
551 WSH_DELIVERY_ASSIGNMENTS WDA,
552 WSH_NEW_DELIVERIES WND
553 WHERE WDD.MOVE_ORDER_LINE_ID = CF_SO_INFOFORMULA.LINE_ID
554 AND WDD.SOURCE_LINE_ID = OEL.LINE_ID
555 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
556 AND WND.DELIVERY_ID (+) = WDA.DELIVERY_ID;
557 BEGIN
558 IF MOVE_ORDER_TYPE = 3 THEN
559 OPEN C_SO_INFO;
560 FETCH C_SO_INFO
561 INTO CP_SO_ORDER_NUMBER,CP_SO_LINE_NUMBER,CP_SO_DELIVERY_NAME;
562 CLOSE C_SO_INFO;
563 END IF;
564 RETURN 1;
565 EXCEPTION
566 WHEN OTHERS THEN
567 RETURN -1;
568 END CF_SO_INFOFORMULA;
569 PROCEDURE INV_CALL_ALLOCATIONS_ENGINE IS
570 L_DETAIL_STATUS VARCHAR2(10);
571 BEGIN
572 IF (P_MOVE_ORDER_TYPE IS NOT NULL AND P_ORG_ID IS NOT NULL) THEN
573 INV_PICK_SLIP_REPORT.RUN_DETAIL_ENGINE(L_DETAIL_STATUS
574 ,P_ORG_ID
575 ,P_MOVE_ORDER_TYPE
576 ,P_MOVE_ORDER_LOW
577 ,P_MOVE_ORDER_HIGH
578 ,P_SOURCE_SUBINV
579 ,P_SOURCE_LOCATOR_ID
580 ,P_DEST_SUBINV
581 ,P_DEST_LOCATOR_ID
582 ,P_SALES_ORDER_LOW
583 ,P_SALES_ORDER_HIGH
584 ,P_FREIGHT_CODE
585 ,P_CUSTOMER_ID
586 ,P_REQUESTED_BY
587 ,P_DATE_REQD_LO
588 ,P_DATE_REQD_HI
589 ,(P_PLAN_TASKS = 'Y')
590 ,P_PICK_SLIP_GROUP_RULE_ID
591 ,P_CONC_REQUEST_ID);
592 IF L_DETAIL_STATUS in ('E','U') THEN
593 /*SRW.MESSAGE(1
594 ,'Detail Engine Failed to release some/all lines')*/NULL;
595 END IF;
596 END IF;
597 END INV_CALL_ALLOCATIONS_ENGINE;
598 FUNCTION P_CUSTOMER_IDVALIDTRIGGER RETURN BOOLEAN IS
599 BEGIN
600 IF P_CUSTOMER_ID IS NOT NULL THEN
601 BEGIN
602 SELECT
603 PARTY.PARTY_NAME
604 INTO P_CUSTOMER_NAME
605 FROM
606 HZ_PARTIES PARTY,
607 HZ_CUST_ACCOUNTS CUST_ACCT
608 WHERE CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER_ID
609 AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID;
610 EXCEPTION
611 WHEN OTHERS THEN
612 NULL;
613 END;
614 END IF;
615 RETURN TRUE;
616 EXCEPTION
617 WHEN OTHERS THEN
618 RETURN TRUE;
619 END P_CUSTOMER_IDVALIDTRIGGER;
620 FUNCTION CF_TASK_STATUSFORMULA(TASK_STATUS IN NUMBER) RETURN CHAR IS
621 L_MEANING VARCHAR2(20);
622 BEGIN
623 IF TASK_STATUS IS NOT NULL AND TASK_STATUS <> 0 THEN
624 SELECT
625 MEANING
626 INTO L_MEANING
627 FROM
628 MFG_LOOKUPS
629 WHERE LOOKUP_TYPE = 'WMS_TASK_STATUS'
630 AND LOOKUP_CODE = TASK_STATUS;
631 ELSIF TASK_STATUS = 0 THEN
632 L_MEANING := P_CONST_UNALLOCATED;
633 END IF;
634 RETURN L_MEANING;
635 EXCEPTION
636 WHEN OTHERS THEN
637 RETURN NULL;
638 END CF_TASK_STATUSFORMULA;
639 FUNCTION CF_TASK_IDFORMULA(TASK_STATUS IN NUMBER
640 ,PARENT_LINE_ID IN NUMBER
641 ,TRANSACTION_ID IN NUMBER) RETURN NUMBER IS
642 BEGIN
643 IF TASK_STATUS not in (0,6) AND P_WMS_INSTALL = 'Y' THEN
644 RETURN NVL(PARENT_LINE_ID
645 ,TRANSACTION_ID);
646 ELSE
647 RETURN NULL;
648 END IF;
649 END CF_TASK_IDFORMULA;
650 FUNCTION P_ALLOCATE_MOVE_ORDERVALIDTRIG RETURN BOOLEAN IS
651 BEGIN
652 IF P_ALLOCATE_MOVE_ORDER IS NOT NULL THEN
653 SELECT
654 MEANING
655 INTO P_ALLOCATE_MOVE_ORDER_MEANING
656 FROM
657 FND_LOOKUPS
658 WHERE LOOKUP_TYPE = 'YES_NO'
659 AND LOOKUP_CODE = P_ALLOCATE_MOVE_ORDER;
660 END IF;
661 RETURN TRUE;
662 EXCEPTION
663 WHEN OTHERS THEN
664 RETURN TRUE;
665 END P_ALLOCATE_MOVE_ORDERVALIDTRIG;
666 FUNCTION P_PLAN_TASKSVALIDTRIGGER RETURN BOOLEAN IS
667 BEGIN
668 IF P_PLAN_TASKS IS NOT NULL THEN
669 SELECT
670 MEANING
671 INTO P_PLAN_TASKS_MEANING
672 FROM
673 FND_LOOKUPS
674 WHERE LOOKUP_TYPE = 'YES_NO'
675 AND LOOKUP_CODE = P_PLAN_TASKS;
676 END IF;
677 RETURN TRUE;
678 EXCEPTION
679 WHEN OTHERS THEN
680 RETURN TRUE;
681 END P_PLAN_TASKSVALIDTRIGGER;
682 FUNCTION P_FREIGHT_CODEVALIDTRIGGER RETURN BOOLEAN IS
683 BEGIN
684 RETURN (TRUE);
685 END P_FREIGHT_CODEVALIDTRIGGER;
686 FUNCTION CF_SEC_QTYFORMULA(INVENTORY_ITEM_ID_1 IN NUMBER
687 ,SEC_TRANSACTION_QTY IN NUMBER) RETURN NUMBER IS
688 L_TRACK_IND VARCHAR2(20);
689 L_SEC_QTY NUMBER;
690 BEGIN
691 IF INVENTORY_ITEM_ID_1 IS NOT NULL THEN
692 SELECT
693 TRACKING_QUANTITY_IND
694 INTO L_TRACK_IND
695 FROM
696 MTL_SYSTEM_ITEMS_B
697 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
698 AND ORGANIZATION_ID = P_ORG_ID;
699 IF (L_TRACK_IND = 'PS') THEN
700 L_SEC_QTY := SEC_TRANSACTION_QTY;
701 ELSE
702 L_SEC_QTY := NULL;
703 END IF;
704 END IF;
705 RETURN (L_SEC_QTY);
706 END CF_SEC_QTYFORMULA;
707 FUNCTION CF_SEC_UOMFORMULA(INVENTORY_ITEM_ID_1 IN NUMBER
708 ,SEC_UOM IN VARCHAR2) RETURN CHAR IS
709 L_TRACK_IND VARCHAR2(20);
710 L_SEC_UOM VARCHAR2(10);
711 BEGIN
712 IF INVENTORY_ITEM_ID_1 IS NOT NULL THEN
713 SELECT
714 TRACKING_QUANTITY_IND
715 INTO L_TRACK_IND
716 FROM
717 MTL_SYSTEM_ITEMS_B
718 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
719 AND ORGANIZATION_ID = P_ORG_ID;
720 IF (L_TRACK_IND = 'PS') THEN
721 L_SEC_UOM := SEC_UOM;
722 ELSE
723 L_SEC_UOM := NULL;
724 END IF;
725 END IF;
726 RETURN (L_SEC_UOM);
727 END CF_SEC_UOMFORMULA;
728 FUNCTION CF_PARENT_SEC_UOMFORMULA(PARENT_ITEM_ID IN NUMBER
729 ,PARENT_SEC_TXN_UOM IN VARCHAR2) RETURN CHAR IS
730 L_TRACK_IND VARCHAR2(20);
731 L_PARENT_SEC_UOM NUMBER;
732 BEGIN
733 IF PARENT_ITEM_ID IS NULL THEN
734 RETURN ('');
735 ELSE
736 SELECT
737 TRACKING_QUANTITY_IND
738 INTO L_TRACK_IND
739 FROM
740 MTL_SYSTEM_ITEMS_B
741 WHERE INVENTORY_ITEM_ID = PARENT_ITEM_ID
742 AND ORGANIZATION_ID = P_ORG_ID;
743 IF (L_TRACK_IND = 'PS') THEN
744 L_PARENT_SEC_UOM := PARENT_SEC_TXN_UOM;
745 ELSE
746 L_PARENT_SEC_UOM := NULL;
747 END IF;
748 END IF;
749 RETURN (L_PARENT_SEC_UOM);
750 END CF_PARENT_SEC_UOMFORMULA;
751 FUNCTION CF_PARENT_SEC_QTYFORMULA(PARENT_ITEM_ID IN NUMBER
752 ,PARENT_SEC_TXN_QTY IN NUMBER) RETURN NUMBER IS
753 L_TRACK_IND VARCHAR2(20);
754 L_PARENT_SEC_QTY NUMBER;
755 BEGIN
756 IF PARENT_ITEM_ID IS NULL THEN
757 RETURN ('');
758 ELSE
759 SELECT
760 TRACKING_QUANTITY_IND
761 INTO L_TRACK_IND
762 FROM
763 MTL_SYSTEM_ITEMS_B
764 WHERE INVENTORY_ITEM_ID = PARENT_ITEM_ID
765 AND ORGANIZATION_ID = P_ORG_ID;
766 IF (L_TRACK_IND = 'PS') THEN
767 L_PARENT_SEC_QTY := PARENT_SEC_TXN_QTY;
768 ELSE
769 L_PARENT_SEC_QTY := NULL;
770 END IF;
771 END IF;
772 RETURN (L_PARENT_SEC_QTY);
773 END CF_PARENT_SEC_QTYFORMULA;
774 FUNCTION CP_SO_ORDER_NUMBER_P RETURN NUMBER IS
775 BEGIN
776 RETURN CP_SO_ORDER_NUMBER;
777 END CP_SO_ORDER_NUMBER_P;
778 FUNCTION CP_SO_LINE_NUMBER_P RETURN NUMBER IS
779 BEGIN
780 RETURN CP_SO_LINE_NUMBER;
781 END CP_SO_LINE_NUMBER_P;
782 FUNCTION CP_SO_DELIVERY_NAME_P RETURN NUMBER IS
783 BEGIN
784 RETURN CP_SO_DELIVERY_NAME;
785 END CP_SO_DELIVERY_NAME_P;
786 FUNCTION CP_WIP_JOB_P RETURN VARCHAR2 IS
787 BEGIN
788 RETURN CP_WIP_JOB;
789 END CP_WIP_JOB_P;
790 FUNCTION CP_WIP_DEPARTMENT_P RETURN VARCHAR2 IS
791 BEGIN
792 RETURN CP_WIP_DEPARTMENT;
793 END CP_WIP_DEPARTMENT_P;
794 FUNCTION CP_WIP_LINE_P RETURN VARCHAR2 IS
795 BEGIN
796 RETURN CP_WIP_LINE;
797 END CP_WIP_LINE_P;
798 FUNCTION CP_WIP_ENTITY_TYPE_P RETURN NUMBER IS
799 BEGIN
800 RETURN CP_WIP_ENTITY_TYPE;
801 END CP_WIP_ENTITY_TYPE_P;
802 FUNCTION CP_WIP_START_DATE_P RETURN VARCHAR2 IS
803 BEGIN
804 RETURN CP_WIP_START_DATE;
805 END CP_WIP_START_DATE_P;
806 FUNCTION CP_WIP_OPERATION_P RETURN VARCHAR2 IS
807 BEGIN
808 RETURN CP_WIP_OPERATION;
809 END CP_WIP_OPERATION_P;
810 FUNCTION CP_CHART_OF_ACCOUNTS_NUM_P RETURN NUMBER IS
811 BEGIN
812 RETURN CP_CHART_OF_ACCOUNTS_NUM;
813 END CP_CHART_OF_ACCOUNTS_NUM_P;
814 END INV_INVTOPKL_XMLP_PKG;
815
[Home] [Help]