1 PACKAGE BODY PO_DOCUMENT_UPGRADE_PVT AS
2 /* $Header: POXVUPGB.pls 120.1 2005/08/09 22:22:29 scolvenk noship $ */
3
4 --Start of Comments
5 --Name:PO_UPDATE_MGR
6 --Pre-reqs:
7 -- None.
8 --Modifies:
9 -- None.
10 --Locks:
11 -- None.
12 --Function:
13 -- The procedure would submit subrequests for updating
14 -- the po line locations.
15 -- This would be called by the Concurrent request POXUPMGR
16 --Parameters:
17 --IN:
18 --p_batch_size
19 -- Batch Commit Size.
20 --p_num_workers
21 -- Number of workers to be used
22 --OUT:
23 --X_errbuf
24 -- Message when existing a PL/SQL concurrent request
25 --X_retcode
26 -- Exit Status for the concurrent request
27 --IN OUT:
28 --N/A
29 --Testing:
30 --End of Comments
31
32 PROCEDURE PO_UPDATE_MGR(
33 X_errbuf OUT NOCOPY VARCHAR2,
34 X_retcode OUT NOCOPY VARCHAR2,
35 p_batch_size IN NUMBER,
36 p_num_workers IN NUMBER)
37
38 IS
39 BEGIN
40 --
41 -- Manager processing
42 --
43
44 AD_CONC_UTILS_PKG.submit_subrequests(
45 X_errbuf=>X_errbuf,
46 X_retcode=>X_retcode,
47 X_workerconc_app_shortname=>'PO',
48 X_workerconc_progname=>'POXUPWKR',
49 X_batch_size=>p_batch_size,
50 X_Num_Workers=>p_num_workers);
51
52
53 END;
54
55
56 --Start of Comments
57 --Name:PO_UPDATE_WKR
58 --Pre-reqs:
59 -- None.
60 --Modifies:
61 -- None.
62 --Locks:
63 -- None.
64 --Function:
65 -- This procedure updates the following columns
66 -- CLOSED_FOR_RECEIVING_DATE with the transaction date (with timestamp) on the
67 -- receving transaction that leads to the shipment status being
68 -- set to 'CLOSED FOR RECEVING' OR 'CLOSED'.If the shipment status is
69 -- changed back to 'OPEN' OR 'CLOSED FOR INVOICE, then this date would
70 -- be nulled out.
71 -- CLOSED_FOR_INVOICE_DATE with the invoice date on the invoice that leads
72 -- to the shipment status being set to 'CLOSED FOR INVOICE' OR 'CLOSED'.
73 -- If the shipment status is changed back to 'OPEN' OR 'CLOSED FOR RECEIVING'
74 -- , then this date would be nulled out.
75 -- SHIPMENT_CLOSED_DATE with maximum of the CLOSED_FOR_RECEIVING_DATE or
76 -- CLOSED_FOR_INVOICE_DATE when the shipment is CLOSED . In all other closure
77 -- status, this would be null
78 -- This would be called by the Concurrent request POXUPMGR
79 --Parameters:
80 --IN:
81 --p_batch_size
82 -- Batch Commit Size.
83 --p_num_workers
84 -- Number of workers to be used
85 --p_worker_id
86 --OUT:
87 --X_errbuf
88 -- Message when existing a PL/SQL concurrent request
89 --X_retcode
90 -- Exit Status for the concurrent request
91 --IN OUT:
92 --N/A
93 --Testing:
94 --End of Comments
95
96 PROCEDURE PO_UPDATE_WKR(
97 X_errbuf OUT NOCOPY VARCHAR2,
98 X_retcode OUT NOCOPY VARCHAR2,
99 p_batch_size IN NUMBER,
100 p_worker_id IN NUMBER,
101 p_num_workers IN NUMBER)
102
103 IS
104
105 l_worker_id number;
106 l_product varchar2(30) := 'PO';
107 l_table_name varchar2(30) := 'PO_LINE_LOCATIONS_ALL';
108 l_update_name varchar2(30) := 'poxucpll.sql';
109 l_status varchar2(30);
110 l_industry varchar2(30);
111 l_retstatus boolean;
112
113 l_table_owner varchar2(30);
114 l_any_rows_to_process boolean;
115
116 l_start_rowid rowid;
117 l_end_rowid rowid;
118 l_rows_processed number;
119
120 l_userid po_line_locations_all.last_updated_by%TYPE;
121 l_loginid po_line_locations_all.last_update_login%TYPE;
122
123 BEGIN
124
125 --
126 -- get schema name of the table for ROWID range processing
127 --
128 l_retstatus := fnd_installation.get_app_info(
129 l_product, l_status, l_industry, l_table_owner);
130
131 if ((l_retstatus = FALSE)
132 OR
133 (l_table_owner is null))
134 then
135 raise_application_error(-20001,
136 'Cannot get schema name for product : '||l_product);
137 end if;
138
139 fnd_file.put_line(FND_FILE.LOG, ' p_worker_id : '||p_worker_id);
140 fnd_file.put_line(FND_FILE.LOG, 'p_num_workers : '||p_num_workers);
141
142 --
143 -- Worker processing
144 --
145
146 --
147 -- The following could be coded to use EXECUTE IMMEDIATE inorder to remove build time
148 -- dependencies as the processing could potentially reference some tables that could
149 -- be obsoleted in the current release
150 --
151 BEGIN
152
153 ad_parallel_updates_pkg.initialize_rowid_range(
154 ad_parallel_updates_pkg.ROWID_RANGE,
155 l_table_owner,
156 l_table_name,
157 l_update_name,
158 p_worker_id,
159 p_num_workers,
160 p_batch_size, 0);
161
162 ad_parallel_updates_pkg.get_rowid_range(
163 l_start_rowid,
164 l_end_rowid,
165 l_any_rows_to_process,
166 p_batch_size,
167 TRUE);
168
169 -- Get User ID to update LAST_UPDATED_BY
170 l_userid := FND_GLOBAL.USER_ID;
171
172 -- Get Login ID to update LAST_UPDATE_LOGIN
173 l_loginid := FND_GLOBAL.LOGIN_ID;
174
175
176 while (l_any_rows_to_process = TRUE)
177 loop
178 -----------------------------------------------------
179 --
180 -- product specific processing here
181 --
182 --
183 -----------------------------------------------------
184
185 UPDATE /*+ ROWID (poll) */ po_line_locations_all poll
186 SET poll.closed_for_receiving_date =
187 ( select nvl(max(RT.transaction_date),
188 decode(poll.closed_code,
189 'FINALLY CLOSED',poll.closed_date,
190 'CLOSED',poll.closed_date,
191 'CLOSED FOR RECEIVING',poll.last_update_date,
192 NULL)
193 )
194 from rcv_transactions RT
195 where RT.TRANSACTION_TYPE IN ('RECEIVE','ACCEPT','CORRECT','MATCH')
196 and RT.po_line_location_id = poll.line_location_id
197 and poll.closed_code IN ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING')
198 )
199 ,poll.closed_for_invoice_date =
200 ( select nvl( max(AIN.invoice_date),
201 decode(poll.closed_code,
202 'FINALLY CLOSED',poll.closed_date,
203 'CLOSED',poll.closed_date,
204 'CLOSED FOR INVOICE',poll.last_update_date,
205 NULL)
206 )
207 from ap_invoice_distributions_all AID,
208 ap_invoices_all AIN,
209 po_distributions_all POD
210 where AID.invoice_id = AIN.invoice_id
211 and AID.po_distribution_id = POD.po_distribution_id
212 and POD.line_location_id = poll.line_location_id
213 and nvl(AID.reversal_flag,'N') NOT IN ('Y')
214 and poll.closed_code IN ('FINALLY CLOSED','CLOSED','CLOSED FOR INVOICE')
215 )
216 ,poll.shipment_closed_date = decode(poll.closed_code,'FINALLY CLOSED',poll.closed_date,
217 'CLOSED',poll.closed_date,
218 NULL)
219 ,last_update_date = sysdate
220 ,last_updated_by = l_userid
221 ,last_update_login = l_loginid
222 WHERE poll.rowid BETWEEN l_start_rowid and l_end_rowid
223 and poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
224 and poll.closed_code IN ('FINALLY CLOSED','CLOSED','CLOSED FOR RECEIVING','CLOSED FOR INVOICE')
225 and ( ( poll.closed_code IN ('CLOSED FOR RECEIVING')
226 AND poll.closed_for_receiving_date IS NULL)
227 OR (poll.closed_code IN ('CLOSED FOR INVOICE')
228 AND poll.closed_for_invoice_date IS NULL)
229 OR (poll.closed_code IN ('FINALLY CLOSED','CLOSED')
230 AND (poll.shipment_closed_date IS NULL
231 OR poll.closed_for_receiving_date IS NULL
232 OR poll.closed_for_invoice_date IS NULL))
233 );
234
235 l_rows_processed := SQL%ROWCOUNT;
236
237 ad_parallel_updates_pkg.processed_rowid_range(
238 l_rows_processed,
239 l_end_rowid);
240
241 commit;
242
243 ad_parallel_updates_pkg.get_rowid_range(
244 l_start_rowid,
245 l_end_rowid,
246 l_any_rows_to_process,
247 p_batch_size,
248 FALSE);
249
250 end loop;
251
252 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 raise;
257 END;
258
259
260 END;
261
262 END;
[Home] [Help]