1 PACKAGE BODY PO_POXDLPDT_XMLP_PKG AS
2 /* $Header: POXDLPDTB.pls 120.1.12020000.3 2013/02/10 17:13:11 vegajula ship $ */
3 FUNCTION GET_P_STRUCT_NUM RETURN BOOLEAN IS
4 L_P_STRUCT_NUM NUMBER;
5 BEGIN
6 SELECT
7 STRUCTURE_ID
8 INTO L_P_STRUCT_NUM
9 FROM
10 MTL_DEFAULT_SETS_VIEW
11 WHERE FUNCTIONAL_AREA_ID = 2;
12 P_STRUCT_NUM := L_P_STRUCT_NUM;
13 RETURN (TRUE);
14 RETURN NULL;
15 EXCEPTION
16 WHEN OTHERS THEN
17 RETURN (FALSE);
18
19 END GET_P_STRUCT_NUM;
20
21 FUNCTION BUYER_PREPARERFORMULA(RRP_TRANSACTION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
22 BEGIN
23 RETURN BOILER_PLATE_BUYER(RRP_TRANSACTION_TYPE);
24 END BUYER_PREPARERFORMULA;
25
26 FUNCTION DIRECT_RECEIVING_CHECK(RRP_FROM_INTERFACE IN VARCHAR2
27 ,RRP_TRANSACTION_ID IN NUMBER) RETURN BOOLEAN IS
28 L_USER_ENTERED_FLAG RCV_TRANSACTIONS.USER_ENTERED_FLAG%TYPE;
29 L_PARENT_TRANSACTION_ID RCV_TRANSACTIONS.PARENT_TRANSACTION_ID%TYPE;
30 L_TRANSACTION_ID RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
31 L_AUTO_TRANSACT_CODE RCV_TRANSACTIONS_INTERFACE.AUTO_TRANSACT_CODE%TYPE;
32 L_TRANSACTION_TYPE RCV_TRANSACTIONS_INTERFACE.TRANSACTION_TYPE%TYPE;
33 BEGIN
34 IF (RRP_FROM_INTERFACE = 'N') THEN
35 BEGIN
36 SELECT
37 TRANSACTION_TYPE
38 INTO L_TRANSACTION_TYPE
39 FROM
40 RCV_TRANSACTIONS
41 WHERE TRANSACTION_ID = RRP_TRANSACTION_ID;
42 IF (L_TRANSACTION_TYPE = 'DELIVER') THEN
43 RETURN FALSE;
44 ELSE
45 RETURN TRUE;
46 END IF;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 RETURN TRUE;
50
51 WHEN OTHERS THEN
52 RETURN TRUE;
53
54 END;
55 ELSE
56 BEGIN
57 SELECT
58 AUTO_TRANSACT_CODE,
59 TRANSACTION_TYPE
60 INTO L_AUTO_TRANSACT_CODE,L_TRANSACTION_TYPE
61 FROM
62 RCV_TRANSACTIONS_INTERFACE
63 WHERE INTERFACE_TRANSACTION_ID = RRP_TRANSACTION_ID;
64 BEGIN
65 IF (L_AUTO_TRANSACT_CODE = 'DELIVER') THEN
66 RETURN FALSE;
67 ELSIF L_AUTO_TRANSACT_CODE IS NULL THEN
68 IF (L_TRANSACTION_TYPE = 'DELIVER') THEN
69 RETURN FALSE;
70 ELSE
71 RETURN TRUE;
72 END IF;
73 END IF;
74 END;
75 EXCEPTION
76 WHEN OTHERS THEN
77 RETURN TRUE;
78
79 END;
80 END IF;
81 RETURN NULL;
82 END DIRECT_RECEIVING_CHECK;
83
84 FUNCTION BLIND_RECEIVING_CHECK(RRP_ORGANIZATION_ID IN NUMBER) RETURN BOOLEAN IS
85 L_BLIND_RECEIVING_FLAG RCV_PARAMETERS.BLIND_RECEIVING_FLAG%TYPE;
86 BEGIN
87 SELECT
88 BLIND_RECEIVING_FLAG
89 INTO L_BLIND_RECEIVING_FLAG
90 FROM
91 RCV_PARAMETERS
92 WHERE ORGANIZATION_ID = RRP_ORGANIZATION_ID;
93 IF (L_BLIND_RECEIVING_FLAG = 'Y') THEN
94 RETURN FALSE;
95 ELSE
96 RETURN TRUE;
97 END IF;
98 RETURN NULL;
99 EXCEPTION
100 WHEN OTHERS THEN
101 RETURN TRUE;
102
103 END BLIND_RECEIVING_CHECK;
104
105 FUNCTION BOILER_PLATE_DOC_TYPE(RRP_TRANSACTION_TYPE IN VARCHAR2) RETURN CHARACTER IS
106 L_TRANSACTION_TYPE VARCHAR2(20);
107 L_DISPLAYED_FIELD PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
108 BEGIN
109 L_TRANSACTION_TYPE := RRP_TRANSACTION_TYPE;
110 IF (L_TRANSACTION_TYPE = 'IN TRANSIT') THEN
111 BEGIN
112 SELECT
113 DISPLAYED_FIELD
114 INTO L_DISPLAYED_FIELD
115 FROM
116 PO_LOOKUP_CODES
117 WHERE LOOKUP_TYPE = 'POXDLPDT TRANSLATE'
118 AND LOOKUP_CODE = 'TYPE';
119 RETURN (L_DISPLAYED_FIELD);
120 END;
121 ELSE
122 BEGIN
123 SELECT
124 DISPLAYED_FIELD
125 INTO L_DISPLAYED_FIELD
126 FROM
127 PO_LOOKUP_CODES
128 WHERE LOOKUP_TYPE = 'POXDLPDT TRANSLATE'
129 AND LOOKUP_CODE = 'DOCUMENT TYPE';
130 RETURN (L_DISPLAYED_FIELD);
131 END;
132 END IF;
133 RETURN NULL;
134 END BOILER_PLATE_DOC_TYPE;
135
136 FUNCTION BOILER_PLATE_DOC_NUM(RRP_TRANSACTION_TYPE IN VARCHAR2) RETURN CHARACTER IS
137 L_TRANSACTION_TYPE VARCHAR2(20);
138 L_DISPLAYED_FIELD PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
139 BEGIN
140 L_TRANSACTION_TYPE := RRP_TRANSACTION_TYPE;
141 IF (L_TRANSACTION_TYPE = 'IN TRANSIT') THEN
142 BEGIN
143 SELECT
144 DISPLAYED_FIELD
145 INTO L_DISPLAYED_FIELD
146 FROM
147 PO_LOOKUP_CODES
148 WHERE LOOKUP_TYPE = 'POXDLPDT TRANSLATE'
149 AND LOOKUP_CODE = 'SHIPMENT NUMBER';
150 RETURN (L_DISPLAYED_FIELD);
151 END;
152 ELSE
153 BEGIN
154 SELECT
155 DISPLAYED_FIELD
156 INTO L_DISPLAYED_FIELD
157 FROM
158 PO_LOOKUP_CODES
159 WHERE LOOKUP_TYPE = 'POXDLPDT TRANSLATE'
160 AND LOOKUP_CODE = 'DOCUMENT NUMBER';
161 RETURN (L_DISPLAYED_FIELD);
162 END;
163 END IF;
164 RETURN NULL;
165 END BOILER_PLATE_DOC_NUM;
166
167 FUNCTION BOILER_PLATE_BUYER(RRP_TRANSACTION_TYPE IN VARCHAR2) RETURN CHARACTER IS
168 L_TRANSACTION_TYPE VARCHAR2(20);
169 L_DISPLAYED_FIELD PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
170 BEGIN
171 L_TRANSACTION_TYPE := RRP_TRANSACTION_TYPE;
172 IF (L_TRANSACTION_TYPE = 'PO') THEN
173 BEGIN
174 SELECT
175 DISPLAYED_FIELD
176 INTO L_DISPLAYED_FIELD
177 FROM
178 PO_LOOKUP_CODES
179 WHERE LOOKUP_TYPE = 'POXDLPDT TRANSLATE'
180 AND LOOKUP_CODE = 'BUYER';
181 RETURN (L_DISPLAYED_FIELD);
182 END;
183 ELSIF (L_TRANSACTION_TYPE = 'REQ') THEN
184 BEGIN
185 SELECT
186 DISPLAYED_FIELD
187 INTO L_DISPLAYED_FIELD
188 FROM
189 PO_LOOKUP_CODES
190 WHERE LOOKUP_TYPE = 'POXDLPDT TRANSLATE'
191 AND LOOKUP_CODE = 'PREPARER';
192 RETURN (L_DISPLAYED_FIELD);
193 END;
194 ELSE
195 RETURN ('');
196 END IF;
197 RETURN NULL;
198 END BOILER_PLATE_BUYER;
199
200 FUNCTION DOC_TYPE_BOILERPLATEFORMULA(RRP_TRANSACTION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
201 BEGIN
202 RETURN BOILER_PLATE_DOC_TYPE(RRP_TRANSACTION_TYPE);
203 END DOC_TYPE_BOILERPLATEFORMULA;
204
205 FUNCTION DOC_NUM_BOILER_PLATEFORMULA(RRP_TRANSACTION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
206 BEGIN
207 RETURN BOILER_PLATE_DOC_NUM(RRP_TRANSACTION_TYPE);
208 END DOC_NUM_BOILER_PLATEFORMULA;
209
210 FUNCTION DELIVERY_CHECK(RRP_FROM_INTERFACE IN VARCHAR2
211 ,RRP_TRANSACTION_ID IN NUMBER) RETURN BOOLEAN IS
212 L_TRANSACTION_TYPE RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
213 L_TRANSACTION_ID RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
214 L_AUTO_TRANSACT_CODE RCV_TRANSACTIONS_INTERFACE.AUTO_TRANSACT_CODE%TYPE;
215 BEGIN
216 IF (RRP_FROM_INTERFACE = 'N') THEN
217 BEGIN
218 SELECT
219 TRANSACTION_TYPE
220 INTO L_TRANSACTION_TYPE
221 FROM
222 RCV_TRANSACTIONS
223 WHERE TRANSACTION_ID = RRP_TRANSACTION_ID;
224 IF (L_TRANSACTION_TYPE = 'DELIVER') THEN
225 RETURN FALSE;
226 END IF;
227 EXCEPTION
228 WHEN OTHERS THEN
229 RETURN TRUE;
230
231 END;
232 ELSE
233 BEGIN
234 SELECT
235 AUTO_TRANSACT_CODE
236 INTO L_AUTO_TRANSACT_CODE
237 FROM
238 RCV_TRANSACTIONS_INTERFACE
239 WHERE INTERFACE_TRANSACTION_ID = RRP_TRANSACTION_ID;
240 BEGIN
241 IF (L_AUTO_TRANSACT_CODE = 'DELIVER') THEN
242 RETURN FALSE;
243 ELSIF L_AUTO_TRANSACT_CODE IS NULL THEN
244 IF (L_TRANSACTION_TYPE = 'DELIVER') THEN
245 RETURN FALSE;
246 ELSE
247 RETURN TRUE;
248 END IF;
249 END IF;
250 END;
251 EXCEPTION
252 WHEN OTHERS THEN
253 RETURN TRUE;
254
255 END;
256 END IF;
257 RETURN NULL;
258 END DELIVERY_CHECK;
259
260 FUNCTION CHILD_DIRECT_RECEIVING_CHECK(RRP_FROM_INTERFACE IN VARCHAR2
261 ,RRP_TRANSACTION_ID IN NUMBER) RETURN BOOLEAN IS
262 L_USER_ENTERED_FLAG RCV_TRANSACTIONS.USER_ENTERED_FLAG%TYPE;
263 L_PARENT_TRANSACTION_ID RCV_TRANSACTIONS.PARENT_TRANSACTION_ID%TYPE;
264 L_TRANSACTION_TYPE RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
265 L_AUTO_TRANSACT_CODE RCV_TRANSACTIONS_INTERFACE.AUTO_TRANSACT_CODE%TYPE;
266 BEGIN
267 IF (RRP_FROM_INTERFACE = 'N') THEN
268 BEGIN
269 SELECT
270 USER_ENTERED_FLAG,
271 TRANSACTION_TYPE
272 INTO L_USER_ENTERED_FLAG,L_TRANSACTION_TYPE
273 FROM
274 RCV_TRANSACTIONS
275 WHERE PARENT_TRANSACTION_ID = RRP_TRANSACTION_ID;
276 IF (L_TRANSACTION_TYPE <> 'DELIVER') THEN
277 RETURN TRUE;
278 ELSIF (L_USER_ENTERED_FLAG = 'Y') THEN
279 RETURN TRUE;
280 ELSE
281 RETURN FALSE;
282 END IF;
283 EXCEPTION
284 WHEN TOO_MANY_ROWS THEN
285 RETURN TRUE;
286
287 WHEN NO_DATA_FOUND THEN
288 RETURN TRUE;
289
290 WHEN OTHERS THEN
291 RETURN TRUE;
292
293 END;
294 ELSE
295 RETURN TRUE;
296 END IF;
297 RETURN NULL;
298 END CHILD_DIRECT_RECEIVING_CHECK;
299
300 FUNCTION G_RECEIPTSGROUPFILTER(RRP_TRANSACTION_ID IN NUMBER
301 ,RRP_FROM_INTERFACE IN VARCHAR2) RETURN BOOLEAN IS
302 BEGIN
303 RETURN CHILD_DIRECT_RECEIVING_CHECK(RRP_FROM_INTERFACE
304 ,RRP_TRANSACTION_ID);
305 RETURN (TRUE);
306 END G_RECEIPTSGROUPFILTER;
307
308 FUNCTION C_UNION_UPPER_UPPERFORMULA RETURN VARCHAR2 IS
309 BEGIN
310 IF (P_WIP_STATUS = 'I') THEN
311 RETURN ('SELECT WE.WIP_ENTITY_NAME Work_Order
312 , to_char(null) Line
313 , WRO.DATE_REQUIRED Date_Required
314 , SUM(NVL(WRO.QUANTITY_ISSUED,0)) Quantity_Issued
315 , SUM(NVL(WRO.REQUIRED_QUANTITY,0)) Quantity_Required
316 , NVL(MOQ2.TRANSACTION_QUANTITY,0) Quantity_On_Hand
317 , ''D'' Processing_Mode');
318 END IF;
319 RETURN NULL;
320 END C_UNION_UPPER_UPPERFORMULA;
321
322 FUNCTION C_UNION_LOWER_UPPERFORMULA RETURN VARCHAR2 IS
323 BEGIN
324 IF (P_WIP_STATUS = 'I') THEN
325 RETURN ('SELECT WE.WIP_ENTITY_NAME Work_Order
326 , WL.LINE_CODE Line
327 , WRO.DATE_REQUIRED Date_Required
328 , SUM(WRO.QUANTITY_ISSUED) Quantity_Issued
329 , SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,BCD1.NEXT_SEQ_NUM + MSI.POSTPROCESSING_LEAD_TIME)
330 - GREATEST(BCD.NEXT_SEQ_NUM,BCD1.NEXT_SEQ_NUM))
331 * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
332 /* this is raw quantity required */
333 + -1 * SUM(WRO.QUANTITY_ISSUED -
334 GREATEST((BCD1.NEXT_SEQ_NUM-BCD.NEXT_SEQ_NUM),0)
335 *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE)
336 /* this is the quantity ahead behind after taking care of the negative sign as quantity ahead behind
337 will always be calulated as negative*/
338 Quantity_Required
339 , NVL(MOQ2.TRANSACTION_QUANTITY,0) Quantity_On_Hand
340 , ''R'' Processing_Mode');
341 END IF;
342 RETURN NULL;
343 END C_UNION_LOWER_UPPERFORMULA;
344
345 FUNCTION C_UNION_LOWER_LOWERFORMULA RETURN VARCHAR2 IS
346 BEGIN
347 IF (P_WIP_STATUS = 'I') THEN
348 RETURN ('FROM MTL_ONHAND_QUANTITIES MOQ2
349 , WIP_ENTITIES WE
350 , WIP_REQUIREMENT_OPERATIONS WRO
351 , WIP_REPETITIVE_SCHEDULES WRS
352 , BOM_CALENDAR_DATES BCD
353 , BOM_CALENDAR_DATES BCD1
354 , WIP_LINES WL
355 , MTL_PARAMETERS MP
356 , MTL_SYSTEM_ITEMS MSI
357 WHERE WRS.STATUS_TYPE IN (1,3,4,6)
358 AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
359 AND WE.ORGANIZATION_ID = WRS.ORGANIZATION_ID
360 AND MP.ORGANIZATION_ID = WRS.ORGANIZATION_ID
361 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
362 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
363 AND BCD1.CALENDAR_DATE = to_char(sysdate,''DD-MON-RR'')
364 AND NVL(MSI.ORGANIZATION_ID,MP.ORGANIZATION_ID)= MP.ORGANIZATION_ID
365 AND WRO.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
366 AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
367 AND WRO.ORGANIZATION_ID = WRS.ORGANIZATION_ID
368 AND WRO.WIP_SUPPLY_TYPE <> 6
369 AND WRO.REQUIRED_QUANTITY > 0
370 AND MOQ2.ORGANIZATION_ID (+) = WRO.ORGANIZATION_ID
371 AND MOQ2.INVENTORY_ITEM_ID (+) = WRO.INVENTORY_ITEM_ID
372 AND WL.LINE_ID = WRS.LINE_ID
373 AND WL.ORGANIZATION_ID = WRS.ORGANIZATION_ID
374 AND BCD.CALENDAR_CODE = MP.CALENDAR_CODE
375 AND BCD.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
376 AND BCD.CALENDAR_DATE = WRO.DATE_REQUIRED
377 AND (BCD.NEXT_SEQ_NUM < BCD1.NEXT_SEQ_NUM + MSI.POSTPROCESSING_LEAD_TIME)
378 AND ((BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS ) > BCD1.NEXT_SEQ_NUM)
379 AND NVL(MSI.ORGANIZATION_ID,MP.ORGANIZATION_ID)= MP.ORGANIZATION_ID
380 AND MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
381 GROUP BY WL.LINE_CODE
382 , WL.DESCRIPTION
383 , WE.WIP_ENTITY_NAME
384 , WE.DESCRIPTION
385 , NVL(WRO.SUPPLY_SUBINVENTORY,'' '')
386 --, MOQ2.SUBINVENTORY_CODE
387 --, MOQ2.LOCATOR_ID
388 --, DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,''-1''),
389 --, DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0)
390 , NVL(MOQ2.TRANSACTION_QUANTITY,0)
391 , WRO.DATE_REQUIRED
392 , WRS.ORGANIZATION_ID
393 , wro.inventory_item_id
394 HAVING (SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,
395 BCD1.NEXT_SEQ_NUM + MSI.POSTPROCESSING_LEAD_TIME)
396 - GREATEST(BCD.NEXT_SEQ_NUM, BCD1.NEXT_SEQ_NUM ))
397 * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
398 + SUM(GREATEST((BCD1.NEXT_SEQ_NUM-BCD.NEXT_SEQ_NUM),0)
399 *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE)
400 - SUM(WRO.QUANTITY_ISSUED)) > 0');
401 END IF;
402 RETURN NULL;
403 END C_UNION_LOWER_LOWERFORMULA;
404
405 FUNCTION C_UNION_UPPER_LOWERFORMULA RETURN VARCHAR2 IS
406 BEGIN
407 IF (P_WIP_STATUS = 'I') THEN
408 RETURN ('FROM MTL_ONHAND_QUANTITIES MOQ2
409 , WIP_ENTITIES WE
410 , WIP_REQUIREMENT_OPERATIONS WRO
411 , WIP_DISCRETE_JOBS WDJ
412 WHERE WDJ.STATUS_TYPE IN (1,3,4,6)
413 AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
414 AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
415 AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
416 AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
417 AND WRO.WIP_SUPPLY_TYPE <> 6
418 AND WRO.REQUIRED_QUANTITY > 0
419 AND MOQ2.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
420 AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
421 GROUP BY WE.WIP_ENTITY_NAME
422 , WRO.DATE_REQUIRED
423 , WDJ.ORGANIZATION_ID
424 , WRO.INVENTORY_ITEM_ID
425 , WE.DESCRIPTION
426 , NVL(WDJ.PRIMARY_ITEM_ID,-1)
427 , WDJ.SCHEDULED_START_DATE
428 , WDJ.START_QUANTITY
429 , WDJ.SCHEDULED_COMPLETION_DATE
430 , WRO.SUPPLY_SUBINVENTORY
431 , NVL(MOQ2.TRANSACTION_QUANTITY,0)
432 --, WRO.QUANTITY_ISSUED
433 --, WRO.REQUIRED_QUANTITY
434 --, WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED
435 --, MOQ2.SUBINVENTORY_CODE
436 --, MOQ2.LOCATOR_ID
437 --, DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,''-1'')
438 --, DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0)
439 HAVING SUM(NVL(WRO.REQUIRED_QUANTITY,0)) - SUM(NVL(WRO.QUANTITY_ISSUED,0)) > 0');
440 END IF;
441 RETURN NULL;
442 END C_UNION_UPPER_LOWERFORMULA;
443
444 FUNCTION WIP_SELECT_DISTFORMULA RETURN VARCHAR2 IS
445 BEGIN
446 IF (P_WIP_STATUS = 'I') THEN
447 RETURN (', we.wip_entity_name job_or_schedule
448 , wl.line_code line
449 , wn.operation_seq_num op_seq
450 , bd.department_code department');
451 END IF;
452 RETURN NULL;
453 END WIP_SELECT_DISTFORMULA;
454
455 FUNCTION WIP_FROM_DISTFORMULA RETURN VARCHAR2 IS
456 BEGIN
457 IF (P_WIP_STATUS = 'I') THEN
458 RETURN ('wip_lines wl,
459 wip_entities we,
460 bom_departments bd,
461 wip_operation_resources wr,
462 wip_operations wn,
463 wip_operations wo,');
464 END IF;
465 RETURN NULL;
466 END WIP_FROM_DISTFORMULA;
467
468 FUNCTION WIP_WHERE_DISTFORMULA RETURN VARCHAR2 IS
469 BEGIN
470 IF (P_WIP_STATUS = 'I') THEN
471 RETURN ('AND wo.wip_entity_id = rdp.wip_entity_id
472 and wo.organization_id = rdp.organization_id
473 and nvl(wo.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
474 and wo.operation_seq_num = rdp.wip_operation_seq_num
475 and wl.line_id (+) = rdp.wip_line_id
476 and wr.wip_entity_id = rdp.wip_entity_id
477 and wr.organization_id = rdp.organization_id
478 and nvl(wr.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
479 and wr.operation_seq_num = rdp.wip_operation_seq_num
480 and wr.resource_seq_num = rdp.wip_resource_seq_num
481 and wn.wip_entity_id = rdp.wip_entity_id
482 and wn.organization_id = rdp.organization_id
483 and nvl(wn.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
484 and wn.operation_seq_num =
485 decode(wr.autocharge_type,
486 4, nvl(wo.next_operation_seq_num, wo.operation_seq_num),
487 wo.operation_seq_num)
488 and bd.department_id = wn.department_id
489 and we.wip_entity_id = rdp.wip_entity_id
490 and we.organization_id = rdp.organization_id');
491 END IF;
492 RETURN NULL;
493 END WIP_WHERE_DISTFORMULA;
494
495 FUNCTION WIP_WHERE_DIST_INTERFACEFORMUL RETURN VARCHAR2 IS
496 BEGIN
497 IF (P_WIP_STATUS = 'I') THEN
498 RETURN ('AND wo.wip_entity_id = rdp.wip_entity_id
499 and wo.organization_id = rct.to_organization_id
500 and nvl(wo.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
501 and wo.operation_seq_num = rdp.wip_operation_seq_num
502 and wl.line_id = rdp.wip_line_id
503 and wr.wip_entity_id = rdp.wip_entity_id
504 and wr.organization_id = rct.to_organization_id
505 and nvl(wr.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
506 and wr.operation_seq_num = rdp.wip_operation_seq_num
507 and wr.resource_seq_num = rdp.wip_resource_seq_num
508 and wn.wip_entity_id = rdp.wip_entity_id
509 and wn.organization_id = rct.to_organization_id
510 and nvl(wn.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
511 and wn.operation_seq_num =
512 decode(wr.autocharge_type,
513 4, nvl(wo.next_operation_seq_num, wo.operation_seq_num),
514 wo.operation_seq_num)
515 and bd.department_id = wn.department_id
516 and we.wip_entity_id = rdp.wip_entity_id
517 and we.organization_id = rct.to_organization_id');
518 END IF;
519 RETURN NULL;
520 END WIP_WHERE_DIST_INTERFACEFORMUL;
521
522 FUNCTION C_SET_RT_NUMFORMULA(COUNT_DISTRIBUTIONS IN NUMBER
523 ,RRP_TRANSACTION_ID IN NUMBER
524 ,RRP_FROM_INTERFACE IN VARCHAR2) RETURN NUMBER IS
525 BEGIN
526 IF (COUNT_DISTRIBUTIONS >= 0) THEN
527 IF (CHILD_DIRECT_RECEIVING_CHECK(RRP_FROM_INTERFACE
528 ,RRP_TRANSACTION_ID)) THEN
529 C_NUM_RTS_PRINTED := C_NUM_RTS_PRINTED + 1;
530 END IF;
531 END IF;
532 RETURN 1;
533 END C_SET_RT_NUMFORMULA;
534
535 FUNCTION QUANTITY_RECEIVED_fn(RRP_FROM_INTERFACE IN VARCHAR2
536 ,RRP_TRANSACTION_ID IN NUMBER
537 ,PARENT_PRIMARY_QUANTITY IN NUMBER
538 ,C_SUM_CORRECTED_QTY IN NUMBER
539 ,QUANTITY_RECEIVED IN NUMBER
540 ,RRP_SHIPMENT_LINE_ID IN NUMBER
541 ,RECEIPT_UNIT_OF_MEASURE IN VARCHAR2
542 ,ITEM_ID IN NUMBER) RETURN NUMBER IS
543 QTY NUMBER;
544 QTY_IN_RCT_UOM NUMBER;
545 L_TRANSACTION_TYPE RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
546 L_UOM RCV_SHIPMENT_LINES.UNIT_OF_MEASURE%TYPE;
547 BEGIN
548 IF (RRP_FROM_INTERFACE = 'N') THEN
549 SELECT
550 TRANSACTION_TYPE
551 INTO L_TRANSACTION_TYPE
552 FROM
553 RCV_TRANSACTIONS
554 WHERE TRANSACTION_ID = RRP_TRANSACTION_ID;
555 ELSE
556 SELECT
557 TRANSACTION_TYPE
558 INTO L_TRANSACTION_TYPE
559 FROM
560 RCV_TRANSACTIONS_INTERFACE
561 WHERE INTERFACE_TRANSACTION_ID = RRP_TRANSACTION_ID;
562 END IF;
563 IF L_TRANSACTION_TYPE <> 'DELIVER' THEN
564 QTY := PARENT_PRIMARY_QUANTITY + NVL(C_SUM_CORRECTED_QTY
565 ,0);
566 QTY_IN_RCT_UOM := ((QTY * QUANTITY_RECEIVED) / (PARENT_PRIMARY_QUANTITY));
567 RETURN (ROUND(QTY_IN_RCT_UOM
568 ,P_QTY_PRECISION));
569 ELSE
570 SELECT
571 QUANTITY_RECEIVED,
572 UNIT_OF_MEASURE
573 INTO QTY,L_UOM
574 FROM
575 RCV_SHIPMENT_LINES
576 WHERE SHIPMENT_LINE_ID = RRP_SHIPMENT_LINE_ID;
577 QTY := QTY * PO_UOM_S.PO_UOM_CONVERT(L_UOM
578 ,RECEIPT_UNIT_OF_MEASURE
579 ,ITEM_ID);
580 RETURN (ROUND(QTY
581 ,P_QTY_PRECISION));
582 END IF;
583 END QUANTITY_RECEIVED_fn;
584
585 FUNCTION SUM_CORRECT_RTV(C_QTY_CORRECTED IN NUMBER
586 ,C_SUM_CORRECT_RTV_QTY IN NUMBER) RETURN NUMBER IS
587 TOTAL NUMBER;
588 BEGIN
589 TOTAL := C_QTY_CORRECTED + C_SUM_CORRECT_RTV_QTY;
590 RETURN (TOTAL);
591 END SUM_CORRECT_RTV;
592
593 PROCEDURE GET_PRECISION(ID IN NUMBER) IS
594 BEGIN
595 IF ID = 0 THEN
596 NULL;
597 ELSE
598 IF ID = 1 THEN
599 NULL;
600 ELSE
601 IF ID = 3 THEN
602 NULL;
603 ELSE
604 IF ID = 4 THEN
605 NULL;
606 ELSE
607 IF ID = 5 THEN
608 NULL;
609 ELSE
610 IF ID = 6 THEN
611 NULL;
612 ELSE
613 IF ID = 7 THEN
614 NULL;
615 ELSE
616 IF ID = 8 THEN
617 NULL;
618 ELSE
619 IF ID = 9 THEN
620 NULL;
621 ELSE
622 IF ID = 10 THEN
623 NULL;
624 ELSE
625 IF ID = 11 THEN
626 NULL;
627 ELSE
628 IF ID = 12 THEN
629 NULL;
630 ELSE
631 IF ID = 13 THEN
632 NULL;
633 ELSE
634 NULL;
635 END IF;
636 END IF;
637 END IF;
638 END IF;
639 END IF;
640 END IF;
641 END IF;
642 END IF;
643 END IF;
644 END IF;
645 END IF;
646 END IF;
647 END IF;
648 END GET_PRECISION;
649
650 FUNCTION C_QUANTITY_REMAINING_PRINTFORM(QUANTITY_REMAINING IN NUMBER
651 ,SOURCE_UNIT_OF_MEASURE IN VARCHAR2
652 ,RECEIPT_UNIT_OF_MEASURE IN VARCHAR2
653 ,ITEM_ID IN NUMBER
654 ,RRP_ORGANIZATION_ID IN NUMBER) RETURN NUMBER IS
655 CONVERTED_QTY NUMBER;
656 BEGIN
657 IF (BLIND_RECEIVING_CHECK(RRP_ORGANIZATION_ID) = TRUE) THEN
658 CONVERTED_QTY := QUANTITY_REMAINING * PO_UOM_S.PO_UOM_CONVERT(SOURCE_UNIT_OF_MEASURE
659 ,RECEIPT_UNIT_OF_MEASURE
660 ,NVL(ITEM_ID
661 ,0));
662 RETURN (ROUND(CONVERTED_QTY
663 ,P_QTY_PRECISION));
664 ELSE
665 RETURN (TO_NUMBER(''));
666 END IF;
667 RETURN NULL;
668 END C_QUANTITY_REMAINING_PRINTFORM;
669
670 FUNCTION QUANTITY_DELIVERED(PARENT_PRIMARY_QUANTITY IN NUMBER
671 ,C_SUM_CORRECTED_QTY IN NUMBER) RETURN NUMBER IS
672 QTY NUMBER;
673 QTY_IN_RCT_UOM NUMBER;
674 BEGIN
675 QTY := PARENT_PRIMARY_QUANTITY + NVL(C_SUM_CORRECTED_QTY
676 ,0);
677 RETURN (ROUND(QTY
678 ,P_QTY_PRECISION));
679 END QUANTITY_DELIVERED;
680
681 FUNCTION AFTERREPORT RETURN BOOLEAN IS
682 BEGIN
683 EXECUTE IMMEDIATE
684 'Alter session set sql_trace FALSE';
685 RETURN (TRUE);
686 END AFTERREPORT;
687
688 FUNCTION ITEM_NOTEFORMULA(ITEM_NOTE_DATATYPE_ID IN NUMBER
689 ,ITEM_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
690 SHORT_NOTE VARCHAR2(2000);
691 LONG_NOTE CLOB;
692 BEGIN
693 IF ITEM_NOTE_DATATYPE_ID = 1 THEN
694 SELECT
695 SHORT_TEXT
696 INTO SHORT_NOTE
697 FROM
698 FND_DOCUMENTS_SHORT_TEXT
699 WHERE MEDIA_ID = ITEM_NOTE_MEDIA_ID;
700 RETURN SHORT_NOTE;
701 ELSIF ITEM_NOTE_DATATYPE_ID = 2 THEN
702 SELECT
703 LONG_TEXT
704 INTO LONG_NOTE
705 FROM
706 FND_DOCUMENTS_LONG_TEXT
707 WHERE MEDIA_ID = ITEM_NOTE_MEDIA_ID;
708 RETURN LONG_NOTE;
709 ELSE
710 RETURN 'Attachment is not a Text format';
711 END IF;
712 END ITEM_NOTEFORMULA;
713
714 FUNCTION LONG_NOTE1FORMULA(LONG_NOTE_DATATYPE_ID IN NUMBER
715 ,LONG_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
716 SHORT_NOTE VARCHAR2(2000);
717 LONG_NOTE CLOB;
718 BEGIN
719 IF LONG_NOTE_DATATYPE_ID = 1 THEN
720 SELECT
721 SHORT_TEXT
722 INTO SHORT_NOTE
723 FROM
724 FND_DOCUMENTS_SHORT_TEXT
725 WHERE MEDIA_ID = LONG_NOTE_MEDIA_ID;
726 RETURN SHORT_NOTE;
727 ELSIF LONG_NOTE_DATATYPE_ID = 2 THEN
728 SELECT
729 LONG_TEXT
730 INTO LONG_NOTE
731 FROM
732 FND_DOCUMENTS_LONG_TEXT
733 WHERE MEDIA_ID = LONG_NOTE_MEDIA_ID;
734 RETURN LONG_NOTE;
735 ELSE
736 RETURN 'Attachment is not a Text format';
737 END IF;
738 END LONG_NOTE1FORMULA;
739
740 FUNCTION LONG_NOTE2FORMULA(TRX_NOTE_DATATYPE_ID IN NUMBER
741 ,TRX_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
742 SHORT_NOTE VARCHAR2(2000);
743 LONG_NOTE CLOB;
744 BEGIN
745 IF TRX_NOTE_DATATYPE_ID = 1 THEN
746 SELECT
747 SHORT_TEXT
748 INTO SHORT_NOTE
749 FROM
750 FND_DOCUMENTS_SHORT_TEXT
751 WHERE MEDIA_ID = TRX_NOTE_MEDIA_ID;
752 RETURN SHORT_NOTE;
753 ELSIF TRX_NOTE_DATATYPE_ID = 2 THEN
754 SELECT
755 LONG_TEXT
756 INTO LONG_NOTE
757 FROM
758 FND_DOCUMENTS_LONG_TEXT
759 WHERE MEDIA_ID = TRX_NOTE_MEDIA_ID;
760 RETURN LONG_NOTE;
761 ELSE
762 RETURN 'Attachment is not a Text format';
763 END IF;
764 END LONG_NOTE2FORMULA;
765
766 FUNCTION PO_HEADER_LONG_NOTEFORMULA(PO_HEADER_NOTE_DATATYPE_ID IN NUMBER
767 ,PO_HEADER_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
768 SHORT_NOTE VARCHAR2(2000);
769 LONG_NOTE CLOB;
770 BEGIN
771 IF PO_HEADER_NOTE_DATATYPE_ID = 1 THEN
772 SELECT
773 SHORT_TEXT
774 INTO SHORT_NOTE
775 FROM
776 FND_DOCUMENTS_SHORT_TEXT
777 WHERE MEDIA_ID = PO_HEADER_NOTE_MEDIA_ID;
778 RETURN SHORT_NOTE;
779 ELSIF PO_HEADER_NOTE_DATATYPE_ID = 2 THEN
780 SELECT
781 LONG_TEXT
782 INTO LONG_NOTE
783 FROM
784 FND_DOCUMENTS_LONG_TEXT
785 WHERE MEDIA_ID = PO_HEADER_NOTE_MEDIA_ID;
786 RETURN LONG_NOTE;
787 ELSE
788 RETURN 'Attachment is not a Text format';
789 END IF;
790 END PO_HEADER_LONG_NOTEFORMULA;
791
792 FUNCTION PO_LINE_LONG_NOTEFORMULA(PO_LINE_NOTE_DATATYPE_ID IN NUMBER
793 ,PO_LINE_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
794 SHORT_NOTE VARCHAR2(2000);
795 LONG_NOTE CLOB;
796 BEGIN
797 IF PO_LINE_NOTE_DATATYPE_ID = 1 THEN
798 SELECT
799 SHORT_TEXT
800 INTO SHORT_NOTE
801 FROM
802 FND_DOCUMENTS_SHORT_TEXT
803 WHERE MEDIA_ID = PO_LINE_NOTE_MEDIA_ID;
804 RETURN SHORT_NOTE;
805 ELSIF PO_LINE_NOTE_DATATYPE_ID = 2 THEN
806 SELECT
807 LONG_TEXT
808 INTO LONG_NOTE
809 FROM
810 FND_DOCUMENTS_LONG_TEXT
811 WHERE MEDIA_ID = PO_LINE_NOTE_MEDIA_ID;
812 RETURN LONG_NOTE;
813 ELSE
814 RETURN 'Attachment is not a Text format';
815 END IF;
816 END PO_LINE_LONG_NOTEFORMULA;
817
818 FUNCTION REQ_HEADER_LONG_NOTEFORMULA(REQ_HEADER_NOTE_DATATYPE_ID IN NUMBER
819 ,REQ_HEADER_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
820 SHORT_NOTE VARCHAR2(2000);
821 LONG_NOTE CLOB;
822 BEGIN
823 IF REQ_HEADER_NOTE_DATATYPE_ID = 1 THEN
824 SELECT
825 SHORT_TEXT
826 INTO SHORT_NOTE
827 FROM
828 FND_DOCUMENTS_SHORT_TEXT
829 WHERE MEDIA_ID = REQ_HEADER_NOTE_MEDIA_ID;
830 RETURN SHORT_NOTE;
831 ELSIF REQ_HEADER_NOTE_DATATYPE_ID = 2 THEN
832 SELECT
833 LONG_TEXT
834 INTO LONG_NOTE
835 FROM
836 FND_DOCUMENTS_LONG_TEXT
837 WHERE MEDIA_ID = REQ_HEADER_NOTE_MEDIA_ID;
838 RETURN LONG_NOTE;
839 ELSE
840 RETURN 'Attachment is not a Text format';
841 END IF;
842 END REQ_HEADER_LONG_NOTEFORMULA;
843
844 FUNCTION REQ_LINE_LONG_NOTEFORMULA(REQ_LINE_NOTE_DATATYPE_ID IN NUMBER
845 ,REQ_LINE_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
846 SHORT_NOTE VARCHAR2(2000);
847 LONG_NOTE CLOB;
848 BEGIN
849 IF REQ_LINE_NOTE_DATATYPE_ID = 1 THEN
850 SELECT
851 SHORT_TEXT
852 INTO SHORT_NOTE
853 FROM
854 FND_DOCUMENTS_SHORT_TEXT
855 WHERE MEDIA_ID = REQ_LINE_NOTE_MEDIA_ID;
856 RETURN SHORT_NOTE;
857 ELSIF REQ_LINE_NOTE_DATATYPE_ID = 2 THEN
858 SELECT
859 LONG_TEXT
860 INTO LONG_NOTE
861 FROM
862 FND_DOCUMENTS_LONG_TEXT
863 WHERE MEDIA_ID = REQ_LINE_NOTE_MEDIA_ID;
864 RETURN LONG_NOTE;
865 ELSE
866 RETURN 'Attachment is not a Text format';
867 END IF;
868 END REQ_LINE_LONG_NOTEFORMULA;
869
870 FUNCTION PO_LINE_LOCATION_LONG_NOTEFORM(SHIPMENT_NOTE_DATATYPE_ID IN NUMBER
871 ,SHIPMENT_NOTE_MEDIA_ID IN NUMBER) RETURN CHAR IS
872 SHORT_NOTE VARCHAR2(2000);
873 LONG_NOTE CLOB;
874 BEGIN
875 IF SHIPMENT_NOTE_DATATYPE_ID = 1 THEN
876 SELECT
877 SHORT_TEXT
878 INTO SHORT_NOTE
879 FROM
880 FND_DOCUMENTS_SHORT_TEXT
881 WHERE MEDIA_ID = SHIPMENT_NOTE_MEDIA_ID;
882 RETURN SHORT_NOTE;
883 ELSIF SHIPMENT_NOTE_DATATYPE_ID = 2 THEN
884 SELECT
885 LONG_TEXT
886 INTO LONG_NOTE
887 FROM
888 FND_DOCUMENTS_LONG_TEXT
889 WHERE MEDIA_ID = SHIPMENT_NOTE_MEDIA_ID;
890 RETURN LONG_NOTE;
891 ELSE
892 RETURN 'Attachment is not a Text format';
893 END IF;
894 END PO_LINE_LOCATION_LONG_NOTEFORM;
895
896 FUNCTION AFTERPFORM RETURN BOOLEAN IS
897 BEGIN
898 DECLARE
899 L_ORG_DISPLAYED ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
900 NUMBERING_TYPE VARCHAR2(40);
901 l_INDUSTRY varchar2(100);
902 l_boolean boolean;
903 l_ORACLE_SCHEMA varchar2(100);
904 BEGIN
905 IF (P_GROUP_ID IS NOT NULL) THEN
906 P_WHERE_GROUP_ID := 'rrp.group_id = :P_group_id ';
907 ELSE
908 P_WHERE_GROUP_ID := '1=1';
909 END IF;
910 IF (P_SHIP_TO_LOCATION IS NOT NULL) THEN
911 P_WHERE_SHIP_TO_LOCATION := 'rrp.receipt_location = :P_ship_to_location ';
912 ELSE
913 P_WHERE_SHIP_TO_LOCATION := '1=1';
914 END IF;
915 IF (P_DELIVERY_LOCATION IS NOT NULL) THEN
916 P_WHERE_DELIVER_TO_LOCATION := 'rrp.deliver_to_location = :P_delivery_location ';
917 ELSE
918 P_WHERE_DELIVER_TO_LOCATION := '1=1';
919 END IF;
920 IF (P_ORG_ID IS NOT NULL) THEN
921 P_WHERE_ORG_ID := 'rrp.organization_id = :P_org_id ';
922 ELSE
923 P_WHERE_ORG_ID := '1=1';
924 END IF;
925 IF (P_GROUP_ID IS NOT NULL) THEN
926 P_WHERE_GROUP_ID := 'rrp.group_id = :P_group_id';
927 ELSE
928 P_WHERE_GROUP_ID := '1=1';
929 END IF;
930 BEGIN
931 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
932 IF (P_ORG_ID IS NOT NULL) THEN
933 BEGIN
934 SELECT
935 ORGANIZATION_NAME
936 INTO L_ORG_DISPLAYED
937 FROM
938 ORG_ORGANIZATION_DEFINITIONS
939 WHERE ORGANIZATION_ID = P_ORG_ID;
940 P_ORG_DISPLAYED := L_ORG_DISPLAYED;
941 END;
942 ELSE
943 BEGIN
944 P_ORG_DISPLAYED := '';
945 END;
946 END IF;
947 IF (P_OPERATING_UNIT_ID IS NOT NULL) THEN
948 PO_MOAC_UTILS_PVT.SET_POLICY_CONTEXT('S'
949 ,P_OPERATING_UNIT_ID);
950 P_OPERATING_UNIT_DISPLAYED := PO_MOAC_UTILS_PVT.GET_OU_NAME(P_OPERATING_UNIT_ID);
951 END IF;
952 SELECT
953 DISTINCT
954 MANUAL_RECEIPT_NUM_TYPE
955 INTO NUMBERING_TYPE
956 FROM
957 RCV_RECEIVING_PARAMETERS_V
958 WHERE ORGANIZATION_ID = P_ORG_ID;
959 IF (P_RECEIPT_NUM_FROM IS NOT NULL) AND (P_RECEIPT_NUM_TO IS NOT NULL) AND (P_RECEIPT_NUM_FROM = P_RECEIPT_NUM_TO) THEN
960 P_WHERE_RECEIPT_NUM_FROM := 'rrp.receipt_num = :P_receipt_num_from ';
961 P_WHERE_RECEIPT_NUM_TO := 'rrp.receipt_num = :P_receipt_num_to ';
962 ELSE
963 IF (P_RECEIPT_NUM_FROM IS NOT NULL) THEN
964 IF (NUMBERING_TYPE = 'ALPHANUMERIC') THEN
965 P_WHERE_RECEIPT_NUM_FROM := 'rrp.receipt_num >= :P_receipt_num_from ';
966 ELSE
967 P_WHERE_RECEIPT_NUM_FROM := 'decode(rtrim(rrp.receipt_num, ''0123456789''), NULL, to_number(rrp.receipt_num), -1) >= ' || ' decode(rtrim(:P_receipt_num_from ,''0123456789''), NULL, to_number(:P_receipt_num_from ), -1) ';
968 END IF;
969 ELSE
970 P_WHERE_RECEIPT_NUM_FROM := '1=1';
971 END IF;
972 IF (P_RECEIPT_NUM_TO IS NOT NULL) THEN
973 IF (NUMBERING_TYPE = 'ALPHANUMERIC') THEN
974 P_WHERE_RECEIPT_NUM_TO := 'rrp.receipt_num <= :P_receipt_num_to ';
975 ELSE
976 P_WHERE_RECEIPT_NUM_TO := 'decode(rtrim(rrp.receipt_num, ''0123456789''), NULL, to_number(rrp.receipt_num), -1) <= ' || ' decode(rtrim(:P_receipt_num_to , ''0123456789''), NULL, to_number(:P_receipt_num_to ), -1) ';
977 END IF;
978 ELSE
979 P_WHERE_RECEIPT_NUM_TO := '1=1';
980 END IF;
981 END IF;
982 IF (NUMBERING_TYPE = 'ALPHANUMERIC') THEN
983 P_ORDERBY := 'order by rrp.item_id, rrp.receipt_num';
984 ELSE
985 P_ORDERBY := 'order by rrp.item_id, decode(rtrim(rrp.receipt_num, ''0123456789''), NULL, to_number(rrp.receipt_num), rrp.receipt_num)';
986 END IF;
987 END;
988 BEGIN
989 IF (GET_P_STRUCT_NUM <> TRUE) THEN
990 NULL;
991 END IF;
992 l_boolean:= fnd_installation.GET_APP_INFO('INV',P_INV_STATUS,l_INDUSTRY,l_ORACLE_SCHEMA);
993 l_boolean:= fnd_installation.GET_APP_INFO('WIP',P_WIP_STATUS,l_INDUSTRY,l_ORACLE_SCHEMA);
994 IF (P_WIP_STATUS = 'I') THEN
995 l_boolean:= fnd_installation.GET_APP_INFO('BOM',P_WIP_STATUS,l_INDUSTRY,l_ORACLE_SCHEMA);
996 END IF;
997 IF (P_INV_STATUS = 'I') THEN
998 Null;
999 ELSE
1000 P_FLEX_LOCATOR := 'TO_CHAR(NULL)';
1001 END IF;
1002 IF (P_WIP_STATUS = 'I') THEN
1003 P_WIP_SELECT_DIST := ' we.wip_entity_name job_or_schedule
1004 , wl.line_code line
1005 , wn.operation_seq_num op_seq
1006 , bd.department_code department ';
1007 P_WIP_FROM_DIST := 'wip_lines wl,
1008 wip_entities we,
1009 bom_departments bd,
1010 wip_operation_resources wr,
1011 wip_operations wn,
1012 wip_operations wo, ';
1013 P_WIP_WHERE_DIST := 'AND wo.wip_entity_id = rdp.wip_entity_id
1014 and wo.organization_id = rdp.organization_id
1015 and nvl(wo.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
1016 and wo.operation_seq_num = rdp.wip_operation_seq_num
1017 and wl.line_id (+) = rdp.wip_line_id
1018 and wr.wip_entity_id = rdp.wip_entity_id
1019 and wr.organization_id = rdp.organization_id
1020 and nvl(wr.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
1021 and wr.operation_seq_num = rdp.wip_operation_seq_num
1022 and wr.resource_seq_num = rdp.wip_resource_seq_num
1023 and wn.wip_entity_id = rdp.wip_entity_id
1024 and wn.organization_id = rdp.organization_id
1025 and nvl(wn.repetitive_schedule_id, -1) = nvl(rdp.wip_repetitive_schedule_id, -1)
1026 and wn.operation_seq_num = decode(wr.autocharge_type, 4,
1027 nvl(wo.next_operation_seq_num, wo.operation_seq_num), wo.operation_seq_num)
1028 and bd.department_id = wn.department_id
1029 and we.wip_entity_id = rdp.wip_entity_id
1030 and we.organization_id = rdp.organization_id ';
1031 P_UNION_UPPER_UPPER := 'SELECT WE.WIP_ENTITY_NAME Work_Order
1032 , to_char(null) Line
1033 , WRO.DATE_REQUIRED Date_Required
1034 , SUM(NVL(WRO.QUANTITY_ISSUED,0)) Quantity_Issued
1035 , SUM(NVL(WRO.REQUIRED_QUANTITY,0)) Quantity_Required
1036 , ''D'' ';
1037 P_UNION_UPPER_LOWER := 'FROM WIP_ENTITIES WE
1038 , WIP_REQUIREMENT_OPERATIONS WRO
1039 , WIP_DISCRETE_JOBS WDJ
1040 WHERE WDJ.STATUS_TYPE IN (1,3,4,6)
1041 AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
1042 AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
1043 AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
1044 AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
1045 AND WRO.WIP_SUPPLY_TYPE <> 6
1046 AND WRO.REQUIRED_QUANTITY > 0
1047 GROUP BY WE.WIP_ENTITY_NAME
1048 , WRO.DATE_REQUIRED
1049 , WDJ.ORGANIZATION_ID
1050 , WRO.INVENTORY_ITEM_ID
1051 , WE.DESCRIPTION
1052 , NVL(WDJ.PRIMARY_ITEM_ID,-1)
1053 , WDJ.SCHEDULED_START_DATE
1054 , WDJ.START_QUANTITY
1055 , WDJ.SCHEDULED_COMPLETION_DATE
1056 , WRO.SUPPLY_SUBINVENTORY
1057 , WRO.QUANTITY_ISSUED
1058 , WRO.REQUIRED_QUANTITY
1059 , WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED
1060 HAVING SUM(NVL(WRO.REQUIRED_QUANTITY,0)) - SUM(NVL(WRO.QUANTITY_ISSUED,0)) > 0';
1061 P_UNION_LOWER_UPPER := 'SELECT WE.WIP_ENTITY_NAME Work_Order
1062 , WL.LINE_CODE Line
1063 , WRO.DATE_REQUIRED Date_Required
1064 , SUM(WRO.QUANTITY_ISSUED) Quantity_Issued
1065 , SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,BCD1.NEXT_SEQ_NUM + MSI.POSTPROCESSING_LEAD_TIME)
1066 - GREATEST(BCD.NEXT_SEQ_NUM,BCD1.NEXT_SEQ_NUM))
1067 * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
1068 /* this is raw quantity required */
1069 + -1 * SUM(WRO.QUANTITY_ISSUED -
1070 GREATEST((BCD1.NEXT_SEQ_NUM-BCD.NEXT_SEQ_NUM),0)
1071 *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE)
1072 /* this is the quantity ahead behind after taking care of the negative sign as quantity ahead behind
1073 will always be calulated as negative*/
1074 Quantity_Required
1075 , ''R'' Processing_Mode';
1076 P_UNION_LOWER_LOWER := 'FROM WIP_ENTITIES WE
1077 , WIP_REQUIREMENT_OPERATIONS WRO
1078 , WIP_REPETITIVE_SCHEDULES WRS
1079 , BOM_CALENDAR_DATES BCD
1080 , BOM_CALENDAR_DATES BCD1
1081 , WIP_LINES WL
1082 , MTL_PARAMETERS MP
1083 , MTL_SYSTEM_ITEMS MSI
1084 WHERE WRS.STATUS_TYPE IN (1,3,4,6)
1085 AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
1086 AND WE.ORGANIZATION_ID = WRS.ORGANIZATION_ID
1087 AND MP.ORGANIZATION_ID = WRS.ORGANIZATION_ID
1088 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
1089 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
1090 AND BCD1.CALENDAR_DATE = to_char(sysdate,''DD-MON-RR'')
1091 AND NVL(MSI.ORGANIZATION_ID,MP.ORGANIZATION_ID)= MP.ORGANIZATION_ID
1092 AND WRO.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
1093 AND WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
1094 AND WRO.ORGANIZATION_ID = WRS.ORGANIZATION_ID
1095 AND WRO.WIP_SUPPLY_TYPE <> 6
1096 AND WRO.REQUIRED_QUANTITY > 0
1097 AND WL.LINE_ID = WRS.LINE_ID
1098 AND WL.ORGANIZATION_ID = WRS.ORGANIZATION_ID
1099 AND BCD.CALENDAR_CODE = MP.CALENDAR_CODE
1100 AND BCD.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
1101 AND BCD.CALENDAR_DATE = WRO.DATE_REQUIRED
1102 AND (BCD.NEXT_SEQ_NUM < BCD1.NEXT_SEQ_NUM + MSI.POSTPROCESSING_LEAD_TIME)
1103 AND ((BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS ) > BCD1.NEXT_SEQ_NUM)
1104 AND NVL(MSI.ORGANIZATION_ID,MP.ORGANIZATION_ID)= MP.ORGANIZATION_ID
1105 AND MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
1106 GROUP BY WL.LINE_CODE
1107 , WL.DESCRIPTION
1108 , WE.WIP_ENTITY_NAME
1109 , WE.DESCRIPTION
1110 , NVL(WRO.SUPPLY_SUBINVENTORY,'' '')
1111 , WRO.DATE_REQUIRED
1112 , WRS.ORGANIZATION_ID
1113 , wro.inventory_item_id
1114 HAVING (SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,
1115 BCD1.NEXT_SEQ_NUM + MSI.POSTPROCESSING_LEAD_TIME)
1116 - GREATEST(BCD.NEXT_SEQ_NUM, BCD1.NEXT_SEQ_NUM ))
1117 * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
1118 + SUM(GREATEST((BCD1.NEXT_SEQ_NUM-BCD.NEXT_SEQ_NUM),0)
1119 *WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE)
1120 - SUM(WRO.QUANTITY_ISSUED)) > 0';
1121 END IF;
1122 BUILD_QRECEIPTS;
1123 BUILD_QDISTRIBUTIONS;
1124 END;
1125 RETURN (TRUE);
1126 END;
1127 END AFTERPFORM;
1128
1129 PROCEDURE BUILD_QRECEIPTS IS
1130 BEGIN
1131 IF (P_RECEIPT_SOURCE_TYPE = 'Supplier') THEN
1132 from_lexical:= 'rcv_receipts_print_po rrp';
1133 and_lexical:= 'AND nvl(msi.organization_id,rrp.org_id) = rrp.org_id';
1134
1135 ELSIF (P_RECEIPT_SOURCE_TYPE = 'Internal Order') THEN
1136 from_lexical:= 'rcv_receipts_print_req rrp';
1137 and_lexical:= 'AND nvl(msi.organization_id,rrp.org_id) = rrp.org_id';
1138
1139 ELSIF (P_RECEIPT_SOURCE_TYPE = 'Inventory') THEN
1140 from_lexical:= 'rcv_receipts_print_inv rrp';
1141 and_lexical:= 'AND nvl(msi.organization_id,rrp.organization_id) = rrp.organization_id';
1142
1143 ELSIF (P_RECEIPT_SOURCE_TYPE = 'Customer') THEN
1144 from_lexical:= 'rcv_receipts_print_rma rrp';
1145 and_lexical:= 'AND nvl(msi.organization_id,rrp.org_id) = rrp.org_id';
1146
1147 ELSE
1148 from_lexical:= 'rcv_receipts_print rrp';
1149 and_lexical:= 'AND nvl(msi.organization_id,rrp.organization_id) = rrp.organization_id';
1150
1151 END IF;
1152 END BUILD_QRECEIPTS;
1153
1154 PROCEDURE BUILD_QDISTRIBUTIONS IS
1155 BEGIN
1156 IF (P_RECEIPT_SOURCE_TYPE = 'Supplier') THEN
1157 from_lexical_1:='rcv_distributions_print_po';
1158 ELSIF (P_RECEIPT_SOURCE_TYPE = 'Internal Order') THEN
1159 from_lexical_1:='rcv_distributions_print_req';
1160 ELSIF (P_RECEIPT_SOURCE_TYPE = 'Inventory') THEN
1161 from_lexical_1:='rcv_distributions_print_inv';
1162 ELSIF (P_RECEIPT_SOURCE_TYPE = 'Customer') THEN
1163 from_lexical_1:='rcv_distributions_print_rma';
1164 ELSE
1165 from_lexical_1:='rcv_distributions_print';
1166 END IF;
1167 END BUILD_QDISTRIBUTIONS;
1168
1169 FUNCTION ROUNDED_QTY_ISSUEDFORMULA(QUANTITY_ISSUED IN NUMBER) RETURN NUMBER IS
1170 BEGIN
1171 RETURN ROUND(QUANTITY_ISSUED
1172 ,P_QTY_PRECISION);
1173 END ROUNDED_QTY_ISSUEDFORMULA;
1174
1175 FUNCTION ROUNDED_QTY_ON_HANDFORMULA(JOIN_ITEM_ID IN NUMBER
1176 ,JOIN_ORGANIZATION_ID IN NUMBER) RETURN NUMBER IS
1177 QUANTITY_ON_HAND NUMBER;
1178 BEGIN
1179 SELECT
1180 SUM(NVL(QUANTITY
1181 ,0))
1182 INTO QUANTITY_ON_HAND
1183 FROM
1184 MTL_ITEM_QUANTITIES_VIEW
1185 WHERE INVENTORY_ITEM_ID = JOIN_ITEM_ID
1186 AND ORGANIZATION_ID = JOIN_ORGANIZATION_ID;
1187 RETURN ROUND(QUANTITY_ON_HAND
1188 ,P_QTY_PRECISION);
1189 END ROUNDED_QTY_ON_HANDFORMULA;
1190
1191 FUNCTION ROUNDED_QTY_REQUIREDFORMULA(QUANTITY_REQUIRED IN NUMBER) RETURN NUMBER IS
1192 BEGIN
1193 RETURN ROUND(QUANTITY_REQUIRED
1194 ,P_QTY_PRECISION);
1195 END ROUNDED_QTY_REQUIREDFORMULA;
1196
1197 FUNCTION QUANTITY_SHORTFORMULA(ROUNDED_QTY_REQUIRED IN NUMBER
1198 ,ROUNDED_QTY_ISSUED IN NUMBER
1199 ,ROUNDED_QTY_ON_HAND IN NUMBER) RETURN NUMBER IS
1200 BEGIN
1201 RETURN (((NVL(ROUNDED_QTY_REQUIRED
1202 ,0) - NVL(ROUNDED_QTY_ISSUED
1203 ,0)) - NVL(ROUNDED_QTY_ON_HAND
1204 ,0)));
1205 END QUANTITY_SHORTFORMULA;
1206
1207 FUNCTION G_WORK_ORDER_SHORTAGEGROUPFILT(QUANTITY_SHORT IN NUMBER) RETURN BOOLEAN IS
1208 BEGIN
1209 IF (QUANTITY_SHORT > 0) THEN
1210 RETURN TRUE;
1211 ELSE
1212 RETURN FALSE;
1213 END IF;
1214 RETURN (TRUE);
1215 END G_WORK_ORDER_SHORTAGEGROUPFILT;
1216
1217 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
1218 BEGIN
1219 QTY_PRECISION := PO_common_xmlp_pkg.get_precision(P_QTY_PRECISION);
1220 CP_DELIVERY_LOCATION := P_DELIVERY_LOCATION;
1221 CP_RECEIPT_NUM_FROM := P_RECEIPT_NUM_FROM;
1222 CP_RECEIPT_NUM_TO := P_RECEIPT_NUM_TO;
1223 CP_ITEM_FROM := P_ITEM_FROM;
1224 CP_ITEM_TO := P_ITEM_TO;
1225 CP_CATEGORY_FROM := P_CATEGORY_FROM;
1226 CP_CATEGORY_TO := P_CATEGORY_TO;
1227 CP_SHIP_TO_LOCATION := P_SHIP_TO_LOCATION;
1228 CP_ORG_DISPLAYED := P_ORG_DISPLAYED;
1229 CP_TITLE := P_TITLE;
1230 CP_WIP_STATUS := P_WIP_STATUS;
1231 CP_OPERATING_UNIT_DISPLAYED := P_OPERATING_UNIT_DISPLAYED;
1232 P_WHERE_ITEM :=nvl(P_WHERE_ITEM,'1=1');
1233 P_WHERE_CAT :=nvl(P_WHERE_CAT,'1=1');
1234 P_WHERE_ITEM :=nvl(P_WHERE_ITEM,'1=1');
1235 P_WIP_WHERE_DIST := nvl(P_WIP_WHERE_DIST,'and 1=1');
1236 P_WIP_FROM_DIST := nvl(P_WIP_FROM_DIST,' ');
1237 P_WIP_WHERE_DIST := nvl(P_WIP_WHERE_DIST,'and 1=1');
1238 RETURN (TRUE);
1239 END BEFOREREPORT;
1240
1241 FUNCTION CF_DO_PRINTFORMULA(COUNT_DISTRIBUTIONS IN NUMBER
1242 ,RRP_TRANSACTION_ID IN NUMBER
1243 ,RRP_FROM_INTERFACE IN VARCHAR2) RETURN CHAR IS
1244 BEGIN
1245 IF (COUNT_DISTRIBUTIONS >= 0) THEN
1246 IF (CHILD_DIRECT_RECEIVING_CHECK(RRP_FROM_INTERFACE
1247 ,RRP_TRANSACTION_ID) = TRUE) THEN
1248 RETURN 'Y';
1249 END IF;
1250 ELSE
1251 RETURN 'N';
1252 END IF;
1253 RETURN ('Y');
1254 END CF_DO_PRINTFORMULA;
1255
1256 FUNCTION CF_BLIND_RCVFORMULA(RRP_ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
1257 BEGIN
1258 IF (BLIND_RECEIVING_CHECK(RRP_ORGANIZATION_ID) = TRUE) THEN
1259 RETURN 'Y';
1260 ELSE
1261 RETURN 'N';
1262 END IF;
1263 END CF_BLIND_RCVFORMULA;
1264
1265 FUNCTION CF_DIRECT_RCV_DLVFORMULA(RRP_TRANSACTION_ID IN NUMBER
1266 ,RRP_FROM_INTERFACE IN VARCHAR2) RETURN CHAR IS
1267 BEGIN
1268 IF DIRECT_RECEIVING_CHECK(RRP_FROM_INTERFACE
1269 ,RRP_TRANSACTION_ID) AND DELIVERY_CHECK(RRP_FROM_INTERFACE
1270 ,RRP_TRANSACTION_ID) THEN
1271 RETURN 'Y';
1272 ELSE
1273 RETURN 'N';
1274 END IF;
1275 END CF_DIRECT_RCV_DLVFORMULA;
1276
1277 FUNCTION CF_SERIAL_NUM_CCFORMULA(SERIAL_NUMBER_CONTROL_COD IN NUMBER) RETURN CHAR IS
1278 BEGIN
1279 IF (SERIAL_NUMBER_CONTROL_COD = 2 OR SERIAL_NUMBER_CONTROL_COD = 3 OR SERIAL_NUMBER_CONTROL_COD = 5 OR SERIAL_NUMBER_CONTROL_COD = 6) THEN
1280 RETURN 'Y';
1281 ELSE
1282 RETURN 'N';
1283 END IF;
1284 END CF_SERIAL_NUM_CCFORMULA;
1285
1286 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
1287 BEGIN
1288 RETURN (TRUE);
1289 END BEFOREPFORM;
1290
1291 FUNCTION CF_OPERATING_UNITFORMULA(ORG_ID IN NUMBER) RETURN VARCHAR2 IS
1292 BEGIN
1293 IF (ORG_ID IS NOT NULL) THEN
1294 RETURN PO_MOAC_UTILS_PVT.GET_OU_NAME(ORG_ID);
1295 ELSE
1296 RETURN NULL;
1297 END IF;
1298 END CF_OPERATING_UNITFORMULA;
1299
1300 FUNCTION CF_RECEIPT_LOCATIONFORMULA(LOCATOR_ID1 IN NUMBER
1301 ,RECEIPT_LOCATION IN VARCHAR2
1302 ,RECEIVING_SUBINVENTORY IN VARCHAR2) RETURN CHAR IS
1303 L_LOCATION VARCHAR2(300);
1304 L_LOCATOR MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
1305 BEGIN
1306 BEGIN
1307 SELECT
1308 CONCATENATED_SEGMENTS
1309 INTO L_LOCATOR
1310 FROM
1311 MTL_ITEM_LOCATIONS_KFV
1312 WHERE INVENTORY_LOCATION_ID = LOCATOR_ID1;
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 L_LOCATOR := NULL;
1316
1317 END;
1318 L_LOCATION := RECEIPT_LOCATION;
1319 IF L_LOCATION IS NOT NULL AND RECEIVING_SUBINVENTORY IS NOT NULL THEN
1320 L_LOCATION := L_LOCATION || '-' || RECEIVING_SUBINVENTORY;
1321 ELSIF L_LOCATION IS NULL AND RECEIVING_SUBINVENTORY IS NOT NULL THEN
1322 L_LOCATION := RECEIVING_SUBINVENTORY;
1323 END IF;
1324 IF L_LOCATION IS NOT NULL AND L_LOCATOR IS NOT NULL THEN
1325 L_LOCATION := L_LOCATION || '-' || L_LOCATOR;
1326 ELSIF L_LOCATION IS NULL AND L_LOCATOR IS NOT NULL THEN
1327 L_LOCATION := L_LOCATOR;
1328 END IF;
1329 RETURN (L_LOCATION);
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 RETURN (NULL);
1333
1334 END CF_RECEIPT_LOCATIONFORMULA;
1335
1336 FUNCTION C_NUM_RTS_PRINTED_P RETURN NUMBER IS
1337 BEGIN
1338 RETURN C_NUM_RTS_PRINTED;
1339 END C_NUM_RTS_PRINTED_P;
1340
1341 FUNCTION CP_DELIVERY_LOCATION_P RETURN VARCHAR2 IS
1342 BEGIN
1343 RETURN CP_DELIVERY_LOCATION;
1344 END CP_DELIVERY_LOCATION_P;
1345
1346 FUNCTION CP_RECEIPT_NUM_FROM_P RETURN VARCHAR2 IS
1347 BEGIN
1348 RETURN CP_RECEIPT_NUM_FROM;
1349 END CP_RECEIPT_NUM_FROM_P;
1350
1351 FUNCTION CP_RECEIPT_NUM_TO_P RETURN VARCHAR2 IS
1352 BEGIN
1353 RETURN CP_RECEIPT_NUM_TO;
1354 END CP_RECEIPT_NUM_TO_P;
1355
1356 FUNCTION CP_ITEM_FROM_P RETURN VARCHAR2 IS
1357 BEGIN
1358 RETURN CP_ITEM_FROM;
1359 END CP_ITEM_FROM_P;
1360
1361 FUNCTION CP_ITEM_TO_P RETURN VARCHAR2 IS
1362 BEGIN
1363 RETURN CP_ITEM_TO;
1364 END CP_ITEM_TO_P;
1365
1366 FUNCTION CP_CATEGORY_FROM_P RETURN VARCHAR2 IS
1367 BEGIN
1368 RETURN CP_CATEGORY_FROM;
1369 END CP_CATEGORY_FROM_P;
1370
1371 FUNCTION CP_CATEGORY_TO_P RETURN VARCHAR2 IS
1372 BEGIN
1373 RETURN CP_CATEGORY_TO;
1374 END CP_CATEGORY_TO_P;
1375
1376 FUNCTION CP_SHIP_TO_LOCATION_P RETURN VARCHAR2 IS
1377 BEGIN
1378 RETURN CP_SHIP_TO_LOCATION;
1379 END CP_SHIP_TO_LOCATION_P;
1380
1381 FUNCTION CP_ORG_DISPLAYED_P RETURN VARCHAR2 IS
1382 BEGIN
1383 RETURN CP_ORG_DISPLAYED;
1384 END CP_ORG_DISPLAYED_P;
1385
1386 FUNCTION CP_TITLE_P RETURN VARCHAR2 IS
1387 BEGIN
1388 RETURN CP_TITLE;
1389 END CP_TITLE_P;
1390
1391 FUNCTION CP_WIP_STATUS_P RETURN VARCHAR2 IS
1392 BEGIN
1393 RETURN CP_WIP_STATUS;
1394 END CP_WIP_STATUS_P;
1395
1396 FUNCTION CP_OPERATING_UNIT_DISPLAYED_P RETURN VARCHAR2 IS
1397 BEGIN
1398 RETURN CP_OPERATING_UNIT_DISPLAYED;
1399 END CP_OPERATING_UNIT_DISPLAYED_P;
1400
1401 END PO_POXDLPDT_XMLP_PKG;
1402