1 PACKAGE WIP_SF_STATUS AUTHID CURRENT_USER AS
2 /* $Header: wipsfsts.pls 120.2 2010/07/14 17:02:06 hliew ship $ */
3
4 /* INSERT_STATUS
5 * Inserts a status into wip_shop_floor_statuses. This procedure
6 * does no validation
7 */
8
9 PROCEDURE INSERT_STATUS
10 (P_wip_entity_id IN NUMBER,
11 P_organization_id IN NUMBER,
12 P_line_id IN NUMBER,
13 P_operation_seq_num IN NUMBER,
14 P_intraoperation_step_type IN NUMBER,
15 P_shop_floor_status IN VARCHAR2);
16
17
18 /* DELETE_STATUS
19 * Inserts a status into wip_shop_floor_statuses. This procedure
20 * does no validation
21 */
22
23 PROCEDURE DELETE_STATUS(
24 P_wip_entity_id IN NUMBER,
25 P_organization_id IN NUMBER,
26 P_line_id IN NUMBER,
27 P_operation_seq_num IN NUMBER,
28 P_intraoperation_step_type IN NUMBER,
29 P_shop_floor_status IN VARCHAR2);
30
31 /* ATTACH
32 This function attaches a shop floor status to a step of an operation. The
33 following validations are performed. If any of the validations fail, an
34 appropriate message is placed on the message stack and an exception is
35 raised.
36 * For discrete jobs, the status must be Unreleased, Released, Complete, or
37 On hold.
38 * For repetitive assemblies, there must be at least one schedule on the
39 line with status Unreleased, Released, Complete, or On hold.
40 * The operation sequence must exist on the routing.
41 * The intraopertion step must be enabled for the organization.
42 * The shop floor status must be enabled.
43 No other validations are performed (i.e. such as whether the operations
44 really exists for the job or repetitive assembly).
45
46 PARAMETERS: No other validation is done on the parameters.
47 P_intraoperation_step_type should be one of he following:
48 WIP_CONSTANTS.QUEUE, WIP_CONSTANTS.RUN, WIP_CONSTANTS.TOMOVE,
49 WIP_CONSTANTS.REJECT, WIP_CONSTANTS.SCRAP
50 P_line_id should be NULL if the status is being attached to a job.
51 */
52
53 PROCEDURE ATTACH
54 (P_wip_entity_id NUMBER,
55 P_organization_id NUMBER,
56 P_line_id NUMBER,
57 P_operation_seq_num NUMBER,
58 P_intraoperation_step_type NUMBER,
59 P_shop_floor_status VARCHAR2);
60
61 /* CREATE_OSP_STATUS
62 * Gets osp_shop_floor_status from wip_parameters, and gets line_id
63 * from wip_repetitive_schedules. Then calls Insert_Status.
64 */
65
66 FUNCTION GetOSPStatus (p_org_id NUMBER) return VARCHAR2;
67
68 PROCEDURE CREATE_OSP_STATUS(
69 P_org_id IN NUMBER,
70 P_wip_entity_id IN NUMBER,
71 P_repetitive_sched_id IN NUMBER DEFAULT NULL,
72 P_operation_seq_num IN NUMBER DEFAULT NULL
73 );
74
75 /* REMOVE_OSP_STATUS
76 * Gets osp_shop_floor_status from wip_parameters, and gets line_id
77 * from wip_repetitive_schedules. Then calls Delete_Status.
78 */
79
80 PROCEDURE REMOVE_OSP_STATUS(
81 P_org_id IN NUMBER,
82 P_wip_entity_id IN NUMBER,
83 P_repetitive_sched_id IN NUMBER DEFAULT NULL,
84 P_operation_seq_num IN NUMBER DEFAULT NULL
85 );
86
87 /* COUNT_NO_MOVE_STATUSES
88 This function returns the number of No Move shop floor statuses
89 between the given From Operation and Intraoperation Step and the
90 To Operation and Intraoperation Step. Step types of scrap and reject
91 are excluded from the count. If the organization allows moves over
92 no move statuses, then the count will be 0.
93
94 This function is pragma'd with restrict_references so that it can be
95 called in a SQL statement.
96 */
97 FUNCTION COUNT_NO_MOVE_STATUSES(
98 p_org_id in number,
99 p_wip_id in number,
100 p_line_id in number,
101 p_sched_id in number,
102 p_fm_op in number,
103 p_fm_step in number,
104 p_to_op in number,
105 p_to_step in number,
106 p_source_code in Varchar2 default null) return number;
107 /* Added in bug 2121222 */
108
109 pragma restrict_references (COUNT_NO_MOVE_STATUSES, WNDS, WNPS);
110
111 /* This function returns the number of no move shop floor status at to move
112 step of the last operation. This function will return either 1 or 0. If
113 a job does not have routing, this API will return 0.
114 */
115 FUNCTION count_no_move_last_step(
116 p_org_id IN NUMBER,
117 p_wip_id IN NUMBER) RETURN NUMBER;
118
119 /* GET_NO_MOVE_STATUSES
120 This function returns the No Move shop floor statuses
121 between the given From Operation and Intraoperation Step and the
122 To Operation and Intraoperation Step.
123 */
124 FUNCTION GET_NO_MOVE_STATUS(
125 p_org_id in number,
126 p_wip_id in number,
127 p_line_id in number,
128 p_sched_id in number,
129 p_fm_op in number,
130 p_fm_step in number,
131 p_to_op in number,
132 p_to_step in number,
133 p_source_code in Varchar2 default null) return varchar2;
134
135 END WIP_SF_STATUS;