1 PACKAGE BODY JA_JAINAR3R_XMLP_PKG AS
2 /* $Header: JAINAR3RB.pls 120.1 2007/12/25 16:11:41 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 IF P_STATUS IS NOT NULL THEN
6 IF P_STATUS = 'RECEIVED' THEN
7 P_W_CLAUSE := 'AND received_date IS NOT NULL';
8 ELSIF P_STATUS = 'NOT RECEIVED' THEN
9 P_W_CLAUSE := 'AND received_date IS NULL';
10 ELSE
11 P_W_CLAUSE := 'AND 1 = 1';
12 END IF;
13 END IF;
14 IF (P_W_CLAUSE IS NULL) THEN
15 P_W_CLAUSE := 'AND 1 = 1';
16 END IF;
17 RETURN (TRUE);
18 END AFTERPFORM;
19
20 FUNCTION CF_1FORMULA(L_TRX_NUMBER IN NUMBER
21 ,TRX_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
22 V_TRX_NUMBER VARCHAR2(20);
23 CURSOR GET_TRX_NUMBER IS
24 SELECT
25 TRX_NUMBER
26 FROM
27 RA_CUSTOMER_TRX_ALL
28 WHERE CUSTOMER_TRX_ID = L_TRX_NUMBER;
29 CURSOR C_GET_ORDER_NUMBER(P_ORDER_NUMBER IN NUMBER) IS
30 SELECT
31 TO_CHAR(ORDER_NUMBER)
32 FROM
33 OE_ORDER_HEADERS_ALL
34 WHERE HEADER_ID = P_ORDER_NUMBER;
35 BEGIN
36 IF TRX_TYPE = 'ORDER' THEN
37 OPEN C_GET_ORDER_NUMBER(L_TRX_NUMBER);
38 FETCH C_GET_ORDER_NUMBER
39 INTO V_TRX_NUMBER;
40 CLOSE C_GET_ORDER_NUMBER;
41 ELSIF TRX_TYPE = 'INVOICE' THEN
42 OPEN GET_TRX_NUMBER;
43 FETCH GET_TRX_NUMBER
44 INTO V_TRX_NUMBER;
45 CLOSE GET_TRX_NUMBER;
46 END IF;
47 RETURN (V_TRX_NUMBER);
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 RETURN 'No TrxNumber';
51 END CF_1FORMULA;
52
53 FUNCTION CF_1FORMULA0031(CUSTOMER_SITE_ID IN NUMBER) RETURN VARCHAR2 IS
54 V_LOCATION VARCHAR2(80);
55 BEGIN
56 SELECT
57 LOCATION
58 INTO V_LOCATION
59 FROM
60 HZ_CUST_SITE_USES_ALL
61 WHERE SITE_USE_ID = NVL(CUSTOMER_SITE_ID
62 ,0);
63 RETURN (V_LOCATION);
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 RETURN (NULL);
67 END CF_1FORMULA0031;
68
69 FUNCTION DELIVERY_NAMEFORMULA(DELIVERY_DETAIL_ID IN NUMBER) RETURN VARCHAR2 IS
70 V_DELIVERY_NAME VARCHAR2(50);
71 CURSOR C_DELIVERY_NAME(P_DELIVERY_DETAIL_ID IN NUMBER) IS
72 SELECT
73 A.NAME
74 FROM
75 WSH_NEW_DELIVERIES A,
76 WSH_DELIVERY_ASSIGNMENTS B
77 WHERE A.DELIVERY_ID = B.DELIVERY_ID
78 AND B.DELIVERY_DETAIL_ID = P_DELIVERY_DETAIL_ID;
79 BEGIN
80 OPEN C_DELIVERY_NAME(DELIVERY_DETAIL_ID);
81 FETCH C_DELIVERY_NAME
82 INTO V_DELIVERY_NAME;
83 CLOSE C_DELIVERY_NAME;
84 RETURN (V_DELIVERY_NAME);
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 RETURN 'No Delivery';
88 END DELIVERY_NAMEFORMULA;
89
90 FUNCTION CF_DELIVERY_IDFORMULA(DELIVERY_DETAIL_ID IN NUMBER) RETURN NUMBER IS
91 CURSOR C_DELIVERY_NAME(P_DELIVERY_DETAIL_ID IN NUMBER) IS
92 SELECT
93 DELIVERY_ID
94 FROM
95 WSH_DELIVERY_ASSIGNMENTS B
96 WHERE B.DELIVERY_DETAIL_ID = P_DELIVERY_DETAIL_ID;
97 V_DELIVERY_ID NUMBER;
98 BEGIN
99 IF DELIVERY_DETAIL_ID IS NULL THEN
100 RETURN NULL;
101 ELSE
102 OPEN C_DELIVERY_NAME(DELIVERY_DETAIL_ID);
103 FETCH C_DELIVERY_NAME
104 INTO V_DELIVERY_ID;
105 CLOSE C_DELIVERY_NAME;
106 RETURN V_DELIVERY_ID;
107 END IF;
108 END CF_DELIVERY_IDFORMULA;
109
110 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
111 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
112 SELECT
113 CONCURRENT_PROGRAM_ID,
114 NVL(ENABLE_TRACE
115 ,'N')
116 FROM
117 FND_CONCURRENT_REQUESTS
118 WHERE REQUEST_ID = P_REQUEST_ID;
119 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
120 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
121 BEGIN
122 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
123 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
124 /*SRW.MESSAGE(1275
125 ,'Report Version is 120.2 Last modified date is 25/07/2005')*/NULL;
126 BEGIN
127 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
128 FETCH C_PROGRAM_ID
129 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
130 CLOSE C_PROGRAM_ID;
131 /*SRW.MESSAGE(1275
132 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
133 IF V_ENABLE_TRACE = 'Y' THEN
134 EXECUTE IMMEDIATE
135 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
136 END IF;
137 EXCEPTION
138 WHEN OTHERS THEN
139 /*SRW.MESSAGE(1275
140 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
141 END;
142 RETURN (TRUE);
143 END BEFOREREPORT;
144
145 FUNCTION AFTERREPORT RETURN BOOLEAN IS
146 BEGIN
147 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
148 RETURN (TRUE);
149 END AFTERREPORT;
150
151 END JA_JAINAR3R_XMLP_PKG;
152
153
[Home] [Help]