1 PACKAGE BODY MRP_END_ASSY AS
2 /* $Header: MRPEPEGB.pls 115.10 2004/05/25 22:40:55 schaudha ship $ */
3 procedure peg(p_org_id IN number,
4 p_compile_desig IN varchar2,
5 p_item_to_peg IN number) is
6
7 count_rows number;
8 direct_using_assembly number;
9 direct_usage number;
10 SYS_YES CONSTANT INTEGER := 1;
11 SYS_NO CONSTANT INTEGER := 2;
12 --
13 --
14 -- Define a procedure that will be recursively called in
15 -- order to perform end assembly pegging
16 --
17 procedure recursively_peg (component IN number,
18 cumulative_usage IN number,
19 cumulative_set_back_time IN number,
20 first_level IN boolean ) is
21 --
22 -- Define a cursor to be used in a cursor FOR loop that
23 -- retrieves the using assemblies of the component.
24 cursor get_us_assy(c_org_id number, c_compile_desig varchar2,
25 c_component number) IS
26 SELECT bom.using_assembly_id using_assembly_item_id,
27 sum(bom.usage_quantity) operation_usage_quantity
28 FROM mrp_bom_components bom,
29 mrp_plan_organizations_v plans
30 WHERE bom.compile_designator = c_compile_desig
31 AND bom.compile_designator = plans.compile_designator
32 AND trunc(bom.effectivity_date) <= trunc(plans.CUTOFF_DATE)
33 AND bom.organization_id = plans.planned_organization
34 AND bom.inventory_item_id = c_component
35 AND bom.alternate_bom_designator is null
36 AND bom.organization_id = c_org_id
37 group by using_assembly_id ;
38
39 check_table number;
40 cum_usage number;
41 old_usage number;
42 cum_set_back_time number;
43 old_set_back_time number;
44 lead_time number;
45 var_user_id number;
46
47 begin
48 --
49 --
50 -- Begin loop to check each using assembly of the component
51 --
52 for ea in get_us_assy (p_org_id, p_compile_desig, component) loop
53 --
54 --
55 -- If the item is a direct using assembly of the item to peg, then
56 -- save direct using assembly id and usage to variables
57 --
58 if first_level then
59 direct_using_assembly := ea.using_assembly_item_id;
60 direct_usage := ea.operation_usage_quantity;
61 end if;
62 --
63 -- Calculate cumulative usage and cumulative set back time
64 --
65 cum_usage := ea.operation_usage_quantity * cumulative_usage;
66 SELECT NVL(full_lead_time, 0) +
67 NVL(postprocessing_lead_time, 0) +
68 NVL(preprocessing_lead_time, 0)
69 INTO lead_time
70 FROM mrp_system_items
71 WHERE organization_id = p_org_id
72 AND compile_designator = p_compile_desig
73 AND inventory_item_id = ea.using_assembly_item_id;
74 cum_set_back_time := lead_time + cumulative_set_back_time;
75 --
76 -- Check to see if using assembly is an end assembly
77 -- (If no rows in mrp_assembly_operations have a value of
78 -- inventory_item_id equal to the using assembly in question,
79 -- then the using assembly is an end assembly.)
80 --
81 SELECT count(inventory_item_id)
82 INTO check_table
83 FROM mrp_bom_components
84 WHERE organization_id = p_org_id
85 AND compile_designator = p_compile_desig
86 AND alternate_bom_designator is null
87 AND inventory_item_id = ea.using_assembly_item_id
88 AND ROWNUM = 1;
89
90 if check_table = 0 then -- yes, it is an end assembly
91 --
92 -- Check to see if a row already exists that has the same
93 -- inventory_item_id, using_assembly_id, and end_assembly_id
94 --
95 SELECT COUNT(inventory_item_id)
96 INTO check_table
97 FROM mrp_end_assemblies
98 WHERE organization_id = p_org_id
99 AND compile_designator = p_compile_desig
100 AND inventory_item_id = p_item_to_peg
101 AND using_assembly_id = direct_using_assembly
102 AND end_assembly_id = ea.using_assembly_item_id
103 AND ROWNUM = 1;
104
105 var_user_id := fnd_profile.value('USER_ID');
106 if var_user_id is NULL then
107 var_user_id := -1;
108 end if;
109 if check_table = 0 then
110 --
111 -- No, the row does not exist, so insert the row.
112 --
113 INSERT INTO mrp_end_assemblies (organization_id,
114 compile_designator, inventory_item_id,
115 using_assembly_id, end_assembly_id, usage,
116 set_back_time, end_usage,
117 last_update_date, last_updated_by,
118 creation_date, created_by)
119 VALUES (p_org_id, p_compile_desig, p_item_to_peg,
120 direct_using_assembly, ea.using_assembly_item_id,
121 direct_usage, cum_set_back_time, cum_usage,
122 SYSDATE, var_user_id, SYSDATE, var_user_id);
123 COMMIT;
124 else
125 --
126 -- Yes, the row exists. If the new set back time is longer than
127 -- the set back time of the row in the database, then update the
128 -- row with the new set back time and usage.
129 --
130 SELECT set_back_time
131 INTO old_set_back_time
132 FROM mrp_end_assemblies
133 WHERE organization_id = p_org_id
134 AND compile_designator = p_compile_desig
135 AND inventory_item_id = p_item_to_peg
136 AND using_assembly_id = direct_using_assembly
137 AND end_assembly_id = ea.using_assembly_item_id;
138
139 if cum_set_back_time > old_set_back_time then
140
141 SELECT end_usage
142 INTO old_usage
143 FROM mrp_end_assemblies
144 WHERE organization_id = p_org_id
145 AND compile_designator = p_compile_desig
146 AND inventory_item_id = p_item_to_peg
147 AND using_assembly_id = direct_using_assembly
148 AND end_assembly_id = ea.using_assembly_item_id;
149
150 cum_usage := cum_usage + old_usage;
151
152 UPDATE mrp_end_assemblies
153 SET end_usage = cum_usage,
154 set_back_time = cum_set_back_time,
155 last_update_date = SYSDATE,
156 last_updated_by = var_user_id
157 WHERE organization_id = p_org_id
158 AND compile_designator = p_compile_desig
159 AND inventory_item_id = p_item_to_peg
160 AND using_assembly_id = direct_using_assembly
161 AND end_assembly_id = ea.using_assembly_item_id;
162
163 COMMIT;
164 end if;
165 end if;
166 else -- no, the using assembly is not an end assembly
167 --
168 -- Make a recursive call of this procedure to continue the end
169 -- assembly pegging process.
170 --
171 recursively_peg(ea.using_assembly_item_id,
172 cum_usage,
173 cum_set_back_time,
174 FALSE );
175 end if;
176 end loop;
177 end;
178
179 begin
180 --
181 -- Has end assembly pegging been previously performed for this item?
182 --
183 SELECT COUNT(*)
184 INTO count_rows
185 FROM mrp_end_assemblies
186 WHERE organization_id = p_org_id
187 AND compile_designator = p_compile_desig
188 AND inventory_item_id = p_item_to_peg
189 AND ROWNUM = 1;
190 --
191 -- If no rows retrieved, then perform end assembly pegging
192 --
193 if count_rows = 0 then
194 --
195 -- Call recursive procedure to peg end assemblies
196 --
197 recursively_peg(p_item_to_peg, 1, 0, TRUE);
198 end if;
199 end peg;
200
201 END MRP_END_ASSY;
[Home] [Help]