1 package body WIP_WS_CUSTOM as
2 /* $Header: wipwsctb.pls 120.9.12020000.3 2012/08/24 11:57:19 sisankar ship $ */
3
4
5 function get_orderby_attribute_1(p_wip_entity_id number, p_op_seq number) return varchar2
6 is
7 begin
8 -- fill in implemenation if needed
9 null;
10 return(null);
11 end;
12
13 function get_orderby_attribute_2(p_wip_entity_id number, p_op_seq number) return varchar2
14 is
15 begin
16 -- fill in implemenation if needed
17 null;
18 return(null);
19 end;
20
21 function get_related_jobs(p_wip_entity_id number) return varchar2
22 is
23 begin
24 -- fill in implemenation if needed
25 null;
26 return(null);
27 end;
28
29 function get_custom_ready_status(
30 wip_entity_id in number,
31 operation_seq_num in number,
32 serial_number in varchar2,
33 attribute1 in varchar2,
34 attribute2 in varchar2,
35 attribute3 in varchar2
36 ) return varchar2
37 is
38 begin
39 -- fill in implemenation if needed
40
41 return 'Y';
42 end get_custom_ready_status;
43
44 procedure validate_transaction(
45 wip_entity_id in number,
46 mtl_header_id in number,
47 txn_type in varchar2,
48 from_op_seq in number,
49 from_step in varchar2,
50 to_op_seq in number,
51 to_step in varchar2,
52 txn_quantity in number,
53 txn_uom in varchar2,
54 scrap_quantity in number,
55 reject_quantity in number,
56 subinv in varchar2,
57 locator_id in number,
58 assembly_lot in varchar2,
59 assembly_serial in varchar2,
60 reason_id in number,
61 reference_str in varchar2,
62 sales_order_id in varchar2,
63 sales_order_line_id in number,
64 overcompletion in string,
65 project_id in number,
66 task_id in number,
67 scrap_acct_id in number,
68 kanban_id in number,
69 attribute1 in varchar2,
70 attribute2 in varchar2,
71 attribute3 in varchar2,
72 attribute4 in varchar2,
73 attribute5 in varchar2,
74 return_status out NOCOPY varchar2,
75 return_message out NOCOPY varchar2,
76 return_attribute1 out NOCOPY varchar2,
77 return_attribute2 out NOCOPY varchar2
78 ) is
79 begin
80 -- fill in implemenation if needed
81 return_status := 'Y';
82 return_message := 'custom message';
83 end validate_transaction;
84
85
86 procedure reorder_ops_for_shortage (
87 p_wip_job_op_tbl IN OUT NOCOPY WIP_WS_SHORTAGE.wip_job_op_tbl_type,
88 p_return_status OUT NOCOPY VARCHAR2,
89 p_retcode OUT NOCOPY NUMBER) IS
90
91 begin
92 null;
93 --fill in implementation if needed
94 p_return_status := 'S';
95 end reorder_ops_for_shortage;
96
97 PROCEDURE LOT_HOOK(
98 org_id in NUMBER,
99 wip_entity_id in NUMBER,
100 operation_seq_num in NUMBER,
101 repetitive_schedule_id in NUMBER,
102 inventory_item_id in NUMBER,
103 supply_subinv in VARCHAR2,
104 supply_locator_id in NUMBER,
105 revision in VARCHAR2,
106 primary_qty in NUMBER,
107 lot_number IN OUT NOCOPY VARCHAR2,
108 return_status OUT NOCOPY VARCHAR2, --Values 'S': Success and 'E': Error
109 return_message OUT NOCOPY VARCHAR2) IS
110 begin
111 null;
112 lot_number := '';
113 return_status := 'S';
114 return_message := 'custom message';
115 end LOT_HOOK;
116
117 PROCEDURE MOVE_TO_STEP_HOOK(
118 org_id IN NUMBER,
119 wip_entity_id IN NUMBER,
120 fm_op_seq IN NUMBER,
121 fm_op_step_type IN NUMBER,
122 fm_dept_id IN NUMBER,
123 to_op_seq IN OUT NOCOPY NUMBER,
124 to_op_step_type IN OUT NOCOPY NUMBER,
125 return_status OUT NOCOPY VARCHAR2, --Values 'S': Success and 'E': Error
126 return_message OUT NOCOPY VARCHAR2) IS
127 begin
128 null;
129
130 to_op_seq := null;
131 to_op_step_type := null;
132 return_status := 'S';
133 return_message := 'custom message';
134 end MOVE_TO_STEP_HOOK;
135
136 /* Hook For validating Component Serial while pressing Add in Exp transact component page. */
137 PROCEDURE validate_comp_serial( p_org_id IN NUMBER,
138 p_wip_entity_id IN NUMBER,
139 p_assy_serial IN VARCHAR2,
140 p_op_seq_num IN NUMBER,
141 p_comp_item_id IN NUMBER,
142 p_comp_serial IN VARCHAR2,
143 p_quantity IN NUMBER,
144 p_txn_type_id IN NUMBER,
145 p_return_status OUT NOCOPY VARCHAR2,
146 p_errmsg OUT NOCOPY VARCHAR2)
147 IS
148 BEGIN
149 /*p_return_status should return E/S/W/I.
150 S- will add component serial successfully.
151 W- will add component serial along with warning message.
152 I- will add component serial along with information message.
153 E- will throw error message and doesn't add the component serial.*/
154 p_return_status := 'S';
155 p_errmsg := null;
156 END validate_comp_serial;
157
158 /* Hook to get Component issued Quantity per Assembly Serial in Exp transact component page as well as view assembly serial Page. */
159 Function get_assy_serial_issue_qty(p_org_id IN NUMBER,
160 p_job_id IN NUMBER,
161 p_op_seq IN NUMBER,
162 p_assy_item_id IN NUMBER,
163 p_assy_serial IN VARCHAR2,
164 p_comp_item_id IN NUMBER)
165 return Number
166 IS
167 l_issue_qty Number := null;
168 BEGIN
169 /* Customize by mentioning the correct column of DFF which is mapped to Assembly Serial Number in the below SQL. */
170 /*
171 select abs(nvl(sum(mmt.transaction_quantity),0))
172 into l_issue_qty
173 from mtl_material_transactions mmt
174 where mmt.transaction_source_type_id = 5
175 and mmt.transaction_source_id = p_job_id
176 and mmt.organization_id= p_org_id
177 and mmt.inventory_item_id = p_comp_item_id
178 and mmt.operation_seq_num = p_op_seq
179 and mmt.transaction_action_id in (1,27,33,34)
180 and mmt.attributeXX = p_assy_serial;
181 */
182 return l_issue_qty;
183 END get_assy_serial_issue_qty;
184
185 /* Hook to get the DFF Column Mapping for Assembly Serial in Exp transact component page. */
186 Function get_assy_serial_dff_column
187 return Number
188 IS
189 l_attr_col Number := null;
190 BEGIN
191 /* Customize by defining the Transaction History DFF column mapping to Assembly Serial Number below .
192 Valid values are only values between 1-15 which maps to the corresponding columns Attribute1 - Attribute15 . */
193 /* l_attr_col := 1; */
194 return l_attr_col;
195 END get_assy_serial_dff_column;
196
197 begin
198 -- Initialization
199 null;
200 end WIP_WS_CUSTOM;
201