1 PACKAGE BODY JA_JAINPROS_XMLP_PKG AS
2 /* $Header: JAINPROSB.pls 120.1 2007/12/25 16:24:41 dwkrishn noship $ */
3 FUNCTION P_END_DATEVALIDTRIGGER RETURN BOOLEAN IS
4 BEGIN
5 P_END_DATE1:=P_END_DATE;
9 RETURN (TRUE);
6 IF P_END_DATE IS NULL THEN
7 P_END_DATE1 := SYSDATE;
8 END IF;
10 END P_END_DATEVALIDTRIGGER;
11
12 FUNCTION P_SYSDATEVALIDTRIGGER RETURN BOOLEAN IS
13 BEGIN
14 P_SYSDATE := TRUNC(SYSDATE);
15 RETURN (TRUE);
16 END P_SYSDATEVALIDTRIGGER;
17
18 FUNCTION P_VENDOR_SITE_LEXVALIDTRIGGER RETURN BOOLEAN IS
19 BEGIN
20 IF P_VENDOR_ID IS NULL AND P_VENDOR_SITE_ID IS NULL THEN
21 P_VENDOR_SITE_LEX := ' ';
22 ELSIF P_VENDOR_ID IS NOT NULL AND P_VENDOR_SITE_ID IS NULL THEN
23 P_VENDOR_SITE_LEX := ' AND jipov.vendor_id = :p_vendor_id';
24 ELSIF P_VENDOR_ID IS NULL AND P_VENDOR_SITE_ID IS NOT NULL THEN
25 P_VENDOR_SITE_LEX := ' AND jipov.vendor_site_id = :p_vendor_site_id';
26 ELSE
27 P_VENDOR_SITE_LEX := ' AND jipov.vendor_id = :p_vendor_id AND jipov.vendor_site_id = :p_vendor_site_id';
28 END IF;
29 IF P_REPORT_TYPE = 'Unregistered Dealers' THEN
30 P_VENDOR_SITE_LEX := P_VENDOR_SITE_LEX || ' AND jipov.st_reg_no IS NULL AND jipov.cst_reg_no IS NULL';
31 ELSIF P_REPORT_TYPE = 'Outside State' THEN
32 P_VENDOR_SITE_LEX := P_VENDOR_SITE_LEX || ' AND jipov.cst_reg_no IS NOT NULL';
33 ELSIF P_REPORT_TYPE = 'Within State' THEN
34 P_VENDOR_SITE_LEX := P_VENDOR_SITE_LEX || ' AND jipov.st_reg_no IS NOT NULL';
35 END IF;
36 RETURN (TRUE);
37 END P_VENDOR_SITE_LEXVALIDTRIGGER;
38
39 FUNCTION CF_ORGN_NAMEFORMULA RETURN CHAR IS
40 CURSOR C_ORGANIZATION_NAME(P_ORGANIZATION_ID IN NUMBER) IS
41 SELECT
42 NAME,
43 INTERNAL_ADDRESS_LINE
44 FROM
45 HR_ALL_ORGANIZATION_UNITS
46 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
47 V_ORGN_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
48 BEGIN
49 OPEN C_ORGANIZATION_NAME(P_INV_ORGANIZATION_ID);
50 FETCH C_ORGANIZATION_NAME
51 INTO V_ORGN_NAME,CP_INTADRS;
52 CLOSE C_ORGANIZATION_NAME;
53 RETURN V_ORGN_NAME;
54 END CF_ORGN_NAMEFORMULA;
55
56 FUNCTION CF_LOC_CODEFORMULA RETURN CHAR IS
57 CURSOR C_LOCATION_CODE(P_LOCATION_ID IN NUMBER) IS
58 SELECT
59 LOCATION_CODE
60 FROM
61 HR_LOCATIONS
62 WHERE LOCATION_ID = P_LOCATION_ID;
63 V_LOC_CODE HR_LOCATIONS.LOCATION_CODE%TYPE;
64 BEGIN
65 OPEN C_LOCATION_CODE(P_LOCATION_ID);
66 FETCH C_LOCATION_CODE
67 INTO V_LOC_CODE;
68 CLOSE C_LOCATION_CODE;
69 RETURN V_LOC_CODE;
70 END CF_LOC_CODEFORMULA;
71
72 FUNCTION P_INVOICE_DATE_LEXVALIDTRIGGER RETURN BOOLEAN IS
73 BEGIN
74 IF P_START_DATE IS NULL AND P_END_DATE1 IS NULL THEN
75 P_INVOICE_DATE_LEX := ' ';
76 ELSIF P_START_DATE IS NULL AND P_END_DATE1 IS NOT NULL THEN
77 P_INVOICE_DATE_LEX := ' AND INV1.INVOICE_DATE <= :P_END_DATE1';
78 ELSIF P_START_DATE IS NOT NULL AND P_END_DATE1 IS NULL THEN
79 P_INVOICE_DATE_LEX := ' AND INV1.INVOICE_DATE BETWEEN :P_START_DATE AND :P_SYSDATE';
80 ELSE
81 P_INVOICE_DATE_LEX := ' AND INV1.INVOICE_DATE BETWEEN :P_START_DATE AND :P_END_DATE1';
82 END IF;
83 RETURN (TRUE);
84 END P_INVOICE_DATE_LEXVALIDTRIGGER;
85
86 FUNCTION CF_VEND_CSTFORMULA(VENDOR_ID IN NUMBER
87 ,VENDOR_SITE_ID IN NUMBER) RETURN CHAR IS
88 CURSOR C_JA_VENDOR_DETAILS(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
89 SELECT
90 CST_REG_NO,
91 ST_REG_NO,
92 VAT_REG_NO
93 FROM
94 JAI_CMN_VENDOR_SITES
95 WHERE VENDOR_ID = P_VENDOR_ID
96 AND VENDOR_SITE_ID = P_VENDOR_SITE_ID;
97 V_CST_REG_NO JAI_CMN_VENDOR_SITES.CST_REG_NO%TYPE;
98 BEGIN
99 OPEN C_JA_VENDOR_DETAILS(VENDOR_ID,VENDOR_SITE_ID);
100 FETCH C_JA_VENDOR_DETAILS
101 INTO V_CST_REG_NO,CP_VEND_LST,CP_VEND_VAT;
102 CLOSE C_JA_VENDOR_DETAILS;
103 RETURN V_CST_REG_NO;
104 END CF_VEND_CSTFORMULA;
105
106 FUNCTION CF_VEND_NAMEFORMULA(VENDOR_ID IN NUMBER) RETURN CHAR IS
107 CURSOR C_VENDOR_NAME(P_VENDOR_ID IN NUMBER) IS
108 SELECT
109 VENDOR_NAME
110 FROM
111 PO_VENDORS
112 WHERE VENDOR_ID = P_VENDOR_ID;
113 V_VENDOR_NAME PO_VENDORS.VENDOR_NAME%TYPE;
114 BEGIN
115 OPEN C_VENDOR_NAME(VENDOR_ID);
116 FETCH C_VENDOR_NAME
117 INTO V_VENDOR_NAME;
118 CLOSE C_VENDOR_NAME;
119 RETURN V_VENDOR_NAME;
120 END CF_VEND_NAMEFORMULA;
121
122 FUNCTION CF_VEND_SITE_CODEFORMULA(VENDOR_SITE_ID IN NUMBER) RETURN CHAR IS
123 CURSOR C_VENDOR_SITE_CODE(P_VENDOR_SITE_ID IN NUMBER) IS
124 SELECT
125 VENDOR_SITE_CODE,
126 STATE
127 FROM
128 PO_VENDOR_SITES_ALL
129 WHERE VENDOR_SITE_ID = P_VENDOR_SITE_ID;
130 V_VENDOR_SITE_CODE PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE;
131 BEGIN
132 OPEN C_VENDOR_SITE_CODE(VENDOR_SITE_ID);
133 FETCH C_VENDOR_SITE_CODE
134 INTO V_VENDOR_SITE_CODE,CP_VEND_STATE;
135 CLOSE C_VENDOR_SITE_CODE;
136 RETURN V_VENDOR_SITE_CODE;
137 END CF_VEND_SITE_CODEFORMULA;
138
139 FUNCTION CF_REG_NOFORMULA(VENDOR_ID IN NUMBER
140 ,VENDOR_SITE_ID IN NUMBER) RETURN CHAR IS
141 CURSOR C_JA_VENDOR_DETAILS(P_VENDOR_ID IN NUMBER,P_VENDOR_SITE_ID IN NUMBER) IS
142 SELECT
143 CST_REG_NO,
144 ST_REG_NO,
145 VAT_REG_NO
146 FROM
147 JAI_CMN_VENDOR_SITES
148 WHERE VENDOR_ID = P_VENDOR_ID
149 AND VENDOR_SITE_ID = P_VENDOR_SITE_ID;
150 V_CST_REG_NO JAI_CMN_VENDOR_SITES.CST_REG_NO%TYPE;
151 V_ST_REG_NO JAI_CMN_VENDOR_SITES.ST_REG_NO%TYPE;
152 V_VAT_REG_NO JAI_CMN_VENDOR_SITES.VAT_REG_NO%TYPE;
153 V_REG_NO JAI_CMN_VENDOR_SITES.CST_REG_NO%TYPE;
154 BEGIN
155 OPEN C_JA_VENDOR_DETAILS(VENDOR_ID,VENDOR_SITE_ID);
156 FETCH C_JA_VENDOR_DETAILS
157 INTO V_CST_REG_NO,V_ST_REG_NO,V_VAT_REG_NO;
158 CLOSE C_JA_VENDOR_DETAILS;
159 IF P_REPORT_TYPE = 'Outside State' THEN
160 V_REG_NO := V_CST_REG_NO;
161 ELSIF P_REPORT_TYPE = 'Within State' THEN
162 V_REG_NO := V_ST_REG_NO;
163 ELSIF P_REPORT_TYPE = 'Unregistered Dealers' THEN
164 V_REG_NO := 'n/a';
165 ELSE
166 V_REG_NO := 'N/A';
167 END IF;
168 RETURN V_REG_NO;
169 END CF_REG_NOFORMULA;
170
171 FUNCTION P_REPORT_TYPE_DESCVALIDTRIGGER RETURN BOOLEAN IS
172 BEGIN
173 IF P_REPORT_TYPE = 'Outside State' THEN
174 P_REPORT_TYPE_DESC := 'Outside State';
175 ELSIF P_REPORT_TYPE = 'Within State' THEN
176 P_REPORT_TYPE_DESC := 'Within State';
177 ELSIF P_REPORT_TYPE = 'Unregistered Dealers' THEN
178 P_REPORT_TYPE_DESC := 'UnRegistered Dealers';
179 ELSE
180 P_REPORT_TYPE_DESC := 'N/A';
181 END IF;
182 RETURN (TRUE);
183 END P_REPORT_TYPE_DESCVALIDTRIGGER;
184
185 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
186 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
187 SELECT
188 CONCURRENT_PROGRAM_ID,
189 NVL(ENABLE_TRACE
190 ,'N')
191 FROM
192 FND_CONCURRENT_REQUESTS
193 WHERE REQUEST_ID = P_REQUEST_ID;
194 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
195 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
196 CURSOR GET_AUDSID IS
197 SELECT
198 A.SID,
199 A.SERIAL#,
200 NVL(B.SPID
201 ,'')
202 FROM
203 V$SESSION A,
204 V$PROCESS B
205 WHERE AUDSID = USERENV('SESSIONID')
206 AND A.PADDR = B.ADDR;
207 CURSOR GET_DBNAME IS
208 SELECT
209 NVL(NAME
210 ,'')
211 FROM
212 V$DATABASE;
213 AUDSID NUMBER := USERENV('SESSIONID');
214 SID NUMBER;
215 SERIAL NUMBER;
216 SPID VARCHAR2(9);
217 NAME1 VARCHAR2(25);
218 temp1 boolean;
219 temp2 boolean;
220
221 BEGIN
222 temp1:=P_END_DATEValidTrigger;
223 temp2:=P_REPORT_TYPE_DESCVALIDTRIGGER;
224 temp2:=P_SYSDATEVALIDTRIGGER;
225 temp2:=P_TAX_TYPE_LEXVALIDTRIGGER;
226 temp2:=P_VENDOR_SITE_LEXVALIDTRIGGER;
227 temp2:=P_INVOICE_DATE_LEXVALIDTRIGGER;
228 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
229 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
230 /*SRW.MESSAGE(1275
231 ,'Report Version is 120.3 Last modified date is 21/06/2007')*/NULL;
232 BEGIN
233 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
234 FETCH C_PROGRAM_ID
235 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
236 CLOSE C_PROGRAM_ID;
237 /*SRW.MESSAGE(1275
238 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
239 IF V_ENABLE_TRACE = 'Y' THEN
240 EXECUTE IMMEDIATE
241 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
242 OPEN GET_AUDSID;
243 FETCH GET_AUDSID
244 INTO SID,SERIAL,SPID;
245 CLOSE GET_AUDSID;
246 OPEN GET_DBNAME;
247 FETCH GET_DBNAME
248 INTO NAME1;
249 CLOSE GET_DBNAME;
250 /*SRW.MESSAGE(1275
251 ,'TraceFile Name = ' || NVL(LOWER(NAME1)
252 ,'') || '_ora_' || NVL(SPID
253 ,'') || '.trc')*/NULL;
254 END IF;
255 EXCEPTION
256 WHEN OTHERS THEN
257 /*SRW.MESSAGE(1275
258 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
259 END;
260 RETURN (TRUE);
261 END BEFOREREPORT;
262
263 FUNCTION P_TAX_TYPE_LEXVALIDTRIGGER RETURN BOOLEAN IS
264 BEGIN
265 IF P_REPORT_TYPE = 'Unregistered Dealers' THEN
266 P_TAX_TYPE_LEX := ' AND jitc.tax_type IN ( ''CST'', ''Sales Tax'' )';
267 ELSIF P_REPORT_TYPE = 'Outside State' THEN
268 P_TAX_TYPE_LEX := ' AND jitc.tax_type = ''CST'' ';
269 ELSIF P_REPORT_TYPE = 'Within State' THEN
270 P_TAX_TYPE_LEX := ' AND jitc.tax_type = ''Sales Tax'' ';
271 END IF;
272 RETURN (TRUE);
273 END P_TAX_TYPE_LEXVALIDTRIGGER;
274
275 FUNCTION AFTERREPORT RETURN BOOLEAN IS
276 BEGIN
277 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
278 RETURN (TRUE);
279 END AFTERREPORT;
280
281 FUNCTION CP_INTADRS_P RETURN VARCHAR2 IS
282 BEGIN
283 RETURN CP_INTADRS;
284 END CP_INTADRS_P;
285
286 FUNCTION CP_VEND_LST_P RETURN VARCHAR2 IS
287 BEGIN
288 RETURN CP_VEND_LST;
289 END CP_VEND_LST_P;
290
291 FUNCTION CP_VEND_VAT_P RETURN VARCHAR2 IS
292 BEGIN
293 RETURN CP_VEND_VAT;
294 END CP_VEND_VAT_P;
295
296 FUNCTION CP_VEND_STATE_P RETURN VARCHAR2 IS
297 BEGIN
298 RETURN CP_VEND_STATE;
299 END CP_VEND_STATE_P;
300
301 END JA_JAINPROS_XMLP_PKG;
302
303
304
[Home] [Help]