[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_FISAP
Source
1 PACKAGE BODY igf_aw_fisap AS
2 /* $Header: IGFAW11B.pls 120.1 2006/01/31 03:24:56 veramach noship $ */
3
4 PROCEDURE log_messages ( p_msg_name VARCHAR2 ,
5 p_msg_val VARCHAR2
6 ) IS
7 ------------------------------------------------------------------
8 --Created by : pkpatel, Oracle IDC
9 --Date created: 01-NOV-2001
10 --
11 --Purpose: This procedure is private to this package body .
12 -- The procedure logs all the parameter values
13 --
14 --Known limitations/enhancements and/or remarks:
15 --
16 --Change History:
17 --Who When What
18 -------------------------------------------------------------------
19 BEGIN
20 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
21 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
22 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
23 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
24 END log_messages ;
25
26 PROCEDURE aggregate_match(
27 errbuf OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY NUMBER,
29 p_award_year IN VARCHAR2,
30 p_sum_type IN VARCHAR2,
31 p_org_id IN NUMBER
32 ) IS
33 /*
34 || Created By : [email protected]
35 || Created On : 01-NOV-2001
36 || Purpose : This is the driving procedure for the concurrent job
37 || 'Aggregate Matching'
38 || Known limitations, enhancements or remarks :
39 || Change History :
40 || Who When What
41 || (reverse chronological order - newest change first)
42 */
43 l_ci_cal_type igs_ca_inst.cal_type%TYPE;
44 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
45 l_tot_fseog NUMBER := 0;
46 l_tot_match NUMBER := 0;
47 l_total NUMBER := 0;
48 l_fseog_pcntg NUMBER(5,2) := 0;
49 l_match_pcntg NUMBER(5,2) := 0;
50 l_alternate_code igs_ca_inst.alternate_code%TYPE;
51
52 --Cursor to find the SUM of Amounts for FSEOG funds
53 CURSOR c_fseog_sum(cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
54 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE)
55 IS
56 SELECT SUM (NVL (DECODE (
57 p_sum_type,
58 'PAID', awd.paid_amt,
59 'OFFERED', awd.offered_amt,
60 'ACCEPTED', awd.accepted_amt
61 ),
62 0)) total_fseog
63 FROM igf_aw_award_all awd,
64 igf_aw_fund_mast_all fmast,
65 igf_aw_fund_cat_all fcat
66 WHERE fcat.fed_fund_code = 'FSEOG'
67 AND fmast.ci_cal_type = cp_ci_cal_type
68 AND fmast.ci_sequence_number = cp_ci_sequence_number
69 AND fmast.fund_code = fcat.fund_code
70 AND awd.fund_id = fmast.fund_id;
71
72 --Cursor to find the SUM of Amounts for Matching funds
73 CURSOR c_match_sum(cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
74 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE)
75 IS
76 SELECT SUM (NVL (DECODE (
77 p_sum_type,
78 'PAID', awd.paid_amt,
79 'OFFERED', awd.offered_amt,
80 'ACCEPTED', awd.accepted_amt
81 ),
82 0)) total_match
83 FROM igf_aw_award_all awd
84 WHERE awd.fund_id IN (
85 SELECT fund_id
86 FROM igf_aw_fseog_match
87 WHERE ci_cal_type = cp_ci_cal_type
88 AND ci_sequence_number = cp_ci_sequence_number);
89
90 --Cursor to find the User Parameter Award Year (which is same as Alternate Code)
91 CURSOR c_alternate_code(cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
92 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
93 SELECT alternate_code
94 FROM igs_ca_inst
95 WHERE cal_type = cp_ci_cal_type
96 AND sequence_number = cp_ci_sequence_number;
97
98 BEGIN
99 retcode := 0 ;
100 igf_aw_gen.set_org_id(p_org_id);
101
102 --Cal Type and Sequence Number are retrived from parameter Award Year
103 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10))) ;
104 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11)) ;
105
106 OPEN c_alternate_code(l_ci_cal_type,l_ci_sequence_number);
107 FETCH c_alternate_code INTO l_alternate_code;
108 CLOSE c_alternate_code;
109
110 log_messages('Award Year : ',l_alternate_code);
111 log_messages('Sum Type : ',p_sum_type);
112
113 OPEN c_fseog_sum(l_ci_cal_type,l_ci_sequence_number);
114 FETCH c_fseog_sum INTO l_tot_fseog;
115 CLOSE c_fseog_sum;
116
117 OPEN c_match_sum(l_ci_cal_type,l_ci_sequence_number);
118 FETCH c_match_sum INTO l_tot_match;
119 CLOSE c_match_sum;
120
121 l_total:= NVL(l_tot_fseog,0) + NVL(l_tot_match,0);
122
123 IF l_total <> 0 THEN
124 l_fseog_pcntg := (NVL(l_tot_fseog,0)/l_total) * 100;
125 l_match_pcntg := (NVL(l_tot_match,0)/l_total) * 100;
126 END IF;
127
128 FND_MESSAGE.SET_NAME('IGF','IGF_AW_FSEOG_PERCENTAGE');
129 FND_MESSAGE.SET_TOKEN('FSEOG_PCNTG',l_fseog_pcntg);
130 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
131
132 FND_MESSAGE.SET_NAME('IGF','IGF_AW_MATCH_PERCENTAGE');
133 FND_MESSAGE.SET_TOKEN('MATCH_PCNTG',l_match_pcntg);
134 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 retcode := 2;
139 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
140 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
141 END aggregate_match;
142 END igf_aw_fisap;