1 PACKAGE AP_WEB_UPLOAD_PDM_PKG AS
2 /* $Header: apwupdms.pls 120.4 2006/08/28 20:40:31 rlangi noship $ */
3
4 g_debug_switch VARCHAR2(1) := 'N';
5 g_last_updated_by NUMBER;
6 g_last_update_login NUMBER;
7
8 g_num_recs_processed NUMBER := 0;
9 g_num_locs_created NUMBER := 0;
10 g_num_locs_invalid NUMBER := 0;
11 g_num_locs_zero_rates NUMBER := 0;
12 g_num_std_rates_created NUMBER := 0;
13 g_num_night_rates_created NUMBER := 0;
14 g_num_std_rates_updated NUMBER := 0;
15
16 type Invalid_Locs is table of varchar2(240);
17 g_invalid_locs Invalid_Locs;
18 type Zero_Rates is table of varchar2(240);
19 g_zero_rates Zero_Rates;
20
21 ------------------------------------------------------------------------
22 FUNCTION MyReplace(p_string IN VARCHAR2) RETURN VARCHAR2;
23 ------------------------------------------------------------------------
24
25 ------------------------------------------------------------------------
26 FUNCTION MySoundex(p_string IN VARCHAR2) RETURN VARCHAR2;
27 ------------------------------------------------------------------------
28
29 ------------------------------------------------------------------------
30 FUNCTION GetTerritory(p_country IN VARCHAR2) RETURN VARCHAR2;
31 ------------------------------------------------------------------------
32
33 ------------------------------------------------------------------------
34 FUNCTION GetCityLocation(p_city_locality IN VARCHAR2,
35 p_county IN VARCHAR2,
36 p_state_province IN VARCHAR2,
37 p_country IN VARCHAR2) RETURN NUMBER;
38 ------------------------------------------------------------------------
39
40 ------------------------------------------------------------------------
41 FUNCTION GetRateIncludesMeals(p_per_diem_type_code IN VARCHAR2) RETURN VARCHAR2;
42 FUNCTION GetRateIncludesIncidentals(p_per_diem_type_code IN VARCHAR2) RETURN VARCHAR2;
43 FUNCTION GetRateIncludesAccommodations(p_per_diem_type_code IN VARCHAR2) RETURN VARCHAR2;
44 ------------------------------------------------------------------------
45
46 ------------------------------------------------------------------------
47 PROCEDURE CreateSchedule(errbuf OUT NOCOPY VARCHAR2,
48 retcode OUT NOCOPY NUMBER,
49 p_ratetype IN VARCHAR2,
50 p_expense_category IN VARCHAR2,
51 p_policy_name IN VARCHAR2,
52 p_policy_start_date IN DATE,
53 p_period_name IN VARCHAR2,
54 p_period_start_date IN DATE,
55 p_rate_incl_meals IN VARCHAR2,
56 p_rate_incl_inc IN VARCHAR2,
57 p_rate_incl_acc IN VARCHAR2,
58 p_meals_rate IN VARCHAR2,
59 p_free_meals_ded IN VARCHAR2,
60 p_use_free_acc_add IN VARCHAR2,
61 p_use_free_acc_ded IN VARCHAR2,
62 p_calc_method IN VARCHAR2,
63 p_single_deduction IN NUMBER,
64 p_breakfast_deduction IN NUMBER,
65 p_lunch_deduction IN NUMBER,
66 p_dinner_deduction IN NUMBER);
67 ------------------------------------------------------------------------
68
69 ------------------------------------------------------------------------
70 PROCEDURE UpdateSchedule(errbuf OUT NOCOPY VARCHAR2,
71 retcode OUT NOCOPY NUMBER,
72 p_ratetype IN VARCHAR2,
73 p_expense_category IN VARCHAR2,
74 p_policy_id IN NUMBER,
75 p_period_type IN VARCHAR2,
76 p_period_id IN VARCHAR2,
77 p_period_name IN VARCHAR2,
78 p_period_start_date IN DATE,
79 p_rate_incl_meals IN VARCHAR2,
80 p_rate_incl_inc IN VARCHAR2,
81 p_rate_incl_acc IN VARCHAR2,
82 p_meals_rate IN VARCHAR2,
83 p_free_meals_ded IN VARCHAR2,
84 p_use_free_acc_add IN VARCHAR2,
85 p_use_free_acc_ded IN VARCHAR2,
86 p_calc_method IN VARCHAR2,
87 p_single_deduction IN NUMBER,
88 p_breakfast_deduction IN NUMBER,
89 p_lunch_deduction IN NUMBER,
90 p_dinner_deduction IN NUMBER);
91 ------------------------------------------------------------------------
92
93 ------------------------------------------------------------------------
94 FUNCTION CheckPolicyExists(p_expense_category IN VARCHAR2,
95 p_policy_name IN VARCHAR2) RETURN VARCHAR2;
96 ------------------------------------------------------------------------
97 ------------------------------------------------------------------------
98 FUNCTION CheckPeriodExists(p_policy_id IN VARCHAR2,
99 p_period_name IN VARCHAR2,
100 p_period_start_date IN DATE) RETURN VARCHAR2;
101 ------------------------------------------------------------------------
102 ------------------------------------------------------------------------
103 FUNCTION GetLatestPeriodStartDate(p_policy_id IN VARCHAR2) RETURN DATE;
104 ------------------------------------------------------------------------
105
106 ------------------------------------------------------------------------
107 PROCEDURE UploadRates(errbuf OUT NOCOPY VARCHAR2,
108 retcode OUT NOCOPY NUMBER,
109 p_ratetype IN VARCHAR2,
110 p_action IN VARCHAR2,
111 p_source IN VARCHAR2,
112 p_datafile IN VARCHAR2,
113 p_expense_category IN VARCHAR2,
114 p_policy_id IN NUMBER,
115 p_policy_name IN VARCHAR2,
116 p_policy_start_date IN VARCHAR2,
117 p_period_type IN VARCHAR2,
118 p_period_id IN NUMBER,
119 p_period_name IN VARCHAR2,
120 p_period_start_date IN VARCHAR2,
121 p_rate_incl_meals IN VARCHAR2,
122 p_rate_incl_inc IN VARCHAR2,
123 p_rate_incl_acc IN VARCHAR2,
124 p_meals_rate IN VARCHAR2,
125 p_free_meals_ded IN VARCHAR2,
126 p_use_free_acc_add IN VARCHAR2,
127 p_use_free_acc_ded IN VARCHAR2,
128 p_calc_method IN VARCHAR2,
129 p_single_deduction IN NUMBER,
130 p_breakfast_deduction IN NUMBER,
131 p_lunch_deduction IN NUMBER,
132 p_dinner_deduction IN NUMBER);
133 ------------------------------------------------------------------------
134
135 ------------------------------------------------------------------------
136 PROCEDURE UploadCONUS(errbuf out nocopy varchar2,
137 retcode out nocopy number,
138 p_datafile in varchar2,
139 p_request_status out nocopy varchar2);
140
141 PROCEDURE UploadOCONUS(errbuf out nocopy varchar2,
142 retcode out nocopy number,
143 p_datafile in varchar2,
144 p_request_status out nocopy varchar2);
145 ------------------------------------------------------------------------
146 PROCEDURE ValidateCONUS(errbuf out nocopy varchar2,
147 retcode out nocopy number,
148 p_datafile in varchar2);
149 PROCEDURE ValidateOCONUS(errbuf out nocopy varchar2,
150 retcode out nocopy number,
151 p_datafile in varchar2);
152 ------------------------------------------------------------------------
153 PROCEDURE ValidateFileFormat(errbuf out nocopy varchar2,
154 retcode out nocopy number,
155 p_ratetype in varchar2,
156 p_datafile in varchar2);
157 /*
158 PROCEDURE ValidateFileFormat(p_ratetype in varchar2,
159 p_datafile in varchar2);
160 */
161 ------------------------------------------------------------------------
162
163 END AP_WEB_UPLOAD_PDM_PKG;
[Home] [Help]