1 package WIP_WS_PTPKPI_UTIL as
2 /* $Header: WIPWSPUS.pls 120.4 2008/01/14 20:41:13 ksuleman noship $ */
3
4 type shift_info_t is record (
5 shift_id varchar2(1000),
6 from_date_char varchar2(1000),
7 to_date_char varchar2(1000),
8 -- shift_start_time_char varchar2(50), -- 08:00 - 17:00
9 -- shift_end_time_char varchar2(50),
10 from_date date,
11 to_date date,
12 shift_num number,
13 seq_num number,
14 display varchar(1000)
15 );
16
17 function get_calendar_code(
18 p_organization_id in number
19 ) return varchar2;
20
21 function get_primary_uom_code(
22 p_org_id in number,
23 p_wip_entity_id in number
24 ) return varchar2;
25
26 function get_project_id(
27 p_org_id in number,
28 p_wip_entity_id in number
29 ) return number;
30
31 function get_task_id(
32 p_org_id in number,
33 p_wip_entity_id in number
34 ) return number;
35
36 function get_operation_lead_time(
37 p_org_id in number,
38 p_wip_entity_id in number,
39 p_op_seq_num in number
40 ) return number;
41
42 function get_shift_id_for_date(
43 p_org_id in number,
44 p_dept_id in number,
45 p_resource_id in number,
46 p_date in date
47 ) return varchar2;
48
49 function get_datetime_for_shift(
50 p_org_id in number,
51 p_shift_id in varchar2,
52 p_start_or_end in number
53 ) return date;
54
55 function get_chart_str_for_shift(
56 p_org_id in number,
57 p_shift_id in varchar2
58 ) return varchar2;
59
60 function get_chart_str_for_day(
61 p_org_id in number,
62 p_shift_id in varchar2
63 ) return varchar2;
64
65 procedure load_shift_information(
66 p_org_id in number,
67 p_shift_id in varchar2,
68 x_shift_day out nocopy date,
69 x_shift_start out nocopy date,
70 x_shift_end out nocopy date,
71 x_shift_chart_str out nocopy varchar2
72 );
73
74 function get_n_previous_working_day(
75 p_org_id number,
76 n number,
77 p_date date
78 ) return date;
79
80 --------------------------------------------------
81 --------------------------------------------------
82 /* start: for ui -- work in progress */
83 procedure get_shifts(
84 p_organization_id in number,
85 p_department_id in number,
86 p_resource_id in number,
87 p_start_shift_date in date,
88 p_end_shift_date in date
89 );
90
91 procedure get_org_shifts(
92 p_organization_id in number,
93 p_start_shift_date in date,
94 p_end_shift_date in date
95 );
96
97 procedure get_dept_resource_shifts(
98 p_organization_id in number,
99 p_department_id in number,
100 p_resource_id in number,
101 p_start_shift_date in date,
102 p_end_shift_date in date
103 );
104
105 /*
106 Find all the candidate shifts that a timestamp of the given day might
107 fall into.
108
109 Suppose we don't know the time part of the timestamp. For the timestamp
110 on a given day, it might belong to any shift
111
112 Take department into consideration.
113 */
114 procedure get_candidate_shifts_for_day(
115 p_organization_id in number,
116 p_department_id in number,
117 p_day in date
118 );
119 /* end: for ui -- work in progress */
120
121 /*
122 Used by UI to construct shift name for a given shift number
123 */
124 function get_shift_name_for_display(
125 p_shift_num in number) return varchar2;
126
127 function get_date_as_string(
128 p_date in date) return varchar2;
129
130 function get_shift_display_str(
131 p_shift_date in date,
132 p_shift_num in number,
133 p_shift_desc in varchar2) return varchar2;
134
135
136 End WIP_WS_PTPKPI_UTIL;
137