1 PACKAGE BODY pa_expenditure_inquiry AS
2 -- $Header: PAXEXINB.pls 120.2.12020000.2 2012/07/19 10:03:55 admarath ship $
3 --================================================================
4
5
6 -- This function will be used in the view to determine the mode
7 FUNCTION Get_Mode RETURN VARCHAR2 IS
8 BEGIN
9 RETURN(pa_expenditure_inquiry.X_Calling_Mode);
10 END Get_Mode ;
11
12 FUNCTION Get_expenditure_item_id RETURN NUMBER IS
13 BEGIN
14 RETURN(pa_expenditure_inquiry.X_expenditure_item_id);
15 END Get_expenditure_item_id ;
16
17 /* Added for bug#9929147 Starts */
18 FUNCTION Get_application_id RETURN NUMBER IS
19 BEGIN
20 RETURN(pa_expenditure_inquiry.X_application_id);
21 END Get_application_id ;
22
23 FUNCTION Get_source_dist_type RETURN VARCHAR2 IS
24 BEGIN
25 RETURN(pa_expenditure_inquiry.X_source_dist_type);
26 END Get_source_dist_type ;
27 /*Added for bug#9929147 Ends*/
28 -- This function will be used in the view to determine the criteria(Bug#680401)
29 FUNCTION Get_Criteria RETURN VARCHAR2 IS
30 BEGIN
31 RETURN(pa_expenditure_inquiry.X_Query_criteria);
32 END Get_Criteria ;
33
34
35 PROCEDURE pa_expenditure_inquiry_driver (
36 x_Mode IN VARCHAR2) IS
37 BEGIN
38 X_Calling_Mode := x_Mode ;
39 END pa_expenditure_inquiry_driver;
40
41 PROCEDURE pa_expenditure_item_driver (
42 x_eiid IN NUMBER) IS
43 BEGIN
44 X_expenditure_item_id := x_eiid ;
45 END pa_expenditure_item_driver;
46
47 /* Added for bug#9929147 Starts */
48 PROCEDURE pa_source_dist_type_driver (
49 p_source_dist_type IN VARCHAR2) IS
50 BEGIN
51 X_source_dist_type := p_source_dist_type ;
52 END pa_source_dist_type_driver;
53
54 PROCEDURE pa_application_id_driver (
55 x_appid IN NUMBER) IS
56 BEGIN
57 X_application_id := x_appid ;
58 END pa_application_id_driver;
59 /* Added for bug#9929147 Ends */
60 -- (Bug#680401)
61 PROCEDURE pa_expenditure_criteria_driver (
62 x_Criteria IN VARCHAR2) IS
63 BEGIN
64 X_Query_criteria := x_Criteria ;
65 END pa_expenditure_criteria_driver;
66
67 PROCEDURE pa_get_cdl_details( p_expenditure_item_id IN NUMBER,
68 x_vendor_id IN OUT NOCOPY NUMBER,
69 x_system_reference2 IN OUT NOCOPY VARCHAR2,
70 x_vendor_name IN OUT NOCOPY VARCHAR2,
71 x_vendor_number IN OUT NOCOPY VARCHAR2,
72 x_burden_sum_rej_code IN OUT NOCOPY VARCHAR2) is
73
74 cursor GetCdlInfo is
75 select vend.vendor_id,
76 cdl.system_reference2,
77 vend.vendor_name,
78 vend.segment1,
79 cdl.burden_sum_rejection_code
80 from po_vendors vend,
81 PA_COST_DIST_LINES_ALL_BAS cdl
82 where cdl.expenditure_item_id = p_expenditure_item_id
83 and cdl.line_num (+) = 1
84 /* Added the getNumericString wrapper over systeem_reference1 for bug3158748 */
85 and pa_utils4.getNumericString(cdl.system_reference1) = vend.vendor_id (+) ;
86 /* Start: Added temp variables for bug 7283824 */
87 l_exp_id pa_expenditure_items_all.expenditure_id%type;
88 l_vendor_id po_vendors.vendor_id%type;
89 l_system_reference2 pa_cost_dist_lines_all_bas.system_reference2%type;
90 l_vendor_name po_vendors.vendor_name%type;
91 l_vendor_number po_vendors.segment1%type;
92 l_burden_sum_rej_code pa_cost_dist_lines_all_bas.burden_sum_rejection_code%type;
93 /* End: Added temp variables for bug 7283824 */
94
95 BEGIN
96
97 open GetCdlInfo ;
98
99 /* Start: Commented as part of the Bug 7283824
100 fetch GetCdlInfo
101 into x_vendor_id,
102 x_system_reference2,
103 x_vendor_name,
104 x_vendor_number,
105 x_burden_sum_rej_code ;
106 End: Commented as part of the Bug 7283824 */
107
108 /* Start: Added as part of the Bug 7283824 */
109 fetch GetCdlInfo
110 into l_vendor_id,
111 l_system_reference2,
112 l_vendor_name,
113 l_vendor_number,
114 l_burden_sum_rej_code ;
115
116 close GetCdlInfo;
117
118 x_system_reference2:=l_system_reference2;
119 x_burden_sum_rej_code:=l_burden_sum_rej_code ;
120
121 if (l_vendor_id IS NOT NULL AND l_vendor_name IS NOT NULL AND l_vendor_number IS NOT NULL ) Then
122 x_vendor_id:= l_vendor_id;
123 x_vendor_name:=l_vendor_name;
124 x_vendor_number:=l_vendor_number;
125
126 else
127
128 select expenditure_id into l_exp_id
129 from pa_expenditure_items_all
130 where
131 expenditure_item_id=p_expenditure_item_id;
132
133 select po.vendor_id,po.vendor_name,po.segment1
134 into x_vendor_id,x_vendor_name,x_vendor_number
135 from po_vendors po
136 where
137 po.vendor_id=(select vendor_id from pa_expenditures_all exp where expenditure_id=l_exp_id);
138
139 end if;
140 /* End: Added as part of the Bug 7283824 */
141
142 EXCEPTION
143 when no_data_found then
144 /* Start: Commented as part of the Bug 7283824
145 x_vendor_id := Null;
146 x_system_reference2 := Null;
147 x_vendor_name := Null;
148 x_vendor_number := Null;
149 x_burden_sum_rej_code := Null;
150 End: Commented as part of the Bug 7283824 */
151 NULL ;
152 when others then
153 /* Start: Commented as part of the Bug 7283824
154 x_vendor_id := Null;
155 x_system_reference2 := Null;
156 x_vendor_name := Null;
157 x_vendor_number := Null;
158 x_burden_sum_rej_code := Null;
159 End: Commented as part of the Bug 7283824 */
160 RAISE ;
161
162 END pa_get_cdl_details;
163
164 END pa_expenditure_inquiry ;
[Home] [Help]