1 PACKAGE BODY ONT_OEXCEXP_XMLP_PKG AS
2 /* $Header: OEXCEXPB.pls 120.1 2007/12/25 07:09:16 npannamp noship $ */
3 FUNCTION CF_GET_RULE_NAMEFORMULA RETURN NUMBER IS
4 BEGIN
5 IF P_CR_CHECK_RULE_ID IS NOT NULL THEN
6 SELECT
7 NAME
8 INTO CP_CHECK_RULE
9 FROM
10 OE_CREDIT_CHECK_RULES
11 WHERE CREDIT_CHECK_RULE_ID = P_CR_CHECK_RULE_ID;
12 END IF;
13 RETURN (0);
14 END CF_GET_RULE_NAMEFORMULA;
15
16 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
17 BEGIN
18 BEGIN
19 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
20 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
21 EXCEPTION
22 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
23 /*SRW.MESSAGE(1
24 ,'FAILED IN BEFORE REPORT TRIGGER')*/NULL;
25 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
26 RETURN (FALSE);
27 END;
28 P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
29 /*SRW.MESSAGE(100
30 ,' p_org_id:' || TO_CHAR(P_ORG_ID))*/NULL;
31 /*SRW.MESSAGE(200
32 ,' Calling OE_CREDIT_CHECK_RPT.Credit_exposure_report_utils')*/NULL;
33 DECLARE
34 L_SOB_NAME VARCHAR2(240);
35 BEGIN
36 SELECT
37 SOB.NAME
38 INTO L_SOB_NAME
39 FROM
40 GL_SETS_OF_BOOKS SOB
41 WHERE SOB.SET_OF_BOOKS_ID = OE_SYS_PARAMETERS.VALUE('SET_OF_BOOKS_ID'
42 ,MO_GLOBAL.GET_CURRENT_ORG_ID);
43 RP_COMPANY_NAME := L_SOB_NAME;
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 RP_COMPANY_NAME := NULL;
47 END;
48 BEGIN
49 OE_CREDIT_CHECK_RPT.CREDIT_EXPOSURE_REPORT_UTILS(P_REPORT_BY_OPTION => P_REPORT_BY_OPTION
50 ,P_SPECIFIC_PARTY_ID => P_SPECIFIC_PARTY_ID
51 ,P_SPEC_PARTY_NUM_ID => P_SPEC_PARTY_NUM_ID
52 ,P_PARTY_NAME_LOW => P_PARTY_NAME_LOW
53 ,P_PARTY_NAME_HIGH => P_PARTY_NAME_HIGH
54 ,P_PARTY_NUMBER_LOW => P_PARTY_NUMBER_LOW
55 ,P_PARTY_NUMBER_HIGH => P_PARTY_NUMBER_HIGH
56 ,P_PROF_CLASS_LOW => P_PROF_CLASS_LOW
57 ,P_PROF_CLASS_HIGH => P_PROF_CLASS_HIGH
58 ,P_CUSTOMER_NAME_LOW => P_CUSTOMER_NAME_LOW
59 ,P_CUSTOMER_NAME_HIGH => P_CUSTOMER_NAME_HIGH
60 ,P_CUST_NUMBER_LOW => P_CUST_NUMBER_LOW
61 ,P_CUST_NUMBER_HIGH => P_CUST_NUMBER_HIGH
62 ,P_CR_CHECK_RULE_ID => P_CR_CHECK_RULE_ID
63 ,P_ORG_ID => P_ORG_ID
64 ,X_RETURN_STATUS => P_RETURN_STATUS);
65 GET_HIERARCHY;
66 /*SRW.MESSAGE(200
67 ,' OUT OE_CREDIT_CHECK_RPT.Credit_exposure_report_utils')*/NULL;
68 EXCEPTION
69 WHEN OTHERS THEN
70 /*SRW.MESSAGE(1
71 ,'Credit_Check_Processor_API Unexpected Failure. Failed in BEFORE REPORT TRIGGER')*/NULL;
72 RETURN (FALSE);
73 END;
74 RETURN (TRUE);
75 END BEFOREREPORT;
76
77 FUNCTION CF_GET_TOTAL_EXPOSUREFORMULA(CS_BASE_EXPOSURE_SUM IN NUMBER
78 ,UNCHECKED_EXPOSURE IN NUMBER) RETURN NUMBER IS
79 BEGIN
80 CP_TOTAL_EXPOSURE := NVL(CS_BASE_EXPOSURE_SUM
81 ,0) + NVL(UNCHECKED_EXPOSURE
82 ,0);
83 RETURN (0);
84 END CF_GET_TOTAL_EXPOSUREFORMULA;
85
86 FUNCTION CF_GET_OPER_UNITFORMULA RETURN NUMBER IS
87 BEGIN
88 SELECT
89 NAME
90 INTO CP_OPERATING_UNIT
91 FROM
92 HR_OPERATING_UNITS
93 WHERE ORGANIZATION_ID = P_ORG_ID;
94 RETURN (0);
95 END CF_GET_OPER_UNITFORMULA;
96
97 FUNCTION CF_GET_BASE_CURRFORMULA(BASE_CURRENCY2 IN VARCHAR2) RETURN NUMBER IS
98 BEGIN
99 CP_BASE_CURRENCY := '(' || BASE_CURRENCY2 || ')';
100 RETURN (0);
101 END CF_GET_BASE_CURRFORMULA;
102
103 FUNCTION CF_GET_SITE_TOTAL_EXPFORMULA(CS_SITE_EXPOSURE_SUM IN NUMBER
104 ,D_UNCHECKED_EXPOSURE IN NUMBER) RETURN NUMBER IS
105 BEGIN
106 CP_SITE_TOTAL_EXPOSURE := NVL(CS_SITE_EXPOSURE_SUM
107 ,0) + NVL(D_UNCHECKED_EXPOSURE
108 ,0);
109 RETURN (0);
110 END CF_GET_SITE_TOTAL_EXPFORMULA;
111
112 FUNCTION CF_GET_BASE_CURRENCY1FORMULA(D_BASE_CURRENCY IN VARCHAR2) RETURN NUMBER IS
113 BEGIN
114 CP_BASE_CURRENCY1 := '(' || D_BASE_CURRENCY || ')';
115 RETURN (0);
116 END CF_GET_BASE_CURRENCY1FORMULA;
117
118 FUNCTION AFTERREPORT RETURN BOOLEAN IS
119 BEGIN
120 BEGIN
121 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
122 EXCEPTION
123 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
124 /*SRW.MESSAGE(1
125 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
126 RETURN (FALSE);
127 END;
128 RETURN (TRUE);
129 END AFTERREPORT;
130
131 FUNCTION CF_GET_LIMIT_G_TOTALFORMULA(D_CUSTOMER_ID IN NUMBER) RETURN NUMBER IS
132 BEGIN
133 SELECT
134 SUM(BASE_CUR_OVERALL_LIMIT)
135 INTO CP_CUST_LIMIT_GRAND_TOTAL
136 FROM
137 OE_CREDIT_EXPOSURE_TEMP
138 WHERE CUSTOMER_ID = D_CUSTOMER_ID
139 AND BILL_TO_SITE_ID IS NOT NULL;
140 RETURN (0);
141 END CF_GET_LIMIT_G_TOTALFORMULA;
142
143 FUNCTION CF_GET_EXP_G_TOTALFORMULA(D_CUSTOMER_ID IN NUMBER) RETURN NUMBER IS
144 L_EXPOSURE NUMBER;
145 L_UNCHECKED_EXPOSURE NUMBER;
146 BEGIN
147 SELECT
148 SUM(BASE_CUR_EXPOSURE)
149 INTO L_EXPOSURE
150 FROM
151 OE_CREDIT_EXPOSURE_TEMP
152 WHERE CUSTOMER_ID = D_CUSTOMER_ID
153 AND BILL_TO_SITE_ID IS NOT NULL;
154 SELECT
155 SUM(UNCHECKED_EXPOSURE)
156 INTO L_UNCHECKED_EXPOSURE
157 FROM
158 OE_CREDIT_EXPOSURE_TEMP
159 WHERE CUSTOMER_ID = D_CUSTOMER_ID
160 AND BILL_TO_SITE_ID IS NOT NULL;
161 CP_CUST_EXPOSURE_GRAND_TOTAL := L_EXPOSURE + L_UNCHECKED_EXPOSURE;
162 RETURN (0);
163 END CF_GET_EXP_G_TOTALFORMULA;
164
165 FUNCTION CF_GET_AVAIL_G_TOTALFO(D_CUSTOMER_ID IN NUMBER) RETURN NUMBER IS
166 BEGIN
167 SELECT
168 SUM(BASE_CUR_AVAILABLE)
169 INTO CP_CUST_AVAILABLE_GRAND_TOTAL
170 FROM
171 OE_CREDIT_EXPOSURE_TEMP
172 WHERE CUSTOMER_ID = D_CUSTOMER_ID
173 AND BILL_TO_SITE_ID IS NOT NULL;
174 RETURN (0);
175 END CF_GET_AVAIL_G_TOTALFO;
176
177 FUNCTION AFTERPFORM RETURN BOOLEAN IS
178 BEGIN
179 IF P_REPORT_BY_OPTION = 'PARTY_SUMMARY' THEN
180 IF P_PARTY_NAME_LOW IS NOT NULL OR P_PARTY_NAME_HIGH IS NOT NULL THEN
181 P_ORDER_BY := 'order by party_name';
182 ELSIF (P_PARTY_NUMBER_LOW IS NOT NULL OR P_PARTY_NUMBER_HIGH IS NOT NULL) AND (P_PARTY_NAME_LOW IS NULL AND P_PARTY_NAME_HIGH IS NULL) THEN
183 P_ORDER_BY := 'order by party_number';
184 END IF;
185 ELSIF P_REPORT_BY_OPTION = 'CUST_SUMMARY' OR P_REPORT_BY_OPTION = 'CUST_DETAILS' THEN
186 IF P_CUSTOMER_NAME_LOW IS NOT NULL OR P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
187 P_ORDER_BY := 'order by customer_name';
188 ELSIF (P_CUST_NUMBER_LOW IS NOT NULL AND P_CUST_NUMBER_HIGH IS NOT NULL) AND (P_CUSTOMER_NAME_LOW IS NULL AND P_CUSTOMER_NAME_HIGH IS NULL) THEN
189 P_ORDER_BY := 'order by customer_number';
190 ELSIF (P_CUST_NUMBER_LOW IS NULL AND P_CUST_NUMBER_HIGH IS NULL) AND (P_CUSTOMER_NAME_LOW IS NULL AND P_CUSTOMER_NAME_HIGH IS NULL) THEN
191 P_ORDER_BY := 'order by customer_name';
192 END IF;
193 ELSE
194 P_ORDER_BY := ' ';
195 END IF;
196 RETURN (TRUE);
197 END AFTERPFORM;
198
199 FUNCTION CF_PARTY_GET_TOTALSFORMULA(CS_PARTY_BASE_CUR_EXPOSURE_SUM IN NUMBER
200 ,UNCHECKED_EXPOSURE_P IN NUMBER) RETURN NUMBER IS
201 BEGIN
202 CP_PARTY_EXP_GRAND_TOTAL := NVL(CS_PARTY_BASE_CUR_EXPOSURE_SUM
203 ,0) + NVL(UNCHECKED_EXPOSURE_P
204 ,0);
205 RETURN (0);
206 END CF_PARTY_GET_TOTALSFORMULA;
207
208 FUNCTION CF_GET_PARTY_BASE_CURRENCYFORM(BASE_CURRENCY_P IN VARCHAR2) RETURN NUMBER IS
209 BEGIN
210 CP_PARTY_BASE_CURRENCY := '(' || BASE_CURRENCY_P || ')';
211 RETURN (0);
212 END CF_GET_PARTY_BASE_CURRENCYFORM;
213
214 FUNCTION CF_GET_REPORT_OPTIONFORMULA RETURN NUMBER IS
215 BEGIN
216 SELECT
217 MEANING
218 INTO CP_REPORT_OPTION
219 FROM
220 OE_LOOKUPS
221 WHERE LOOKUP_TYPE = 'ONT_CC_EXC_REPORTBY_OPTION'
222 AND ENABLED_FLAG = 'Y'
223 AND LOOKUP_CODE = P_REPORT_BY_OPTION;
224 RETURN (0);
225 END CF_GET_REPORT_OPTIONFORMULA;
226
227 FUNCTION CF_GET_BASE_CUR_DFORMULA(BASE_CURRENCY_D IN VARCHAR2) RETURN NUMBER IS
228 BEGIN
229 CP_BASE_CURRENCY_D := '(' || BASE_CURRENCY_D || ')';
230 RETURN (0);
231 END CF_GET_BASE_CUR_DFORMULA;
232
233 FUNCTION CF_GET_EXPOSURE_TOTAL_DFORMULA(CS_EXPOSURE_SUM_D IN NUMBER
234 ,UNCHECKED_EXPOSURE_D IN NUMBER) RETURN NUMBER IS
235 BEGIN
236 CP_EXPOSURE_TOTAL_D := NVL(CS_EXPOSURE_SUM_D
237 ,0) + NVL(UNCHECKED_EXPOSURE_D
238 ,0);
239 RETURN (0);
240 END CF_GET_EXPOSURE_TOTAL_DFORMULA;
241
242 FUNCTION CF_GET_HIERARCHY_NAMEFORMULA RETURN NUMBER IS
243 BEGIN
244 CP_HIERARCHY_NAME := FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE');
245 RETURN (0);
246 END CF_GET_HIERARCHY_NAMEFORMULA;
247
248 FUNCTION CF_GET_B_CURRENCY_CUSTFORMULA(BASE_CURRENCY_CUST IN VARCHAR2) RETURN NUMBER IS
249 BEGIN
250 CP_BASE_CURRENCY_CUST := '(' || NVL(BASE_CURRENCY_CUST
251 ,' ') || ')';
252 RETURN (0);
253 END CF_GET_B_CURRENCY_CUSTFORMULA;
254
255 FUNCTION CF_GET_EXP_AND_UNCHFORMULA(BASE_EXPOSURE_CUST IN NUMBER) RETURN NUMBER IS
256 BEGIN
257 CP_EXP_AND_UNCHECKED := NVL(BASE_EXPOSURE_CUST
258 ,0);
259 RETURN (0);
260 END CF_GET_EXP_AND_UNCHFORMULA;
261
262 FUNCTION CF_GET_PARTY_NAME_TOTALFORMULA(PARTY_NAME IN VARCHAR2) RETURN NUMBER IS
263 BEGIN
264 CP_PARTY_NAME_TOTAL_EXP := PARTY_NAME || ' Total:';
265 RETURN (0);
266 END CF_GET_PARTY_NAME_TOTALFORMULA;
267
268 FUNCTION CF_GET_PARTY_NAME_UNFORMULA(PARTY_NAME IN VARCHAR2) RETURN NUMBER IS
269 BEGIN
270 CP_PARTY_NAME_UNCHECK := 'Exposure not checked at ' || PARTY_NAME || ':';
271 RETURN (0);
272 END CF_GET_PARTY_NAME_UNFORMULA;
273
274 FUNCTION CF_GET_PARTY_NAMEFORMULA RETURN NUMBER IS
275 BEGIN
276 IF P_SPECIFIC_PARTY_ID IS NOT NULL THEN
277 SELECT
278 DISTINCT
279 PARTY_NAME
280 INTO CP_PARTY_NAME
281 FROM
282 HZ_PARTIES
283 WHERE PARTY_ID = P_SPECIFIC_PARTY_ID;
284 ELSIF P_SPEC_PARTY_NUM_ID IS NOT NULL THEN
285 SELECT
286 DISTINCT
287 PARTY_NUMBER
288 INTO CP_PARTY_NUMBER
289 FROM
290 HZ_PARTIES
291 WHERE PARTY_ID = P_SPEC_PARTY_NUM_ID;
292 END IF;
293 RETURN (0);
294 END CF_GET_PARTY_NAMEFORMULA;
295
296 PROCEDURE GET_HIERARCHY IS
297 L_REL_TYPE VARCHAR2(100);
298 L_TOP_PARENT NUMBER;
299 L_MSG VARCHAR2(200);
300 L_PARTY_NAME VARCHAR2(360);
301 L_PARTY_NUMBER VARCHAR2(30);
302 CURSOR HIER_CSR IS
303 SELECT
304 PARTY_ID
305 FROM
306 OE_CREDIT_HIERARCHY_TEMP;
307 BEGIN
308 FND_PROFILE.GET(NAME => 'AR_CMGT_HIERARCHY_TYPE'
309 ,VAL => L_REL_TYPE);
310 SELECT
311 A.PARENT_ID
312 INTO L_TOP_PARENT
313 FROM
314 HZ_HIERARCHY_NODES A
315 WHERE A.TOP_PARENT_FLAG = 'Y'
316 AND A.HIERARCHY_TYPE = L_REL_TYPE
317 AND A.CHILD_ID IN (
318 SELECT
319 B.PARENT_ID
320 FROM
321 HZ_HIERARCHY_NODES B
322 WHERE B.HIERARCHY_TYPE = L_REL_TYPE
323 AND B.CHILD_ID = NVL(P_SPECIFIC_PARTY_ID
324 ,P_SPEC_PARTY_NUM_ID) );
325 INSERT INTO OE_CREDIT_HIERARCHY_TEMP
326 (PARTY_ID
327 ,PARTY_PARENT_ID
328 ,PARTY_NAME
329 ,PARTY_NUMBER
330 ,PARTY_LEVEL)
331 SELECT
332 PARTY_ID,
333 NULL,
334 P.PARTY_NAME,
335 P.PARTY_NUMBER,
336 0
337 FROM
338 HZ_PARTIES P
339 WHERE P.PARTY_ID = L_TOP_PARENT;
340 INSERT INTO OE_CREDIT_HIERARCHY_TEMP
341 (PARTY_ID
342 ,PARTY_PARENT_ID
343 ,PARTY_LEVEL)
344 SELECT
345 OBJECT_ID,
346 SUBJECT_ID,
347 LEVEL
348 FROM
349 HZ_RELATIONSHIPS
350 WHERE RELATIONSHIP_TYPE = L_REL_TYPE
351 START WITH SUBJECT_ID = L_TOP_PARENT
352 CONNECT BY PRIOR OBJECT_ID = SUBJECT_ID
353 AND DIRECTION_CODE = 'P';
354 FOR hier_csr_rec IN HIER_CSR LOOP
355 SELECT
356 PARTY_NAME,
357 PARTY_NUMBER
358 INTO L_PARTY_NAME,L_PARTY_NUMBER
359 FROM
360 HZ_PARTIES
361 WHERE PARTY_ID = HIER_CSR_REC.PARTY_ID;
362 UPDATE
363 OE_CREDIT_HIERARCHY_TEMP
364 SET
365 PARTY_NAME = L_PARTY_NAME
366 ,PARTY_NUMBER = L_PARTY_NUMBER
367 WHERE PARTY_ID = HIER_CSR_REC.PARTY_ID;
368 END LOOP;
369 EXCEPTION
370 WHEN OTHERS THEN
371 L_MSG := SQLERRM;
372 /*SRW.MESSAGE(400
373 ,'Error in Get_Hierarchy:' || L_MSG)*/NULL;
374 END GET_HIERARCHY;
375
376 FUNCTION CF_TOTAL_UNCHECKED_EXP_CUSTFOR(CS_TOTAL_EXP_AND_UNCH IN NUMBER
377 ,UNCHECKED_EXPOSURE_CUST IN NUMBER) RETURN NUMBER IS
378 BEGIN
379 CP_TOTAL_UNCHECKED_EXP_CUST := 0;
380 CP_TOTAL_UNCHECKED_EXP_CUST := NVL(CS_TOTAL_EXP_AND_UNCH
381 ,0) + NVL(UNCHECKED_EXPOSURE_CUST
382 ,0);
383 RETURN (0);
384 END CF_TOTAL_UNCHECKED_EXP_CUSTFOR;
385
386 FUNCTION CF_TOTAL_AVAIL_PARTY_CUSTFORMU(CS_TOTAL_OVERALL_CUST IN NUMBER) RETURN NUMBER IS
387 BEGIN
388 CP_TOTAL_AVAIL_PARTY_CUST := 0;
389 CP_TOTAL_AVAIL_PARTY_CUST := CS_TOTAL_OVERALL_CUST - NVL(CP_TOTAL_UNCHECKED_EXP_CUST
390 ,0);
391 RETURN (0);
392 END CF_TOTAL_AVAIL_PARTY_CUSTFORMU;
393
394 FUNCTION CF_PARTY_SUM_GRAND_AVAILFORMUL(CS_PARTY_BASE_CUR_OVERALL_SUM IN NUMBER) RETURN NUMBER IS
395 BEGIN
396 CP_PARTY_SUM_GRAND_AVAIL := 0;
397 CP_PARTY_SUM_GRAND_AVAIL := NVL(CS_PARTY_BASE_CUR_OVERALL_SUM
398 ,0) - NVL(CP_PARTY_EXP_GRAND_TOTAL
399 ,0);
400 RETURN (0);
401 END CF_PARTY_SUM_GRAND_AVAILFORMUL;
402
403 FUNCTION CF_PARTY_DTL_GRAND_TOTAL_AVAIL(CS_OVERALL_LIMIT_SUM_D IN NUMBER) RETURN NUMBER IS
404 BEGIN
405 CP_PARTY_DTL_GRAND_TOTAL_AVAIL := 0;
406 CP_PARTY_DTL_GRAND_TOTAL_AVAIL := NVL(CS_OVERALL_LIMIT_SUM_D
407 ,0) - NVL(CP_EXPOSURE_TOTAL_D
408 ,0);
409 RETURN (0);
410 END CF_PARTY_DTL_GRAND_TOTAL_AVAIL;
411
412 FUNCTION CP_PARTY_NAMEFORMULA RETURN CHAR IS
413 BEGIN
414 NULL;
415 END CP_PARTY_NAMEFORMULA;
416
417 FUNCTION CP_TOTAL_EXPOSURE_P RETURN NUMBER IS
418 BEGIN
419 RETURN CP_TOTAL_EXPOSURE;
420 END CP_TOTAL_EXPOSURE_P;
421
422 FUNCTION CP_BASE_CURRENCY_P RETURN VARCHAR2 IS
423 BEGIN
424 RETURN CP_BASE_CURRENCY;
425 END CP_BASE_CURRENCY_P;
426
427 FUNCTION CP_CUST_LIMIT_GRAND_TOTAL_P RETURN NUMBER IS
428 BEGIN
429 RETURN CP_CUST_LIMIT_GRAND_TOTAL;
430 END CP_CUST_LIMIT_GRAND_TOTAL_P;
431
432 FUNCTION CP_CUST_EXPOSURE_GRAND_TOTAL_P RETURN NUMBER IS
433 BEGIN
434 RETURN CP_CUST_EXPOSURE_GRAND_TOTAL;
435 END CP_CUST_EXPOSURE_GRAND_TOTAL_P;
436
437 FUNCTION CP_CUST_AVAILABLE_GRAND_TOT_P RETURN NUMBER IS
438 BEGIN
439 RETURN CP_CUST_AVAILABLE_GRAND_TOTAL;
440 END CP_CUST_AVAILABLE_GRAND_TOT_P;
441
442 FUNCTION CP_SITE_TOTAL_EXPOSURE_P RETURN NUMBER IS
443 BEGIN
444 RETURN CP_SITE_TOTAL_EXPOSURE;
445 END CP_SITE_TOTAL_EXPOSURE_P;
446
447 FUNCTION CP_BASE_CURRENCY1_P RETURN VARCHAR2 IS
448 BEGIN
449 RETURN CP_BASE_CURRENCY1;
450 END CP_BASE_CURRENCY1_P;
451
452 FUNCTION CP_UNCHECKED_EXPOSURE_P RETURN NUMBER IS
453 BEGIN
454 RETURN CP_UNCHECKED_EXPOSURE;
455 END CP_UNCHECKED_EXPOSURE_P;
456
457 FUNCTION CP_PARTY_EXP_GRAND_TOTAL_P RETURN NUMBER IS
458 BEGIN
459 RETURN CP_PARTY_EXP_GRAND_TOTAL;
460 END CP_PARTY_EXP_GRAND_TOTAL_P;
461
462 FUNCTION CP_PARTY_BASE_CURRENCY_P RETURN VARCHAR2 IS
463 BEGIN
464 RETURN CP_PARTY_BASE_CURRENCY;
465 END CP_PARTY_BASE_CURRENCY_P;
466
467 FUNCTION CP_PARTY_NAME_TOTAL_EXP_P RETURN VARCHAR2 IS
468 BEGIN
469 RETURN CP_PARTY_NAME_TOTAL_EXP;
470 END CP_PARTY_NAME_TOTAL_EXP_P;
471
472 FUNCTION CP_PARTY_NAME_UNCHECK_P RETURN VARCHAR2 IS
473 BEGIN
474 RETURN CP_PARTY_NAME_UNCHECK;
475 END CP_PARTY_NAME_UNCHECK_P;
476
477 FUNCTION CP_PARTY_SUM_GRAND_AVAIL_P RETURN NUMBER IS
478 BEGIN
479 RETURN CP_PARTY_SUM_GRAND_AVAIL;
480 END CP_PARTY_SUM_GRAND_AVAIL_P;
481
482 FUNCTION CP_BASE_CURRENCY_D_P RETURN VARCHAR2 IS
483 BEGIN
484 RETURN CP_BASE_CURRENCY_D;
485 END CP_BASE_CURRENCY_D_P;
486
487 FUNCTION CP_EXPOSURE_TOTAL_D_P RETURN NUMBER IS
488 BEGIN
489 RETURN CP_EXPOSURE_TOTAL_D;
490 END CP_EXPOSURE_TOTAL_D_P;
491
492 FUNCTION CP_PARTY_DTL_GRAND_TOTAL_AVAI RETURN NUMBER IS
493 BEGIN
494 RETURN CP_PARTY_DTL_GRAND_TOTAL_AVAIL;
495 END CP_PARTY_DTL_GRAND_TOTAL_AVAI;
496
497 FUNCTION CP_BASE_CURRENCY_CUST_P RETURN VARCHAR2 IS
498 BEGIN
499 RETURN CP_BASE_CURRENCY_CUST;
500 END CP_BASE_CURRENCY_CUST_P;
501
502 FUNCTION CP_TOTAL_UNCHECKED_EXP_CUST_P RETURN NUMBER IS
503 BEGIN
504 RETURN CP_TOTAL_UNCHECKED_EXP_CUST;
505 END CP_TOTAL_UNCHECKED_EXP_CUST_P;
506
507 FUNCTION CP_TOTAL_AVAIL_PARTY_CUST_P RETURN NUMBER IS
508 BEGIN
509 RETURN CP_TOTAL_AVAIL_PARTY_CUST;
510 END CP_TOTAL_AVAIL_PARTY_CUST_P;
511
512 FUNCTION CP_EXP_AND_UNCHECKED_P RETURN NUMBER IS
513 BEGIN
514 RETURN CP_EXP_AND_UNCHECKED;
515 END CP_EXP_AND_UNCHECKED_P;
516
517 FUNCTION CP_CUSTOMER_LOW_P RETURN VARCHAR2 IS
518 BEGIN
519 RETURN CP_CUSTOMER_LOW;
520 END CP_CUSTOMER_LOW_P;
521
522 FUNCTION CP_CUSTOMER_HIGH_P RETURN VARCHAR2 IS
523 BEGIN
524 RETURN CP_CUSTOMER_HIGH;
525 END CP_CUSTOMER_HIGH_P;
526
527 FUNCTION CP_PROF_CLASS_LOW_P RETURN VARCHAR2 IS
528 BEGIN
529 RETURN CP_PROF_CLASS_LOW;
530 END CP_PROF_CLASS_LOW_P;
531
532 FUNCTION CP_PROF_CLASS_HIGH_P RETURN VARCHAR2 IS
533 BEGIN
534 RETURN CP_PROF_CLASS_HIGH;
535 END CP_PROF_CLASS_HIGH_P;
536
537 FUNCTION CP_CUST_NUM_LOW_P RETURN VARCHAR2 IS
538 BEGIN
539 RETURN CP_CUST_NUM_LOW;
540 END CP_CUST_NUM_LOW_P;
541
542 FUNCTION CP_CUST_NUM_HIGH_P RETURN VARCHAR2 IS
543 BEGIN
544 RETURN CP_CUST_NUM_HIGH;
545 END CP_CUST_NUM_HIGH_P;
546
547 FUNCTION CP_OPERATING_UNIT_P RETURN VARCHAR2 IS
548 BEGIN
549 RETURN CP_OPERATING_UNIT;
550 END CP_OPERATING_UNIT_P;
551
552 FUNCTION CP_REPORT_OPTION_P RETURN VARCHAR2 IS
553 BEGIN
554 RETURN CP_REPORT_OPTION;
555 END CP_REPORT_OPTION_P;
556
557 FUNCTION CP_HIERARCHY_NAME_P RETURN VARCHAR2 IS
558 BEGIN
559 RETURN CP_HIERARCHY_NAME;
560 END CP_HIERARCHY_NAME_P;
561
562 FUNCTION CP_PARTY_NAME_P RETURN VARCHAR2 IS
563 BEGIN
564 RETURN CP_PARTY_NAME;
565 END CP_PARTY_NAME_P;
566
567 FUNCTION CP_PARTY_NUMBER_P RETURN VARCHAR2 IS
568 BEGIN
569 RETURN CP_PARTY_NUMBER;
570 END CP_PARTY_NUMBER_P;
571
572 FUNCTION CP_CHECK_RULE_P RETURN VARCHAR2 IS
573 BEGIN
574 RETURN CP_CHECK_RULE;
575 END CP_CHECK_RULE_P;
576
577 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
578 BEGIN
579 RETURN RP_COMPANY_NAME;
580 END RP_COMPANY_NAME_P;
581
582 FUNCTION M_HIERARCHYFORMATTRIGGER RETURN VARCHAR2 IS
583 L_COUNT NUMBER;
584 L_PROFILE VARCHAR2(100);
585
586 BEGIN
587 FND_PROFILE.GET( NAME => 'AR_CMGT_HIERARCHY_TYPE'
588 , VAL => L_PROFILE);
589
590 IF P_SPECIFIC_PARTY_ID IS NOT NULL
591 THEN
592 SELECT COUNT(1)
593 INTO L_COUNT
594 FROM HZ_RELATIONSHIPS
595 WHERE RELATIONSHIP_TYPE=L_PROFILE
596 AND (SUBJECT_ID=P_SPECIFIC_PARTY_ID
597 OR OBJECT_ID=P_SPECIFIC_PARTY_ID);
598
599 ELSIF P_SPEC_PARTY_NUM_ID IS NOT NULL
600 THEN
601 SELECT COUNT(1)
602 INTO L_COUNT
603 FROM HZ_RELATIONSHIPS
604 WHERE RELATIONSHIP_TYPE=L_PROFILE
605 AND (SUBJECT_ID=P_SPEC_PARTY_NUM_ID
606 OR OBJECT_ID=P_SPEC_PARTY_NUM_ID);
607
608 END IF;
609
610 IF P_REPORT_BY_OPTION='PARTY_DETAILS'
611 AND L_COUNT<>0
612 THEN
613 RETURN ('TRUE');
614 ELSE
615 RETURN('FALSE');
616 END IF;
617 END M_HIERARCHYFORMATTRIGGER;
618
619 FUNCTION M_9FORMATTRIGGER RETURN NUMBER IS
620
621 L_COUNT NUMBER;
622
623 BEGIN
624 SELECT COUNT(1)
625 INTO L_COUNT
626 FROM OE_CREDIT_HIERARCHY_TEMP;
627
628 /* IF L_COUNT=0
629 THEN
630 RETURN(L_COUNT);
631 ELSE
632 RETURN (L_COUNT);
633 END IF;*/
634 RETURN(L_COUNT);
635
636 END M_9FORMATTRIGGER;
637
638 FUNCTION M_6FORMATTRIGGER RETURN VARCHAR2 IS
639
640 L_COUNT NUMBER;
641
642 BEGIN
643 SELECT COUNT(1)
644 INTO L_COUNT
645 FROM OE_CREDIT_EXPOSURE_TEMP
646 WHERE PARTY_ID IS NOT NULL
647 AND CUSTOMER_ID IS NOT NULL
648 AND REPORT_BY_OPTION='PARTY_DETAILS';
649
650 IF P_REPORT_BY_OPTION='PARTY_DETAILS'
651 AND L_COUNT>0
652 THEN
653 RETURN ('TRUE');
654 ELSE
655 RETURN('FALSE');
656 END IF;
657 END M_6FORMATTRIGGER;
658
659 END ONT_OEXCEXP_XMLP_PKG;
660
[Home] [Help]