1 PACKAGE BODY JG_JGZZSRCR_XMLP_PKG AS
2 /* $Header: JGZZSRCRB.pls 120.2.12010000.2 2008/08/04 13:51:27 vgadde ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 INIT_FAILURE EXCEPTION;
7 BEGIN
8 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
9 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
10 BEGIN
11 SELECT
12 GLP.CHART_OF_ACCOUNTS_ID,
13 GLP.LEDGER_ID,
14 C.PRECISION,
15 C.CURRENCY_CODE
16 INTO C_COAI,C_SOB,C_PRECISION,C_FUNCT_CURR
17 FROM
18 AP_SYSTEM_PARAMETERS_ALL ASP,
19 GL_LEDGERS_PUBLIC_V GLP,
20 FND_CURRENCIES C
21 WHERE ASP.SET_OF_BOOKS_ID = GLP.LEDGER_ID
22 AND ASP.BASE_CURRENCY_CODE = C.CURRENCY_CODE
23 AND ASP.SET_OF_BOOKS_ID = DECODE(P_REPORTING_LEVEL
24 ,'1000'
25 ,P_REPORTING_ENTITY_ID
26 ,GLP.LEDGER_ID)
27 AND ASP.ORG_ID = DECODE(P_REPORTING_LEVEL
28 ,'3000'
29 ,P_REPORTING_ENTITY_ID
30 ,ASP.ORG_ID);
31 EXCEPTION
32 WHEN NO_DATA_FOUND THEN
33 RAISE INIT_FAILURE;
34 END;
35 BEGIN
36 SELECT
37 USER_CONCURRENT_PROGRAM_NAME
38 INTO C_TITLE
39 FROM
40 FND_CONCURRENT_REQUESTS R,
41 FND_CONCURRENT_PROGRAMS_VL P
42 WHERE R.REQUEST_ID = P_CONC_REQUEST_ID
43 AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
44 AND R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 C_TITLE := 'Regional Cash Requirement Report';
48 END;
49 IF (P_DEBUG_SWITCH = 'Y') THEN
50 /*SRW.MESSAGE('1'
51 ,'After select coai')*/NULL;
52 END IF;
53 IF (NLS_PARAMETERS = FALSE) THEN
54 RAISE INIT_FAILURE;
55 END IF;
56 IF (P_DEBUG_SWITCH = 'Y') THEN
57 /*SRW.MESSAGE('1'
58 ,'After select nls parameters.')*/NULL;
59 END IF;
60 IF (SQL_PAYMENT_GROUP_F = FALSE) THEN
61 RAISE INIT_FAILURE;
62 END IF;
63 IF (P_DEBUG_SWITCH = 'Y') THEN
64 /*SRW.MESSAGE('1'
65 ,'After sql_payment_group')*/NULL;
66 END IF;
67 IF (SQL_CURRENCY = FALSE) THEN
68 RAISE INIT_FAILURE;
69 END IF;
70 IF (P_DEBUG_SWITCH = 'Y') THEN
71 /*SRW.MESSAGE('1'
72 ,'After sql_currency')*/NULL;
73 END IF;
74 IF (SQL_VENDOR = FALSE) THEN
75 RAISE INIT_FAILURE;
76 END IF;
77 IF (P_DEBUG_SWITCH = 'Y') THEN
78 /*SRW.MESSAGE('1'
79 ,'After sql_vendor')*/NULL;
80 END IF;
81 IF (SQL_ONLY_PAST = FALSE) THEN
82 RAISE INIT_FAILURE;
83 END IF;
84 IF (P_DEBUG_SWITCH = 'Y') THEN
85 /*SRW.MESSAGE('1'
86 ,'After sql_only_past')*/NULL;
87 END IF;
88 IF (SQL_DISTRIBUTIONS_F = FALSE) THEN
89 RAISE INIT_FAILURE;
90 END IF;
91 IF (P_DEBUG_SWITCH = 'Y') THEN
92 /*SRW.MESSAGE('1'
93 ,'After sql_distributions')*/NULL;
94 END IF;
95 IF (SQL_PAYMENTS = FALSE) THEN
96 RAISE INIT_FAILURE;
97 END IF;
98 IF (P_DEBUG_SWITCH = 'Y') THEN
99 /*SRW.MESSAGE('1'
100 ,'After sql_payments')*/NULL;
101 /*SRW.BREAK*/NULL;
102 END IF;
103 CP_PARTIAL_LEDGER_MSG := P_PARTIAL_LEDGER_MSG;
104 EXCEPTION
105 WHEN OTHERS THEN
106 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
107 END;
108 RETURN (TRUE);
109 END BEFOREREPORT;
110
111 FUNCTION AFTERREPORT RETURN BOOLEAN IS
112 BEGIN
113 BEGIN
114 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
115 END;
116 RETURN (TRUE);
117 END AFTERREPORT;
118
119 FUNCTION C_INV_OPEN_AMOUNTFORMULA(C_INV_GROSS_AMOUNT IN NUMBER
120 ,C_INV_PAY_AMOUNT0 IN NUMBER
121 ,C_INV_DISCOUNT_TAKEN0 IN NUMBER
122 ,C_INV_PAY_AMOUNT IN NUMBER
123 ,C_INV_DISCOUNT_TAKEN IN NUMBER) RETURN NUMBER IS
124 BEGIN
125 IF P_GROUP_FIELD = 'T' THEN
126 RETURN ((C_INV_GROSS_AMOUNT - C_INV_PAY_AMOUNT0 - C_INV_DISCOUNT_TAKEN0));
127 ELSE
128 RETURN ((C_INV_GROSS_AMOUNT - C_INV_PAY_AMOUNT - C_INV_DISCOUNT_TAKEN));
129 END IF;
130 END C_INV_OPEN_AMOUNTFORMULA;
131
132 FUNCTION C_INV_OPEN_BASEFORMULA(C_INV_OPEN_AMOUNT IN NUMBER
133 ,EXCHANGE_RATE IN NUMBER
134 ,PAYMENT_CROSS_RATE IN NUMBER) RETURN NUMBER IS
135 BEGIN
136 IF P_GROUP_FIELD = 'T' THEN
137 RETURN ROUND((C_INV_OPEN_AMOUNT * EXCHANGE_RATE)
138 ,C_PRECISION);
139 ELSE
140 RETURN ROUND(((C_INV_OPEN_AMOUNT / PAYMENT_CROSS_RATE) * EXCHANGE_RATE)
141 ,C_PRECISION);
142 END IF;
143 END C_INV_OPEN_BASEFORMULA;
144
145 FUNCTION C_CUR_OPEN_AMOUNTFORMULA(C_CUR_GROSS_AMOUNT IN NUMBER
146 ,C_CUR_PAY_AMOUNT IN NUMBER
147 ,C_CUR_DISCOUNT_TAKEN IN NUMBER) RETURN NUMBER IS
148 BEGIN
149 RETURN (C_CUR_GROSS_AMOUNT - C_CUR_PAY_AMOUNT - C_CUR_DISCOUNT_TAKEN);
150 END C_CUR_OPEN_AMOUNTFORMULA;
151
152 FUNCTION C_CUR_OPEN_BASEFORMULA(C_CUR_OPEN_AMOUNT IN NUMBER
153 ,EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
154 BEGIN
155 RETURN (C_CUR_OPEN_AMOUNT * EXCHANGE_RATE);
156 END C_CUR_OPEN_BASEFORMULA;
157
158 FUNCTION C_SCH_OPEN_AMOUNTFORMULA(GROSS_AMOUNT IN NUMBER
159 ,C_SCH_PAY_AMOUNT IN NUMBER
160 ,C_SCH_DISCOUNT_TAKEN IN NUMBER) RETURN NUMBER IS
161 BEGIN
162 RETURN (GROSS_AMOUNT - C_SCH_PAY_AMOUNT - C_SCH_DISCOUNT_TAKEN);
163 END C_SCH_OPEN_AMOUNTFORMULA;
164
165 FUNCTION C_SCH_OPEN_BASEFORMULA(C_SCH_OPEN_AMOUNT IN NUMBER
166 ,EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
167 BEGIN
168 RETURN (C_SCH_OPEN_AMOUNT * EXCHANGE_RATE);
169 END C_SCH_OPEN_BASEFORMULA;
170
171 FUNCTION C_GRP_OPEN_BASEFORMULA(C_GRP_GROSS_BASE IN NUMBER
172 ,C_GRP_PAY_BASE IN NUMBER
173 ,C_GRP_DISCOUNT_TAKEN_BASE IN NUMBER
174 ,C_GRP_GAINLOSS IN NUMBER) RETURN NUMBER IS
175 BEGIN
176 RETURN (C_GRP_GROSS_BASE - C_GRP_PAY_BASE - C_GRP_DISCOUNT_TAKEN_BASE + C_GRP_GAINLOSS);
177 END C_GRP_OPEN_BASEFORMULA;
178
179 FUNCTION C_VEN_OPEN_AMOUNTFORMULA(C_VEN_GROSS_AMOUNT IN NUMBER
180 ,C_VEN_PAY_AMOUNT IN NUMBER
181 ,C_VEN_DISCOUNT_TAKEN IN NUMBER) RETURN NUMBER IS
182 BEGIN
183 RETURN (C_VEN_GROSS_AMOUNT - C_VEN_PAY_AMOUNT - C_VEN_DISCOUNT_TAKEN);
184 END C_VEN_OPEN_AMOUNTFORMULA;
185
186 FUNCTION C_VEN_OPEN_BASEFORMULA(C_VEN_OPEN_AMOUNT IN NUMBER
187 ,EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
188 BEGIN
189 RETURN (C_VEN_OPEN_AMOUNT * EXCHANGE_RATE);
190 END C_VEN_OPEN_BASEFORMULA;
191
192 FUNCTION C_RPT_OPEN_BASEFORMULA(C_RPT_INV_BASE IN NUMBER
193 ,C_RPT_PAY_BASE IN NUMBER
194 ,C_RPT_DISCOUNT_TAKEN_BASE IN NUMBER
195 ,C_RPT_GAINLOSS IN NUMBER) RETURN NUMBER IS
196 BEGIN
197 RETURN (C_RPT_INV_BASE - C_RPT_PAY_BASE - C_RPT_DISCOUNT_TAKEN_BASE + C_RPT_GAINLOSS);
198 END C_RPT_OPEN_BASEFORMULA;
199
200 FUNCTION C_VENDOR_NAMEFORMULA RETURN VARCHAR2 IS
201 BEGIN
202 DECLARE
203 APU2 PO_VENDORS.VENDOR_NAME%TYPE;
204 BEGIN
205 SELECT
206 SEGMENT1 || ' ' || VENDOR_NAME
207 INTO APU2
208 FROM
209 PO_VENDORS
210 WHERE VENDOR_ID = NVL(P_VENDOR_ID
211 ,-1);
212 RETURN (APU2);
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 RETURN (C_ALL);
216 END;
217 RETURN NULL;
218 END C_VENDOR_NAMEFORMULA;
219
220 FUNCTION C_FLEXPROMPTFORMULA RETURN VARCHAR2 IS
221 BEGIN
222 DECLARE
223 L_GROUP VARCHAR2(80);
224 BEGIN
225 SELECT
226 MEANING
227 INTO L_GROUP
228 FROM
229 FND_LOOKUPS
230 WHERE LOOKUP_TYPE = 'JGZZ_AP_INVOICE_GROUP'
231 AND LOOKUP_CODE = P_GROUP_FIELD;
232 RETURN (L_GROUP);
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 RETURN ('');
236 END;
237 RETURN NULL;
238 END C_FLEXPROMPTFORMULA;
239
240 FUNCTION C_SOB_NAMEFORMULA RETURN VARCHAR2 IS
241 BEGIN
242 DECLARE
243 APU VARCHAR2(40);
244 BEGIN
245 SELECT
246 GLP.NAME
247 INTO APU
248 FROM
249 GL_LEDGERS_PUBLIC_V GLP,
250 AP_SYSTEM_PARAMETERS_ALL ASP
251 WHERE GLP.LEDGER_ID = ASP.SET_OF_BOOKS_ID
252 AND ASP.SET_OF_BOOKS_ID = DECODE(P_REPORTING_LEVEL
253 ,'1000'
254 ,P_REPORTING_ENTITY_ID
255 ,GLP.LEDGER_ID)
256 AND ASP.ORG_ID = DECODE(P_REPORTING_LEVEL
257 ,'3000'
258 ,P_REPORTING_ENTITY_ID
259 ,ASP.ORG_ID);
260 RETURN (APU);
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 RETURN ('*ERROR*');
264 END;
265 RETURN NULL;
266 END C_SOB_NAMEFORMULA;
267
268 FUNCTION SQL_CURRENCY RETURN BOOLEAN IS
269 BEGIN
270 IF P_INVOICE_CURRENCY IS NOT NULL THEN
271 IF P_GROUP_FIELD = 'T' THEN
272 SQL_INVOICE_CURRENCY := 'and i.invoice_currency_code=''' || P_INVOICE_CURRENCY || '''';
273 ELSE
274 SQL_INVOICE_CURRENCY := 'and i.payment_currency_code=''' || P_INVOICE_CURRENCY || '''';
275 END IF;
276 END IF;
277 IF (P_DEBUG_SWITCH = 'Y') THEN
278 /*SRW.MESSAGE('801'
279 ,'SQL_INVOICE_CURRENCY = ' || SQL_INVOICE_CURRENCY)*/NULL;
280 END IF;
281 RETURN (TRUE);
282 END SQL_CURRENCY;
283
284 FUNCTION SQL_VENDOR RETURN BOOLEAN IS
285 BEGIN
286 IF P_VENDOR_ID IS NOT NULL THEN
287 SQL_VENDOR_ID := 'and i.vendor_id=' || TO_CHAR(P_VENDOR_ID);
288 END IF;
289 IF (P_DEBUG_SWITCH = 'Y') THEN
290 /*SRW.MESSAGE('501'
291 ,'SQL_VENDOR_ID = ' || SQL_VENDOR_ID)*/NULL;
292 END IF;
293 RETURN (TRUE);
294 END SQL_VENDOR;
295
296 FUNCTION SQL_ONLY_PAST RETURN BOOLEAN IS
297 BEGIN
298 P_CUT_DATE_v := P_CUT_DATE;
299 IF P_CUT_DATE_v IS NULL THEN
300 P_CUT_DATE_v := SYSDATE;
301 END IF;
302 IF P_ONLY_PAST_FLAG = 'Y' THEN
303 SQL_ONLY_PAST_FLAG2 := ' and DECODE( sign( nvl(ps.discount_date,to_date(''' || TO_CHAR(P_CUT_DATE
304 ,'DD/MM/YYYY') || ''',''DD/MM/YYYY'') -1) - to_date(''' || TO_CHAR(P_CUT_DATE
305 ,'DD/MM/YYYY') || ''',''DD/MM/YYYY'')), ' || ' -1, to_char(ps.due_date,''YYYYMMDD''), ' ||
306 ' 0,to_char(ps.discount_date,''YYYYMMDD''),
307 ' || ' to_char(ps.discount_date,''YYYYMMDD'')) <= ''' || TO_CHAR(P_CUT_DATE
308 ,'YYYYMMDD') || ''' ';
309 SQL_ONLY_PAST_FLAG := 'and exists (select null from ap_payment_schedules ps2 ' || ' where ps2.invoice_id=i.invoice_id ' ||
310 ' AND DECODE( sign( nvl(ps2.discount_date,to_date(''' || TO_CHAR(P_CUT_DATE
311 ,'DD/MM/YYYY') || ''',''DD/MM/YYYY'') -1) - to_date(''' || TO_CHAR(P_CUT_DATE
312 ,'DD/MM/YYYY') || ''',''DD/MM/YYYY'')), ' || ' -1, to_char(ps2.due_date,''YYYYMMDD''),
313 ' || ' 0, to_char(ps2.discount_date,''YYYYMMDD''),
314 ' || ' to_char(ps2.discount_date,''YYYYMMDD'')) <= ''' || TO_CHAR(P_CUT_DATE
315 ,'YYYYMMDD') || ''' )';
316 END IF;
317 IF (P_DEBUG_SWITCH = 'Y') THEN
318 /*SRW.MESSAGE('701'
319 ,'SQL_ONLY_PAST_FLAG = ' || SQL_ONLY_PAST_FLAG)*/NULL;
320 /*SRW.MESSAGE('702'
321 ,'SQL_ONLY_PAST_FLAG2 = ' || SQL_ONLY_PAST_FLAG2)*/NULL;
322 /*SRW.MESSAGE('703'
323 ,'P_CUT_DATE = ' || TO_CHAR(P_CUT_DATE
324 ,'DD-MON-YYYY'))*/NULL;
325 END IF;
326 RETURN (TRUE);
327 END SQL_ONLY_PAST;
328
329 FUNCTION SQL_PAYMENT_GROUP_F RETURN BOOLEAN IS
330
331 BEGIN
332
333 IF P_PAYMENT_GROUP IS NOT NULL THEN
334 IF SUBSTR(P_GROUP_FIELD,1,1)='T' THEN
335 SQL_PAYMENT_GROUP_WH:='and &'||'SQL_PAYMENT_GROUP';
336 ELSE
337 SQL_PAYMENT_GROUP_WH:='and i.pay_group_lookup_code='''||P_PAYMENT_GROUP||'''';
338
339 END IF;
340 END IF;
341
342 RETURN (TRUE);
343 END SQL_PAYMENT_GROUP_F;
344
345
346
347 FUNCTION SQL_DISTRIBUTIONS_F RETURN BOOLEAN IS
348 ENCUMBRANCE_FLAG VARCHAR2(1);
349 BEGIN
350 SELECT
351 PURCH_ENCUMBRANCE_FLAG
352 INTO ENCUMBRANCE_FLAG
353 FROM
354 FINANCIALS_SYSTEM_PARAMETERS
355 WHERE SET_OF_BOOKS_ID = DECODE(P_REPORTING_LEVEL
356 ,'1000'
357 ,P_REPORTING_ENTITY_ID
358 ,SET_OF_BOOKS_ID)
359 AND ORG_ID = DECODE(P_REPORTING_LEVEL
360 ,'3000'
361 ,P_REPORTING_ENTITY_ID
362 ,ORG_ID);
363 IF P_MATCH_STATUS_FLAG_V = 'A' THEN
364 SQL_DISTRIBUTIONS := 'and not exists' ||
365 '((select aid1.invoice_id from ap_invoice_distributions aid1' ||
366 ' where aid1.invoice_id = id.invoice_id';
367 IF NVL(ENCUMBRANCE_FLAG
368 ,'N') = 'Y' THEN
369 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || ' and nvl(aid1.match_status_flag,''N'') <> ''A'') ';
370 END IF;
371 IF NVL(ENCUMBRANCE_FLAG
372 ,'N') = 'N' THEN
373 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || ' AND nvl(aid1.match_status_flag,''N'') not in (''A'', ''T'')) ';
374 END IF;
375 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || ' UNION' ||
376 ' (select aih1.invoice_id from ap_holds aih1' ||
377 ' where aih1.invoice_id = id.invoice_id' ||
378 ' and aih1.release_lookup_code is null))' ||
379 'and exists' || '(select null from ap_invoice_distributions aid2' ||
380 ' where aid2.invoice_id = id.invoice_id)';
381 END IF;
382 IF P_MATCH_STATUS_FLAG_V = 'N' THEN
383 SQL_DISTRIBUTIONS := 'and exists' || '((select aid2.invoice_id from ap_invoice_distributions aid2' ||
384 ' where aid2.invoice_id = id.invoice_id';
385 IF NVL(ENCUMBRANCE_FLAG
386 ,'N') = 'Y' THEN
387 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || ' and nvl(aid2.match_status_flag,''N'') <> ''A'')';
388 END IF;
389 IF NVL(ENCUMBRANCE_FLAG
390 ,'N') = 'N' THEN
391 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || ' AND nvl(aid2.match_status_flag,''N'') not in (''A'', ''T'')) ';
392 END IF;
393 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || ' UNION' || ' (select aih2.invoice_id from ap_holds aih2' ||
394 ' where aih2.invoice_id = id.invoice_id' || ' and aih2.release_lookup_code is null))';
395 END IF;
396 IF P_INV_POSTED_FLAG IS NOT NULL THEN
397 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || 'and nvl(ach1.gl_transfer_flag,''N'')=''' || P_INV_POSTED_FLAG || ''' ';
398 END IF;
399 IF P_CUT_DATE_v IS NOT NULL THEN
400 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || 'and to_char(nvl(id.accounting_date,sysdate),''YYYYMMDD'') <= ''' || TO_CHAR(P_CUT_DATE_v
401 ,'YYYYMMDD') || ''' ';
402 END IF;
403 IF P_INVOICE_PERIOD IS NOT NULL THEN
404 SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || 'and id.period_name=''' || P_INVOICE_PERIOD || '''';
405 END IF;
406 IF (P_DEBUG_SWITCH = 'Y') THEN
407 /*SRW.MESSAGE('601'
408 ,'SQL_DISTRIBUTIONS = ' || SQL_DISTRIBUTIONS)*/NULL;
409 END IF;
410 RETURN (TRUE);
411 END SQL_DISTRIBUTIONS_F;
412
413 FUNCTION SQL_PAYMENTS RETURN BOOLEAN IS
414 L_START_DATE DATE;
415 BEGIN
416 IF P_CUT_DATE_v IS NULL THEN
417 P_CUT_DATE_v := SYSDATE;
418 END IF;
419 SQL_PAYMENTS1 := '';
420 SQL_PAYMENTS2 := '';
421 SQL_PAYMENTS3 := '';
422 P_ONLY_OPEN_INVOICES_V := P_ONLY_OPEN_INVOICES;
423 P_ONLY_PAID_INVOICES_V := P_ONLY_PAID_INVOICES;
424 IF P_ONLY_OPEN_INVOICES_v = 'N' AND P_ONLY_PAID_INVOICES_v = 'N' THEN
425 P_ONLY_OPEN_INVOICES_v := 'Y';
426 END IF;
427 IF P_PAYMENT_PERIOD IS NOT NULL THEN
428 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and ip.period_name=''' || P_PAYMENT_PERIOD || ''' ';
429 P_ONLY_PAID_INVOICES_v := 'Y';
430 P_ONLY_OPEN_INVOICES_v := 'N';
431 END IF;
432 IF P_CHECK_VOUCHER IS NOT NULL THEN
433 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and c.doc_sequence_value=''' || P_CHECK_VOUCHER || ''' ';
434 P_ONLY_PAID_INVOICES_v := 'Y';
435 P_ONLY_OPEN_INVOICES_v := 'N';
436 END IF;
437 IF P_CHECKRUN_NAME IS NOT NULL THEN
438 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and c.checkrun_name=''' || P_CHECKRUN_NAME || ''' ';
439 P_ONLY_PAID_INVOICES_v := 'Y';
440 P_ONLY_OPEN_INVOICES_v := 'N';
441 END IF;
442 IF P_CONFIRMED = 'N' THEN
443 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and to_char(ip.accounting_date,''DD-MM-YYYY'')=''31-12-2099'' ';
444 ELSIF P_CONFIRMED = 'Y' THEN
445 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and to_char(ip.accounting_date,''YYYYMMDD'') <= ''' || TO_CHAR(P_CUT_DATE_v
446 ,'YYYYMMDD') || ''' ';
447 ELSIF P_CONFIRMED IS NULL THEN
448 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and (to_char(ip.accounting_date,''YYYYMMDD'') <= ''' || TO_CHAR(P_CUT_DATE_v
449 ,'YYYYMMDD') || ''' or to_char(ip.accounting_date,''DD-MM-YYYY'')=''31-12-2099'') ';
450 END IF;
451 IF P_PAY_POSTED_FLAG IS NOT NULL THEN
452 SQL_PAYMENTS2 := SQL_PAYMENTS2 || 'and nvl(ach.gl_transfer_flag,''N'')=''' || P_PAY_POSTED_FLAG || ''' ';
453 END IF;
454 IF P_ONLY_PAID_INVOICES_v = 'Y' AND P_ONLY_OPEN_INVOICES_v = 'N' THEN
455 SQL_PAYMENTS1 := SQL_PAYMENTS1 || 'and exists (select null from ap_invoice_payments ip, ap_checks c where i.invoice_id=ip.invoice_id and ip.check_id=c.check_id and c.void_date is null ' || SQL_PAYMENTS2 || ')';
456 SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae,
457 ap_ae_headers ach ' ||
458 ' where ps.invoice_id=ip.invoice_id and ps.payment_num=ip.payment_num and ' ||
459 ' c.check_id = aae.source_id(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) and ' || ' ip.check_id=c.check_id and c.void_date is null ' || SQL_PAYMENTS2 || ')';
460 END IF;
461 IF P_ONLY_OPEN_INVOICES_v = 'Y' AND P_ONLY_PAID_INVOICES_v = 'Y' THEN
462 SELECT
463 TRUNC(START_DATE)
464 INTO L_START_DATE
465 FROM
466 GL_PERIOD_STATUSES
467 WHERE APPLICATION_ID = 200
468 AND NVL(ADJUSTMENT_PERIOD_FLAG
469 ,'N') = 'N'
470 AND TO_CHAR(P_CUT_DATE_v
471 ,'YYYYMMDD') between TO_CHAR(START_DATE
472 ,'YYYYMMDD')
473 AND TO_CHAR(END_DATE
474 ,'YYYYMMDD')
475 AND LEDGER_ID = C_SOB;
476 SQL_PAYMENTS1 := SQL_PAYMENTS1 || 'and (exists (' || 'select null from ap_payment_schedules ps where ps.invoice_id=i.invoice_id ' ||
477 'and (nvl(ps.amount_remaining,0)<>0 or ' ||
478 'not exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae, ap_ae_headers ach ' ||
479 'where ip.invoice_id=i.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' ||
480 'and c.check_id = aae.source_id(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) ' ||
481 SQL_PAYMENTS2 || ') ) )' || 'or exists (select null from ap_invoice_payments ip, ap_checks c '
482 || ' where i.invoice_id=ip.invoice_id and ip.check_id=c.check_id and c.void_date is null ' ||
483 ' and to_char(ip.accounting_date,''YYYYMMDD'') >=''' || TO_CHAR(L_START_DATE
484 ,'YYYYMMDD') || '''' || ' and to_char(ip.accounting_date,''YYYYMMDD'') <=''' || TO_CHAR(P_CUT_DATE_v
485 ,'YYYYMMDD') || '''))';
486 SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and ((nvl(ps.amount_remaining,0)<>0 or ' || 'not exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae, ap_ae_headers ach '
487 || 'where ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' ||
488 'and c.check_id = aae.source_id(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) '
489 || SQL_PAYMENTS2 || '))' || 'or exists (select null from ap_invoice_payments ip, ap_checks c '
490 || ' where ps.invoice_id=ip.invoice_id and ip.check_id=c.check_id and c.void_date is null and ps.payment_num=ip.payment_num ' ||
491 ' and to_char(ip.accounting_date,''YYYYMMDD'') >= ''' || TO_CHAR(L_START_DATE
492 ,'YYYYMMDD') || '''' || ' and to_char(ip.accounting_date,''YYYYMMDD'') <= ''' || TO_CHAR(P_CUT_DATE_v
493 ,'YYYYMMDD') || '''))' || 'and not exists (select null from ap_invoice_payments ip, ap_checks c '
494 || 'where ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' ||
495 ' and to_char(ip.accounting_date,''YYYYMMDD'') > ''' || TO_CHAR(P_CUT_DATE_v
496 ,'YYYYMMDD') || ''' and ip.invoice_payment_type=''PREPAY'' and ps.amount_remaining=0)';
497 END IF;
498 IF P_ONLY_OPEN_INVOICES_v = 'Y' AND P_ONLY_PAID_INVOICES_v = 'N' THEN
499 SQL_PAYMENTS1 := SQL_PAYMENTS1 || 'and (exists (' || 'select null from ap_payment_schedules ps where ps.invoice_id=i.invoice_id ' ||
500 'and (nvl(ps.amount_remaining,0)<>0 or ' ||
501 'not exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae, ap_ae_headers ach ' ||
502 'where ip.invoice_id=i.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' ||
503 'and c.check_id = aae.source_id(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) ' || SQL_PAYMENTS2 || '))))';
504 SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and ((nvl(ps.amount_remaining,0)<>0 or ' || 'not exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae, ap_ae_headers ach ' ||
505 'where ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null '
506 || 'and c.check_id = aae.source_id(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) '
507 || SQL_PAYMENTS2 || ')))' || 'and not exists (select null from ap_invoice_payments ip, ap_checks c '
508 || 'where ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' || ' and to_char(ip.accounting_date,''YYYYMMDD'') > ''' ||
509 TO_CHAR(P_CUT_DATE_v
510 ,'YYYYMMDD') || ''' and ip.invoice_payment_type=''PREPAY'' and ps.amount_remaining=0)';
511 END IF;
512
513 IF (P_DEBUG_SWITCH = 'Y') THEN
514 /*SRW.MESSAGE('901'
515 ,'SQL_PAYMEMTS1 = ' || SQL_PAYMENTS1)*/NULL;
516 /*SRW.MESSAGE('902'
517 ,'SQL_PAYMEMTS2 = ' || SQL_PAYMENTS2)*/NULL;
518 /*SRW.MESSAGE('903'
519 ,'SQL_PAYMEMTS3 = ' || SQL_PAYMENTS3)*/NULL;
520 /*SRW.MESSAGE('904'
521 ,'P_CUT_DATE_v = ' || TO_CHAR(P_CUT_DATE_v
522 ,'DD-MON-YYYY'))*/NULL;
523 END IF;
524 RETURN (TRUE);
525 RETURN NULL;
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 RETURN (FALSE);
529 END SQL_PAYMENTS;
530
531 FUNCTION C_BAL_FACTORFORMULA(DIST_BASE_AMOUNT IN NUMBER
532 ,INVOICE_ID_v IN NUMBER) RETURN NUMBER IS
533 BEGIN
534 DECLARE
535 DIST_TOTAL NUMBER;
536 BEGIN
537 IF (DIST_BASE_AMOUNT IS NOT NULL) THEN
538 BEGIN
539 SELECT
540 SUM(NVL(BASE_AMOUNT
541 ,AMOUNT))
542 INTO DIST_TOTAL
543 FROM
544 AP_INVOICE_DISTRIBUTIONS
545 WHERE INVOICE_ID = INVOICE_ID_v;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 DIST_TOTAL := DIST_BASE_AMOUNT;
549 END;
550 IF (DIST_TOTAL <> 0) THEN
551 RETURN (DIST_BASE_AMOUNT / DIST_TOTAL);
552 ELSE
553 RETURN (1);
554 END IF;
555 ELSE
556 RETURN (1);
557 END IF;
558 END;
559 RETURN NULL;
560 END C_BAL_FACTORFORMULA;
561
562 FUNCTION C_INV_GROSS_BASEFORMULA(CANCELLED_DATE IN DATE
563 ,C_INV_GROSS_BASE0 IN NUMBER
564 ,CANCELLED_AMOUNT IN NUMBER
565 ,EXCHANGE_RATE IN NUMBER) RETURN NUMBER IS
566 BEGIN
567 IF NVL(CANCELLED_DATE
568 ,P_CUT_DATE_v) > P_CUT_DATE_v THEN
569 RETURN (C_INV_GROSS_BASE0 + ROUND(CANCELLED_AMOUNT * EXCHANGE_RATE
570 ,C_PRECISION));
571 ELSE
572 RETURN (C_INV_GROSS_BASE0);
573 END IF;
574 RETURN NULL;
575 END C_INV_GROSS_BASEFORMULA;
576
577 FUNCTION C_INV_GROSS_AMOUNTFORMULA(CANCELLED_DATE IN DATE
578 ,C_INV_GROSS_AMOUNT1 IN NUMBER
579 ,CANCELLED_AMOUNT IN NUMBER
580 ,C_INV_GROSS_AMOUNT0 IN NUMBER) RETURN NUMBER IS
581 BEGIN
582 IF P_GROUP_FIELD = 'T' THEN
583 IF NVL(CANCELLED_DATE
584 ,P_CUT_DATE_v) > P_CUT_DATE_v THEN
585 RETURN (C_INV_GROSS_AMOUNT1 + CANCELLED_AMOUNT + NVL(CP_INVPP_OPEN_AMOUNT
586 ,0));
587 ELSE
588 RETURN (C_INV_GROSS_AMOUNT1 + NVL(CP_INVPP_OPEN_AMOUNT
589 ,0));
590 END IF;
591 ELSE
592 IF NVL(CANCELLED_DATE
593 ,P_CUT_DATE_v) > P_CUT_DATE_v THEN
594 RETURN (C_INV_GROSS_AMOUNT0 + CANCELLED_AMOUNT + NVL(CP_INVPP_OPEN_BASE
595 ,0));
596 ELSE
597 RETURN (C_INV_GROSS_AMOUNT0 + NVL(CP_INVPP_OPEN_BASE
598 ,0));
599 END IF;
600 END IF;
601 RETURN NULL;
602 END C_INV_GROSS_AMOUNTFORMULA;
603
604 FUNCTION C_INV_PAY_BASEFORMULA(INVOICE_ID_V IN NUMBER
605 ,C_INV_PAY_BASE0 IN NUMBER) RETURN NUMBER IS
606 L_CLEARING VARCHAR2(40);
607 L_START_DATE DATE;
608 BEGIN
609 SELECT
610 TRUNC(START_DATE)
611 INTO L_START_DATE
612 FROM
613 GL_PERIOD_STATUSES
614 WHERE APPLICATION_ID = 200
615 AND NVL(ADJUSTMENT_PERIOD_FLAG
616 ,'N') = 'N'
617 AND TRUNC(P_CUT_DATE_v) between START_DATE
618 AND END_DATE
619 AND SET_OF_BOOKS_ID = C_SOB;
620 SELECT
621 SUM(NVL(ID.BASE_AMOUNT
622 ,ID.AMOUNT)),
623 SUM(ID.AMOUNT),
624 MAX(ID.ACCOUNTING_DATE)
625 INTO CP_INVPP_OPEN_BASE,CP_INVPP_OPEN_AMOUNT,CP_INVPP_GL_DATE
626 FROM
627 AP_INVOICE_DISTRIBUTIONS ID,
628 AP_INVOICE_DISTRIBUTIONS PPD,
629 AP_INVOICES PP
630 WHERE ID.INVOICE_ID = INVOICE_ID_V
631 AND ( ( ID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
632 AND ID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID )
633 OR ( ID.LINE_TYPE_LOOKUP_CODE = 'TAX'
634 AND exists (
635 SELECT
636 1
637 FROM
638 AP_INVOICE_DISTRIBUTIONS SAMEID
639 WHERE SAMEID.INVOICE_ID = INVOICE_ID
640 AND SAMEID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
641 AND SAMEID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID
642 AND SAMEID.INVOICE_DISTRIBUTION_ID = ID.PREPAY_TAX_PARENT_ID ) ) )
643 AND PPD.INVOICE_ID = PP.INVOICE_ID
644 AND PP.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT';
645 RETURN (NVL(C_INV_PAY_BASE0
646 ,0) - NVL(CP_INVPP_OPEN_BASE
647 ,0));
648 EXCEPTION
649 WHEN OTHERS THEN
650 CP_INVPP_GL_DATE := TO_DATE('1952/01/01'
651 ,'YYYY/MM/DD');
652 RETURN (C_INV_PAY_BASE0);
653 END C_INV_PAY_BASEFORMULA;
654
655 FUNCTION C_INV_GAINLOSSFORMULA(C_INV_GAINLOSS0 IN NUMBER) RETURN NUMBER IS
656 BEGIN
657 RETURN (C_INV_GAINLOSS0);
658 END C_INV_GAINLOSSFORMULA;
659
660 FUNCTION C_INV_DISCOUNT_TAKEN_BASEFORMU(C_INV_DISC_TAKEN_BASE0 IN NUMBER) RETURN NUMBER IS
661 BEGIN
662 RETURN (C_INV_DISC_TAKEN_BASE0);
663 END C_INV_DISCOUNT_TAKEN_BASEFORMU;
664
665 FUNCTION C_INV_DISCOUNT_AVAILABLEFORMUL(C_INV_DISC_AVAIL0 IN NUMBER
666 ,C_BAL_FACTOR IN NUMBER) RETURN NUMBER IS
667 BEGIN
668 RETURN (C_INV_DISC_AVAIL0 * C_BAL_FACTOR);
669 END C_INV_DISCOUNT_AVAILABLEFORMUL;
670
671 FUNCTION NLS_PARAMETERS RETURN BOOLEAN IS
672 L_SUMMARY VARCHAR2(80);
673 L_YES VARCHAR2(80);
674 L_NO VARCHAR2(80);
675 L_ALL VARCHAR2(80);
676 L_APPROVAL_STATUS VARCHAR2(80);
677 BEGIN
678 P_MATCH_STATUS_FLAG_V := P_MATCH_STATUS_FLAG;
679 SELECT
680 MEANING
681 INTO L_SUMMARY
682 FROM
683 FND_LOOKUPS
684 WHERE LOOKUP_TYPE = 'JGZZ_AP_SUMMARY_LEVEL'
685 AND LOOKUP_CODE = P_SUMMARY_LEVEL;
686 C_SUMMARY_LEVEL := L_SUMMARY;
687 SELECT
688 MEANING
689 INTO L_APPROVAL_STATUS
690 FROM
691 FND_LOOKUPS
692 WHERE LOOKUP_TYPE = 'JGZZ_INVOICE_VALIDATION_STATUS'
693 AND LOOKUP_CODE = NVL(P_MATCH_STATUS_FLAG_V
694 ,'*');
695 C_APPROVAL_STATUS := L_APPROVAL_STATUS;
696 IF (P_MATCH_STATUS_FLAG_V = 'V') THEN
697 P_MATCH_STATUS_FLAG_V := 'A';
698 END IF;
699 SELECT
700 MEANING
701 INTO L_YES
702 FROM
703 FND_LOOKUPS
704 WHERE LOOKUP_TYPE = 'YES_NO_ALL'
705 AND LOOKUP_CODE = 'Y';
706 C_YES := L_YES;
707 SELECT
708 MEANING
709 INTO L_NO
710 FROM
711 FND_LOOKUPS
712 WHERE LOOKUP_TYPE = 'YES_NO_ALL'
713 AND LOOKUP_CODE = 'N';
714 C_NO := L_NO;
715 SELECT
716 MEANING
717 INTO L_ALL
718 FROM
719 FND_LOOKUPS
720 WHERE LOOKUP_TYPE = 'YES_NO_ALL'
721 AND LOOKUP_CODE = 'A';
722 C_ALL := L_ALL;
723 RETURN (TRUE);
724 RETURN NULL;
725 EXCEPTION
726 WHEN NO_DATA_FOUND THEN
727 RETURN (FALSE);
728 END NLS_PARAMETERS;
729
730 FUNCTION C_APPROVE_FLAGFORMULA(INVOICE_ID_v IN NUMBER) RETURN VARCHAR2 IS
731 BEGIN
732 DECLARE
733 A_FLAG FND_LOOKUPS.MEANING%TYPE;
734 A_COUNT NUMBER;
735 BEGIN
736 A_FLAG := C_NO;
737 A_COUNT := 0;
738 SELECT
739 count(*)
740 INTO A_COUNT
741 FROM
742 AP_INVOICE_DISTRIBUTIONS AID
743 WHERE AID.INVOICE_ID = INVOICE_ID_v
744 AND NVL(AID.MATCH_STATUS_FLAG
745 ,'N') <> 'A';
746 IF (A_COUNT = 0) THEN
747 A_FLAG := C_YES;
748 END IF;
749 A_COUNT := 0;
750 SELECT
751 count(*)
752 INTO A_COUNT
753 FROM
754 AP_HOLDS AIH
755 WHERE AIH.INVOICE_ID = INVOICE_ID_v
756 AND AIH.RELEASE_LOOKUP_CODE is null;
757 IF (A_COUNT <> 0) THEN
758 A_FLAG := C_NO;
759 END IF;
760 RETURN (A_FLAG);
761 END;
762 RETURN NULL;
763 END C_APPROVE_FLAGFORMULA;
764
765 FUNCTION CF_CUT_DATEFORMULA RETURN CHAR IS
766 BEGIN
767 RETURN (FND_DATE.DATE_TO_CHARDATE(P_CUT_DATE_v));
768 END CF_CUT_DATEFORMULA;
769
770 FUNCTION CF_SYSDATEFORMULA RETURN CHAR IS
771 BEGIN
772 RETURN (FND_DATE.DATE_TO_CHARDT(SYSDATE));
773 END CF_SYSDATEFORMULA;
774
775 FUNCTION CF_C_INV_DISCOUNT_DATEFORMULA(C_INV_DISCOUNT_DATE IN DATE) RETURN CHAR IS
776 BEGIN
777 RETURN (FND_DATE.DATE_TO_CHARDATE(C_INV_DISCOUNT_DATE));
778 END CF_C_INV_DISCOUNT_DATEFORMULA;
779
780 FUNCTION CF_INV_CHECK_GL_DATEFORMULA(C_INV_CHECK_GL_DATE IN DATE) RETURN CHAR IS
781 BEGIN
782 IF NVL(C_INV_CHECK_GL_DATE
783 ,TO_DATE('1952/01/01'
784 ,'YYYY/MM/DD')) < NVL(CP_INVPP_GL_DATE
785 ,TO_DATE('1952/01/01'
786 ,'YYYY/MM/DD')) THEN
787 RETURN (FND_DATE.DATE_TO_CHARDATE(CP_INVPP_GL_DATE));
788 ELSE
789 RETURN (FND_DATE.DATE_TO_CHARDATE(C_INV_CHECK_GL_DATE));
790 END IF;
791 END CF_INV_CHECK_GL_DATEFORMULA;
792
793 FUNCTION CF_ORGANIZATIONFORMULA(ORG_ID IN NUMBER) RETURN VARCHAR2 IS
794 BEGIN
795 DECLARE
796 OPERATING_UNIT_NAME HR_OPERATING_UNITS.NAME%TYPE;
797 BEGIN
798 SELECT
799 HR.NAME
800 INTO OPERATING_UNIT_NAME
801 FROM
802 HR_OPERATING_UNITS HR
803 WHERE HR.ORGANIZATION_ID = ORG_ID;
804 RETURN (OPERATING_UNIT_NAME);
805 EXCEPTION
806 WHEN NO_DATA_FOUND THEN
807 RETURN TO_CHAR(ORG_ID);
808 END;
809 RETURN NULL;
810 END CF_ORGANIZATIONFORMULA;
811
812 FUNCTION CF_FLEXPROMPTFORMULA RETURN VARCHAR2 IS
813 BEGIN
814 DECLARE
815 L_GROUP VARCHAR2(80);
816 BEGIN
817 SELECT
818 MEANING
819 INTO L_GROUP
820 FROM
821 FND_LOOKUPS
822 WHERE LOOKUP_TYPE = 'JGZZ_AP_INVOICE_GROUP'
823 AND LOOKUP_CODE = P_GROUP_FIELD;
824 RETURN (L_GROUP);
825 EXCEPTION
826 WHEN NO_DATA_FOUND THEN
827 RETURN ('');
828 END;
829 RETURN NULL;
830 END CF_FLEXPROMPTFORMULA;
831
832 FUNCTION AFTERPFORM RETURN BOOLEAN IS
833 BEGIN
834 FND_MO_REPORTING_API.INITIALIZE(P_REPORTING_LEVEL
835 ,P_REPORTING_ENTITY_ID
836 ,'AUTO');
837 P_ORG_WHERE_I := FND_MO_REPORTING_API.GET_PREDICATE('i'
838 ,NULL);
839 P_ORG_WHERE_ID := FND_MO_REPORTING_API.GET_PREDICATE('id'
840 ,NULL);
841 P_ORG_WHERE_VS := FND_MO_REPORTING_API.GET_PREDICATE('vs'
842 ,NULL);
843 P_ORG_WHERE_PS := FND_MO_REPORTING_API.GET_PREDICATE('ps'
844 ,NULL);
845 P_ORG_WHERE_AAE1 := FND_MO_REPORTING_API.GET_PREDICATE('aae1'
846 ,NULL);
847 P_ORG_WHERE_ACH1 := FND_MO_REPORTING_API.GET_PREDICATE('ach1'
848 ,NULL);
849 P_ORG_WHERE_SP := FND_MO_REPORTING_API.GET_PREDICATE('sp'
850 ,NULL);
851 P_ORG_WHERE_AAE := FND_MO_REPORTING_API.GET_PREDICATE('aae'
852 ,NULL);
853 P_ORG_WHERE_ACH := FND_MO_REPORTING_API.GET_PREDICATE('ach'
854 ,NULL);
855 P_ORG_WHERE_IP := FND_MO_REPORTING_API.GET_PREDICATE('ip'
856 ,NULL);
857 P_ORG_WHERE_C := FND_MO_REPORTING_API.GET_PREDICATE('c'
858 ,NULL);
859 P_REPORTING_ENTITY_NAME := SUBSTRB(FND_MO_REPORTING_API.GET_REPORTING_ENTITY_NAME
860 ,1
861 ,50);
862 P_REPORTING_LEVEL_NAME := SUBSTRB(FND_MO_REPORTING_API.GET_REPORTING_LEVEL_NAME
863 ,1
864 ,50);
865 IF P_REPORTING_LEVEL = '1000' THEN
866 P_CHECK_LEDGER_IN_SP := MO_UTILS.CHECK_LEDGER_IN_SP(P_REPORTING_ENTITY_ID);
867 IF P_CHECK_LEDGER_IN_SP = 'N' THEN
868 FND_MESSAGE.SET_NAME('FND'
869 ,'FND_MO_RPT_PARTIAL_LEDGER');
870 P_PARTIAL_LEDGER_MSG := FND_MESSAGE.GET;
871 END IF;
872 END IF;
873 RETURN (TRUE);
874 END AFTERPFORM;
875
876 FUNCTION CP_INV_DUE_DATE_FMT_P RETURN VARCHAR2 IS
877 BEGIN
878 RETURN CP_INV_DUE_DATE_FMT;
879 END CP_INV_DUE_DATE_FMT_P;
880
881 FUNCTION CP_INVPP_OPEN_BASE_P RETURN NUMBER IS
882 BEGIN
883 RETURN CP_INVPP_OPEN_BASE;
884 END CP_INVPP_OPEN_BASE_P;
885
886 FUNCTION CP_INVPP_OPEN_AMOUNT_P RETURN NUMBER IS
887 BEGIN
888 RETURN CP_INVPP_OPEN_AMOUNT;
889 END CP_INVPP_OPEN_AMOUNT_P;
890
891 FUNCTION CP_INVPP_GL_DATE_P RETURN DATE IS
892 BEGIN
893 RETURN CP_INVPP_GL_DATE;
894 END CP_INVPP_GL_DATE_P;
895
896 FUNCTION C_FLEXDATA_P RETURN VARCHAR2 IS
897 BEGIN
898 RETURN C_FLEXDATA;
899 END C_FLEXDATA_P;
900
901 FUNCTION SQL_PAYMENT_GROUP_P RETURN VARCHAR2 IS
902 BEGIN
903 RETURN SQL_PAYMENT_GROUP;
904 END SQL_PAYMENT_GROUP_P;
905
906 FUNCTION SQL_INVOICE_CURRENCY_P RETURN VARCHAR2 IS
907 BEGIN
908 RETURN SQL_INVOICE_CURRENCY;
909 END SQL_INVOICE_CURRENCY_P;
910
911 FUNCTION SQL_VENDOR_ID_P RETURN VARCHAR2 IS
912 BEGIN
913 RETURN SQL_VENDOR_ID;
914 END SQL_VENDOR_ID_P;
915
916 FUNCTION SQL_ONLY_PAST_FLAG_P RETURN VARCHAR2 IS
917 BEGIN
918 RETURN SQL_ONLY_PAST_FLAG;
919 END SQL_ONLY_PAST_FLAG_P;
920
921 FUNCTION SQL_DISTRIBUTIONS_P RETURN VARCHAR2 IS
922 BEGIN
923 RETURN SQL_DISTRIBUTIONS;
924 END SQL_DISTRIBUTIONS_P;
925
926 FUNCTION SQL_PAYMENTS2_P RETURN VARCHAR2 IS
927 BEGIN
928 RETURN SQL_PAYMENTS2;
929 END SQL_PAYMENTS2_P;
930
931 FUNCTION SQL_ONLY_PAST_FLAG2_P RETURN VARCHAR2 IS
932 BEGIN
933 RETURN SQL_ONLY_PAST_FLAG2;
934 END SQL_ONLY_PAST_FLAG2_P;
935
936 FUNCTION SQL_PAYMENTS3_P RETURN VARCHAR2 IS
937 BEGIN
938 RETURN SQL_PAYMENTS3;
939 END SQL_PAYMENTS3_P;
940
941 FUNCTION C_COAI_P RETURN NUMBER IS
942 BEGIN
943 RETURN C_COAI;
944 END C_COAI_P;
945
946 FUNCTION SQL_PAYMENTS1_P RETURN VARCHAR2 IS
947 BEGIN
948 RETURN SQL_PAYMENTS1;
949 END SQL_PAYMENTS1_P;
950
951 FUNCTION C_TITLE_P RETURN VARCHAR2 IS
952 BEGIN
953 RETURN C_TITLE;
954 END C_TITLE_P;
955
956 FUNCTION C_SUMMARY_LEVEL_P RETURN VARCHAR2 IS
957 BEGIN
958 RETURN C_SUMMARY_LEVEL;
959 END C_SUMMARY_LEVEL_P;
960
961 FUNCTION C_YES_P RETURN VARCHAR2 IS
962 BEGIN
963 RETURN C_YES;
964 END C_YES_P;
965
966 FUNCTION C_APPROVAL_STATUS_P RETURN VARCHAR2 IS
967 BEGIN
968 RETURN C_APPROVAL_STATUS;
969 END C_APPROVAL_STATUS_P;
970
971 FUNCTION C_NO_P RETURN VARCHAR2 IS
972 BEGIN
973 RETURN C_NO;
974 END C_NO_P;
975
976 FUNCTION C_ALL_P RETURN VARCHAR2 IS
977 BEGIN
978 RETURN C_ALL;
979 END C_ALL_P;
980
981 FUNCTION C_SOB_P RETURN NUMBER IS
982 BEGIN
983 RETURN C_SOB;
984 END C_SOB_P;
985
986 FUNCTION C_HOLD_FLAG_P RETURN VARCHAR2 IS
987 BEGIN
988 RETURN C_HOLD_FLAG;
989 END C_HOLD_FLAG_P;
990
991 FUNCTION C_PRECISION_P RETURN NUMBER IS
992 BEGIN
993 RETURN C_PRECISION;
994 END C_PRECISION_P;
995
996 FUNCTION C_FUNCT_CURR_P RETURN VARCHAR2 IS
997 BEGIN
998 RETURN C_FUNCT_CURR;
999 END C_FUNCT_CURR_P;
1000
1001 FUNCTION CP_PARTIAL_LEDGER_MSG_P RETURN VARCHAR2 IS
1002 BEGIN
1003 RETURN CP_PARTIAL_LEDGER_MSG;
1004 END CP_PARTIAL_LEDGER_MSG_P;
1005 END JG_JGZZSRCR_XMLP_PKG;
1006
[Home] [Help]