[Home] [Help]
PACKAGE BODY: APPS.PO_RELGEN_PKG1
Source
1 PACKAGE BODY PO_RELGEN_PKG1 AS
2 /* $Header: porelg2b.pls 120.1.12010000.2 2008/09/11 09:55:55 grohit ship $ */
3 /* ============================================================================
4 NAME: ARCHIVE_RELEASE
5 DESC: Archive approved releases
6 ARGS: IN : x_po_release_id IN number
7 ALGR: If the system is setup to archive on approval, archive the release
8 header, shipments and distributions
9
10 ===========================================================================*/
11
12 PROCEDURE ARCHIVE_RELEASE(x_po_release_id IN number)
13 IS
14 x_when_to_archive PO_DOCUMENT_TYPES.ARCHIVE_EXTERNAL_REVISION_CODE%TYPE;
15 BEGIN
16
17 -- <FPJ Refactor Archiving API>
18 RETURN;
19
20 EXCEPTION
21 WHEN OTHERS THEN
22 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
23
24 END ARCHIVE_RELEASE;
25
26 /* ============================================================================
27 NAME: MRP_SUPPLY
28 DESC: Calculate the primary uom, quantity and lead time for supply rows.
29 ARGS: none
30 ALGR:
31
32 ===========================================================================*/
33
34 PROCEDURE MRP_SUPPLY
35 IS
36
37 /* Bug# 7390590
38 * Added an extra FOR UPDATE clause in the below cursor to avoid the
39 * deadlock scenario.
40 */
41
42 cursor supply_cursor is
43 select ms.quantity,
44 ms.unit_of_measure,
45 ms.item_id,
46 ms.from_organization_id,
47 ms.to_organization_id,
48 ms.receipt_date,
49 ms.supply_type_code,
50 ms.supply_source_id,
51 ms.rowid row_id
52 from mtl_supply ms
53 where ms.change_flag = 'Y'
54 FOR UPDATE ;
55
56 supply_rec supply_cursor%rowtype;
57
58 primary_qty number := 0;
59 lead_time number := 0;
60 conversion_rate number := 0;
61 primary_uom varchar2(25);
62 fsp_org_id number;
63
64 begin
65
66 /*
67 ** Get the purchasing organization id from financials_system_parameters
68 */
69
70 begin
71
72 select inventory_organization_id
73 into fsp_org_id
74 from financials_system_parameters;
75
76 exception
77
78 when no_data_found then
79 fsp_org_id := 101;
80
81 when others then
82 msgbuf := msgbuf||'Statement: 007 ';
83 raise;
84
85 end;
86
87
88 open supply_cursor;
89
90 loop
91
92 fetch supply_cursor into supply_rec;
93 exit when supply_cursor%notfound;
94
95
96 if (supply_rec.quantity = 0) then
97
98 /*
99 ** Remove unnecessary supply records
100 */
101
102 delete from mtl_supply
103 where rowid = supply_rec.row_id;
104
105 else
106
107 primary_uom := po_uom_s.get_primary_uom(supply_rec.item_id,
108 supply_rec.to_organization_id,
109 supply_rec.unit_of_measure);
110
111
112 if (supply_rec.item_id is null) then
113
114 lead_time := 0;
115
116 else
117
118 /*
119 ** get lead time for a pre-defined item
120 */
121
122 begin
123
124 select postprocessing_lead_time
125 into lead_time
126 from mtl_system_items
127 where inventory_item_id = supply_rec.item_id
128 and organization_id =
129 nvl(supply_rec.to_organization_id,
130 fsp_org_id);
131
132
133 exception
134
135 when others then
136 msgbuf := msgbuf||'Statement: 002.';
137 msgbuf := msgbuf||' Item id ';
138 msgbuf := msgbuf||supply_rec.item_id;
139 msgbuf := msgbuf||' To Org id ';
140 msgbuf := msgbuf||
141 supply_rec.to_organization_id;
142 raise;
143
144 end;
145
146 end if;
147
148 begin
149
150 conversion_rate := po_uom_s.po_uom_convert(supply_rec.unit_of_measure,
151 primary_uom,
152 supply_rec.item_id);
153 exception
154
155 when others then
156 msgbuf := msgbuf||'Function: 001. ';
157 msgbuf := msgbuf||'From UOM: ';
158 msgbuf := msgbuf||supply_rec.unit_of_measure;
159 msgbuf := msgbuf||'. To UOM: ';
160 msgbuf := msgbuf||Primary_uom;
161 msgbuf := msgbuf||'. Item ID: ';
162 msgbuf := msgbuf||supply_rec.item_id;
163 raise;
164
165 end;
166
167 primary_qty := supply_rec.quantity * conversion_rate;
168
169
170 begin
171
172 update mtl_supply
173 set to_org_primary_quantity = primary_qty,
174 to_org_primary_uom = primary_uom,
175 change_flag = null,
176 expected_delivery_date =
177 decode(supply_rec.item_id, null, null,
178 (supply_rec.receipt_date)
179 + nvl(lead_time, 0 ))
180 where rowid = supply_rec.row_id;
181
182 exception
183
184 when others then
185 msgbuf := msgbuf||'Statement: 003.';
186 msgbuf := msgbuf||' Item id ';
187 msgbuf := msgbuf||supply_rec.item_id;
188 msgbuf := msgbuf||' To Org id ';
189 msgbuf := msgbuf||
190 supply_rec.to_organization_id;
191 raise;
192
193 end;
194
195
196 end if;
197
198
199 end loop;
200
201 close supply_cursor;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
206
207 END MRP_SUPPLY;
208
209 END PO_RELGEN_PKG1;