[Home] [Help]
PACKAGE BODY: APPS.JA_JAIN23D_XMLP_PKG
Source
1 PACKAGE BODY JA_JAIN23D_XMLP_PKG AS
2 /* $Header: JAIN23DB.pls 120.1 2007/12/25 16:07:34 dwkrishn noship $ */
3 FUNCTION CF_COL9FORMULA(REGISTER_ID IN NUMBER
4 ,TRANSACTION_TYPE IN VARCHAR2
5 ,COL3 IN VARCHAR2
6 ,QUERY IN VARCHAR2) RETURN VARCHAR2 IS
7 CURSOR C_CUST_ADDRESS(P_CUSTOMER_ID IN NUMBER,P_ADDRESS_ID IN NUMBER) IS
8 SELECT
9 SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' '
10 || F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
11 ,1
12 ,255)
13 FROM
14 HZ_PARTIES E,
15 HZ_CUST_ACCOUNTS HZCA,
16 JAI_CMN_CUS_ADDRESSES F,
17 HZ_LOCATIONS G,
18 HZ_PARTY_SITES HZPS,
19 HZ_CUST_ACCT_SITES_ALL HZCAS,
20 HZ_CUST_SITE_USES_ALL H
21 WHERE E.PARTY_ID = HZCA.PARTY_ID
22 AND HZCA.CUST_ACCOUNT_ID = F.CUSTOMER_ID
23 AND F.ADDRESS_ID = H.CUST_ACCT_SITE_ID
24 AND G.LOCATION_ID = HZPS.LOCATION_ID
25 AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
26 AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
27 AND F.CUSTOMER_ID = P_CUSTOMER_ID
28 AND H.CUST_ACCT_SITE_ID = P_ADDRESS_ID;
29 CURSOR C_CUSTOMER_NAME(P_CUSTOMER_ID IN NUMBER) IS
30 SELECT
31 PARTY_NAME CUSTOMER_NAME
32 FROM
33 HZ_PARTIES HZP,
34 HZ_CUST_ACCOUNTS HZCA
35 WHERE HZP.PARTY_ID = HZCA.PARTY_ID
36 AND HZCA.CUST_ACCOUNT_ID = P_CUSTOMER_ID;
37 CURSOR C_VEND_ADDRESS(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
38 SELECT
39 SUBSTR(B.VENDOR_NAME || ' ' || C.ADDRESS_LINE1 || ' ' || ADDRESS_LINE2 || ' ' || ADDRESS_LINE3 || ' ' || D.EXCISE_DUTY_RANGE || ' ' || D.EXCISE_DUTY_DIVISION || ' ' || D.EXCISE_DUTY_COMM
40 ,1
41 ,255)
42 FROM
43 JAI_CMN_RG_23D_TRXS A,
44 PO_VENDORS B,
45 PO_VENDOR_SITES_ALL C,
46 JAI_CMN_VENDOR_SITES D
47 WHERE A.VENDOR_ID = B.VENDOR_ID
48 AND A.VENDOR_ID = C.VENDOR_ID
49 AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
50 AND A.VENDOR_ID = D.VENDOR_ID
51 AND A.VENDOR_SITE_ID = D.VENDOR_SITE_ID
52 AND C.VENDOR_SITE_ID = D.VENDOR_SITE_ID
53 AND A.VENDOR_ID = P_VENDOR_ID
54 AND A.VENDOR_SITE_ID = P_VENDOR_SITE_ID;
55 CURSOR C_ORG_ADDRESS(P_CUSTOMER_ID IN NUMBER,P_SHIP_TO_SITE_ID IN NUMBER) IS
56 SELECT
57 SUBSTR(A.NAME || ' ' || ((SUBSTR(ADDRESS_LINE_1
58 ,1
59 ,30)) || (SUBSTR(ADDRESS_LINE_2
60 ,1
61 ,20)) || (SUBSTR(ADDRESS_LINE_3
62 ,1
63 ,10)))
64 ,1
65 ,255)
66 FROM
67 HR_ALL_ORGANIZATION_UNITS A,
68 HR_LOCATIONS B
69 WHERE A.ORGANIZATION_ID = B.INVENTORY_ORGANIZATION_ID
70 AND A.ORGANIZATION_ID = ABS(P_CUSTOMER_ID)
71 AND B.LOCATION_ID = ABS(P_SHIP_TO_SITE_ID);
72 CURSOR C_RCV_DTLS(P_REGISTER_ID IN NUMBER) IS
73 SELECT
74 RCV.SHIPMENT_LINE_ID,
75 RCV.CUSTOMER_ID,
76 RCV.CUSTOMER_SITE_ID
77 FROM
78 RCV_TRANSACTIONS RCV,
79 JAI_CMN_RG_23D_TRXS D
80 WHERE RCV.TRANSACTION_ID = D.RECEIPT_REF
81 AND D.REGISTER_ID = P_REGISTER_ID;
82 CURSOR C_DELIVER_DTLS(P_SHIPMENT_LINE_ID IN NUMBER,CP_TRANSACTION_TYPE IN RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE) IS
83 SELECT
84 CUSTOMER_ID,
85 CUSTOMER_SITE_ID
86 FROM
87 RCV_TRANSACTIONS
88 WHERE SHIPMENT_LINE_ID = P_SHIPMENT_LINE_ID
89 AND TRANSACTION_TYPE = CP_TRANSACTION_TYPE;
90 CURSOR C_RMA_CUST_ADDRESS(P_CUSTOMER_ID IN NUMBER,P_CUSTOMER_SITE_ID IN NUMBER) IS
91 SELECT
92 SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' ' ||
93 F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
94 ,1
95 ,255)
96 FROM
97 HZ_PARTIES E,
98 HZ_CUST_ACCOUNTS HZCA,
99 JAI_CMN_CUS_ADDRESSES F,
100 HZ_LOCATIONS G,
101 HZ_PARTY_SITES HZPS,
102 HZ_CUST_ACCT_SITES_ALL HZCAS,
103 HZ_CUST_SITE_USES_ALL H
104 WHERE HZCA.CUST_ACCOUNT_ID = P_CUSTOMER_ID
105 AND E.PARTY_ID = HZCA.PARTY_ID
106 AND H.SITE_USE_ID = P_CUSTOMER_SITE_ID
107 AND F.CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID
108 AND F.ADDRESS_ID = H.CUST_ACCT_SITE_ID
109 AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
110 AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
111 AND G.LOCATION_ID = HZPS.LOCATION_ID;
112 CURSOR C_CUST_VEND_IDS(P_REGISTER_ID IN NUMBER) IS
113 SELECT
114 NVL(CUSTOMER_ID
115 ,0),
116 NVL(SHIP_TO_SITE_ID
117 ,0),
118 NVL(VENDOR_ID
119 ,0),
120 NVL(VENDOR_SITE_ID
121 ,0)
122 FROM
123 JAI_CMN_RG_23D_TRXS
124 WHERE REGISTER_ID = P_REGISTER_ID;
125 V_SHIPMENT_LINE_ID NUMBER;
126 V_CUST_ID NUMBER;
127 V_CUST_SITE_ID NUMBER;
128 V_CUSTOMER_ID NUMBER;
129 V_ADDRESS_ID NUMBER;
130 V_VENDOR_ID NUMBER;
131 V_VENDOR_SITE_ID NUMBER;
132 V_ADDRESS VARCHAR2(255);
133 BEGIN
134 OPEN C_CUST_VEND_IDS(REGISTER_ID);
135 FETCH C_CUST_VEND_IDS
136 INTO V_CUSTOMER_ID,V_ADDRESS_ID,V_VENDOR_ID,V_VENDOR_SITE_ID;
137 CLOSE C_CUST_VEND_IDS;
138 IF TRANSACTION_TYPE in ('I','MI','MRTV','RTV') THEN
139 IF (V_CUSTOMER_ID < 0) THEN
140 OPEN C_ORG_ADDRESS(V_CUSTOMER_ID,V_ADDRESS_ID);
141 FETCH C_ORG_ADDRESS
142 INTO V_ADDRESS;
143 CLOSE C_ORG_ADDRESS;
144 RETURN (V_ADDRESS);
145 END IF;
146 IF TRANSACTION_TYPE in ('MRTV','RTV') THEN
147 OPEN C_VEND_ADDRESS(V_VENDOR_ID,V_VENDOR_SITE_ID);
148 FETCH C_VEND_ADDRESS
149 INTO V_ADDRESS;
150 CLOSE C_VEND_ADDRESS;
151 RETURN (V_ADDRESS);
152 ELSIF (TRANSACTION_TYPE in ('I','MI') AND COL3 IS NULL) THEN
153 /*SRW.MESSAGE(1271
154 ,'cust_id->' || V_CUSTOMER_ID || ', addr_id->' || V_ADDRESS_ID)*/NULL;
155 IF V_CUSTOMER_ID > 0 AND V_ADDRESS_ID > 0 THEN
156 OPEN C_CUST_ADDRESS(V_CUSTOMER_ID,V_ADDRESS_ID);
157 FETCH C_CUST_ADDRESS
158 INTO V_ADDRESS;
159 CLOSE C_CUST_ADDRESS;
160 ELSIF V_CUSTOMER_ID > 0 THEN
161 OPEN C_CUSTOMER_NAME(V_CUSTOMER_ID);
162 FETCH C_CUSTOMER_NAME
163 INTO V_ADDRESS;
164 CLOSE C_CUSTOMER_NAME;
165 ELSE
166 V_ADDRESS := '';
167 END IF;
168 RETURN (V_ADDRESS);
169 ELSE
170 RETURN (COL3);
171 END IF;
172 ELSIF QUERY = '4' AND TRANSACTION_TYPE = 'R' THEN
173 OPEN C_RCV_DTLS(REGISTER_ID);
174 FETCH C_RCV_DTLS
175 INTO V_SHIPMENT_LINE_ID,V_CUST_ID,V_CUST_SITE_ID;
176 CLOSE C_RCV_DTLS;
177 IF V_CUST_SITE_ID IS NULL THEN
178 V_CUST_ID := NULL;
179 OPEN C_DELIVER_DTLS(V_SHIPMENT_LINE_ID,'DELIVER');
180 FETCH C_DELIVER_DTLS
181 INTO V_CUST_ID,V_CUST_SITE_ID;
182 CLOSE C_DELIVER_DTLS;
183 END IF;
184 OPEN C_RMA_CUST_ADDRESS(V_CUST_ID,V_CUST_SITE_ID);
185 FETCH C_RMA_CUST_ADDRESS
186 INTO V_ADDRESS;
187 CLOSE C_RMA_CUST_ADDRESS;
188 RETURN (V_ADDRESS);
189 ELSE
190 RETURN (NULL);
191 END IF;
192 RETURN NULL;
193 END CF_COL9FORMULA;
194
195 FUNCTION CF_COL10FORMULA(REGISTER_ID IN NUMBER
196 ,TRANSACTION_TYPE IN VARCHAR2
197 ,COL3 IN VARCHAR2) RETURN VARCHAR2 IS
198 CURSOR C_CUST_ADDRESS(P_CUSTOMER_ID IN NUMBER,P_ADDRESS_ID IN NUMBER) IS
199 SELECT
200 SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' ' ||
201 F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
202 ,1
203 ,255)
204 FROM
205 HZ_PARTIES E,
206 HZ_CUST_ACCOUNTS HZCA,
207 JAI_CMN_CUS_ADDRESSES F,
208 HZ_LOCATIONS G,
209 HZ_PARTY_SITES HZPS,
210 HZ_CUST_ACCT_SITES_ALL HZCAS,
211 HZ_CUST_SITE_USES_ALL H
212 WHERE E.PARTY_ID = HZCA.PARTY_ID
213 AND HZCA.CUST_ACCOUNT_ID = F.CUSTOMER_ID
214 AND F.ADDRESS_ID = HZCAS.CUST_ACCT_SITE_ID
215 AND G.LOCATION_ID = HZPS.PARTY_SITE_ID
216 AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
217 AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
218 AND F.CUSTOMER_ID = P_CUSTOMER_ID
219 AND H.CUST_ACCT_SITE_ID = P_ADDRESS_ID;
220 CURSOR C_VEND_ADDRESS(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
221 SELECT
222 SUBSTR(B.VENDOR_NAME || ' ' || C.ADDRESS_LINE1 || ' ' || ADDRESS_LINE2 || ' ' || ADDRESS_LINE3 || ' ' || D.EXCISE_DUTY_RANGE || ' ' || D.EXCISE_DUTY_DIVISION || ' ' || D.EXCISE_DUTY_COMM
223 ,1
224 ,255)
225 FROM
226 JAI_CMN_RG_23D_TRXS A,
227 PO_VENDORS B,
228 PO_VENDOR_SITES_ALL C,
229 JAI_CMN_VENDOR_SITES D
230 WHERE A.VENDOR_ID = B.VENDOR_ID
231 AND A.VENDOR_ID = C.VENDOR_ID
232 AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
233 AND A.VENDOR_ID = D.VENDOR_ID
234 AND A.VENDOR_SITE_ID = D.VENDOR_SITE_ID
235 AND C.VENDOR_SITE_ID = D.VENDOR_SITE_ID
236 AND A.VENDOR_ID = P_VENDOR_ID
237 AND A.VENDOR_SITE_ID = P_VENDOR_SITE_ID;
238 CURSOR C_ORG_ADDRESS(P_CUSTOMER_ID IN NUMBER,P_SHIP_TO_SITE_ID IN NUMBER) IS
239 SELECT
240 SUBSTR(A.NAME || ' ' || ((SUBSTR(ADDRESS_LINE_1
241 ,1
242 ,30)) || (SUBSTR(ADDRESS_LINE_2
243 ,1
244 ,20)) || (SUBSTR(ADDRESS_LINE_3
245 ,1
246 ,10)))
247 ,1
248 ,255)
249 FROM
250 HR_ALL_ORGANIZATION_UNITS A,
251 HR_LOCATIONS B
252 WHERE A.ORGANIZATION_ID = B.INVENTORY_ORGANIZATION_ID
253 AND A.ORGANIZATION_ID = ABS(P_CUSTOMER_ID)
254 AND B.LOCATION_ID = ABS(P_SHIP_TO_SITE_ID);
255 CURSOR C_CUST_VEND_IDS(P_REGISTER_ID IN NUMBER) IS
256 SELECT
257 NVL(CUSTOMER_ID
258 ,0),
259 NVL(SHIP_TO_SITE_ID
260 ,0),
261 NVL(VENDOR_ID
262 ,0),
263 NVL(VENDOR_SITE_ID
264 ,0)
265 FROM
266 JAI_CMN_RG_23D_TRXS
267 WHERE REGISTER_ID = P_REGISTER_ID;
268 V_CUSTOMER_ID NUMBER;
269 V_ADDRESS_ID NUMBER;
270 V_VENDOR_ID NUMBER;
271 V_VENDOR_SITE_ID NUMBER;
272 V_ADDRESS VARCHAR2(255);
273 BEGIN
274 OPEN C_CUST_VEND_IDS(REGISTER_ID);
275 FETCH C_CUST_VEND_IDS
276 INTO V_CUSTOMER_ID,V_ADDRESS_ID,V_VENDOR_ID,V_VENDOR_SITE_ID;
277 CLOSE C_CUST_VEND_IDS;
278 IF TRANSACTION_TYPE in ('I','MI','MRTV','RTV') THEN
279 IF (V_CUSTOMER_ID < 0) THEN
280 OPEN C_ORG_ADDRESS(V_CUSTOMER_ID,V_ADDRESS_ID);
281 FETCH C_ORG_ADDRESS
282 INTO V_ADDRESS;
283 CLOSE C_ORG_ADDRESS;
284 RETURN (V_ADDRESS);
285 END IF;
286 IF TRANSACTION_TYPE in ('RTV','MRTV') THEN
287 OPEN C_VEND_ADDRESS(V_VENDOR_ID,V_VENDOR_SITE_ID);
288 FETCH C_VEND_ADDRESS
289 INTO V_ADDRESS;
290 CLOSE C_VEND_ADDRESS;
291 RETURN (V_ADDRESS);
292 ELSIF (TRANSACTION_TYPE in ('I','MI') AND COL3 IS NULL) THEN
293 OPEN C_CUST_ADDRESS(V_CUSTOMER_ID,V_ADDRESS_ID);
294 FETCH C_CUST_ADDRESS
295 INTO V_ADDRESS;
296 CLOSE C_CUST_ADDRESS;
297 RETURN (V_ADDRESS);
298 ELSE
299 RETURN (COL3);
300 END IF;
301 ELSE
302 RETURN (NULL);
303 END IF;
304 RETURN NULL;
305 END CF_COL10FORMULA;
306
307 FUNCTION CF_SOB_NAMEFORMULA(ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
308 CURSOR FOR_SOB_ID(INV_ORG_ID IN NUMBER) IS
309 SELECT
310 SET_OF_BOOKS_ID
311 FROM
312 ORG_ORGANIZATION_DEFINITIONS
313 WHERE ORGANIZATION_ID = INV_ORG_ID;
314 CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
315 SELECT
316 NAME
317 FROM
318 GL_SETS_OF_BOOKS
319 WHERE SET_OF_BOOKS_ID = SOB_ID;
320 V_NAME VARCHAR2(30);
321 V_SOB_ID NUMBER;
322 BEGIN
323 OPEN FOR_SOB_ID(ORGANIZATION_ID);
324 FETCH FOR_SOB_ID
325 INTO V_SOB_ID;
326 CLOSE FOR_SOB_ID;
330 CLOSE FOR_SOB_NAME;
327 OPEN FOR_SOB_NAME(V_SOB_ID);
328 FETCH FOR_SOB_NAME
329 INTO V_NAME;
331 RETURN (V_NAME);
332 END CF_SOB_NAMEFORMULA;
333
334 FUNCTION CF_P_NAMEFORMULA RETURN VARCHAR2 IS
335 CURSOR FOR_NAME(ORG_ID IN NUMBER) IS
336 SELECT
337 NAME
338 FROM
339 HR_ALL_ORGANIZATION_UNITS
340 WHERE ORGANIZATION_ID = ORG_ID;
341 V_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
342 BEGIN
343 OPEN FOR_NAME(P_ORGANIZATION_ID);
344 FETCH FOR_NAME
345 INTO V_NAME;
346 CLOSE FOR_NAME;
347 RETURN (V_NAME);
348 END CF_P_NAMEFORMULA;
349
350 FUNCTION CF_P_LOCFORMULA RETURN VARCHAR2 IS
351 CURSOR FOR_LOC(LOC_ID IN NUMBER) IS
352 SELECT
353 DESCRIPTION
354 FROM
355 HR_LOCATIONS
356 WHERE LOCATION_ID = LOC_ID;
357 V_DESCRIPTION VARCHAR2(100);
358 BEGIN
359 OPEN FOR_LOC(P_LOCATION_ID);
360 FETCH FOR_LOC
361 INTO V_DESCRIPTION;
362 CLOSE FOR_LOC;
363 RETURN (V_DESCRIPTION);
364 END CF_P_LOCFORMULA;
365
366 FUNCTION CF_P_ITEMFORMULA RETURN VARCHAR2 IS
367 CURSOR FOR_ITEM(ID IN NUMBER) IS
368 SELECT
369 CONCATENATED_SEGMENTS
370 FROM
371 MTL_SYSTEM_ITEMS_KFV
372 WHERE INVENTORY_ITEM_ID = ID;
373 V_ITEM VARCHAR2(100);
374 BEGIN
375 OPEN FOR_ITEM(P_INVENTORY_ITEM_ID);
376 FETCH FOR_ITEM
377 INTO V_ITEM;
378 CLOSE FOR_ITEM;
379 IF P_INVENTORY_ITEM_ID IS NOT NULL AND V_ITEM IS NULL THEN
380 V_ITEM := 'Invalid Item Specified';
381 END IF;
382 RETURN (V_ITEM);
383 END CF_P_ITEMFORMULA;
384
385 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
386 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
387 SELECT
388 CONCURRENT_PROGRAM_ID,
389 NVL(ENABLE_TRACE
390 ,'N')
391 FROM
392 FND_CONCURRENT_REQUESTS
393 WHERE REQUEST_ID = P_REQUEST_ID;
394 CURSOR GET_AUDSID IS
395 SELECT
396 A.SID,
397 A.SERIAL#,
398 B.SPID
399 FROM
400 V$SESSION A,
401 V$PROCESS B
402 WHERE AUDSID = USERENV('SESSIONID')
403 AND A.PADDR = B.ADDR;
404 CURSOR GET_DBNAME IS
405 SELECT
406 NVL(NAME
407 ,'')
408 FROM
409 V$DATABASE;
410 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
411 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
412 V_SID V$SESSION.SID%TYPE;
413 V_SERIAL V$SESSION.SERIAL#%TYPE;
414 V_SPID V$PROCESS.SPID%TYPE;
415 V_NAME V$DATABASE.NAME%TYPE;
416 BEGIN
417 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
418 CP_TRN_FROM_DATE := TO_CHAR(P_TRN_FROM_DATE,'DD-MON-YYYY');
419 CP_TRN_TO_DATE := TO_CHAR(P_TRN_TO_DATE,'DD-MON-YYYY');
420 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
421 /*SRW.MESSAGE(1275
422 ,'Report Version is 120.3 Last modified date is 21/07/2006')*/NULL;
423 BEGIN
424 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
425 FETCH C_PROGRAM_ID
426 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
427 CLOSE C_PROGRAM_ID;
428 /*SRW.MESSAGE(1275
429 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
430 IF V_ENABLE_TRACE = 'Y' THEN
431 OPEN GET_AUDSID;
432 FETCH GET_AUDSID
433 INTO V_SID,V_SERIAL,V_SPID;
434 CLOSE GET_AUDSID;
435 OPEN GET_DBNAME;
436 FETCH GET_DBNAME
437 INTO V_NAME;
438 CLOSE GET_DBNAME;
439 /*SRW.MESSAGE(1275
440 ,'TraceFile Name = ' || LOWER(V_NAME) || '_ora_' || V_SPID || '.trc')*/NULL;
441 EXECUTE IMMEDIATE
442 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
443 END IF;
444 RETURN (TRUE);
445 EXCEPTION
446 WHEN OTHERS THEN
447 /*SRW.MESSAGE(1275
448 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
449 END;
450 END BEFOREREPORT;
451
452 FUNCTION CF_2FORMULA(TRANSACTION_TYPE IN VARCHAR2
453 ,REGISTER_ID IN NUMBER
454 ,RECEIPT_REMAINING_QTY IN NUMBER
455 ,QUERY IN VARCHAR2
456 ,COL3 IN VARCHAR2) RETURN CHAR IS
457 CURSOR C_CUST_ADDRESS(P_CUSTOMER_ID IN NUMBER,P_ADDRESS_ID IN NUMBER) IS
458 SELECT
459 SUBSTR(E.PARTY_NAME || ' ' || G.ADDRESS1 || ' ' || G.ADDRESS2 || ' ' || G.ADDRESS3 || ' ' || G.ADDRESS4 || ' ' || G.CITY || ' ' || G.PROVINCE || ' ' || G.COUNTRY || ' ' || F.EXCISE_DUTY_RANGE || ' ' ||
460 F.EXCISE_DUTY_DIVISION || ' ' || F.EXCISE_DUTY_COMM
461 ,1
462 ,255)
463 FROM
464 HZ_PARTIES E,
465 HZ_CUST_ACCOUNTS HZCA,
466 JAI_CMN_CUS_ADDRESSES F,
467 HZ_LOCATIONS G,
468 HZ_PARTY_SITES HZPS,
469 HZ_CUST_ACCT_SITES_ALL HZCAS,
470 HZ_CUST_SITE_USES_ALL H
471 WHERE E.PARTY_ID = HZCA.CUST_ACCOUNT_ID
472 AND HZCA.CUST_ACCOUNT_ID = F.CUSTOMER_ID
473 AND F.ADDRESS_ID = HZCAS.CUST_ACCT_SITE_ID
474 AND HZCAS.CUST_ACCT_SITE_ID = H.CUST_ACCT_SITE_ID
475 AND HZPS.PARTY_SITE_ID = HZCAS.PARTY_SITE_ID
476 AND G.LOCATION_ID = HZPS.LOCATION_ID
477 AND F.CUSTOMER_ID = P_CUSTOMER_ID
478 AND H.CUST_ACCT_SITE_ID = P_ADDRESS_ID;
479 CURSOR C_ORG_ADDRESS(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
480 SELECT
484 ,1
481 SUBSTR(A.NAME || ' ' || ((SUBSTR(ADDRESS_LINE_1
482 ,1
483 ,30)) || (SUBSTR(ADDRESS_LINE_2
485 ,20)) || (SUBSTR(ADDRESS_LINE_3
486 ,1
487 ,10)))
488 ,1
489 ,255)
490 FROM
491 HR_ALL_ORGANIZATION_UNITS A,
492 HR_LOCATIONS B
493 WHERE A.ORGANIZATION_ID = B.INVENTORY_ORGANIZATION_ID
494 AND A.ORGANIZATION_ID = ABS(P_VENDOR_ID)
495 AND B.LOCATION_ID = ABS(P_VENDOR_SITE_ID);
496 CURSOR C_VEND_ADDRESS(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
497 SELECT
498 SUBSTR(B.VENDOR_NAME || ' ' || C.ADDRESS_LINE1 || ' ' || ADDRESS_LINE2 || ' ' || ADDRESS_LINE3 || ' ' || D.EXCISE_DUTY_RANGE || ' ' || D.EXCISE_DUTY_DIVISION || ' ' || D.EXCISE_DUTY_COMM
499 ,1
500 ,255),
501 D.VENDOR_TYPE
502 FROM
503 JAI_CMN_RG_23D_TRXS A,
504 PO_VENDORS B,
505 PO_VENDOR_SITES_ALL C,
506 JAI_CMN_VENDOR_SITES D
507 WHERE A.VENDOR_ID = B.VENDOR_ID
508 AND A.VENDOR_ID = C.VENDOR_ID
509 AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
510 AND A.VENDOR_ID = D.VENDOR_ID
511 AND A.VENDOR_SITE_ID = D.VENDOR_SITE_ID
512 AND C.VENDOR_SITE_ID = D.VENDOR_SITE_ID
513 AND A.VENDOR_ID = P_VENDOR_ID
514 AND A.VENDOR_SITE_ID = P_VENDOR_SITE_ID;
515 CURSOR C_CUST_VEND_IDS(P_REGISTER_ID IN NUMBER) IS
516 SELECT
517 NVL(CUSTOMER_ID
518 ,0),
519 NVL(SHIP_TO_SITE_ID
520 ,0),
521 NVL(VENDOR_ID
522 ,0),
523 NVL(VENDOR_SITE_ID
524 ,0)
525 FROM
526 JAI_CMN_RG_23D_TRXS
527 WHERE REGISTER_ID = P_REGISTER_ID;
528 V_SUPPLIER_TYPE VARCHAR2(100) := 'NONE';
529 V_ENTERED_INTO_LOOP VARCHAR2(5) := 'NO';
530 CURSOR C_ORG_SUPPLIER_TYPE(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
531 SELECT
532 DECODE(TRADING
533 ,'N'
534 ,'Manufacturer'
535 ,'Dealer')
536 FROM
537 JAI_CMN_INVENTORY_ORGS
538 WHERE ORGANIZATION_ID = ABS(P_VENDOR_ID)
539 AND LOCATION_ID = ABS(P_VENDOR_SITE_ID);
540 V_CUSTOMER_ID NUMBER;
541 V_ADDRESS_ID NUMBER;
542 V_VENDOR_ID NUMBER;
543 V_VENDOR_SITE_ID NUMBER;
544 V_ADDRESS VARCHAR2(255);
545 BEGIN
546 /*SRW.MESSAGE(1274
547 ,'tr_type -> ' || TRANSACTION_TYPE || ', register_id -> ' || REGISTER_ID || ', qty_rem -> ' || RECEIPT_REMAINING_QTY || ', qry_type -> ' || QUERY)*/NULL;
548 IF TRANSACTION_TYPE in ('I','MRTV','MI','RTV') THEN
549 CP_SUPPLIER_TYPE := NULL;
550 RETURN (' ');
551 END IF;
552 OPEN C_CUST_VEND_IDS(cf_2formula.REGISTER_ID);
553 FETCH C_CUST_VEND_IDS
554 INTO V_CUSTOMER_ID,V_ADDRESS_ID,V_VENDOR_ID,V_VENDOR_SITE_ID;
555 CLOSE C_CUST_VEND_IDS;
556 IF (V_VENDOR_ID < 0) THEN
557 OPEN C_ORG_ADDRESS(V_VENDOR_ID,V_VENDOR_SITE_ID);
558 FETCH C_ORG_ADDRESS
559 INTO V_ADDRESS;
560 CLOSE C_ORG_ADDRESS;
561 OPEN C_ORG_SUPPLIER_TYPE(V_VENDOR_ID,V_VENDOR_SITE_ID);
562 FETCH C_ORG_SUPPLIER_TYPE
563 INTO V_SUPPLIER_TYPE;
564 CLOSE C_ORG_SUPPLIER_TYPE;
565 V_ENTERED_INTO_LOOP := 'YES';
566 END IF;
567 IF TRANSACTION_TYPE = 'MCR' THEN
568 OPEN C_CUST_ADDRESS(V_CUSTOMER_ID,V_ADDRESS_ID);
569 FETCH C_CUST_ADDRESS
570 INTO V_ADDRESS;
571 CLOSE C_CUST_ADDRESS;
572 V_ENTERED_INTO_LOOP := 'YES';
573 ELSIF V_VENDOR_ID > 0 THEN
574 OPEN C_VEND_ADDRESS(V_VENDOR_ID,V_VENDOR_SITE_ID);
575 FETCH C_VEND_ADDRESS
576 INTO V_ADDRESS,V_SUPPLIER_TYPE;
577 CLOSE C_VEND_ADDRESS;
578 V_ENTERED_INTO_LOOP := 'YES';
579 END IF;
580 IF V_SUPPLIER_TYPE IS NULL OR V_SUPPLIER_TYPE in ('Manufacturer','Importer') THEN
581 CP_SUPPLIER_TYPE := 'MANUFACTURER';
582 ELSE
583 CP_SUPPLIER_TYPE := 'DEALER';
584 END IF;
585 IF V_ENTERED_INTO_LOOP = 'YES' THEN
586 RETURN (NVL(V_ADDRESS
587 ,' '));
588 ELSE
589 RETURN (COL3);
590 END IF;
591 END CF_2FORMULA;
592
593 FUNCTION CF_MATCHED_SHIPPED_QTYFORMULA(QUERY IN VARCHAR2
594 ,REGISTER_ID IN NUMBER
595 ,RECEIPT_REMAINING_QTY IN NUMBER
596 ,TRANSACTION_TYPE IN VARCHAR2) RETURN CHAR IS
597 V_QUANTITY VARCHAR2(100);
598 CURSOR C_SHIPPED_QTY(P_REGISTER_ID IN NUMBER) IS
599 SELECT
600 QTY_TO_ADJUST
601 FROM
602 JAI_CMN_RG_23D_TRXS
603 WHERE REGISTER_ID = P_REGISTER_ID;
604 CURSOR C_MATCHED_QTY(P_REGISTER_ID IN NUMBER) IS
605 SELECT
606 SUM(QUANTITY_APPLIED)
607 FROM
608 JAI_CMN_MATCH_RECEIPTS
609 WHERE RECEIPT_ID = P_REGISTER_ID;
610 CURSOR C_SHIPPED_QTY_ISO(P_REGISTER_ID IN NUMBER) IS
611 SELECT
612 NVL(SUM(QTY_TO_ADJUST)
613 ,0)
614 FROM
615 JAI_CMN_RG_23D_TRXS
616 WHERE REGISTER_ID = P_REGISTER_ID;
617 CURSOR C_MATCHED_QTY_ISO(P_REGISTER_ID IN NUMBER,CP_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.TRANSACTION_TYPE%TYPE) IS
618 SELECT
619 NVL(SUM(QUANTITY_APPLIED)
620 ,0)
621 FROM
622 JAI_CMN_MATCH_RECEIPTS
623 WHERE RECEIPT_ID IN (
624 SELECT
625 REGISTER_ID
626 FROM
627 JAI_CMN_RG_23D_TRXS A
631 LOCATION_ID,
628 WHERE ( A.ORGANIZATION_ID , A.LOCATION_ID , A.INVENTORY_ITEM_ID , A.COMM_INVOICE_NO , A.FIN_YEAR ) IN (
629 SELECT
630 ORGANIZATION_ID,
632 INVENTORY_ITEM_ID,
633 COMM_INVOICE_NO,
634 FIN_YEAR
635 FROM
636 JAI_CMN_RG_23D_TRXS
637 WHERE REGISTER_ID = P_REGISTER_ID )
638 AND TRANSACTION_TYPE = CP_TRANSACTION_TYPE );
639 V_SHIPPED_QTY NUMBER;
640 V_MATCHED_QTY NUMBER;
641 BEGIN
642 IF QUERY = 'ISO' THEN
643 OPEN C_MATCHED_QTY_ISO(REGISTER_ID,'R');
644 FETCH C_MATCHED_QTY_ISO
645 INTO V_MATCHED_QTY;
646 CLOSE C_MATCHED_QTY_ISO;
647 V_QUANTITY := NVL(RECEIPT_REMAINING_QTY
648 ,0) || ' / ' || NVL(V_MATCHED_QTY
649 ,0);
650 ELSIF TRANSACTION_TYPE in ('R','MR','CR','MCR') THEN
651 OPEN C_MATCHED_QTY(REGISTER_ID);
652 FETCH C_MATCHED_QTY
653 INTO V_MATCHED_QTY;
654 CLOSE C_MATCHED_QTY;
655 V_QUANTITY := NVL(RECEIPT_REMAINING_QTY
656 ,0) || ' / ' || NVL(V_MATCHED_QTY
657 ,0);
658 END IF;
659 RETURN V_QUANTITY;
660 END CF_MATCHED_SHIPPED_QTYFORMULA;
661
662 FUNCTION CF_MANU_NAMEFORMULA(REGISTER_ID IN NUMBER
663 ,CF_2 IN VARCHAR2
664 ,QTY IN NUMBER
665 ,EXCISE_DUTY_RATE IN NUMBER
666 ,COL6 IN NUMBER
667 ,COL7 IN NUMBER
668 ,COL2 IN VARCHAR2
669 ,MANUFACTURER_ADDRESS IN VARCHAR2
670 ,QTY_RECEIVED_FROM_MANUFACTURER IN NUMBER
671 ,MANUFACTURER_RATE_AMT_PER_UNIT IN VARCHAR2
672 ,TOT_AMT_PAID_TO_MANUFACTURER IN NUMBER
673 ,MANUFACTURER_NAME IN VARCHAR2) RETURN CHAR IS
674 BEGIN
675 /*SRW.MESSAGE(1275
676 ,'reg_id:' || REGISTER_ID || ',sup_type:' || CP_SUPPLIER_TYPE || ', cf2:' || CF_2)*/NULL;
677 IF CP_SUPPLIER_TYPE = 'MANUFACTURER' THEN
678 CP_MANU_ADDRESS := CF_2;
679 CP_QTY_RECEIVED_FROM_MANU := QTY;
680 CP_MANU_RATE_AMT_PER_UNIT := NVL(EXCISE_DUTY_RATE
681 ,'') || '/' || NVL(COL6
682 ,'');
683 CP_TOT_AMT_PAID_TO_MANU := COL7;
684 RETURN COL2;
685 ELSIF CP_SUPPLIER_TYPE = 'DEALER' THEN
686 CP_MANU_ADDRESS := MANUFACTURER_ADDRESS;
687 CP_QTY_RECEIVED_FROM_MANU := QTY_RECEIVED_FROM_MANUFACTURER;
688 CP_MANU_RATE_AMT_PER_UNIT := MANUFACTURER_RATE_AMT_PER_UNIT;
689 CP_TOT_AMT_PAID_TO_MANU := TOT_AMT_PAID_TO_MANUFACTURER;
690 RETURN MANUFACTURER_NAME;
691 ELSE
692 CP_MANU_ADDRESS := NULL;
693 CP_QTY_RECEIVED_FROM_MANU := NULL;
694 CP_MANU_RATE_AMT_PER_UNIT := NULL;
695 CP_TOT_AMT_PAID_TO_MANU := NULL;
696 RETURN '';
697 END IF;
698 END CF_MANU_NAMEFORMULA;
699
700 FUNCTION CF_ISSUE_CESS_AMTFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
701 LN_CESS_AMT NUMBER;
702 CURSOR CUR_GET_CESS_AMT IS
703 SELECT
704 SUM(DEBIT) TAX_VAL
705 FROM
706 JAI_CMN_RG_OTHERS RGOTH
707 WHERE RGOTH.SOURCE_TYPE = 3
708 AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
709 AND RGOTH.TAX_TYPE in ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' );
710 BEGIN
711 OPEN CUR_GET_CESS_AMT;
712 FETCH CUR_GET_CESS_AMT
713 INTO LN_CESS_AMT;
714 CLOSE CUR_GET_CESS_AMT;
715 RETURN (NVL(LN_CESS_AMT
716 ,0));
717 END CF_ISSUE_CESS_AMTFORMULA;
718
719 FUNCTION CF_ISSUE_CESS_AMT_PER_UNITFORM(COL11 IN NUMBER
720 ,CF_ISSUE_CESS_AMT IN NUMBER) RETURN NUMBER IS
721 BEGIN
722 IF COL11 > 0 THEN
723 RETURN ROUND((NVL(CF_ISSUE_CESS_AMT
724 ,0) / COL11)
725 ,2);
726 ELSE
727 RETURN 0;
728 END IF;
729 END CF_ISSUE_CESS_AMT_PER_UNITFORM;
730
731 FUNCTION CF_RECEIPT_CESS_AMTFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
732 LN_CESS_AMT NUMBER;
733 CURSOR CUR_GET_CESS_AMT IS
734 SELECT
735 SUM(CREDIT) TAX_VAL
736 FROM
737 JAI_CMN_RG_OTHERS RGOTH
738 WHERE RGOTH.SOURCE_TYPE = 3
739 AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
740 AND RGOTH.TAX_TYPE in ( 'EXCISE_EDUCATION_CESS' , 'CVD_EDUCATION_CESS' );
741 BEGIN
742 OPEN CUR_GET_CESS_AMT;
743 FETCH CUR_GET_CESS_AMT
744 INTO LN_CESS_AMT;
745 CLOSE CUR_GET_CESS_AMT;
746 RETURN (NVL(LN_CESS_AMT
747 ,0));
748 END CF_RECEIPT_CESS_AMTFORMULA;
749
750 FUNCTION CF_RECEIPT_CESS_AMT_PER_UNIT(QTY IN NUMBER
751 ,CF_RECEIPT_CESS_AMT IN NUMBER) RETURN NUMBER IS
752 BEGIN
753 IF QTY > 0 THEN
754 RETURN ROUND((NVL(CF_RECEIPT_CESS_AMT
755 ,0) / QTY)
756 ,2);
757 ELSE
758 RETURN 0;
759 END IF;
760 END CF_RECEIPT_CESS_AMT_PER_UNIT;
761
762 FUNCTION AFTERREPORT RETURN BOOLEAN IS
763 BEGIN
764 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
765 RETURN (TRUE);
766 END AFTERREPORT;
767
768 FUNCTION CF_ISSUE_SH_CESS_AMTFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
769 LN_CESS_AMT NUMBER;
770 CURSOR CUR_GET_CESS_AMT IS
771 SELECT
772 SUM(DEBIT) TAX_VAL
773 FROM
774 JAI_CMN_RG_OTHERS RGOTH
775 WHERE RGOTH.SOURCE_TYPE = 3
776 AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
777 AND RGOTH.TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
778 BEGIN
779 OPEN CUR_GET_CESS_AMT;
780 FETCH CUR_GET_CESS_AMT
781 INTO LN_CESS_AMT;
782 CLOSE CUR_GET_CESS_AMT;
783 RETURN (NVL(LN_CESS_AMT
784 ,0));
785 END CF_ISSUE_SH_CESS_AMTFORMULA;
786
787 FUNCTION CF_ISSUE_SH_CESS_AMT_PER_UNITF(COL11 IN NUMBER
788 ,CF_ISSUE_SH_CESS_AMT IN NUMBER) RETURN NUMBER IS
789 BEGIN
790 IF COL11 > 0 THEN
791 RETURN ROUND((NVL(CF_ISSUE_SH_CESS_AMT
792 ,0) / COL11)
793 ,2);
794 ELSE
795 RETURN NULL;
796 END IF;
797 END CF_ISSUE_SH_CESS_AMT_PER_UNITF;
798
799 FUNCTION CF_RECEIPT_SH_CESS_AMTFORMULA(REGISTER_ID IN NUMBER) RETURN NUMBER IS
800 LN_CESS_AMT NUMBER;
801 CURSOR CUR_GET_CESS_AMT IS
802 SELECT
803 SUM(CREDIT) TAX_VAL
804 FROM
805 JAI_CMN_RG_OTHERS RGOTH
806 WHERE RGOTH.SOURCE_TYPE = 3
807 AND RGOTH.SOURCE_REGISTER_ID = REGISTER_ID
808 AND RGOTH.TAX_TYPE in ( 'EXCISE_SH_EDU_CESS' , 'CVD_SH_EDU_CESS' );
809 BEGIN
810 OPEN CUR_GET_CESS_AMT;
811 FETCH CUR_GET_CESS_AMT
812 INTO LN_CESS_AMT;
813 CLOSE CUR_GET_CESS_AMT;
814 RETURN (LN_CESS_AMT);
815 END CF_RECEIPT_SH_CESS_AMTFORMULA;
816
817 FUNCTION CF_RECEIPT_SH_CESS_AMT_PER_UNT(QTY IN NUMBER
818 ,CF_RECEIPT_SH_CESS_AMT IN NUMBER) RETURN NUMBER IS
819 BEGIN
820 IF QTY > 0 THEN
821 RETURN ROUND((NVL(CF_RECEIPT_SH_CESS_AMT
822 ,0) / QTY)
823 ,2);
824 ELSE
825 RETURN NULL;
826 END IF;
827 END CF_RECEIPT_SH_CESS_AMT_PER_UNT;
828
829 FUNCTION CP_SUPPLIER_TYPE_P RETURN VARCHAR2 IS
830 BEGIN
831 RETURN CP_SUPPLIER_TYPE;
832 END CP_SUPPLIER_TYPE_P;
833
834 FUNCTION CP_MANU_ADDRESS_P RETURN VARCHAR2 IS
835 BEGIN
836 RETURN CP_MANU_ADDRESS;
837 END CP_MANU_ADDRESS_P;
838
839 FUNCTION CP_QTY_RECEIVED_FROM_MANU_P RETURN NUMBER IS
840 BEGIN
841 RETURN CP_QTY_RECEIVED_FROM_MANU;
842 END CP_QTY_RECEIVED_FROM_MANU_P;
843
844 FUNCTION CP_MANU_RATE_AMT_PER_UNIT_P RETURN VARCHAR2 IS
845 BEGIN
846 RETURN CP_MANU_RATE_AMT_PER_UNIT;
847 END CP_MANU_RATE_AMT_PER_UNIT_P;
848
849 FUNCTION CP_TOT_AMT_PAID_TO_MANU_P RETURN NUMBER IS
850 BEGIN
851 RETURN CP_TOT_AMT_PAID_TO_MANU;
852 END CP_TOT_AMT_PAID_TO_MANU_P;
853
854 END JA_JAIN23D_XMLP_PKG;
855
856
857