[Home] [Help]
PACKAGE BODY: APPS.WIP_REQUIREMENT_DEFAULT
Source
1 Package Body WIP_REQUIREMENT_DEFAULT as
2 /* $Header: wiprqdfb.pls 120.7 2006/05/10 03:36:14 panagara noship $ */
3
4 Procedure Default_Requirement(
5 p_group_id in number,
6 p_wip_entity_id in number,
7 p_organization_id in number,
8 p_substitution_type in number,
9 p_operation_seq_num in number,
10 p_inventory_item_id_old in number,
11 p_inventory_item_id_new in number,
12 p_quantity_per_assembly in number,
13 -- p_required_quantity in number, /* LBM Project not required */
14 p_basis_type in number, /* LBM Project */
15 p_component_yield_factor in number,/*Component Yield Enhancement(Bug 4369064)*/
16 p_err_code out nocopy varchar2,
17 p_err_msg out nocopy varchar2) IS
18
19 x_department_id NUMBER;
20 x_wip_supply_type NUMBER;
21 x_date_required DATE;
22 x_required_quantity NUMBER;
23 x_quantity_issued NUMBER;
24 x_supply_subinventory VARCHAR2(10);
25 x_supply_locator_id NUMBER;
26 x_mrp_net_flag NUMBER;
27 x_mps_required_quantity NUMBER;
28 x_mps_date_required DATE;
29 x_scheduled_start_date DATE; /* Fix for bug 4318495 */
30
31 X_temp_wip_supply_type number;
32 X_start_quantity number;
33 X_job_type number; /* standard 1, non-std: 3 */
34 X_entity_type number;
35
36 /* Added for Bug#3080103 */
37 X_comp_seq_id number ;
38 X_quantity_per_assembly number ;
39 X_substitute_item_quantity number ;
40
41 x_state_num number := 0;
42
43 BEGIN
44
45 IF (p_inventory_item_id_new IS NULL or p_inventory_item_id_new = p_inventory_item_id_old) THEN /*Component Yield Enhancement(Bug 4369064)*/
46 RETURN;
47
48 END IF;
49 begin
50 /* derive p_required_quantity, p_MPS_required_quantity */
51 SELECT start_quantity, scheduled_start_date
52 INTO X_start_quantity, x_scheduled_start_date
53 FROM WIP_DISCRETE_JOBS
54 WHERE wip_entity_id = p_wip_entity_id
55 AND organization_id = p_organization_id;
56
57 x_state_num := x_state_num + 1;
58
59 /* begin LBM Project */
60 if( p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL) then
61 x_required_quantity := round(p_quantity_per_assembly, 6);
62 x_MPS_required_quantity := x_required_quantity;
63 else
64 x_required_quantity :=
65 round(p_quantity_per_assembly * X_start_quantity/nvl(p_component_yield_factor,1), 6);/*Component Yield Enhancement(Bug 4369064)*/
66 x_MPS_required_quantity := x_required_quantity;
67
68 end if;
69 /* end LBM Project */
70
71 /* Derive department_id, date_required and MPS_date_required */
72 begin
73 SELECT department_Id, first_unit_start_date, first_unit_start_date
74 INTO x_department_id, x_date_required, x_MPS_date_required
75 FROM WIP_OPERATIONS
76 WHERE wip_entity_id = p_wip_entity_id
77 AND organization_id = p_organization_id
78 AND operation_seq_num = p_operation_seq_num;
79 exception
80 when no_data_found then
81 x_department_id := null;
82 x_date_required := x_scheduled_start_date; /* Fix for bug 4318495 */
83 x_MPS_date_required := x_scheduled_start_date; /* Fix for bug 4318495 */
84 end;
85
86 x_state_num := x_state_num + 1;
87
88 /* Derive supply info */
89 SELECT wip_supply_type, wip_supply_subinventory, wip_supply_locator_id
90 INTO x_wip_supply_type, x_supply_subinventory, x_supply_locator_id
91 FROM MTL_SYSTEM_ITEMS
92 WHERE inventory_item_id = p_inventory_item_id_new
93 AND organization_id = p_organization_id;
94
95 /* fix for bug 5206375. While adding a pull component,
96 if supply subinv/locator is not present at item level,
97 copy from org parameters as they are mandatory */
98 SELECT wip_supply_type
99 INTO X_temp_wip_supply_type
100 FROM WIP_JOB_DTLS_INTERFACE
101 WHERE group_id = p_group_id
102 AND wip_entity_id = p_wip_entity_id
103 AND organization_id = p_organization_id
104 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
105 AND substitution_type = p_substitution_type
106 AND operation_seq_num = p_operation_seq_num
107 AND inventory_item_id_new = p_inventory_item_id_new;
108
109 if( (nvl(X_temp_wip_supply_type, x_wip_supply_type) = 2 or
110 nvl(X_temp_wip_supply_type, x_wip_supply_type) = 3) and
111 x_supply_subinventory IS NULL) then
112 SELECT default_pull_supply_subinv, default_pull_supply_locator_id
113 INTO x_supply_subinventory, x_supply_locator_id
114 FROM WIP_PARAMETERS
115 WHERE organization_id = p_organization_id;
116 end if;
117 /* end of fix for bug 5206375 */
118
119 x_state_num := x_state_num + 1;
120
121 /* MRP_net_flag */
122 select wdj.wip_supply_type, wdj.job_type, we.entity_type
123 into X_temp_wip_supply_type, X_job_type, X_entity_type
124 from wip_entities we,
125 wip_discrete_jobs wdj
126 where wdj.wip_entity_id = p_wip_entity_id
127 and wdj.organization_id = p_organization_id
128 and wdj.wip_entity_id = we.wip_entity_id
129 and wdj.organization_id = we.organization_id ;
130
131 /* bug#2811687 */
132 if (X_entity_type = wip_constants.eam) then
133 x_wip_supply_type := wip_constants.push;
134 end if;
135
136 x_state_num := x_state_num + 1;
137
138 x_mrp_net_flag := 1; /* by default */
139
140 IF (X_temp_wip_supply_type = 5) THEN /* vendor */
141 x_mrp_net_flag := 2;
142 END IF;
143
144 IF ((x_required_quantity < 0) AND (X_job_type = 1)) THEN
145 x_mrp_net_flag := 2;
146 END IF;
147
148 /* Fix for Bug#3430727 */
149 X_quantity_per_assembly := p_quantity_per_assembly;
150 /* Fix for Bug#3080103 */
151 if (p_inventory_item_id_new is not null and p_quantity_per_assembly is null ) then
152 begin
153
154 SELECT component_sequence_id
155 into X_comp_seq_id
156 FROM wip_requirement_operations
157 WHERE wip_entity_id = p_wip_entity_id
158 AND organization_id = p_organization_id
159 AND operation_seq_num = p_operation_seq_num
160 AND inventory_item_id = p_inventory_item_id_old;
161
162 select substitute_item_quantity
163 into X_substitute_item_quantity
164 from bom_substitute_components
165 where substitute_component_id = p_inventory_item_id_new
166 and component_sequence_id = X_comp_seq_id
167 and acd_type is null ;
168
169 X_quantity_per_assembly := X_substitute_item_quantity ;
170
171 exception
172 when others then
173 p_err_msg := 'WIPRQDFB(' || x_state_num || '): ' || SQLERRM;
174 p_err_code := SQLCODE;
175 end ;
176
177 end if ;
178
179 UPDATE WIP_JOB_DTLS_INTERFACE
180 SET quantity_issued = Decode(p_substitution_type,WIP_JOB_DETAILS.WIP_ADD,0,quantity_issued),
181 quantity_per_assembly = round(X_quantity_per_assembly, 6),
182 department_id = x_department_id,
183 wip_supply_type = nvl(wip_supply_type,x_wip_supply_type),
184 date_required = nvl(date_required,x_date_required),
185 required_quantity = nvl(required_quantity,x_required_quantity),
186 supply_subinventory = nvl(supply_subinventory,x_supply_subinventory),
187 /* Fix for bug 3138448. Added Decode to default supply locator id only when supply
188 subinventory is null.
189 supply_locator_id = nvl(supply_locator_id,x_supply_locator_id),
190 */
191 supply_locator_id = Decode(nvl(supply_subinventory,'@@@'),
192 '@@@',x_supply_locator_id,
193 supply_locator_id),
194 mrp_net_flag = nvl(mrp_net_flag,x_mrp_net_flag),
195 mps_required_quantity = nvl(required_quantity,x_mps_required_quantity), /*Bug 4369064 */
196 mps_date_required = nvl(mps_date_required,x_mps_date_required),
197 component_yield_factor = round(x_quantity_per_assembly * decode(p_basis_type,
198 wip_constants.lot_based_mtl,
199 1,
200 X_start_quantity) /
201 nvl(required_quantity,x_required_quantity),6)
202 /*Component Yield Enhancement(Bug 4369064)->default component yield factor based on
203 required quantity and qpa */
204 WHERE group_id = p_group_id
205 AND wip_entity_id = p_wip_entity_id
206 AND organization_id = p_organization_id
207 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
208 AND substitution_type = p_substitution_type
209 AND operation_seq_num = p_operation_seq_num
210 AND inventory_item_id_new = p_inventory_item_id_new;
211
212 x_state_num := x_state_num + 1;
213
214 exception
215 when others then
216 p_err_msg := 'WIPRQDFB(' || x_state_num || '): ' || SQLERRM;
217 p_err_code := SQLCODE;
218 end;
219
220 END Default_Requirement;
221
222 END WIP_REQUIREMENT_DEFAULT;