[Home] [Help]
PACKAGE BODY: APPS.JA_JAIN23C1_XMLP_PKG
Source
1 PACKAGE BODY JA_JAIN23C1_XMLP_PKG AS
2 /* $Header: JAIN23C1B.pls 120.1 2007/12/25 16:07:03 dwkrishn noship $ */
3 FUNCTION CF_1FORMULA(REGISTER_ID IN NUMBER
4 ,OPENING_BALANCE_QTY IN NUMBER
5 ,CLOSING_BALANCE_QTY IN NUMBER
6 ,INVENTORY_ITEM_ID IN NUMBER
7 ,SLNO IN NUMBER) RETURN VARCHAR2 IS
8 BEGIN
9 /*SRW.MESSAGE(11
10 ,'register_id = ' || REGISTER_ID || ', opening balance = ' || OPENING_BALANCE_QTY)*/NULL;
11 /*SRW.MESSAGE(12
12 ,'register_id = ' || REGISTER_ID || ', closing balance = ' || CLOSING_BALANCE_QTY)*/NULL;
13 RETURN (TO_CHAR(INVENTORY_ITEM_ID) || '/' || TO_CHAR(SLNO));
14 END CF_1FORMULA;
15 FUNCTION AFTERREPORT RETURN BOOLEAN IS
16 V_MONTH VARCHAR2(3);
17 V_FINAL_FOLIO NUMBER;
18 BEGIN
19 V_MONTH := TO_CHAR(P_TRN_FROM_DATE_1
20 ,'MON');
21 V_FINAL_FOLIO := PREV_PAGE + LAST_PAGE;
22 IF VALIDATION_FLAG = 'Y' THEN
23 NULL;
24 END IF;
25 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
26 RETURN (TRUE);
27 END AFTERREPORT;
28 FUNCTION AFTERPFORM RETURN BOOLEAN IS
29 FOLIOMONTH DATE;
30 MAXENDDATE DATE;
31 BEGIN
32 P_TRN_FROM_DATE_1 :=P_TRN_FROM_DATE;
33 P_TRN_TO_DATE_1 := P_TRN_TO_DATE;
34 IF P_FISCAL_YEAR IS NOT NULL AND P_MONTH IS NOT NULL THEN
35 FOLIOMONTH := TO_DATE('01-' || UPPER(P_MONTH) || TO_CHAR(P_FISCAL_YEAR
36 ,'-YYYY')
37 ,'DD-MON-YYYY');
38 IF TRUNC(FOLIOMONTH) < TRUNC(P_FISCAL_YEAR) THEN
39 FOLIOMONTH := ADD_MONTHS(FOLIOMONTH
40 ,12);
41 END IF;
42 IF TRUNC(LAST_DAY(FOLIOMONTH)) < TRUNC(SYSDATE) THEN
43 P_TRN_FROM_DATE_1 := TO_DATE('01-' || TO_CHAR(FOLIOMONTH
44 ,'MON-YYYY')
45 ,'DD-MON-YYYY');
46 P_TRN_TO_DATE_1 := LAST_DAY(P_TRN_FROM_DATE_1);
47 VALIDATION_FLAG := 'Y';
48 IF PREV_PAGE = -1 THEN
49 /*SRW.MESSAGE(999
50 ,'This Report is not run for the Previous Month')*/NULL;
51 VALIDATION_FLAG := 'N';
52 END IF;
53 END IF;
54 ELSE
55 VALIDATION_FLAG := 'N';
56 END IF;
57 RETURN (TRUE);
58 END AFTERPFORM;
59 FUNCTION CF_QTY_RECEIVEDFORMULA(SLNO_1 IN NUMBER
60 ,INVENTORY_ITEM_ID_1 IN NUMBER
61 ,TRANSACTION_DATE_1 IN DATE
62 ,TRANSACTION_TYPE IN VARCHAR2) RETURN NUMBER IS
63 RCVD_QTY NUMBER;
64 BEGIN
65 BEGIN
66 SELECT
67 QUANTITY_RECEIVED
68 INTO RCVD_QTY
69 FROM
70 JAI_CMN_RG_23AC_I_TRXS
71 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
72 AND LOCATION_ID = P_LOCATION_ID
73 AND SLNO = SLNO_1
74 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
75 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
76 EXCEPTION
77 WHEN OTHERS THEN
78 RCVD_QTY := '';
79 END;
80 IF RCVD_QTY IS NULL THEN
81 BEGIN
82 SELECT
83 OTH_RECEIPT_QUANTITY
84 INTO RCVD_QTY
85 FROM
86 JAI_CMN_RG_23AC_I_TRXS
87 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
88 AND LOCATION_ID = P_LOCATION_ID
89 AND SLNO = SLNO_1
90 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
91 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
92 EXCEPTION
93 WHEN OTHERS THEN
94 RCVD_QTY := '';
95 END;
96 END IF;
97 IF TRANSACTION_TYPE = 'RTV' THEN
98 RCVD_QTY := NULL;
99 END IF;
100 RETURN (RCVD_QTY);
101 END CF_QTY_RECEIVEDFORMULA;
102 FUNCTION CF_INV_IDFORMULA(SLNO_1 IN NUMBER
103 ,INVENTORY_ITEM_ID_1 IN NUMBER
104 ,TRANSACTION_DATE_1 IN DATE) RETURN CHAR IS
105 RCVD_QTY NUMBER;
106 INV_ID VARCHAR2(30);
107 BEGIN
108 BEGIN
109 SELECT
110 QUANTITY_RECEIVED
111 INTO RCVD_QTY
112 FROM
113 JAI_CMN_RG_23AC_I_TRXS
114 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
115 AND LOCATION_ID = P_LOCATION_ID
116 AND SLNO = SLNO_1
117 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
118 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
119 EXCEPTION
120 WHEN OTHERS THEN
121 RCVD_QTY := '';
122 END;
123 IF RCVD_QTY IS NOT NULL THEN
124 BEGIN
125 SELECT
126 EXCISE_INVOICE_NO
127 INTO INV_ID
128 FROM
129 JAI_CMN_RG_23AC_I_TRXS
130 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
131 AND LOCATION_ID = P_LOCATION_ID
132 AND SLNO = SLNO_1
133 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
134 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
135 EXCEPTION
136 WHEN OTHERS THEN
137 INV_ID := '';
138 END;
139 ELSE
140 BEGIN
141 SELECT
142 OTH_RECEIPT_QUANTITY
143 INTO RCVD_QTY
144 FROM
145 JAI_CMN_RG_23AC_I_TRXS
146 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
147 AND LOCATION_ID = P_LOCATION_ID
148 AND SLNO = SLNO_1
149 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
150 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
151 EXCEPTION
152 WHEN OTHERS THEN
153 RCVD_QTY := '';
154 END;
155 IF RCVD_QTY IS NOT NULL THEN
156 BEGIN
157 SELECT
158 OTH_RECEIPT_ID_REF
159 INTO INV_ID
160 FROM
161 JAI_CMN_RG_23AC_I_TRXS
162 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
163 AND LOCATION_ID = P_LOCATION_ID
164 AND SLNO = SLNO_1
165 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
166 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
167 EXCEPTION
168 WHEN OTHERS THEN
169 INV_ID := '';
170 END;
171 END IF;
172 END IF;
173 RETURN (INV_ID);
174 END CF_INV_IDFORMULA;
175 FUNCTION CF_INV_DTFORMULA(SLNO_1 IN NUMBER
176 ,INVENTORY_ITEM_ID_1 IN NUMBER
177 ,TRANSACTION_DATE_1 IN DATE) RETURN DATE IS
178 RCVD_QTY NUMBER;
179 INV_DT DATE;
180 BEGIN
181 BEGIN
182 SELECT
183 QUANTITY_RECEIVED
184 INTO RCVD_QTY
185 FROM
186 JAI_CMN_RG_23AC_I_TRXS
187 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
188 AND LOCATION_ID = P_LOCATION_ID
189 AND SLNO = SLNO_1
190 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
191 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
192 EXCEPTION
193 WHEN OTHERS THEN
194 RCVD_QTY := '';
195 END;
196 IF RCVD_QTY IS NOT NULL THEN
197 BEGIN
198 SELECT
199 EXCISE_INVOICE_DATE
200 INTO INV_DT
201 FROM
202 JAI_CMN_RG_23AC_I_TRXS
203 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
204 AND LOCATION_ID = P_LOCATION_ID
205 AND SLNO = SLNO_1
206 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
207 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
208 EXCEPTION
209 WHEN OTHERS THEN
210 INV_DT := '';
211 END;
212 ELSE
213 BEGIN
214 SELECT
215 OTH_RECEIPT_QUANTITY
216 INTO RCVD_QTY
217 FROM
218 JAI_CMN_RG_23AC_I_TRXS
219 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
220 AND LOCATION_ID = P_LOCATION_ID
221 AND SLNO = SLNO_1
222 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
223 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
224 EXCEPTION
225 WHEN OTHERS THEN
226 RCVD_QTY := '';
227 END;
228 IF RCVD_QTY IS NOT NULL THEN
229 BEGIN
230 SELECT
231 OTH_RECEIPT_DATE
232 INTO INV_DT
233 FROM
234 JAI_CMN_RG_23AC_I_TRXS
235 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
236 AND LOCATION_ID = P_LOCATION_ID
237 AND SLNO = SLNO_1
238 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
239 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
240 EXCEPTION
241 WHEN OTHERS THEN
242 INV_DT := '';
243 END;
244 END IF;
245 END IF;
246 RETURN (INV_DT);
247 END CF_INV_DTFORMULA;
248 FUNCTION CF_SALES_INV_IDFORMULA(SLNO_1 IN NUMBER
249 ,INVENTORY_ITEM_ID_1 IN NUMBER
250 ,TRANSACTION_DATE_1 IN DATE) RETURN CHAR IS
251 B_ED NUMBER;
252 A_ED NUMBER;
253 O_ED NUMBER;
254 S_INV_ID VARCHAR2(30);
255 LN_ADD_CVD JAI_CMN_RG_23AC_I_TRXS.ADDITIONAL_CVD%TYPE := 0;
256 BEGIN
257 BEGIN
258 SELECT
259 NVL(BASIC_ED
260 ,0),
261 NVL(ADDITIONAL_ED
262 ,0),
263 NVL(OTHER_ED
264 ,0),
265 NVL(ADDITIONAL_CVD
266 ,0)
267 INTO B_ED,A_ED,O_ED,LN_ADD_CVD
268 FROM
269 JAI_CMN_RG_23AC_I_TRXS
270 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
271 AND LOCATION_ID = P_LOCATION_ID
272 AND SLNO = SLNO_1
273 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
274 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
275 EXCEPTION
276 WHEN OTHERS THEN
277 B_ED := 0;
278 A_ED := 0;
279 O_ED := 0;
280 LN_ADD_CVD := 0;
281 END;
282 IF B_ED + A_ED + O_ED + LN_ADD_CVD > 0 THEN
283 SELECT
284 SALES_INVOICE_NO
285 INTO S_INV_ID
286 FROM
287 JAI_CMN_RG_23AC_I_TRXS
288 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
289 AND LOCATION_ID = P_LOCATION_ID
290 AND SLNO = SLNO_1
291 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
292 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
293 ELSE
294 S_INV_ID := '';
295 END IF;
296 RETURN (S_INV_ID);
297 END CF_SALES_INV_IDFORMULA;
298 FUNCTION CF_SALES_INV_DTFORMULA(SLNO_1 IN NUMBER
299 ,INVENTORY_ITEM_ID_1 IN NUMBER
300 ,TRANSACTION_DATE_1 IN DATE) RETURN DATE IS
301 B_ED NUMBER;
302 A_ED NUMBER;
303 O_ED NUMBER;
304 S_INV_DT DATE;
305 LN_ADD_CVD JAI_CMN_RG_23AC_I_TRXS.ADDITIONAL_CVD%TYPE := 0;
306 BEGIN
307 BEGIN
308 SELECT
309 NVL(BASIC_ED
310 ,0),
311 NVL(ADDITIONAL_ED
312 ,0),
313 NVL(OTHER_ED
314 ,0),
315 NVL(ADDITIONAL_CVD
316 ,0)
317 INTO B_ED,A_ED,O_ED,LN_ADD_CVD
318 FROM
319 JAI_CMN_RG_23AC_I_TRXS
320 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
321 AND LOCATION_ID = P_LOCATION_ID
322 AND SLNO = SLNO_1
323 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
324 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
325 EXCEPTION
326 WHEN OTHERS THEN
327 B_ED := 0;
328 A_ED := 0;
329 O_ED := 0;
330 LN_ADD_CVD := 0;
331 END;
332 IF (B_ED + A_ED + O_ED + LN_ADD_CVD) > 0 THEN
333 SELECT
334 SALES_INVOICE_DATE
335 INTO S_INV_DT
336 FROM
337 JAI_CMN_RG_23AC_I_TRXS
338 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
339 AND LOCATION_ID = P_LOCATION_ID
340 AND SLNO = SLNO_1
341 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
342 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
343 ELSE
344 S_INV_DT := '';
345 END IF;
346 RETURN (S_INV_DT);
347 END CF_SALES_INV_DTFORMULA;
348 FUNCTION CF_SALES_INV_QTYFORMULA(SLNO_1 IN NUMBER
349 ,INVENTORY_ITEM_ID_1 IN NUMBER
350 ,TRANSACTION_DATE_1 IN DATE
351 ,TRANSACTION_TYPE IN VARCHAR2) RETURN NUMBER IS
352 B_ED NUMBER;
353 A_ED NUMBER;
354 O_ED NUMBER;
355 S_INV_QTY NUMBER;
356 LN_ADD_CVD JAI_CMN_RG_23AC_I_TRXS.ADDITIONAL_CVD%TYPE := 0;
357 CURSOR C_FETCH_QTY_FOR_RTV IS
358 SELECT
359 ABS(QUANTITY_RECEIVED)
360 FROM
361 JAI_CMN_RG_23AC_I_TRXS
362 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
363 AND LOCATION_ID = P_LOCATION_ID
364 AND SLNO = SLNO_1
365 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
366 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
367 BEGIN
368 BEGIN
369 SELECT
370 NVL(BASIC_ED
371 ,0),
372 NVL(ADDITIONAL_ED
373 ,0),
374 NVL(OTHER_ED
375 ,0),
376 NVL(ADDITIONAL_CVD
377 ,0)
378 INTO B_ED,A_ED,O_ED,LN_ADD_CVD
379 FROM
380 JAI_CMN_RG_23AC_I_TRXS
381 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
382 AND LOCATION_ID = P_LOCATION_ID
383 AND SLNO = SLNO_1
384 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
385 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
386 EXCEPTION
387 WHEN OTHERS THEN
388 B_ED := 0;
389 A_ED := 0;
390 O_ED := 0;
391 LN_ADD_CVD := 0;
392 END;
393 IF B_ED + A_ED + O_ED + LN_ADD_CVD > 0 THEN
394 SELECT
395 SALES_INVOICE_QUANTITY
396 INTO S_INV_QTY
397 FROM
398 JAI_CMN_RG_23AC_I_TRXS
399 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
400 AND LOCATION_ID = P_LOCATION_ID
401 AND SLNO = SLNO_1
402 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1
403 AND TRUNC(TRANSACTION_DATE) = TRUNC(TRANSACTION_DATE_1);
404 ELSE
405 S_INV_QTY := '';
406 END IF;
407 IF TRANSACTION_TYPE = 'RTV' THEN
408 OPEN C_FETCH_QTY_FOR_RTV;
409 FETCH C_FETCH_QTY_FOR_RTV
410 INTO S_INV_QTY;
411 CLOSE C_FETCH_QTY_FOR_RTV;
412 END IF;
413 RETURN (S_INV_QTY);
414 END CF_SALES_INV_QTYFORMULA;
415 FUNCTION CF_FOLIO_A2FORMULA(A2FOLIONO2 IN VARCHAR2) RETURN CHAR IS
416 BEGIN
417 IF A2FOLIONO2 = '/' THEN
418 RETURN (NULL);
419 ELSE
420 RETURN (A2FOLIONO2);
421 END IF;
422 END CF_FOLIO_A2FORMULA;
423 FUNCTION CF_ITEM_TARRIFFORMULA(INVENTORY_ITEM_ID_1 IN NUMBER) RETURN CHAR IS
424 I_TARRIF VARCHAR2(50);
425 BEGIN
426 BEGIN
427 SELECT
428 ITEM_TARIFF
429 INTO I_TARRIF
430 FROM
431 JAI_INV_ITM_SETUPS
432 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
433 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1;
434 EXCEPTION
435 WHEN OTHERS THEN
436 I_TARRIF := '';
437 END;
438 RETURN (I_TARRIF);
439 END CF_ITEM_TARRIFFORMULA;
440 FUNCTION CF_ITEM_FOLIOFORMULA(INVENTORY_ITEM_ID_1 IN NUMBER) RETURN CHAR IS
441 I_FOLIO VARCHAR2(50);
442 BEGIN
443 BEGIN
444 SELECT
445 ITEM_FOLIO
446 INTO I_FOLIO
447 FROM
448 JAI_INV_ITM_SETUPS
449 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
450 AND INVENTORY_ITEM_ID = INVENTORY_ITEM_ID_1;
451 EXCEPTION
452 WHEN OTHERS THEN
453 I_FOLIO := '';
454 END;
455 RETURN (I_FOLIO);
456 END CF_ITEM_FOLIOFORMULA;
457 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
458 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
459 SELECT
460 CONCURRENT_PROGRAM_ID,
461 NVL(ENABLE_TRACE
462 ,'N')
463 FROM
464 FND_CONCURRENT_REQUESTS
465 WHERE REQUEST_ID = P_REQUEST_ID;
466 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
467 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
468 BEGIN
469 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
470 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
471 /*SRW.MESSAGE(1275
472 ,'Report Version is 120.2 Last modified date is 22/11/2006')*/NULL;
473 BEGIN
474 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
475 FETCH C_PROGRAM_ID
476 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
477 CLOSE C_PROGRAM_ID;
478 /*SRW.MESSAGE(1275
479 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
480 IF V_ENABLE_TRACE = 'Y' THEN
481 EXECUTE IMMEDIATE
482 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
483 END IF;
484 EXCEPTION
485 WHEN OTHERS THEN
486 /*SRW.MESSAGE(1275
487 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
488 END;
489 RETURN (TRUE);
490 END BEFOREREPORT;
491 FUNCTION CF_VEND_DTLSFORMULA(VENDOR_ID IN NUMBER
492 ,VENDOR_SITE_ID IN NUMBER
493 ,RECEIPT_ID IN VARCHAR2
494 ,VEND_DTLS IN VARCHAR2) RETURN CHAR IS
495 LV_RCV_TRANSACTION CONSTANT RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE DEFAULT 'RECEIVE';
496 LCV_INT_ORD CONSTANT RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE DEFAULT 'INTERNAL ORDER';
497 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2) IS
498 SELECT
499 SUBSTR(HRU.NAME
500 ,1
501 ,20) || ' ' || SUBSTR(HRL.ADDRESS_LINE_1
502 ,1
503 ,12) || ' ' || SUBSTR(HRL.ADDRESS_LINE_2
504 ,1
505 ,8) || ' ' || SUBSTR(HRL.ADDRESS_LINE_3
506 ,1
507 ,7) || ' ' || SUBSTR(HRL.TOWN_OR_CITY
508 ,1
509 ,15) || ' ' || SUBSTR(HRL.POSTAL_CODE
510 ,1
511 ,10) || ' ' || SUBSTR(HRL.COUNTRY
512 ,1
513 ,10)
514 FROM
515 HR_LOCATIONS HRL,
516 RCV_TRANSACTIONS RCVT,
517 RCV_SHIPMENT_HEADERS RCVSH,
518 JAI_OM_WSH_LINES_ALL JSPL,
519 HR_ALL_ORGANIZATION_UNITS HRU
520 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
521 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION
522 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
523 AND RCVSH.RECEIPT_SOURCE_CODE = LCV_INT_ORD
524 AND RCVSH.SHIPMENT_NUM = JSPL.DELIVERY_ID
525 AND HRL.LOCATION_ID = JSPL.LOCATION_ID
526 AND HRU.ORGANIZATION_ID = RCVSH.ORGANIZATION_ID;
527 CURSOR C_VENDOR_NAME_FOR_ISO IS
528 SELECT
529 SUBSTR(HRU.NAME
530 ,1
531 ,20) || ' ' || SUBSTR(HRL.ADDRESS_LINE_1
532 ,1
533 ,12) || ' ' || SUBSTR(HRL.ADDRESS_LINE_2
534 ,1
535 ,8) || ' ' || SUBSTR(HRL.ADDRESS_LINE_3
536 ,1
537 ,7) || ' ' || SUBSTR(HRL.TOWN_OR_CITY
538 ,1
539 ,15) || ' ' || SUBSTR(HRL.POSTAL_CODE
540 ,1
541 ,10) || ' ' || SUBSTR(HRL.COUNTRY
542 ,1
543 ,10)
544 FROM
545 HR_ALL_ORGANIZATION_UNITS HRU,
546 HR_LOCATIONS HRL
547 WHERE HRU.ORGANIZATION_ID = ABS(VENDOR_ID)
548 AND HRL.LOCATION_ID = ABS(VENDOR_SITE_ID)
549 AND HRU.LOCATION_ID = HRL.LOCATION_ID;
550 V_VENDOR_DTLS VARCHAR2(200);
551 BEGIN
552 IF VENDOR_ID IS NULL THEN
553 OPEN INT_ORDER_CUR(RECEIPT_ID);
554 FETCH INT_ORDER_CUR
555 INTO V_VENDOR_DTLS;
556 CLOSE INT_ORDER_CUR;
557 RETURN V_VENDOR_DTLS;
558 ELSIF VENDOR_ID < 0 THEN
559 OPEN C_VENDOR_NAME_FOR_ISO;
560 FETCH C_VENDOR_NAME_FOR_ISO
561 INTO V_VENDOR_DTLS;
562 CLOSE C_VENDOR_NAME_FOR_ISO;
563 RETURN V_VENDOR_DTLS;
564 ELSE
565 RETURN VEND_DTLS;
566 END IF;
567 EXCEPTION
568 WHEN OTHERS THEN
569 /*SRW.MESSAGE(1275
570 ,'Unable to fetch vendor details in case of an Internal Order' || SQLERRM)*/NULL;
571 RETURN NULL;
572 END CF_VEND_DTLSFORMULA;
573 FUNCTION CF_RANGE1FORMULA(VENDOR_ID IN NUMBER
574 ,VENDOR_SITE_ID IN NUMBER
575 ,RECEIPT_ID IN VARCHAR2
576 ,RANGE1 IN VARCHAR2) RETURN CHAR IS
577 LV_RCV_TRANSACTION CONSTANT RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE DEFAULT 'RECEIVE';
578 LV_INT_ORD CONSTANT RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE DEFAULT 'INTERNAL ORDER';
579 CURSOR C_RANGE_NO_FOR_ISO IS
580 SELECT
581 EXCISE_DUTY_RANGE
582 FROM
583 JAI_CMN_INVENTORY_ORGS
584 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
585 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
586 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2) IS
587 SELECT
588 DISTINCT
589 JU.EXCISE_DUTY_RANGE
590 FROM
591 RCV_TRANSACTIONS RCVT,
592 RCV_SHIPMENT_HEADERS RCVSH,
593 JAI_CMN_INVENTORY_ORGS JU
594 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
595 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION
596 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
597 AND RCVSH.RECEIPT_SOURCE_CODE = LV_INT_ORD
598 AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
599 V_RANGE_NO JAI_CMN_INVENTORY_ORGS.EXCISE_DUTY_RANGE%TYPE;
600 BEGIN
601 IF VENDOR_ID < 0 THEN
602 OPEN C_RANGE_NO_FOR_ISO;
603 FETCH C_RANGE_NO_FOR_ISO
604 INTO V_RANGE_NO;
605 CLOSE C_RANGE_NO_FOR_ISO;
606 RETURN V_RANGE_NO;
607 ELSIF VENDOR_ID IS NULL THEN
608 OPEN INT_ORDER_CUR(RECEIPT_ID);
609 FETCH INT_ORDER_CUR
610 INTO V_RANGE_NO;
611 CLOSE INT_ORDER_CUR;
612 RETURN V_RANGE_NO;
613 ELSE
614 RETURN RANGE1;
615 END IF;
616 END CF_RANGE1FORMULA;
617 FUNCTION CF_DIVISION1FORMULA(VENDOR_ID IN NUMBER
618 ,VENDOR_SITE_ID IN NUMBER
619 ,RECEIPT_ID IN VARCHAR2
620 ,DIVISION1 IN VARCHAR2) RETURN CHAR IS
621 LV_RCV_TRANSACTION CONSTANT RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE DEFAULT 'RECEIVE';
622 LV_INT_ORD CONSTANT RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE%TYPE DEFAULT 'INTERNAL ORDER';
623 CURSOR C_DIVISION_NO_FOR_ISO IS
624 SELECT
625 EXCISE_DUTY_DIVISION
626 FROM
627 JAI_CMN_INVENTORY_ORGS
628 WHERE ORGANIZATION_ID = ABS(VENDOR_ID)
629 AND LOCATION_ID = ABS(VENDOR_SITE_ID);
630 CURSOR INT_ORDER_CUR(P_RECEIPT_ID IN VARCHAR2) IS
631 SELECT
632 DISTINCT
633 JU.EXCISE_DUTY_DIVISION
634 FROM
635 RCV_TRANSACTIONS RCVT,
636 RCV_SHIPMENT_HEADERS RCVSH,
637 JAI_CMN_INVENTORY_ORGS JU
638 WHERE RCVT.TRANSACTION_ID = P_RECEIPT_ID
639 AND RCVT.TRANSACTION_TYPE = LV_RCV_TRANSACTION
640 AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID
641 AND RCVSH.RECEIPT_SOURCE_CODE = LV_INT_ORD
642 AND RCVSH.ORGANIZATION_ID = JU.ORGANIZATION_ID;
643 V_DIVISION_NO JAI_CMN_INVENTORY_ORGS.EXCISE_DUTY_DIVISION%TYPE;
644 BEGIN
645 IF VENDOR_ID < 0 THEN
646 OPEN C_DIVISION_NO_FOR_ISO;
647 FETCH C_DIVISION_NO_FOR_ISO
648 INTO V_DIVISION_NO;
649 CLOSE C_DIVISION_NO_FOR_ISO;
650 RETURN V_DIVISION_NO;
651 ELSIF VENDOR_ID IS NULL THEN
652 OPEN INT_ORDER_CUR(RECEIPT_ID);
653 FETCH INT_ORDER_CUR
654 INTO V_DIVISION_NO;
655 CLOSE INT_ORDER_CUR;
656 RETURN V_DIVISION_NO;
657 ELSE
658 RETURN DIVISION1;
659 END IF;
660 END CF_DIVISION1FORMULA;
661 FUNCTION CP_PAGE_NO_P RETURN NUMBER IS
662 BEGIN
663 RETURN CP_PAGE_NO;
664 END CP_PAGE_NO_P;
665 FUNCTION CP_PAGE_P RETURN NUMBER IS
666 BEGIN
667 RETURN CP_PAGE;
668 END CP_PAGE_P;
669 END JA_JAIN23C1_XMLP_PKG;
670
671