1 PACKAGE BODY AP_APXINHIS_XMLP_PKG AS
2 /* $Header: APXINHISB.pls 120.0 2007/12/27 07:54:04 vjaganat noship $ */
3 FUNCTION C_OPEN_BALANCE_AMT1FORMULA(C_TRANSACTION_TYPE3 IN VARCHAR2
4 ,C_OPEN_BALANCE_AMT IN NUMBER
5 ,C_TRX_AMOUNT_SUM IN NUMBER
6 ,C_PREPAY_AMT_F IN NUMBER
7 ,C_TRANSACTION_AMT1_DUMMY_SUM IN NUMBER) RETURN NUMBER IS
8 BEGIN
9 IF C_TRANSACTION_TYPE3 not in ('Gain','Loss') THEN
10 RETURN (NVL(C_OPEN_BALANCE_AMT
11 ,0) + NVL(C_TRX_AMOUNT_SUM
12 ,0) + NVL(C_PREPAY_AMT_F
13 ,0));
14 ELSE
15 RETURN NVL(C_OPEN_BALANCE_AMT
16 ,0) + NVL(C_TRANSACTION_AMT1_DUMMY_SUM
17 ,0) + NVL(C_PREPAY_AMT_F
18 ,0);
19 END IF;
20 END C_OPEN_BALANCE_AMT1FORMULA;
21
22 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
23 BEGIN
24 P_INVOICE_DATE_FROM_T := to_char(P_INVOICE_DATE_FROM,'DD-MON-YY');
25 P_INVOICE_DATE_TO_T := to_char(P_INVOICE_DATE_TO,'DD-MON-YY');
26
27 DECLARE
28 ERRORBUF VARCHAR2(132);
29 INIT_FAILURE EXCEPTION;
30 BEGIN
31 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
32 IF (P_DEBUG_SWITCH = 'Y') THEN
33 NULL;
34 END IF;
35 IF (GET_NLS_STRINGS <> TRUE) THEN
36 RAISE INIT_FAILURE;
37 END IF;
38 SELECT
39 GL.NAME,
40 CUR.PRECISION,
41 GL.CHART_OF_ACCOUNTS_ID,
42 GL.CURRENCY_CODE
43 INTO
44 H_COMPANY_NAME
45 ,P_MIN_PRECISION
46 ,STRUCT_NUM
47 ,H_FUNCTIONAL_CURRENCY
48 FROM
49 GL_SETS_OF_BOOKS GL,
50 FND_CURRENCIES_VL CUR,
51 AP_SYSTEM_PARAMETERS AP
52 WHERE GL.SET_OF_BOOKS_ID = AP.SET_OF_BOOKS_ID
53 AND GL.CURRENCY_CODE = CUR.CURRENCY_CODE
54 AND GL.SET_OF_BOOKS_ID = P_BOOK;
55 IF (P_DEBUG_SWITCH = 'Y') THEN
56 NULL;
57 END IF;
58 /*LP_VENDOR_ID := NULL;
59 LP_VENDOR_SITE := NULL;
60 LP_INVOICES := NULL;
61 LP_INVOICES_NUMBER_FROM := NULL;
62 LP_INVOICES_NUMBER_TO := NULL;
63 LP_DOC_SEQUENCE_NAME := NULL;
64 LP_DOC_SEQUENCE_NUMBER_FROM := NULL;
65 LP_DOC_SEQUENCE_NUMBER_TO := NULL;
66 LP_INVOICE_DATE_FROM := NULL;
67 LP_INVOICE_DATE_TO := NULL;*/
68 LP_VENDOR_ID := ' ';
69 LP_VENDOR_SITE := ' ';
70 LP_INVOICES := ' ';
71 LP_INVOICES_NUMBER_FROM := ' ';
72 LP_INVOICES_NUMBER_TO := ' ';
73 LP_DOC_SEQUENCE_NAME := ' ';
74 LP_DOC_SEQUENCE_NUMBER_FROM := ' ';
75 LP_DOC_SEQUENCE_NUMBER_TO := ' ';
76 LP_INVOICE_DATE_FROM := ' ';
77 LP_INVOICE_DATE_TO := ' ';
78 IF (P_VENDOR_ID IS NOT NULL) THEN
79 LP_VENDOR_ID := 'and pv1.vendor_id = ' || TO_CHAR(P_VENDOR_ID);
80 SELECT
81 VENDOR_NAME
82 INTO
83 P_VENDOR_NAME
84 FROM
85 PO_VENDORS
86 WHERE VENDOR_ID = P_VENDOR_ID;
87 END IF;
88 IF (P_VENDOR_SITE IS NOT NULL) THEN
89 LP_VENDOR_SITE := 'and pvs.vendor_site_code = ''' || P_VENDOR_SITE || '''';
90 END IF;
91 IF (P_INVOICES_NUMBER_FROM IS NOT NULL) THEN
92 LP_INVOICES_NUMBER_FROM := 'and i.invoice_num >= ''' || P_INVOICES_NUMBER_FROM || '''';
93 END IF;
94 IF (P_INVOICES_NUMBER_TO IS NOT NULL) THEN
95 LP_INVOICES_NUMBER_TO := 'and i.invoice_num <= ''' || P_INVOICES_NUMBER_TO || '''';
96 END IF;
97 IF (P_DOC_SEQUENCE_NAME IS NOT NULL) THEN
98 LP_DOC_SEQUENCE_NAME := 'and f2.name = ''' || P_DOC_SEQUENCE_NAME || '''';
99 END IF;
100 IF (P_DOC_SEQUENCE_NUMBER_FROM IS NOT NULL) THEN
101 LP_DOC_SEQUENCE_NUMBER_FROM := 'and i.doc_sequence_value >= ''' || P_DOC_SEQUENCE_NUMBER_FROM || '''';
102 END IF;
103 IF (P_DOC_SEQUENCE_NUMBER_TO IS NOT NULL) THEN
104 LP_DOC_SEQUENCE_NUMBER_TO := 'and i.doc_sequence_value <= ''' || P_DOC_SEQUENCE_NUMBER_TO || '''';
105 END IF;
106 IF (P_INVOICE_DATE_FROM IS NOT NULL) THEN
107 LP_INVOICE_DATE_FROM := 'and i.invoice_date >= ''' || TO_CHAR(P_INVOICE_DATE_FROM) || ''' ';
108 END IF;
109 IF (P_INVOICE_DATE_TO IS NOT NULL) THEN
110 LP_INVOICE_DATE_TO := 'and i.invoice_date <= ''' || TO_CHAR(P_INVOICE_DATE_TO) || ''' ';
111 END IF;
112 IF (P_INVOICES = 'Y') THEN
113 LP_INVOICES := 'and i.invoice_type_lookup_code = ''' || P_PREPAYMENT || '''';
114 END IF;
115 EXCEPTION
116 WHEN OTHERS THEN
117 ERRORBUF := SQLERRM(SQLCODE);
118 RAISE_APPLICATION_ERROR(-20101
119 ,NULL);
120 END;
121 RETURN (TRUE);
122 END BEFOREREPORT;
123
124 FUNCTION AFTERREPORT RETURN BOOLEAN IS
125 BEGIN
126 BEGIN
127 IF (P_DEBUG_SWITCH = 'Y') THEN
128 NULL;
129 END IF;
130 EXCEPTION
131 WHEN OTHERS THEN
132 RAISE_APPLICATION_ERROR(-20101
133 ,NULL);
134 END;
135 RETURN (TRUE);
136 END AFTERREPORT;
137
138 FUNCTION C_VENDOR_SITE_TOTALFORMULA(C_TOTAL_INVOICE_AMOUNT_SUM IN NUMBER
139 ,C_TOTAL_PAYMENT_AMOUNT_SUM IN NUMBER
140 ,C_TOTAL_PREPAY_AMT_SUM IN NUMBER) RETURN NUMBER IS
141 BEGIN
142 RETURN (NVL(C_TOTAL_INVOICE_AMOUNT_SUM
143 ,0) + NVL(C_TOTAL_PAYMENT_AMOUNT_SUM
144 ,0) + NVL(C_TOTAL_PREPAY_AMT_SUM
145 ,0));
146 END C_VENDOR_SITE_TOTALFORMULA;
147
148 FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
149 BEGIN
150 FND_MESSAGE.SET_NAME('SQLAP'
151 ,'AP_APPRVL_NO_DATA');
152 -- C_NLS_NO_DATA_EXISTS := ' ' || C_NLS_NO_DATA_EXISTS || ' ';
153 FND_MESSAGE.SET_NAME('SQLAP'
154 ,'AP_ALL_END_OF_REPORT');
155 -- C_NLS_END_OF_REPORT := ' ' || C_NLS_END_OF_REPORT || ' ';
156
157 RETURN (TRUE);
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN (FALSE);
161 END GET_NLS_STRINGS;
162
163 FUNCTION C_CURRENCY_TOTALFORMULA(C_TOTAL_INVOICE_AMT_CURR IN NUMBER
164 ,C_TOTAL_PAYMENT_AMT_CURR IN NUMBER
165 ,C_TOTAL_PREPAY_AMT IN NUMBER) RETURN NUMBER IS
166 BEGIN
167 RETURN (NVL(C_TOTAL_INVOICE_AMT_CURR
168 ,0) + NVL(C_TOTAL_PAYMENT_AMT_CURR
169 ,0) + NVL(C_TOTAL_PREPAY_AMT
170 ,0));
171 END C_CURRENCY_TOTALFORMULA;
172
173 FUNCTION T_VENDOR_TOTALFORMULA(T_TOTAL_INV_AMT IN NUMBER
174 ,T_TOTAL_PAY_AMT IN NUMBER
175 ,T_TOTAL_PREPAY_AMT IN NUMBER) RETURN NUMBER IS
176 BEGIN
177 RETURN (NVL(T_TOTAL_INV_AMT
178 ,0) + NVL(T_TOTAL_PAY_AMT
179 ,0) + NVL(T_TOTAL_PREPAY_AMT
180 ,0));
181 END T_VENDOR_TOTALFORMULA;
182
183 FUNCTION T_REPORT_TOTALFORMULA(T_REPORT_INV_AMT IN NUMBER
184 ,T_REPORT_PAY_AMT IN NUMBER
185 ,T_REPORT_PREPAY_AMT IN NUMBER) RETURN NUMBER IS
186 BEGIN
187 RETURN (NVL(T_REPORT_INV_AMT
188 ,0) + NVL(T_REPORT_PAY_AMT
189 ,0) + NVL(T_REPORT_PREPAY_AMT
190 ,0));
191 END T_REPORT_TOTALFORMULA;
192
193 FUNCTION C_TRANSACTION_AMT1_DUMFORMULA(C_TRANSACTION_TYPE3 IN VARCHAR2
194 ,C_TRANSACTION_AMT1 IN NUMBER) RETURN NUMBER IS
195 BEGIN
196 IF C_TRANSACTION_TYPE3 not in ('Gain','Loss') THEN
197 RETURN (NVL(C_TRANSACTION_AMT1
198 ,0));
199 ELSE
200 RETURN 0;
201 END IF;
202 END C_TRANSACTION_AMT1_DUMFORMULA;
203
204 FUNCTION T_TRANSACTION_AMT_DUMFORMULA(T_TRANSACTION_TYPE4 IN VARCHAR2
205 ,T_TRANSACTION_AMT IN NUMBER) RETURN NUMBER IS
206 BEGIN
207 IF T_TRANSACTION_TYPE4 not in ('Gain','Loss') THEN
208 RETURN (NVL(T_TRANSACTION_AMT
209 ,0));
210 ELSE
211 RETURN 0;
212 END IF;
213 END T_TRANSACTION_AMT_DUMFORMULA;
214
215 FUNCTION T_TRANSACTION_AMT1_DUMFORMULA(T_TRANSACTION_TYPE3 IN VARCHAR2
216 ,T_TRANSACTION_AMT2 IN NUMBER) RETURN NUMBER IS
217 BEGIN
218 IF T_TRANSACTION_TYPE3 not in ('Gain','Loss') THEN
219 RETURN (NVL(T_TRANSACTION_AMT2
220 ,0));
221 ELSE
222 RETURN 0;
223 END IF;
224 END T_TRANSACTION_AMT1_DUMFORMULA;
225
226 FUNCTION P_TRX_AMT_SUM_FFORMULA(P_TRX_AMOUNT_SUM IN NUMBER
227 ,C_TOTAL_INVOICE_AMT IN NUMBER) RETURN NUMBER IS
228 BEGIN
229 RETURN P_TRX_AMOUNT_SUM + C_TOTAL_INVOICE_AMT;
230 END P_TRX_AMT_SUM_FFORMULA;
231
232 FUNCTION C_PREPAY_AMT_FFORMULA(C_INVOICE_ID1 IN NUMBER) RETURN NUMBER IS
233 PREPAY_AMT_F NUMBER;
234 BEGIN
235 SELECT
236 SUM(NVL(AP_UTILITIES_PKG.AP_ROUND_CURRENCY(NVL(AID1.AMOUNT
237 ,0) * I2.PAYMENT_CROSS_RATE
238 ,I2.PAYMENT_CURRENCY_CODE)
239 ,0))
240 INTO
241 PREPAY_AMT_F
242 FROM
243 AP_INVOICE_DISTRIBUTIONS AID1,
244 AP_INVOICE_DISTRIBUTIONS AID2,
245 AP_INVOICES I2
246 WHERE NVL(AID1.REVERSAL_FLAG
247 ,'N') <> 'Y'
248 AND AID1.PREPAY_DISTRIBUTION_ID is not null
249 AND AID2.INVOICE_DISTRIBUTION_ID = AID1.PREPAY_DISTRIBUTION_ID
250 AND AID2.INVOICE_ID = I2.INVOICE_ID
251 AND AID1.INVOICE_ID = C_INVOICE_ID1;
252 RETURN (PREPAY_AMT_F);
253 END C_PREPAY_AMT_FFORMULA;
254
255 FUNCTION T_PREPAY_AMT_FFORMULA(T_INVOICE_ID IN NUMBER) RETURN NUMBER IS
256 T_PREPAY_AMT_F NUMBER;
257 BEGIN
258 SELECT
259 SUM(NVL(AP_UTILITIES_PKG.AP_ROUND_CURRENCY(NVL(AID1.AMOUNT
260 ,0) * I2.PAYMENT_CROSS_RATE
261 ,I2.PAYMENT_CURRENCY_CODE)
262 ,0))
263 INTO
264 T_PREPAY_AMT_F
265 FROM
266 AP_INVOICE_DISTRIBUTIONS AID1,
267 AP_INVOICE_DISTRIBUTIONS AID2,
268 AP_INVOICES I2
269 WHERE NVL(AID1.REVERSAL_FLAG
270 ,'N') <> 'Y'
271 AND AID1.PREPAY_DISTRIBUTION_ID is not null
272 AND AID2.INVOICE_DISTRIBUTION_ID = AID1.PREPAY_DISTRIBUTION_ID
273 AND AID2.INVOICE_ID = I2.INVOICE_ID
274 AND AID1.INVOICE_ID = T_INVOICE_ID;
275 RETURN (T_PREPAY_AMT_F);
276 END T_PREPAY_AMT_FFORMULA;
277
278 FUNCTION T_PREPAY_AMT_F1FORMULA(T_INVOICE_ID1 IN NUMBER) RETURN NUMBER IS
279 T1_PREPAY_AMT_F NUMBER;
280 BEGIN
281 SELECT
282 SUM(NVL(AP_UTILITIES_PKG.AP_ROUND_CURRENCY(NVL(AID1.AMOUNT
283 ,0) * I2.PAYMENT_CROSS_RATE
284 ,I2.PAYMENT_CURRENCY_CODE)
285 ,0))
286 INTO
287 T1_PREPAY_AMT_F
288 FROM
289 AP_INVOICE_DISTRIBUTIONS AID1,
290 AP_INVOICE_DISTRIBUTIONS AID2,
291 AP_INVOICES I2
292 WHERE NVL(AID1.REVERSAL_FLAG
293 ,'N') <> 'Y'
294 AND AID1.PREPAY_DISTRIBUTION_ID is not null
295 AND AID2.INVOICE_DISTRIBUTION_ID = AID1.PREPAY_DISTRIBUTION_ID
296 AND AID2.INVOICE_ID = I2.INVOICE_ID
297 AND AID1.INVOICE_ID = T_INVOICE_ID1;
298 RETURN (T1_PREPAY_AMT_F);
299 END T_PREPAY_AMT_F1FORMULA;
300
301 FUNCTION STRUCT_NUM_P RETURN VARCHAR2 IS
302 BEGIN
303 RETURN STRUCT_NUM;
304 END STRUCT_NUM_P;
305
306 FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
307 BEGIN
308 RETURN C_NLS_NO_DATA_EXISTS;
309 END C_NLS_NO_DATA_EXISTS_P;
310
311 FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
312 BEGIN
313 RETURN C_NLS_END_OF_REPORT;
314 END C_NLS_END_OF_REPORT_P;
315
316 FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
317 BEGIN
318 RETURN C_NLS_YES;
319 END C_NLS_YES_P;
320
321 FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
322 BEGIN
323 RETURN C_NLS_NO;
324 END C_NLS_NO_P;
325
326 FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
327 BEGIN
328 RETURN C_NLS_ALL;
329 END C_NLS_ALL_P;
330
331 END AP_APXINHIS_XMLP_PKG;
332
333
[Home] [Help]