1 PACKAGE BODY WSH_WSHRDINV_XMLP_PKG AS
2 /* $Header: WSHRDINVB.pls 120.2 2008/02/18 10:13:28 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 EIN_NUMBER VARCHAR2(150);
7 STRUCT_NUMBER NUMBER;
8 L_MSG_BUFFER VARCHAR2(2000);
9 CURSOR ORG_NAME IS
10 SELECT
11 ORGANIZATION_NAME
12 FROM
13 ORG_ORGANIZATION_DEFINITIONS
14 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
15 CURSOR ORG_EIN IS
16 SELECT
17 OI.ORG_INFORMATION1
18 FROM
19 HR_ORGANIZATION_INFORMATION OI
20 WHERE OI.ORGANIZATION_ID = P_ORGANIZATION_ID
21 AND OI.ORG_INFORMATION_CONTEXT = 'Employer Identification';
22 CURSOR MASTER_ORG_EIN IS
23 SELECT
24 MOI.ORG_INFORMATION1
25 FROM
26 MTL_PARAMETERS MP,
27 HR_ORGANIZATION_INFORMATION MOI
28 WHERE MP.ORGANIZATION_ID = P_ORGANIZATION_ID
29 AND MP.MASTER_ORGANIZATION_ID = MOI.ORGANIZATION_ID
30 AND MOI.ORG_INFORMATION_CONTEXT = 'Employer Identification';
31 CURSOR STRUCT_NUM(FLEX_CODE IN VARCHAR2) IS
32 SELECT
33 ID_FLEX_NUM
34 FROM
35 FND_ID_FLEX_STRUCTURES
36 WHERE ID_FLEX_CODE = FLEX_CODE;
37 L_WH_ID NUMBER;
38 L_TMP VARCHAR2(100);
39 BEGIN
40 FND_MESSAGE.SET_NAME('WSH'
41 ,'WSH_COMM_INV_RPT_EXPORT_TXT');
42 L_MSG_BUFFER := FND_MESSAGE.GET;
43 P_EXPORT_TXT := SUBSTRB(L_MSG_BUFFER
44 ,1
45 ,300);
46 OPEN ORG_NAME;
47 FETCH ORG_NAME
48 INTO H_WAREHOUSE_NAME;
49 CLOSE ORG_NAME;
50 OPEN ORG_EIN;
51 FETCH ORG_EIN
52 INTO EIN_NUMBER;
53 CLOSE ORG_EIN;
54 IF EIN_NUMBER IS NULL THEN
55 OPEN MASTER_ORG_EIN;
56 FETCH MASTER_ORG_EIN
57 INTO EIN_NUMBER;
58 CLOSE MASTER_ORG_EIN;
59 END IF;
60 H_EIN := EIN_NUMBER;
61 IF H_REPORT_ID IS NULL THEN
62 H_REPORT_ID := TO_NUMBER(VALUE('CONC_REQUEST_ID'));
63 END IF;
64 IF H_REPORT_ID IS NULL THEN
65 H_REPORT_ID := TO_NUMBER(TO_CHAR(SYSDATE
66 ,'HH24MMSS'));
67 END IF;
68 OPEN STRUCT_NUM(P_ITEM_FLEX_CODE);
69 FETCH STRUCT_NUM
70 INTO STRUCT_NUMBER;
71 CLOSE STRUCT_NUM;
72 LP_STRUCTURE_NUM := STRUCT_NUMBER;
73 IF LP_DEPARTURE_DATE_HIGH IS NOT NULL THEN
74 LP_DEPARTURE_DATE_HIGH := LP_DEPARTURE_DATE_HIGH + (86399 / 86400);
75 END IF;
76 IF P_DEPARTURE_DATE_LOW IS NOT NULL OR LP_DEPARTURE_DATE_HIGH IS NOT NULL THEN
77 IF P_DEPARTURE_DATE_LOW IS NULL THEN
78 LP_DEPARTURE_DATE := 'AND wnd.delivery_id IN (select distinct delivery_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date <= :LP_DEPARTURE_DATE_HIGH))';
79 ELSIF LP_DEPARTURE_DATE_HIGH IS NULL THEN
80 LP_DEPARTURE_DATE := 'AND wnd.delivery_id IN (select distinct delivery_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date >= :P_DEPARTURE_DATE_LOW))';
81 ELSE
82 LP_DEPARTURE_DATE := 'AND wnd.delivery_id in (select distinct delivery_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date between :P_DEPARTURE_DATE_LOW
83 and :LP_DEPARTURE_DATE_HIGH))';
84 END IF;
85 END IF;
86 IF P_DELIVERY_ID IS NOT NULL THEN
87 LP_DELIVERY_ID := 'AND wnd.delivery_id = :P_DELIVERY_ID ';
88 END IF;
89 IF P_FREIGHT_CODE IS NOT NULL THEN
90 LP_FREIGHT_CODE := 'AND wnd.ship_method_code = :P_FREIGHT_CODE ';
91 END IF;
92 IF P_ORGANIZATION_ID IS NOT NULL THEN
93 LP_ORGANIZATION_ID := 'AND wdd.organization_id = :P_ORGANIZATION_ID ';
94 END IF;
95 IF P_TRIP_STOP_ID IS NOT NULL THEN
96 LP_TRIP_STOP_ID := 'AND wnd.delivery_id in (select distinct wdl.delivery_id from wsh_delivery_legs wdl where (wdl.pick_up_stop_id = :P_TRIP_STOP_ID
97 OR wdl.drop_off_stop_id = :P_TRIP_STOP_ID)) ';
98 END IF;
99 RETURN (TRUE);
100 EXCEPTION
101 WHEN OTHERS THEN
102 RETURN FALSE;
103 END;
104 RETURN (TRUE);
105 END AFTERPFORM;
106
107 FUNCTION AFTERREPORT RETURN BOOLEAN IS
108 BEGIN
109 BEGIN
110 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
111 EXCEPTION
112 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
113 /*SRW.MESSAGE(1
114 ,'Failed in SRWEXIT')*/NULL;
115 RAISE;
116 END;
117 RETURN (TRUE);
118 END AFTERREPORT;
119
120 FUNCTION C_ITEM_DISPFORMULA(CUSTOMER_ITEM_ID1 IN NUMBER
121 ,INVENTORY_ITEM_ID1 IN NUMBER
122 ,ORGANIZATION_ID1 IN NUMBER
123 ,ITEM_DESCRIPTION IN VARCHAR2) RETURN VARCHAR2 IS
124 BEGIN
125 DECLARE
126 CURSOR CUSTOMER_LABEL(ID IN NUMBER,FLAG IN VARCHAR2) IS
127 SELECT
128 DECODE(FLAG
129 ,'D'
130 ,CUSTOMER_ITEM_DESC
131 ,'F'
132 ,CUSTOMER_ITEM_NUMBER
133 ,CUSTOMER_ITEM_NUMBER || ' ' || CUSTOMER_ITEM_DESC) LABEL
134 FROM
135 MTL_CUSTOMER_ITEMS
136 WHERE CUSTOMER_ITEM_ID = ID;
137 CURSOR INVENTORY_LABEL(ID IN NUMBER,ORG_ID IN NUMBER) IS
138 SELECT
139 DESCRIPTION
140 FROM
141 MTL_SYSTEM_ITEMS_VL
142 WHERE INVENTORY_ITEM_ID = ID
143 AND ORGANIZATION_ID = ORG_ID;
144 NAME VARCHAR2(800);
145 USE_SHIPPER_NAME BOOLEAN := TRUE;
146 BEGIN
147 IF P_PRINT_CUST_ITEM = 'Y' AND CUSTOMER_ITEM_ID1 IS NOT NULL THEN
148 BEGIN
149 OPEN CUSTOMER_LABEL(CUSTOMER_ITEM_ID1,P_ITEM_DISPLAY);
150 FETCH CUSTOMER_LABEL
151 INTO NAME;
152 CLOSE CUSTOMER_LABEL;
153 IF NAME IS NULL OR NAME = ' ' THEN
154 USE_SHIPPER_NAME := TRUE;
155 ELSE
156 USE_SHIPPER_NAME := FALSE;
157 END IF;
158 EXCEPTION
159 WHEN OTHERS THEN
160 CLOSE CUSTOMER_LABEL;
161 USE_SHIPPER_NAME := TRUE;
162 END;
163 END IF;
164 IF USE_SHIPPER_NAME THEN
165 IF P_ITEM_DISPLAY = 'D' THEN
166 IF INVENTORY_ITEM_ID1 IS NOT NULL THEN
167 OPEN INVENTORY_LABEL(INVENTORY_ITEM_ID1,ORGANIZATION_ID1);
168 FETCH INVENTORY_LABEL
169 INTO NAME;
170 CLOSE INVENTORY_LABEL;
171 ELSE
172 NAME := ITEM_DESCRIPTION;
173 END IF;
174 ELSIF P_ITEM_DISPLAY = 'F' THEN
175 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(INVENTORY_ITEM_ID1
176 ,ORGANIZATION_ID1
177 ,P_ITEM_FLEX_CODE
178 ,LP_STRUCTURE_NUM);
179 ELSE
180 IF INVENTORY_ITEM_ID1 IS NOT NULL THEN
181 OPEN INVENTORY_LABEL(INVENTORY_ITEM_ID1,ORGANIZATION_ID1);
182 FETCH INVENTORY_LABEL
183 INTO NAME;
184 CLOSE INVENTORY_LABEL;
185 ELSE
186 NAME := ITEM_DESCRIPTION;
187 END IF;
188 NAME := WSH_UTIL_CORE.GET_ITEM_NAME(INVENTORY_ITEM_ID1
189 ,ORGANIZATION_ID1
190 ,P_ITEM_FLEX_CODE
191 ,LP_STRUCTURE_NUM) || ' ' || NAME;
192 END IF;
193 END IF;
194 RETURN NAME;
195 END;
196 RETURN NULL;
197 END C_ITEM_DISPFORMULA;
198
199 FUNCTION C_NUM_BOXESFORMULA(DELIVERY_ID3 IN NUMBER
200 ,NUM_LPN IN NUMBER) RETURN NUMBER IS
201 BEGIN
202 DECLARE
203 CURSOR BOXES IS
204 SELECT
205 count(*)
206 FROM
207 WSH_DELIVERY_ASSIGNMENTS_V WDA,
208 WSH_DELIVERY_DETAILS WDD
209 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
210 AND WDD.CONTAINER_FLAG = 'Y'
211 AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL
212 AND WDA.DELIVERY_ID is not null
213 AND WDA.DELIVERY_ID = DELIVERY_ID3;
214 NUM_OF_BOXES NUMBER;
215 BEGIN
216 IF (NUM_LPN IS NULL) THEN
217 OPEN BOXES;
218 FETCH BOXES
219 INTO NUM_OF_BOXES;
220 CLOSE BOXES;
221 RETURN (NUM_OF_BOXES);
222 ELSE
223 RETURN (NUM_LPN);
224 END IF;
225 END;
226 RETURN NULL;
227 END C_NUM_BOXESFORMULA;
228
229 FUNCTION C_DATA_FOUNDFORMULA(DELIVERY_ID3 IN NUMBER) RETURN NUMBER IS
230 BEGIN
231 RP_DATA_FOUND := DELIVERY_ID3;
232 RETURN (0);
233 END C_DATA_FOUNDFORMULA;
234
235 FUNCTION LP_STOP_IDVALIDTRIGGER RETURN BOOLEAN IS
236 BEGIN
237 RETURN (TRUE);
238 END LP_STOP_IDVALIDTRIGGER;
239
240 FUNCTION C_SHIP_VIAFORMULA(DELIVERY_ID3 IN NUMBER
241 ,SHIP_VIA IN VARCHAR2
242 ,ORGANIZATION_ID1 IN NUMBER) RETURN CHAR IS
243 CURSOR OTHER_LEGS IS
244 SELECT
245 DISTINCT
246 WCS.SHIP_METHOD_MEANING
247 FROM
248 WSH_TRIPS T,
249 WSH_TRIP_STOPS ST,
250 WSH_DELIVERY_LEGS DG,
251 WSH_CARRIER_SERVICES WCS,
252 WSH_ORG_CARRIER_SERVICES WOCS
253 WHERE DG.DELIVERY_ID = DELIVERY_ID3
254 AND DG.PICK_UP_STOP_ID = ST.STOP_ID
255 AND ST.TRIP_ID = T.TRIP_ID
256 AND T.SHIP_METHOD_CODE <> NVL(SHIP_VIA
257 ,'-1')
258 AND T.SHIP_METHOD_CODE = WCS.SHIP_METHOD_CODE
259 AND WCS.CARRIER_SERVICE_ID = WOCS.CARRIER_SERVICE_ID
260 AND WOCS.ORGANIZATION_ID = ORGANIZATION_ID1;
261 CURSOR GET_SHIP_METHOD_MEANING IS
262 SELECT
263 WCS.SHIP_METHOD_MEANING
264 FROM
265 WSH_CARRIER_SERVICES WCS
266 WHERE WCS.SHIP_METHOD_CODE = SHIP_VIA;
267 L_SHIP_METHOD VARCHAR2(500) := SHIP_VIA;
268 BEGIN
269 IF NVL(L_SHIP_METHOD
270 ,'-1') = '-1' THEN
271 L_SHIP_METHOD := NULL;
272 ELSE
273 OPEN GET_SHIP_METHOD_MEANING;
274 FETCH GET_SHIP_METHOD_MEANING
275 INTO L_SHIP_METHOD;
276 CLOSE GET_SHIP_METHOD_MEANING;
277 END IF;
278 FOR dl IN OTHER_LEGS LOOP
279 IF (L_SHIP_METHOD IS NOT NULL) THEN
280 L_SHIP_METHOD := L_SHIP_METHOD || ', ' || DL.SHIP_METHOD_MEANING;
281 ELSE
282 L_SHIP_METHOD := DL.SHIP_METHOD_MEANING;
283 END IF;
284 END LOOP;
285 RETURN L_SHIP_METHOD;
286 END C_SHIP_VIAFORMULA;
287
288 FUNCTION H_WAREHOUSE_NAMEVALIDTRIGGER RETURN BOOLEAN IS
289 BEGIN
290 RETURN (TRUE);
291 END H_WAREHOUSE_NAMEVALIDTRIGGER;
292
293 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
294 apf boolean;
295 br0008 boolean;
296 BEGIN
297 br0008 := BEFOREREPORT0008;
298 apf := AFTERPFORM;
299 RETURN (TRUE);
300 END BEFOREPFORM;
301
302 FUNCTION F_SHIP_TO_CUST_NAMEFORMULA(SHIP_TO_SITE_USE_ID IN NUMBER) RETURN CHAR IS
303 BEGIN
304 DECLARE
305 SHIP_TO_CUST_NAME HZ_PARTIES.PARTY_NAME%TYPE;
306 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
307 L_LOOKUP_TYPE VARCHAR2(20);
308 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
309 BEGIN
310 /*SRW.REFERENCE(SHIP_TO_SITE_USE_ID)*/NULL;
311 SELECT
312 HP.PARTY_NAME,
313 NVL(HP.PERSON_PRE_NAME_ADJUNCT
314 ,HP.PERSON_TITLE) TITLE
315 INTO SHIP_TO_CUST_NAME,L_PERSON_TITLE
316 FROM
317 HZ_PARTY_SITES PS,
318 HZ_CUST_ACCT_SITES_ALL CA,
319 HZ_CUST_SITE_USES_ALL SU,
320 HZ_PARTIES HP
321 WHERE SU.SITE_USE_ID = SHIP_TO_SITE_USE_ID
322 AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
323 AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
324 AND HP.PARTY_ID = PS.PARTY_ID;
325 IF L_PERSON_TITLE IS NOT NULL THEN
326 BEGIN
327 L_LOOKUP_TYPE := 'RESPONSIBILITY';
328 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
329 SELECT
330 MEANING || ' ' || SHIP_TO_CUST_NAME
331 INTO SHIP_TO_CUST_NAME
332 FROM
333 AR_LOOKUPS
334 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
335 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
336 EXCEPTION
337 WHEN OTHERS THEN
338 SHIP_TO_CUST_NAME := L_PERSON_TITLE || ' ' || SHIP_TO_CUST_NAME;
339 END;
340 END IF;
341 IF SQL%NOTFOUND THEN
342 RETURN (NULL);
343 END IF;
344 RETURN (SHIP_TO_CUST_NAME);
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 RETURN (NULL);
348 WHEN OTHERS THEN
349 RAISE;
350 END;
351 END F_SHIP_TO_CUST_NAMEFORMULA;
352
353 FUNCTION CF_CONTACT_NAMEFORMULA(SHIP_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
354 CONTACT_NAME HZ_PARTIES.PARTY_NAME%TYPE;
355 L_PERSON_TITLE HZ_PARTIES.PERSON_TITLE%TYPE;
356 L_PERSON_TITLE_UP HZ_PARTIES.PERSON_TITLE%TYPE;
357 L_LOOKUP_TYPE VARCHAR2(20);
358 BEGIN
359 /*SRW.REFERENCE(SHIP_TO_CONTACT_ID)*/NULL;
360 IF (SHIP_TO_CONTACT_ID IS NOT NULL) THEN
361 SELECT
362 PARTY.PARTY_NAME,
363 NVL(PARTY.PERSON_PRE_NAME_ADJUNCT
364 ,PARTY.PERSON_TITLE) TITLE
365 INTO CONTACT_NAME,L_PERSON_TITLE
366 FROM
367 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
368 HZ_PARTIES PARTY,
369 HZ_RELATIONSHIPS REL,
370 HZ_ORG_CONTACTS ORG_CONT,
371 HZ_PARTIES REL_PARTY
372 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = SHIP_TO_CONTACT_ID
373 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
374 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
375 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
376 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
377 AND REL.DIRECTIONAL_FLAG = 'F'
378 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
379 AND REL.SUBJECT_ID = PARTY.PARTY_ID
380 AND REL.PARTY_ID = REL_PARTY.PARTY_ID;
381 IF L_PERSON_TITLE IS NOT NULL THEN
382 BEGIN
383 L_LOOKUP_TYPE := 'RESPONSIBILITY';
384 L_PERSON_TITLE_UP := UPPER(L_PERSON_TITLE);
385 SELECT
386 MEANING || ' ' || CONTACT_NAME
387 INTO CONTACT_NAME
388 FROM
389 AR_LOOKUPS
390 WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
391 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
392 EXCEPTION
393 WHEN OTHERS THEN
394 CONTACT_NAME := L_PERSON_TITLE || ' ' || CONTACT_NAME;
395 END;
396 END IF;
397 ELSE
398 CONTACT_NAME := ' ';
399 END IF;
400 RETURN (CONTACT_NAME);
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 CONTACT_NAME := ' ';
404 RETURN (CONTACT_NAME);
405 WHEN OTHERS THEN
406 RAISE;
407 END CF_CONTACT_NAMEFORMULA;
408
409 FUNCTION CF_CUSTOMER_NAMEFORMULA RETURN CHAR IS
410 CUSTOMER_NAME VARCHAR2(120) := 'X';
411 BEGIN
412 RETURN (CUSTOMER_NAME);
413 END CF_CUSTOMER_NAMEFORMULA;
414
415 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
416 BEGIN
417 RETURN (TRUE);
418 END BEFOREREPORT;
419
420 FUNCTION BEFOREREPORT0008 RETURN BOOLEAN IS
421 BEGIN
422 BEGIN
423 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
424 LP_DEPARTURE_DATE_HIGH := P_DEPARTURE_DATE_HIGH;
425 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
426 EXCEPTION
427 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
428 /*SRW.MESSAGE(1
429 ,'Failed FND SRWINIT.')*/NULL;
430 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
431 END;
432 RETURN (TRUE);
433 END BEFOREREPORT0008;
434
435 FUNCTION CF_1FORMULA RETURN CHAR IS
436 BEGIN
437 RETURN H_WAREHOUSE_NAME;
438 END CF_1FORMULA;
439
440 FUNCTION CF_EINFORMULA0007 RETURN CHAR IS
441 BEGIN
442 RETURN H_EIN;
443 END CF_EINFORMULA0007;
444
445 FUNCTION CF_COMMODITY_CLASSFORMULA(INVENTORY_ITEM_ID1 IN NUMBER
446 ,ORGANIZATION_ID1 IN NUMBER) RETURN CHAR IS
447 L_CLASS_LIST VARCHAR2(1000);
448 CURSOR C_CATEGORY(C_INV_ITEM_ID IN NUMBER,C_INV_ORG_ID IN NUMBER) IS
449 SELECT
450 CONCATENATED_SEGMENTS COMM_CLASS
451 FROM
452 MTL_CATEGORIES_KFV MC,
453 MTL_ITEM_CATEGORIES MIC,
454 MTL_CATEGORY_SETS_VL MCSTL
455 WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
456 AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
457 AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
458 AND MC.CATEGORY_ID = MIC.CATEGORY_ID
459 AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
460 ORDER BY
461 MC.CATEGORY_ID;
462 BEGIN
463 /*SRW.REFERENCE(INVENTORY_ITEM_ID1)*/NULL;
464 /*SRW.REFERENCE(ORGANIZATION_ID1)*/NULL;
465 FOR c_rec IN C_CATEGORY(c_inv_item_id => INVENTORY_ITEM_ID1,
466 c_inv_org_id => ORGANIZATION_ID1) LOOP
467 IF (L_CLASS_LIST IS NULL) THEN
468 L_CLASS_LIST := C_REC.COMM_CLASS;
469 ELSE
470 L_CLASS_LIST := L_CLASS_LIST || ', ' || C_REC.COMM_CLASS;
471 END IF;
472 END LOOP;
473 RETURN (L_CLASS_LIST);
474 EXCEPTION
475 WHEN OTHERS THEN
476 RETURN (NULL);
477 END CF_COMMODITY_CLASSFORMULA;
478
479 FUNCTION CP_SHIP_TO_ADDR1_P RETURN VARCHAR2 IS
480 BEGIN
481 RETURN CP_SHIP_TO_ADDR1;
482 END CP_SHIP_TO_ADDR1_P;
483
484 FUNCTION CP_SHIP_TO_ADDR2_P RETURN VARCHAR2 IS
485 BEGIN
486 RETURN CP_SHIP_TO_ADDR2;
487 END CP_SHIP_TO_ADDR2_P;
488
489 FUNCTION CP_SHIP_TO_ADDR3_P RETURN VARCHAR2 IS
490 BEGIN
491 RETURN CP_SHIP_TO_ADDR3;
492 END CP_SHIP_TO_ADDR3_P;
493
494 FUNCTION CP_SHIP_TO_ADDR4_P RETURN VARCHAR2 IS
495 BEGIN
496 RETURN CP_SHIP_TO_ADDR4;
497 END CP_SHIP_TO_ADDR4_P;
498
499 FUNCTION CP_SHIP_TO_CITY_STATE_P RETURN VARCHAR2 IS
500 BEGIN
501 RETURN CP_SHIP_TO_CITY_STATE;
502 END CP_SHIP_TO_CITY_STATE_P;
503
504 FUNCTION CP_SHIP_TO_COUNTRY_P RETURN VARCHAR2 IS
505 BEGIN
506 RETURN CP_SHIP_TO_COUNTRY;
507 END CP_SHIP_TO_COUNTRY_P;
508
509 FUNCTION CP_ITEM_COST_P RETURN NUMBER IS
510 BEGIN
511 RETURN CP_ITEM_COST;
512 END CP_ITEM_COST_P;
513
514 FUNCTION CP_EXTENDED_COST_P RETURN NUMBER IS
515 BEGIN
516 RETURN CP_EXTENDED_COST;
517 END CP_EXTENDED_COST_P;
518
519 PROCEDURE PUT(NAME IN VARCHAR2
520 ,VAL IN VARCHAR2) IS
521 BEGIN
522 /*STPROC.INIT('begin FND_PROFILE.PUT(:NAME, :VAL); end;');
523 STPROC.BIND_I(NAME);
524 STPROC.BIND_I(VAL);
525 STPROC.EXECUTE;*/
526 FND_PROFILE.PUT(NAME, VAL);
527 END PUT;
528
529 FUNCTION DEFINED(NAME IN VARCHAR2) RETURN BOOLEAN IS
530 X0 BOOLEAN;
531 X1 integer;
532 BEGIN
533 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.DEFINED(:NAME); :X0 := sys.diutil.bool_to_int(X0rv); end;');
534 STPROC.BIND_I(NAME);
535 STPROC.BIND_O(X0);
536 STPROC.EXECUTE;
537 STPROC.RETRIEVE(2
538 ,X0);
539 RETURN X0;*/
540 declare
541 X0rv BOOLEAN;
542 begin
543 X0rv := FND_PROFILE.DEFINED(NAME);
544 X1 := sys.diutil.bool_to_int(X0rv);
545 end;
546 RETURN sys.diutil.int_to_bool(X1);
547 END DEFINED;
548
549 PROCEDURE GET(NAME IN VARCHAR2
550 ,VAL OUT NOCOPY VARCHAR2) IS
551 BEGIN
552 /*STPROC.INIT('begin FND_PROFILE.GET(:NAME, :VAL); end;');
553 STPROC.BIND_I(NAME);
554 STPROC.BIND_O(VAL);
555 STPROC.EXECUTE;
556 STPROC.RETRIEVE(2
557 ,VAL);*/
558 FND_PROFILE.GET(NAME, VAL);
559 END GET;
560
561 FUNCTION VALUE(NAME IN VARCHAR2) RETURN VARCHAR2 IS
562 X0 VARCHAR2(2000);
563 BEGIN
564 /*STPROC.INIT('begin :X0 := FND_PROFILE.VALUE(:NAME); end;');
565 STPROC.BIND_O(X0);
566 STPROC.BIND_I(NAME);
567 STPROC.EXECUTE;
568 STPROC.RETRIEVE(1
569 ,X0);
570 RETURN X0;*/
571
572 X0 := FND_PROFILE.VALUE(NAME);
573 RETURN X0;
574 END VALUE;
575
576 FUNCTION SAVE_USER(X_NAME IN VARCHAR2
577 ,X_VALUE IN VARCHAR2) RETURN BOOLEAN IS
578 X0 BOOLEAN;
579 X1 integer;
580 BEGIN
581 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE_USER(:X_NAME, :X_VALUE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
582 STPROC.BIND_I(X_NAME);
583 STPROC.BIND_I(X_VALUE);
584 STPROC.BIND_O(X0);
585 STPROC.EXECUTE;
586 STPROC.RETRIEVE(3
587 ,X0);
588 RETURN X0;*/
589
590 declare
591 X0rv BOOLEAN;
592 begin
593 X0rv := FND_PROFILE.SAVE_USER(X_NAME, X_VALUE);
594 X1 := sys.diutil.bool_to_int(X0rv);
595 end;
596 RETURN sys.diutil.int_to_bool(X1);
597 END SAVE_USER;
598
599 FUNCTION SAVE(X_NAME IN VARCHAR2
600 ,X_VALUE IN VARCHAR2
601 ,X_LEVEL_NAME IN VARCHAR2
602 ,X_LEVEL_VALUE IN VARCHAR2
603 ,X_LEVEL_VALUE_APP_ID IN VARCHAR2) RETURN BOOLEAN IS
604 X0 BOOLEAN;
605 X1 integer;
606 BEGIN
607 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := FND_PROFILE.SAVE(:X_NAME, :X_VALUE, :X_LEVEL_NAME, :X_LEVEL_VALUE, :X_LEVEL_VALUE_APP_ID); :X0 := sys.diutil.bool_to_int(X0rv); end;');
608 STPROC.BIND_I(X_NAME);
609 STPROC.BIND_I(X_VALUE);
610 STPROC.BIND_I(X_LEVEL_NAME);
611 STPROC.BIND_I(X_LEVEL_VALUE);
612 STPROC.BIND_I(X_LEVEL_VALUE_APP_ID);
613 STPROC.BIND_O(X0);
614 STPROC.EXECUTE;
615 STPROC.RETRIEVE(6
616 ,X0);
617 RETURN X0;*/
618
619 declare
620 X0rv BOOLEAN;
621 begin
622 X0rv := FND_PROFILE.SAVE(X_NAME, X_VALUE, X_LEVEL_NAME, X_LEVEL_VALUE, X_LEVEL_VALUE_APP_ID);
623 X1 := sys.diutil.bool_to_int(X0rv);
624 end;
625 RETURN sys.diutil.int_to_bool(X1);
626 END SAVE;
627
628 PROCEDURE GET_SPECIFIC(NAME_Z IN VARCHAR2
629 ,USER_ID_Z IN NUMBER
630 ,RESPONSIBILITY_ID_Z IN NUMBER
631 ,APPLICATION_ID_Z IN NUMBER
632 ,VAL_Z OUT NOCOPY VARCHAR2
633 ,DEFINED_Z OUT NOCOPY BOOLEAN) IS
634 BEGIN
635 /*STPROC.INIT('declare DEFINED_Z BOOLEAN; begin DEFINED_Z := sys.diutil.int_to_bool(:DEFINED_Z); FND_PROFILE.GET_SPECIFIC(:NAME_Z, :USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :VAL_Z, DEFINED_Z);
636 :DEFINED_Z := sys.diutil.bool_to_int(DEFINED_Z); end;');
637 STPROC.BIND_O(DEFINED_Z);
638 STPROC.BIND_I(NAME_Z);
639 STPROC.BIND_I(USER_ID_Z);
640 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
641 STPROC.BIND_I(APPLICATION_ID_Z);
642 STPROC.BIND_O(VAL_Z);
643 STPROC.EXECUTE;
644 STPROC.RETRIEVE(1
645 ,DEFINED_Z);
646 STPROC.RETRIEVE(6,VAL_Z);*/
647
648 declare
649 DEFINED_Z BOOLEAN;
650 DEFINED_Z1 integer;
651 begin
652 DEFINED_Z := sys.diutil.int_to_bool(DEFINED_Z1);
653 FND_PROFILE.GET_SPECIFIC(NAME_Z, USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z, VAL_Z, DEFINED_Z);
654 DEFINED_Z1 := sys.diutil.bool_to_int(DEFINED_Z);
655 end;
656 END GET_SPECIFIC;
657
658 FUNCTION VALUE_SPECIFIC(NAME IN VARCHAR2
659 ,USER_ID IN NUMBER
660 ,RESPONSIBILITY_ID IN NUMBER
661 ,APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
662 X0 VARCHAR2(2000);
663 BEGIN
664 /*STPROC.INIT('begin :X0 := FND_PROFILE.VALUE_SPECIFIC(:NAME, :USER_ID, :RESPONSIBILITY_ID, :APPLICATION_ID); end;');
665 STPROC.BIND_O(X0);
666 STPROC.BIND_I(NAME);
667 STPROC.BIND_I(USER_ID);
668 STPROC.BIND_I(RESPONSIBILITY_ID);
669 STPROC.BIND_I(APPLICATION_ID);
670 STPROC.EXECUTE;
671 STPROC.RETRIEVE(1
672 ,X0);
673 RETURN X0;*/
674
675 X0 := FND_PROFILE.VALUE_SPECIFIC(NAME, USER_ID, RESPONSIBILITY_ID, APPLICATION_ID);
676 RETURN X0;
677 END VALUE_SPECIFIC;
678
679 PROCEDURE INITIALIZE(USER_ID_Z IN NUMBER
680 ,RESPONSIBILITY_ID_Z IN NUMBER
681 ,APPLICATION_ID_Z IN NUMBER
682 ,SITE_ID_Z IN NUMBER) IS
683 BEGIN
684 /*STPROC.INIT('begin FND_PROFILE.INITIALIZE(:USER_ID_Z, :RESPONSIBILITY_ID_Z, :APPLICATION_ID_Z, :SITE_ID_Z); end;');
685 STPROC.BIND_I(USER_ID_Z);
686 STPROC.BIND_I(RESPONSIBILITY_ID_Z);
687 STPROC.BIND_I(APPLICATION_ID_Z);
688 STPROC.BIND_I(SITE_ID_Z);
689 STPROC.EXECUTE;*/
690
691 FND_PROFILE.INITIALIZE(USER_ID_Z, RESPONSIBILITY_ID_Z, APPLICATION_ID_Z, SITE_ID_Z);
692 END INITIALIZE;
693
694 PROCEDURE PUTMULTIPLE(NAMES IN VARCHAR2
695 ,VALS IN VARCHAR2
696 ,NUM IN NUMBER) IS
697 BEGIN
698 /*STPROC.INIT('begin FND_PROFILE.PUTMULTIPLE(:NAMES, :VALS, :NUM); end;');
699 STPROC.BIND_I(NAMES);
700 STPROC.BIND_I(VALS);
701 STPROC.BIND_I(NUM);
702 STPROC.EXECUTE;*/
703
704 FND_PROFILE.PUTMULTIPLE(NAMES, VALS, NUM);
705 END PUTMULTIPLE;
706
707
708 function C_ext_cost_fmtFormula(source_code varchar2, line_id number, unit_of_measure varchar2,
709 source_uom varchar2, shipped_quantity number, inventory_item_id1 number) return VARCHAR2 is
710 cursor oe_selling_price is
711 select unit_selling_price
712 from oe_order_lines_all
713 where line_id = line_id;
714
715 cursor oke_selling_price is
716 select unit_price
717 from oke_k_deliverables_b
718 where deliverable_id = line_id;
719
720 l_unit_selling_price NUMBER := 0;
721 begin
722 If source_code = 'OE' then
723 open oe_selling_price;
724 fetch oe_selling_price into l_unit_selling_price;
725 close oe_selling_price;
726 Elsif source_code = 'OKE' then
727 open oke_selling_price;
728 fetch oke_selling_price into l_unit_selling_price;
729 close oke_selling_price;
730 End If;
731 cp_extended_cost := ROUND(l_unit_selling_price *
732 WSH_WV_UTILS.CONVERT_UOM(unit_of_measure, source_uom,
733 shipped_quantity,
734 inventory_item_id1),2);
735 p_extended_cost := cp_extended_cost;
736 /*BEGIN
737 SRW.REFERENCE(:CURRENCY_CODE);
738
739 IF (:CURRENCY_CODE) IS NOT NULL THEN
740 SRW.USER_EXIT('FND FORMAT_CURRENCY
741 CODE=":CURRENCY_CODE"
742 DISPLAY_WIDTH="11"
743 AMOUNT=":p_extended_cost"
744 DISPLAY=":C_EXT_COST_FMT" ');
745
746 RETURN(:C_EXT_COST_FMT);
747 END IF;
748 RETURN NULL;
749 END;*/
750 end;
751
752 function C_item_cost_fmtFormula(source_code varchar2, line_id number, unit_of_measure varchar2,
753 source_uom varchar2, inventory_item_id1 number) return VARCHAR2 is
754 cursor oe_selling_price is
755 select unit_selling_price
756 from oe_order_lines_all
757 where line_id = line_id;
758
759 cursor oke_selling_price is
760 select unit_price
761 from oke_k_deliverables_b
762 where deliverable_id = line_id;
763
764 l_unit_selling_price NUMBER := 0;
765 begin
766 If source_code = 'OE' then
767 open oe_selling_price;
768 fetch oe_selling_price into l_unit_selling_price;
769 close oe_selling_price;
770 Elsif source_code = 'OKE' then
771 open oke_selling_price;
772 fetch oke_selling_price into l_unit_selling_price;
773 close oke_selling_price;
774 End If;
775 cp_item_cost := ROUND(l_unit_selling_price *
776 WSH_WV_UTILS.CONVERT_UOM(unit_of_measure, source_uom, 1,
777 inventory_item_id1),2);
778
779 p_item_cost := cp_item_cost;
780 /*BEGIN
781 SRW.REFERENCE(:currency_code);
782
783 IF (:currency_code) is not null then
784 SRW.USER_EXIT('FND FORMAT_CURRENCY
785 CODE=":CURRENCY_CODE"
786 DISPLAY_WIDTH="11"
787 AMOUNT=":p_item_cost"
788 DISPLAY=":C_ITEM_COST_FMT" ');
789
790 RETURN(:C_ITEM_COST_FMT);
791 END IF;
792 END;*/
793 RETURN NULL;
794 end;
795
796 END WSH_WSHRDINV_XMLP_PKG;
797
798
[Home] [Help]