1 PACKAGE WIP_SFCB_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: wipsfcbs.pls 120.0.12010000.2 2008/12/15 10:08:53 adasa ship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE Populate_Efficiency
7 (p_group_id IN NUMBER,
8 p_organization_id IN NUMBER,
9 p_date_from IN DATE,
10 p_date_to IN DATE,
11 p_department_id IN NUMBER,
12 p_resource_id IN NUMBER,
13 p_userid IN NUMBER,
14 p_applicationid IN NUMBER,
15 p_errnum OUT NOCOPY NUMBER,
16 p_errmesg OUT NOCOPY VARCHAR2 );
17
18
19 PROCEDURE Populate_Utilization
20 (p_group_id IN NUMBER,
21 p_organization_id IN NUMBER,
22 p_date_from IN DATE,
23 p_date_to IN DATE,
24 p_department_id IN NUMBER,
25 p_resource_id IN NUMBER,
26 p_userid IN NUMBER,
27 p_applicationid IN NUMBER,
28 p_errnum OUT NOCOPY NUMBER,
29 p_errmesg OUT NOCOPY VARCHAR2);
30
31
32
33 PROCEDURE Populate_Productivity (
34 p_group_id IN NUMBER,
35 p_organization_id IN NUMBER,
36 p_date_from IN DATE,
37 p_date_to IN DATE,
38 p_department_id IN NUMBER,
39 p_resource_id IN NUMBER,
40 p_userid IN NUMBER,
41 p_applicationid IN NUMBER,
42 p_errnum OUT NOCOPY NUMBER,
43 p_errmesg OUT NOCOPY VARCHAR2);
44
45 PROCEDURE Populate_Resource_Load (
46 p_group_id IN NUMBER,
47 p_organization_id IN NUMBER,
48 p_date_from IN DATE,
49 p_date_to IN DATE,
50 p_department_id IN NUMBER,
51 p_resource_id IN NUMBER,
52 p_userid IN NUMBER,
53 p_applicationid IN NUMBER,
54 p_errnum OUT NOCOPY NUMBER,
55 p_errmesg OUT NOCOPY VARCHAR2);
56
57 PROCEDURE Resource_Txn (
58 p_DEPARTMENT_ID IN NUMBER,
59 p_EMPLOYEE_ID IN NUMBER,
60 p_EMPLOYEE_NUM IN NUMBER,
61 p_LINE_ID IN NUMBER,
62 p_OPERATION_SEQ_NUM IN NUMBER,
63 p_ORGANIZATION_ID IN NUMBER,
64 p_PRIMARY_QUANTITY IN NUMBER,
65 p_PROJECT_ID IN NUMBER,
66 p_REASON_ID IN NUMBER,
67 p_REFERENCE IN VARCHAR2,
68 p_RESOURCE_ID IN NUMBER,
69 p_RESOURCE_SEQ_NUM IN NUMBER,
70 p_REPETITIVE_SCHEDULE_ID IN NUMBER,
71 p_SOURCE_CODE IN VARCHAR2,
72 p_TASK_ID IN NUMBER,
73 p_TRANSACTION_DATE IN DATE,
74 p_TRANSACTION_QUANTITY IN NUMBER,
75 p_WIP_ENTITY_ID IN NUMBER,
76 p_ACCT_PERIOD_ID IN NUMBER DEFAULT NULL,
77 p_ACTIVITY_ID IN NUMBER DEFAULT NULL,
78 p_ACTIVITY_NAME IN VARCHAR2 DEFAULT NULL,
79 p_ACTUAL_RESOURCE_RATE IN NUMBER DEFAULT NULL,
80 p_CREATED_BY IN NUMBER DEFAULT NULL,
81 p_CREATED_BY_NAME IN VARCHAR2 DEFAULT NULL,
82 p_LAST_UPDATED_BY IN NUMBER,
83 p_LAST_UPDATED_BY_NAME IN VARCHAR2 DEFAULT NULL,
84 p_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
85 p_LAST_UPDATE_LOGIN IN NUMBER,
86 p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
87 p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
88 p_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
89 p_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
90 p_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
91 p_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
92 p_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
93 p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
94 p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
95 p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
96 p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
97 p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
98 p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
99 p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
100 p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
101 p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
102 p_AUTOCHARGE_TYPE IN NUMBER DEFAULT NULL,
103 p_BASIS_TYPE IN NUMBER DEFAULT NULL,
104 p_COMPLETION_TRANSACTION_ID IN NUMBER DEFAULT NULL,
105 p_CREATION_DATE IN DATE DEFAULT NULL,
106 p_CURRENCY_ACTUAL_RSC_RATE IN NUMBER DEFAULT NULL,
107 p_CURRENCY_CODE IN VARCHAR2 DEFAULT NULL,
108 p_CURRENCY_CONVERSION_DATE IN DATE DEFAULT NULL,
109 p_CURRENCY_CONVERSION_RATE IN NUMBER DEFAULT NULL,
110 p_CURRENCY_CONVERSION_TYPE IN VARCHAR2 DEFAULT NULL,
111 p_DEPARTMENT_CODE IN VARCHAR2 DEFAULT NULL,
112 p_ENTITY_TYPE IN NUMBER DEFAULT NULL,
113 p_GROUP_ID IN NUMBER DEFAULT NULL,
114 p_LINE_CODE IN VARCHAR2 DEFAULT NULL,
115 p_MOVE_TRANSACTION_ID IN NUMBER DEFAULT NULL,
116 p_ORGANIZATION_CODE IN VARCHAR2 DEFAULT NULL,
117 p_PO_HEADER_ID IN NUMBER DEFAULT NULL,
118 p_PO_LINE_ID IN NUMBER DEFAULT NULL,
119 p_PRIMARY_ITEM_ID IN NUMBER DEFAULT NULL,
120 p_PRIMARY_UOM IN VARCHAR2 DEFAULT NULL,
121 p_PRIMARY_UOM_CLASS IN VARCHAR2 DEFAULT NULL,
122 p_PROCESS_PHASE IN NUMBER DEFAULT NULL,
123 p_PROCESS_STATUS IN NUMBER DEFAULT NULL,
124 p_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
125 p_PROGRAM_ID IN NUMBER DEFAULT NULL,
126 p_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
127 p_RCV_TRANSACTION_ID IN NUMBER DEFAULT NULL,
128 p_REASON_NAME IN VARCHAR2 DEFAULT NULL,
129 p_RECEIVING_ACCOUNT_ID IN NUMBER DEFAULT NULL,
130 p_REQUEST_ID IN NUMBER DEFAULT NULL,
131 p_RESOURCE_CODE IN VARCHAR2 DEFAULT NULL,
132 p_RESOURCE_TYPE IN NUMBER DEFAULT NULL,
133 p_SOURCE_LINE_ID IN NUMBER DEFAULT NULL,
134 p_STANDARD_RATE_FLAG IN NUMBER DEFAULT NULL,
135 p_TRANSACTION_ID IN NUMBER DEFAULT NULL,
136 p_TRANSACTION_TYPE IN NUMBER DEFAULT NULL,
137 p_TRANSACTION_UOM IN VARCHAR2 DEFAULT NULL,
138 p_USAGE_RATE_OR_AMOUNT IN NUMBER DEFAULT NULL,
139 p_WIP_ENTITY_NAME IN VARCHAR2 DEFAULT NULL,
140 p_ret_status OUT NOCOPY VARCHAR2
141 ) ;
142
143
144
145 PROCEDURE Populate_Line_Load (
146 p_group_id IN NUMBER,
147 p_date_from IN DATE,
148 p_date_to IN DATE,
149 p_line_id IN NUMBER,
150 p_userid IN NUMBER,
151 p_applicationid IN NUMBER,
152 p_errnum OUT NOCOPY NUMBER,
153 p_errmesg OUT NOCOPY VARCHAR2);
154
155
156
157
158 PROCEDURE Update_Line_Operation (
159 p_line_operation IN NUMBER,
160 p_wip_entity_id IN NUMBER,
161 p_organization_id IN NUMBER );
162
163
164
165 PROCEDURE set_Organization(p_org_id IN NUMBER);
166 PROCEDURE set_Linearity_Dates(
167 p_from_date IN DATE DEFAULT NULL,
168 p_to_date IN DATE DEFAULT NULL );
169 PROCEDURE set_Line( p_line_id IN NUMBER) ;
170
171 FUNCTION get_Organization RETURN NUMBER;
172 FUNCTION get_Linearity_From_Date RETURN DATE ;
173 FUNCTION get_Linearity_To_Date RETURN DATE ;
174 FUNCTION get_Line RETURN NUMBER ;
175
176 --
177 -- Determines whether a schedule still needs to perform
178 -- the specified line operation.
179 --
180 -- Returns 1 if p_line_op could come after p_current_line_op in the
181 -- specified routing. If p_current_line_op is null, returns 1 if p_line_op
182 -- is in the routing. Returns 2 otherwise.
183 --
184 FUNCTION line_op_is_pending (
185 p_line_op in number,
186 p_rtg_seq_id in number,
187 p_assy_item_id IN NUMBER,
188 p_org_id IN NUMBER,
189 p_alt_rtg_designator IN VARCHAR2,
190 p_current_line_op in NUMBER DEFAULT NULL
191 ) RETURN NUMBER ;
192
193 PROCEDURE Populate_Line_Resource_Load (
194 p_group_id IN NUMBER,
195 p_organization_id IN NUMBER,
196 p_date_from IN DATE,
197 p_date_to IN DATE,
198 p_line_id IN NUMBER,
199 p_line_op_id IN NUMBER,
200 p_userid IN NUMBER,
201 p_applicationid IN NUMBER,
202 p_errnum OUT NOCOPY NUMBER,
203 p_errmesg OUT NOCOPY VARCHAR2);
204
205 FUNCTION Get_Workday_Factor
206 (p_sched_start_date IN DATE,
207 p_sched_completion_date IN DATE,
208 p_date_from IN DATE,
209 p_date_to IN DATE,
210 p_resource_id IN NUMBER,
211 p_organization_id IN NUMBER )
212 RETURN NUMBER ;
213
214 -- Wrapper function for getting all line operations. Makes a call to the
215 -- bom_rtg_network_api, and returns all line operations from the PL/SQL table
216 -- into a deliminated string, so that we can use the values in Java.
217 FUNCTION get_all_line_ops (
218 p_rtg_sequence_id IN NUMBER,
219 p_assy_item_id IN NUMBER,
220 p_org_id IN NUMBER,
221 p_alt_rtg_desig IN VARCHAR2 )
222 RETURN VARCHAR2 ;
223
224 -- Wrapper function to return whether we are at the last line op or not.
225 -- Will return 1 if true, 2 if false. This is needed since we cannot retrieve
226 -- boolean values in Java from PL/SQL calls. Makes a call to function in
227 -- bom_rtg_network_api.
228 FUNCTION check_last_line_op (
229 p_rtg_sequence_id IN NUMBER,
230 p_assy_item_id IN NUMBER,
231 p_org_id IN NUMBER,
232 p_alt_rtg_desig IN VARCHAR2,
233 p_curr_line_op IN NUMBER )
234 RETURN NUMBER ;
235
236
237 /* Pragmas to restrict the references of the various functions */
238
239 PRAGMA RESTRICT_REFERENCES(get_Workday_Factor, WNDS, WNPS);
240 PRAGMA RESTRICT_REFERENCES(get_Organization, WNDS, WNPS);
241 PRAGMA RESTRICT_REFERENCES(get_Linearity_From_Date, WNDS, WNPS);
242 PRAGMA RESTRICT_REFERENCES(get_Linearity_To_Date, WNDS, WNPS);
243 PRAGMA RESTRICT_REFERENCES(get_Line, WNDS, WNPS);
244 PRAGMA RESTRICT_REFERENCES(line_op_is_pending, WNDS);
245
246 /* these api are added to support oracle timezone in the workstation */
247 function displaydate_to_displayDT(p_displaydate IN VARCHAR2) return VARCHAR2;
248 function displaydate_to_date_tz(p_displaydate IN VARCHAR2) return DATE;
249 function displaydt_to_date_tz(p_displaydt IN VARCHAR2) return DATE;
250
251 function date_to_displaydate_tz(p_date IN DATE) return VARCHAR2;
252 function date_to_displaydt_tz(p_date IN DATE) return VARCHAR2;
253
254 function is_validate_displaydate(p_date IN VARCHAR2) return VARCHAR2;
255 function is_validate_displayDT(p_date IN VARCHAR2) return VARCHAR2;
256
257 function sdate_to_cdate(p_sdate IN DATE) return DATE;
258 function cdate_to_sdate(p_cdate IN DATE) return DATE;
259
260 function calculate_dt_range(p_from_dt IN VARCHAR2,
261 p_to_dt IN VARCHAR2) return VARCHAR2;
262
263 procedure init_timezone(p_output_mask IN VARCHAR2, p_outputdt_mask IN VARCHAR2);
264
265 procedure check_attachment_and_contract(p_pkey1 in VARCHAR2,
266 p_pkey2 in VARCHAR2,
270 x_hasContract out nocopy VARCHAR2);
267 p_pkey3 in VARCHAR2,
268 p_jobID in number,
269 x_hasAttachement out nocopy VARCHAR2,
271
272
273 END WIP_SFCB_UTILITIES;
[Home] [Help]