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