PACKAGE BODY: APPS.IGW_AWARD_PARTICULARS_PKG

Source

1 PACKAGE BODY IGW_AWARD_PARTICULARS_PKG as
2 --$Header: igwprsub.pls 115.10 2002/11/14 18:38:31 vmedikon ship $
3   procedure get_award_costs(
4                 i_award_id in NUMBER,
5                 i_proposal_id in NUMBER,
6                 o_direct_cost out NOCOPY NUMBER,
7                 o_total_cost out NOCOPY NUMBER) is
8   prop_start_date    date;
9 
10   Begin
11       select proposal_start_date
12       into prop_start_date
13       from igw_proposals
14       where proposal_id = i_proposal_id;
15 
16       select sum(direct_cost), sum(direct_cost + indirect_cost)
17       into o_direct_cost, o_total_cost
18       from gms_installments
19       where award_id = i_award_id and
20           active_flag = 'Y' and
21           prop_start_date >= start_date_active and
22           prop_start_date <= nvl(end_date_active, sysdate);
23    Exception
24        when no_data_found then
25            o_direct_cost := null;
26            o_total_cost := null;
27    End get_award_costs;
28  ---------------------------------------------------------------------------------------------------
29   procedure get_old_award_costs(
30                 i_award_id in NUMBER,
31                 o_direct_cost out NOCOPY NUMBER,
32                 o_total_cost out NOCOPY NUMBER) is
33 
34   Begin
35       select sum(direct_cost), sum(direct_cost + indirect_cost)
36       into o_direct_cost, o_total_cost
37       from gms_installments
38       where award_id = i_award_id;
39    Exception
40        when no_data_found then
41            o_direct_cost := null;
42            o_total_cost := null;
43    End get_old_award_costs;
44  ---------------------------------------------------------------------------------------------------
45 
46   Procedure get_pi (i_award_id in NUMBER, i_proposal_id in NUMBER,
47                               o_pi_id out NOCOPY NUMBER, o_pi_name out NOCOPY VARCHAR2) is
48   prop_start_date    date;
49   Begin
50       select proposal_start_date
51       into prop_start_date
52       from igw_proposals
53       where proposal_id = i_proposal_id;
54 
55       o_pi_id := 0;
56       o_pi_name := 'pi';
57 
58       select gp.person_id, ppx.full_name
59       into o_pi_id, o_pi_name
60       from gms_personnel gp, per_people_x ppx
61       where gp.award_id = i_award_id and
62             gp.award_role = 'AM' and
63             prop_start_date >= gp.start_date_active and
64             prop_start_date <= nvl(gp.end_date_active, sysdate) and
65             gp.person_id = ppx.person_id;
66    Exception
67        when no_data_found then
68        begin
69            select gp.person_id, ppx.full_name
70            into o_pi_id, o_pi_name
71            from gms_personnel gp, per_people_x ppx
72            where gp.award_id = i_award_id and
73                  gp.award_role = 'AM' and
74                  gp.person_id = ppx.person_id and
75                  rownum = 1;
76         Exception
77              when no_data_found then
78                 o_pi_id := null;
79                 o_pi_name := null;
80         End;
81    End get_pi;
82 
83   END IGW_AWARD_PARTICULARS_PKG;