1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
| CREATE OR REPLACE
PACKAGE "STAFF_PROF" AS
TYPE LOAD_REC_BUD IS RECORD ( n_reqid NUMBER,
v_reqname VARCHAR2(200),
v_reqtype VARCHAR2(40),
v_expense_type VARCHAR2(60),
v_category VARCHAR2(60),
v_expense_code VARCHAR2(60),
v_labor_type VARCHAR2(60),
v_period_name VARCHAR2(30),
v_amout NUMBER);
TYPE LOAD_REC_BUD_FULL IS RECORD ( n_reqid NUMBER, --Proposale Or Project Number
v_reqname VARCHAR2(200), --Proposale or Project Name
v_reqtype VARCHAR2(40), --Proposale or Project
v_budid NUMBER, --Budget ID
v_businesspurpose VARCHAR2(60),
v_product VARCHAR2(100),
v_subsidiary VARCHAR2(60),
v_plan VARCHAR2(60),
v_platform VARCHAR2(100),
v_origin VARCHAR2(200),
v_security VARCHAR2(200),
v_module VARCHAR2(200),
v_oldtmscode VARCHAR2(200),
v_expense_type VARCHAR2(60),
v_category VARCHAR2(60),
v_expense_code VARCHAR2(60),
v_labor_type VARCHAR2(60),
v_period_name VARCHAR2(30),
v_working_days NUMBER,
v_amount NUMBER);
TYPE load_set_bud_full IS TABLE OF LOAD_REC_BUD_FULL;
FUNCTION BUD_REQ_POS
( P_SCENARIO_ID IN NUMBER,
P_START_PERIOD IN NUMBER DEFAULT NULL,
P_FINISH_PERIOD IN NUMBER DEFAULT NULL,
P_PERIOD_TYPE IN VARCHAR2 DEFAULT 'M')
RETURN load_set_bud_full PIPELINED;
END;
CREATE OR REPLACE
PACKAGE BODY "STAFF_PROF" AS
FUNCTION BUD_REQ_POS
( P_SCENARIO_ID IN NUMBER,
P_START_PERIOD IN NUMBER DEFAULT NULL,
P_FINISH_PERIOD IN NUMBER DEFAULT NULL,
P_PERIOD_TYPE IN VARCHAR2 DEFAULT 'M')
RETURN load_set_bud_full PIPELINED IS
TYPE req_list_values IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_req_list_values req_list_values;
l_prj_bud_id NUMBER; -- Project Budget ID
l_loads_set_bud_full LOAD_REC_BUD_FULL;
TYPE num_list_values IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_list_values num_list_values;
n_is_project NUMBER;
l_rec_cursor LOAD_REC;
l_rec_bud_full LOAD_REC_BUD_FULL;
CURSOR c_loads_bud_values(C_BUDGET_ID NUMBER) IS
SELECT work_item_id , work_item_name ,work_item, expense_type_code , category_meaning
, budget_line_udata2 EXPENSE_CODE, labor_type_code LABOR_TYPE
, to_char(to_date(to_char(period_month),'YYYYMM'),'MM/YYYY') PERIOD
, sum(forecast_value) AMOUNT
FROM bud_lines_v
WHERE 1=1
and budget_id=C_BUDGET_ID
and period_id>=P_START_PERIOD
and period_id<=P_FINISH_PERIOD
group by work_item_id,work_item_name,work_item,budget_line_udata2,expense_type_code
,category_meaning,labor_type_code,period_month
order by work_item_name,to_char(to_date(period_month),'Q YYYY');
l_count NUMBER;
l_load_bud_values c_loads_bud_values%ROWTYPE;
n_reqid NUMBER;
v_reqname VARCHAR2(200);
v_reqtype VARCHAR2(40);
v_expense_type VARCHAR2(60);
v_businesspurpose VARCHAR2(60);
v_product VARCHAR2(100);
v_subsidiary VARCHAR2(60);
v_plan VARCHAR2(60);
v_platform VARCHAR2(100);
v_origin VARCHAR2(200);
v_security VARCHAR2(200);
v_module VARCHAR2(200);
v_oldtmscode VARCHAR2(200);
BEGIN
select distinct(ksc.request_id) as request_id
bulk collect into l_req_list_values
from t_scen_cont ksc, k_req_ts krt ,k_reqs kr
where ksc.s_comp_id=P_SCENARIO_ID
and kr.req_id=ksc.request_id
and krt.req_t_id in kr.req_t_id;
IF l_req_list_values.FIRST IS NOT NULL THEN
FOR i IN l_req_list_values.FIRST..l_req_list_values.LAST LOOP
select count(prj_project_id)
into l_count
from t_proj
where request_id=l_req_list_values(i);
if l_count>0 then --is a project
--get the project_request_id and Budget_ID
select prj_budget_id
into l_prj_bud_id
from t_proj
where request_id=l_req_list_values(i);
v_reqtype:='Project';
else --is a proposal
select prop_budget_id
into l_prj_bud_id
from t_pro
where req_id=l_req_list_values(i);
v_reqtype:='Proposal';
end if;
select visible_parameter1 ,visible_parameter2
,visible_parameter13 ,visible_parameter9
,visible_parameter4 ,visible_parameter10
,visible_parameter11 ,visible_parameter6
,visible_parameter8
into v_businesspurpose,v_product,v_subsidiary,v_plan,v_platform,v_origin
,v_security,v_module,v_oldtmscode
from req_de krd
where krd.batch_number=1
and request_id=l_req_list_values(i);
OPEN c_loads_bud_values(l_prj_bud_id);
LOOP
==>error ici FETCH c_loads_bud_values INTO l_load_bud_values;
l_loads_set_bud_full.n_reqid := l_req_list_values(i);
l_loads_set_bud_full.v_reqname := l_load_bud_values.work_item_name;
l_loads_set_bud_full.v_reqtype := v_reqtype;
l_loads_set_bud_full.v_budid := l_prj_bud_id;
l_loads_set_bud_full.v_businesspurpose := v_businesspurpose;
l_loads_set_bud_full.v_product := v_product;
l_loads_set_bud_full.v_subsidiary := v_subsidiary;
l_loads_set_bud_full.v_plan := v_plan;
l_loads_set_bud_full.v_platform := v_platform;
l_loads_set_bud_full.v_origin := v_origin;
l_loads_set_bud_full.v_security := v_security;
l_loads_set_bud_full.v_module := v_module;
l_loads_set_bud_full.v_oldtmscode := v_oldtmscode;
l_loads_set_bud_full.v_expense_type := l_load_bud_values.expense_type_code;
l_loads_set_bud_full.v_category := l_load_bud_values.category_meaning;
l_loads_set_bud_full.v_expense_code := l_load_bud_values.EXPENSE_CODE;
l_loads_set_bud_full.v_labor_type := l_load_bud_values.LABOR_TYPE;
l_loads_set_bud_full.v_period_name := l_load_bud_values.PERIOD;
l_loads_set_bud_full.v_working_days := 0;
l_loads_set_bud_full.v_amount := l_load_bud_values.AMOUNT;
if l_load_bud_values.AMOUNT>0 then
PIPE ROW(l_loads_set_bud_full);
end if;
EXIT WHEN c_loads_bud_values%NOTFOUND;
END LOOP;
CLOSE c_loads_bud_values;
END LOOP;
END IF;
RETURN;
END;
END; |
ORA-01861: literal does not match format string
Répondre avec citation
Partager