1 PACKAGE BODY JA_JAINEXCL_XMLP_PKG AS
2 /* $Header: JAINEXCLB.pls 120.1 2007/12/25 16:18:17 dwkrishn noship $ */
3 FUNCTION CF_ORG_NAMEFORMULA(ORGANIZATION_ID_V IN NUMBER) RETURN VARCHAR2 IS
4 V_ORGANIZATION_NAME VARCHAR2(60);
5 BEGIN
6 SELECT
7 ORGANIZATION_NAME
8 INTO V_ORGANIZATION_NAME
9 FROM
10 ORG_ORGANIZATION_DEFINITIONS
11 WHERE ORGANIZATION_ID = ORGANIZATION_ID_V;
12 RETURN (V_ORGANIZATION_NAME);
13 END CF_ORG_NAMEFORMULA;
14
15 FUNCTION CF_LOCFORMULA(LOCATION_ID_V IN NUMBER) RETURN VARCHAR2 IS
16 V_LOCATION_CODE VARCHAR2(60);
17 BEGIN
18 SELECT
19 LOCATION_CODE
20 INTO V_LOCATION_CODE
21 FROM
22 HR_LOCATIONS
23 WHERE LOCATION_ID = LOCATION_ID_V;
24 RETURN (V_LOCATION_CODE);
25 END CF_LOCFORMULA;
26
27 FUNCTION CF_SOB_NAMEFORMULA(ORGANIZATION_ID_V IN NUMBER) RETURN VARCHAR2 IS
28 CURSOR FOR_SOB_ID(INV_ORG_ID IN NUMBER) IS
29 SELECT
30 SET_OF_BOOKS_ID
31 FROM
32 ORG_ORGANIZATION_DEFINITIONS
33 WHERE ORGANIZATION_ID = INV_ORG_ID;
34 CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
35 SELECT
36 NAME
37 FROM
38 GL_SETS_OF_BOOKS
39 WHERE SET_OF_BOOKS_ID = SOB_ID;
40 V_NAME VARCHAR2(30);
41 V_SOB_ID NUMBER;
42 BEGIN
43 OPEN FOR_SOB_ID(ORGANIZATION_ID_V);
44 FETCH FOR_SOB_ID
45 INTO V_SOB_ID;
46 CLOSE FOR_SOB_ID;
47 OPEN FOR_SOB_NAME(V_SOB_ID);
48 FETCH FOR_SOB_NAME
49 INTO V_NAME;
50 CLOSE FOR_SOB_NAME;
51 RETURN (V_NAME);
52 END CF_SOB_NAMEFORMULA;
53
54 FUNCTION CF_P_CUSTOMERFORMULA RETURN VARCHAR2 IS
55 CURSOR FOR_CUST_NAME(C_ID IN NUMBER) IS
56 SELECT
57 PARTY_NAME
58 FROM
59 HZ_PARTIES HP,
60 HZ_CUST_ACCOUNTS HCA
61 WHERE HP.PARTY_ID = HCA.PARTY_ID
62 AND HCA.CUST_ACCOUNT_ID = C_ID;
63 V_NAME VARCHAR2(100);
64 BEGIN
65 OPEN FOR_CUST_NAME(P_CUSTOMER_ID);
66 FETCH FOR_CUST_NAME
67 INTO V_NAME;
68 CLOSE FOR_CUST_NAME;
69 RETURN (V_NAME);
70 END CF_P_CUSTOMERFORMULA;
71
72 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
73 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
74 SELECT
75 CONCURRENT_PROGRAM_ID,
76 NVL(ENABLE_TRACE
77 ,'N')
78 FROM
79 FND_CONCURRENT_REQUESTS
80 WHERE REQUEST_ID = P_REQUEST_ID;
81 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
82 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
83 CURSOR GET_AUDSID IS
84 SELECT
85 A.SID,
86 A.SERIAL#,
87 B.SPID
88 FROM
89 V$SESSION A,
90 V$PROCESS B
91 WHERE AUDSID = USERENV('SESSIONID')
92 AND A.PADDR = B.ADDR;
93 CURSOR GET_DBNAME IS
94 SELECT
95 NAME
96 FROM
97 V$DATABASE;
98 AUDSID NUMBER := USERENV('SESSIONID');
99 SID NUMBER;
100 SERIAL NUMBER;
101 SPID VARCHAR2(9);
102 NAME1 VARCHAR2(25);
103 BEGIN
104 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
105 P_START_DATE_T:=TO_CHAR(P_START_DATE,'DD-MON-YYYY');
106 P_END_DATE_T:=TO_CHAR(P_END_DATE,'DD-MON-YYYY');
107 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
108 /*SRW.MESSAGE(1275
109 ,'Report Version is 120.2 Last modified date is 22-Feb-2006')*/NULL;
110 IF P_END_DATE < P_START_DATE THEN
111 /*SRW.MESSAGE(1275
112 ,'Start Date can''t be greater than end date Date')*/NULL;
113 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
114 END IF;
115 BEGIN
116 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
117 FETCH C_PROGRAM_ID
118 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
119 CLOSE C_PROGRAM_ID;
120 /*SRW.MESSAGE(1275
121 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
122 IF V_ENABLE_TRACE = 'Y' THEN
123 OPEN GET_AUDSID;
124 FETCH GET_AUDSID
125 INTO SID,SERIAL,SPID;
126 CLOSE GET_AUDSID;
127 OPEN GET_DBNAME;
128 FETCH GET_DBNAME
129 INTO NAME1;
130 CLOSE GET_DBNAME;
131 /*SRW.MESSAGE(599
132 ,'TraceFile Name = ' || LOWER(NAME1) || '_ora_' || SPID || '.trc')*/NULL;
133 EXECUTE IMMEDIATE
134 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
135 END IF;
136 RETURN (TRUE);
137 EXCEPTION
138 WHEN OTHERS THEN
139 /*SRW.MESSAGE(1275
140 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg ->' || SQLERRM)*/NULL;
141 RETURN (TRUE);
142 END;
143 END BEFOREREPORT;
144
145 FUNCTION P_CUSTOMER_IDVALIDTRIGGER RETURN BOOLEAN IS
146 BEGIN
147 RETURN (TRUE);
148 END P_CUSTOMER_IDVALIDTRIGGER;
149
150 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
151 BEGIN
152 RETURN (TRUE);
153 END BEFOREPFORM;
154
155 FUNCTION AFTERPFORM RETURN BOOLEAN IS
156 BEGIN
157 RETURN (TRUE);
158 END AFTERPFORM;
159
160 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
161 BEGIN
162 RETURN (TRUE);
163 END BETWEENPAGE;
164
165 FUNCTION AFTERREPORT RETURN BOOLEAN IS
166 BEGIN
167 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
168 RETURN (TRUE);
169 END AFTERREPORT;
170
171 FUNCTION CF_1FORMULA(RMA_LINE_ID_v IN NUMBER
172 ,EXCISE_INVOICE_NO IN VARCHAR2
173 ,EXCISE_INVOICE_DATE IN DATE) RETURN NUMBER IS
174 LN_CESS_AMT NUMBER;
175 BEGIN
176 SELECT
177 SUM(RTL.TAX_AMOUNT)
178 INTO LN_CESS_AMT
179 FROM
180 JAI_OM_OE_RMA_TAXES RTL,
181 JAI_CMN_TAXES_ALL JTC
182 WHERE RTL.RMA_LINE_ID = RMA_LINE_ID_V
183 AND JTC.TAX_ID = RTL.TAX_ID
184 AND JTC.TAX_TYPE in ( 'CVD_EDUCATION_CESS' , 'EXCISE_EDUCATION_CESS' );
185 /*SRW.MESSAGE(1275
186 ,' ex inv : ' || EXCISE_INVOICE_NO || ' date : ' || EXCISE_INVOICE_DATE)*/NULL;
187 RETURN LN_CESS_AMT;
188 END CF_1FORMULA;
189
190 END JA_JAINEXCL_XMLP_PKG;
191
192
[Home] [Help]