1 PACKAGE BODY JL_JLCOPWHR_XMLP_PKG AS
2 /* $Header: JLCOPWHRB.pls 120.1 2007/12/25 16:51:59 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_CHART_OF_ACCOUNTS_ID GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
5 INIT_FAILURE EXCEPTION;
6 BASE_CURR VARCHAR2(17);
7 PREC NUMBER;
8 BEGIN
9 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
10
11 SELECT
12 DISTINCT
13 ( CHART_OF_ACCOUNTS_ID )
14 INTO L_CHART_OF_ACCOUNTS_ID
15 FROM
16 GL_SETS_OF_BOOKS G,
17 AP_SYSTEM_PARAMETERS A
18 WHERE G.SET_OF_BOOKS_ID = P_SOB_ID;
19 P_COA_ID := L_CHART_OF_ACCOUNTS_ID;
20 /*SRW.REFERENCE(P_COA_ID)*/NULL;
21 /*SRW.REFERENCE(P_COA_ID)*/NULL;
22 SELECT
23 P.BASE_CURRENCY_CODE,
24 C.PRECISION
25 INTO BASE_CURR,PREC
26 FROM
27 AP_SYSTEM_PARAMETERS P,
28 FND_CURRENCIES_VL C
29 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE
30 AND P.SET_OF_BOOKS_ID = P_SOB_ID;
31
32 C_BASE_CURRENCY_CODE := BASE_CURR;
33 P_MIN_PRECISION := PREC;
34 C_BASE_PRECISION := PREC;
35 C_LOCATION_ID := JG_ZZ_COMPANY_INFO.GET_LOCATION_ID;
36 SELECT
37 HR.GLOBAL_ATTRIBUTE8
38 INTO P_1
39 FROM
40 HR_LOCATIONS HR
41 WHERE HR.LOCATION_ID = C_LOCATION_ID;
42 RETURN (TRUE);
43 END BEFOREREPORT;
44
45 FUNCTION CALCULATE_VAT_AMOUNT(P_TAX_NAME IN VARCHAR2
46 ,P_VENDOR_ID IN NUMBER
47 ,P_VENDOR_NUMBER IN VARCHAR2,document_number in varchar2) RETURN NUMBER IS
48 V_VAT_AMOUNT NUMBER;
49 BEGIN
50 SELECT
51 ROUND(SUM((AID.AMOUNT * ATC2.TAX_RATE) / 100)
52 ,P_MIN_PRECISION) VAT_AMOUNT
53 INTO V_VAT_AMOUNT
54 FROM
55 AP_TAX_CODES ATC,
56 JL_ZZ_AP_SUP_AWT_CD JZSAC,
57 JL_ZZ_AP_INV_DIS_WH JZIDW,
58 AP_TAX_CODES ATC2,
59 AP_INVOICES AI,
60 AP_INVOICE_DISTRIBUTIONS AID
61 WHERE AID.ACCOUNTING_DATE BETWEEN P_ACCOUNTING_DATE_FROM
62 AND P_ACCOUNTING_DATE_TO
63 AND AID.MATCH_STATUS_FLAG = 'A'
64 AND AID.LINE_TYPE_LOOKUP_CODE <> 'AWT'
65 AND AID.LINE_TYPE_LOOKUP_CODE <> 'TAX'
66 AND JZIDW.INVOICE_ID = AID.INVOICE_ID
67 AND JZIDW.DISTRIBUTION_LINE_NUMBER = AID.DISTRIBUTION_LINE_NUMBER
68 AND JZSAC.SUPP_AWT_CODE_ID = JZIDW.SUPP_AWT_CODE_ID
69 AND ATC.TAX_ID = JZSAC.TAX_ID
70 AND ATC.GLOBAL_ATTRIBUTE19 is not null
71 AND ATC.NAME = P_TAX_NAME
72 AND AID.TAX_CODE_ID is not null
73 AND ATC2.TAX_ID = AID.TAX_CODE_ID
74 AND AI.INVOICE_ID = AID.INVOICE_ID
75 AND AI.INVOICE_NUM = DOCUMENT_NUMBER
76 AND AI.CANCELLED_DATE is null
77 AND ( ( AID.GLOBAL_ATTRIBUTE2 is null
78 AND AID.INVOICE_ID = AI.INVOICE_ID
79 AND AI.VENDOR_ID = P_VENDOR_ID )
80 OR ( AID.GLOBAL_ATTRIBUTE2 = P_VENDOR_NUMBER
81 AND AID.INVOICE_ID = AI.INVOICE_ID ) )
82 AND exists (
83 SELECT
84 1
85 FROM
86 AP_INVOICE_DISTRIBUTIONS AID2
87 WHERE AID2.INVOICE_ID = AID.INVOICE_ID
88 AND AID2.MATCH_STATUS_FLAG = 'A'
89 AND AID2.LINE_TYPE_LOOKUP_CODE = 'AWT'
90 AND AID2.TAX_CODE_ID = ATC.TAX_ID
91 AND ( ( AID2.GLOBAL_ATTRIBUTE2 is null )
92 OR ( AID2.GLOBAL_ATTRIBUTE2 = P_VENDOR_NUMBER ) ) );
93 RETURN (V_VAT_AMOUNT);
94 END CALCULATE_VAT_AMOUNT;
95
96 FUNCTION CF_VAT_AMOUNTFORMULA(CERTIFICATE_TYPE IN VARCHAR2
97 ,TAX_NAME IN VARCHAR2
98 ,VENDOR_ID IN NUMBER
99 ,VENDOR_NUMBER IN VARCHAR2
100 ,DOCUMENT_NUMBER IN VARCHAR2) RETURN NUMBER IS
101 BEGIN
102 IF CERTIFICATE_TYPE = 'VAT' THEN
103 RETURN (TO_NUMBER(CALCULATE_VAT_AMOUNT(P_VENDOR_NUMBER
104 ,P_VENDOR_ID
105 ,P_TAX_NAME
106 ,DOCUMENT_NUMBER)));
107 ELSE
108 RETURN (0);
109 END IF;
110 RETURN NULL;
111 END CF_VAT_AMOUNTFORMULA;
112
113 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
114 L_CHART_OF_ACCOUNTS_ID GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
115 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
116 L_REPORT_START_DATE DATE;
117 BEGIN
118 L_REPORT_START_DATE := SYSDATE;
119 SELECT
120 NAME,
121 CHART_OF_ACCOUNTS_ID
122 INTO L_NAME,L_CHART_OF_ACCOUNTS_ID
123 FROM
124 GL_SETS_OF_BOOKS G,
125 AP_SYSTEM_PARAMETERS_ALL A
126 WHERE G.SET_OF_BOOKS_ID = A.SET_OF_BOOKS_ID;
127 C_COMPANY_NAME_HEADER := L_NAME;
128 C_CHART_OF_ACCOUNTS_ID := L_CHART_OF_ACCOUNTS_ID;
129 C_REPORT_START_DATE := L_REPORT_START_DATE;
130 RETURN (TRUE);
131 RETURN NULL;
132 EXCEPTION
133 WHEN OTHERS THEN
134 RETURN (FALSE);
135 END GET_COMPANY_NAME;
136
137 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
138 BEGIN
139 RETURN (TRUE);
140 END BEFOREPFORM;
141
142 FUNCTION CF_1FORMULA(COMPANY_NAME IN VARCHAR2) RETURN VARCHAR2 IS
143 BEGIN
144 RETURN (COMPANY_NAME);
145 END CF_1FORMULA;
146
147 FUNCTION CP_1FORMULA(CF_1 IN VARCHAR2) RETURN VARCHAR2 IS
148 BEGIN
149 RETURN (CF_1);
150 END CP_1FORMULA;
151
152 FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
153 BASE_CURR AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
154 PREC FND_CURRENCIES_VL.PRECISION%TYPE;
155 MIN_AU FND_CURRENCIES_VL.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
156 DESCR FND_CURRENCIES_VL.DESCRIPTION%TYPE;
157 BEGIN
158 BASE_CURR := '';
159 PREC := 0;
160 MIN_AU := 0;
161 DESCR := '';
162 SELECT
163 P.BASE_CURRENCY_CODE,
164 C.PRECISION,
165 C.MINIMUM_ACCOUNTABLE_UNIT,
166 C.DESCRIPTION
167 INTO BASE_CURR,PREC,MIN_AU,DESCR
168 FROM
169 AP_SYSTEM_PARAMETERS P,
170 FND_CURRENCIES_VL C
171 WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
172 C_BASE_CURRENCY_CODE := BASE_CURR;
173 C_BASE_PRECISION := PREC;
174 P_MIN_PRECISION := PREC;
175 C_BASE_MIN_ACCT_UNIT := MIN_AU;
176 C_BASE_DESCRIPTION := DESCR;
177 RETURN (TRUE);
178 RETURN NULL;
179 EXCEPTION
180 WHEN OTHERS THEN
181 RETURN (FALSE);
182 END GET_BASE_CURR_DATA;
183
184 FUNCTION AFTERREPORT RETURN BOOLEAN IS
185 BEGIN
186 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
187 RETURN (TRUE);
188 END AFTERREPORT;
189
190 FUNCTION F_NO_DATA_FOUNDFORMATTRIGGER(CS_NO_DATA_FOUND IN NUMBER) RETURN BOOLEAN IS
191 BEGIN
192 IF CS_NO_DATA_FOUND > 0 THEN
193 RETURN (FALSE);
194 END IF;
195 RETURN (TRUE);
196 END F_NO_DATA_FOUNDFORMATTRIGGER;
197
198 FUNCTION P_PERIOD_FROMVALIDTRIGGER RETURN BOOLEAN IS
199 X_START_DATE DATE;
200 BEGIN
201 SELECT
202 START_DATE
203 INTO X_START_DATE
204 FROM
205 GL_PERIODS A,
206 GL_SETS_OF_BOOKS B
207 WHERE B.SET_OF_BOOKS_ID = P_SOB_ID
208 AND A.PERIOD_SET_NAME = B.PERIOD_SET_NAME
209 AND UPPER(A.PERIOD_NAME) = UPPER(P_PERIOD_FROM);
210 P_ACCOUNTING_DATE_FROM := X_START_DATE;
211 RETURN (TRUE);
212 END P_PERIOD_FROMVALIDTRIGGER;
213
214 FUNCTION P_PERIOD_TOVALIDTRIGGER RETURN BOOLEAN IS
215 X_END_DATE DATE;
216 BEGIN
217 SELECT
218 END_DATE
219 INTO X_END_DATE
220 FROM
221 GL_PERIODS A,
222 GL_SETS_OF_BOOKS B
223 WHERE B.SET_OF_BOOKS_ID = P_SOB_ID
224 AND A.PERIOD_SET_NAME = B.PERIOD_SET_NAME
225 AND UPPER(A.PERIOD_NAME) = UPPER(P_PERIOD_TO);
226 P_ACCOUNTING_DATE_TO := X_END_DATE;
227 RETURN (TRUE);
228 END P_PERIOD_TOVALIDTRIGGER;
229
230 FUNCTION F_SELECTED_TAXPAYERID RETURN VARCHAR2 IS
231 RET_TEXT VARCHAR2(1000) := NULL;
232
233 BEGIN
234 IF P_TAX_PAYER_ID_FROM IS NOT NULL THEN
235 RET_TEXT := 'AND PV_1.NUM_1099 BETWEEN :p_tax_payer_id_from ';
236 ELSE
237 RET_TEXT := 'AND PV_1.NUM_1099 BETWEEN PV_1.num_1099 ';
238 END IF;
239 IF P_TAX_PAYER_ID_TO IS NOT NULL THEN
240 RET_TEXT := RET_TEXT || ' and :p_tax_payer_id_to';
241 ELSE
242 RET_TEXT := RET_TEXT || ' and PV_1.num_1099 ';
243 END IF;
244 IF RET_TEXT IS NOT NULL THEN
245 RET_TEXT := RET_TEXT;
246 END IF;
247 RETURN RET_TEXT;
248 END F_SELECTED_TAXPAYERID;
249
250 FUNCTION AFTERPFORM RETURN BOOLEAN IS
251 BEGIN
252
253 P_SELECTED_TAXPAYERID := F_SELECTED_TAXPAYERID;
254 X:=P_PERIOD_FROMVALIDTRIGGER;
255 Y:=P_PERIOD_TOVALIDTRIGGER;
256 RETURN (TRUE);
257 END AFTERPFORM;
258
259 FUNCTION C_LOCATION_ID_P RETURN NUMBER IS
260 BEGIN
261 RETURN C_LOCATION_ID;
262 END C_LOCATION_ID_P;
263
264 FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
265 BEGIN
266 RETURN C_BASE_CURRENCY_CODE;
267 END C_BASE_CURRENCY_CODE_P;
268
269 FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
270 BEGIN
271 RETURN C_BASE_MIN_ACCT_UNIT;
272 END C_BASE_MIN_ACCT_UNIT_P;
273
274 FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
275 BEGIN
276 RETURN C_BASE_PRECISION;
277 END C_BASE_PRECISION_P;
278
279 FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
280 BEGIN
281 RETURN C_CHART_OF_ACCOUNTS_ID;
282 END C_CHART_OF_ACCOUNTS_ID_P;
283
284 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
285 BEGIN
286 RETURN C_COMPANY_NAME_HEADER;
287 END C_COMPANY_NAME_HEADER_P;
288
289 FUNCTION C_BASE_DESCRIPTION_P RETURN NUMBER IS
290 BEGIN
291 RETURN C_BASE_DESCRIPTION;
292 END C_BASE_DESCRIPTION_P;
293
294 FUNCTION C_REPORT_RUN_TIME_P RETURN VARCHAR2 IS
295 BEGIN
296 RETURN C_REPORT_RUN_TIME;
297 END C_REPORT_RUN_TIME_P;
298
299 FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
300 BEGIN
301 RETURN C_REPORT_START_DATE;
302 END C_REPORT_START_DATE_P;
303
304 FUNCTION CP_1_P RETURN VARCHAR2 IS
305 BEGIN
306 RETURN CP_1;
307 END CP_1_P;
308
309 FUNCTION C_WHERE_FLEX_P RETURN VARCHAR2 IS
310 BEGIN
311 RETURN C_WHERE_FLEX;
312 END C_WHERE_FLEX_P;
313
314 FUNCTION C_ALL_SEGS_P RETURN VARCHAR2 IS
315 BEGIN
316 RETURN C_ALL_SEGS;
317 END C_ALL_SEGS_P;
318
319 END JL_JLCOPWHR_XMLP_PKG;
320
321
322
323
[Home] [Help]