1 PACKAGE PO_RELGEN_PKG AS
2 /* $Header: porelges.pls 120.2.12000000.2 2007/04/02 06:40:27 puppulur ship $ */
3
4
5 /* Declare cursor for requisition lines that meet the following criteria
6
7 - the requisition lines must be on approved requisitions that are not
8 already on a PO or a release
9 - the requisition lines must not be cancelled or closed
10 - the requisition lines must be sourced to an approved and active blanket
11 that is not on hold
12 - the vendor associated with the blanket must be active, and must not be
13 on hold if vendors holds are enforced
14 - the autosource rule for the item must be active and the document
15 generation method must be either 'CREATE' or 'CREATE_AND_APPROVE'
16 - the amount (qty*price) must be greater than the minimum release amount
17 on the blanket line if one is specified
18 */
19 /** Bug 787120
20 * bgu, June 09, 1999
21 * Port bug fix 772055 to 11.5
22 */
23 /*
24 Bug # : 772055 - SVAIDYAN : Added condn. that the modified_by_agent_flag = 'N'
25 so that the req. lines modified thru autocreate form do not get processed.
26 */
27 /* Bug no 777230
28 In both the cursor requisition_lines_cursor and
29 requisition_lines_cursor1
30 we were not checking the if the line of the blanket is cancelled
31 before allowing the release to be created.
32 Made the fix to check for that.nvl(pol.cancel_flag,'N') = 'N'
33 */
34 /* Bug no 996349
35 In both the cursor requisition_lines_cursor and
36 requisition_lines_cursor1
37 we were not checking the if the line of the blanket line is finally closed
38 before allowing the release to be created.
39 Made the fix to check for that.
40 nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
41 */
42 /* Bug #947709 - FRKHAN 8/4/99
43 'requisition_header_id' is added to requisition_lines_cursor and
44 requisition_lines_cursor1.
45 */
46 /* Supplier PCard FPH. Get the pcard_id from the function
47 * po_pcard_pkg.get_valid_pcard_id. This will fetch pcard_id
48 * if it is valid or null if it is not. The orderby is done
49 * in such a way that all the null pcard_ids and the non null
50 * pcard_ids are grouped together. This way all null will be
51 * grouped into one release. For the others all the same non null
52 * pcard_ids will be grouped into one.
53 */
54 cursor requisition_lines_cursor is
55 select porl.requisition_line_id requisition_line_id,
56 poh.agent_id agent_id,
57 porl.blanket_po_header_id blanket_po_header_id,
58 pol.po_line_id blanket_po_line_id,
59 poh.vendor_id vendor_id,
60 poh.vendor_site_id vendor_site_id,
61 nvl(poh.rate,1) rate,
62 nvl(poh.rate_date,sysdate) rate_date,
63 porl.last_updated_by last_updated_by,
64 porl.last_update_login last_update_login,
65 porl.destination_type_code destination_type_code,
66 porl.item_id item_id,
67 porl.unit_price unit_price,
68 porl.quantity quantity,
69 porl.need_by_date need_by_date,
70 --togeorge 09/28/2000
71 --added note to receiver
72 porl.note_to_receiver note_to_receiver,
73 porl.destination_organization_id destination_organization_id,
74 porl.deliver_to_location_id deliver_to_location_id,
75 porl.to_person_id deliver_to_person_id,
76 decode(pad.doc_generation_method, 'CREATE_AND_APPROVE',
77 decode(fsp.req_encumbrance_flag, 'Y', 'CREATE', 'CREATE_AND_APPROVE'),
78 pad.doc_generation_method) doc_generation_method,
79 porl.unit_meas_lookup_code req_uom,
80 pol.unit_meas_lookup_code po_uom,
81 prh.requisition_header_id requisition_header_id,
82 porl.secondary_unit_of_measure secondary_unit_of_measure,
83 porl.secondary_quantity secondary_quantity,
84 porl.preferred_grade preferred_grade, /* B1548597 OPM */
85 decode(porl.pcard_flag,'N',null,'S',po_pcard_pkg.get_valid_pcard_id(-99999,porl.vendor_id,porl.vendor_site_id),'Y',po_pcard_pkg.get_valid_pcard_id(prh.pcard_id,porl.vendor_id,porl.vendor_site_id)) pcard_id,
86 porl.vmi_flag, -- VMI FPH
87 porl.drop_ship_flag, -- <DropShip FPJ>
88 porl.org_id -- <R12 MOAC>
89 from po_requisition_lines porl,
90 po_requisition_headers prh,
91 financials_system_parameters fsp,
92 po_system_parameters psp,
93 po_autosource_documents pad,
94 po_autosource_rules par,
95 po_headers poh,
96 po_lines pol,
97 po_vendors pov
98 where porl.requisition_header_id = prh.requisition_header_id
99 and prh.authorization_status = 'APPROVED'
100 and porl.line_location_id is null
101 and nvl(porl.cancel_flag,'N') = 'N'
102 and nvl(porl.closed_code,'OPEN') <> 'FINALLY CLOSED'
103 and nvl(porl.modified_by_agent_flag, 'N') = 'N'
104 and porl.blanket_po_header_id is not null
105 and porl.blanket_po_line_num is not null
106 and porl.unit_price is not null
107 and porl.item_id is not null
108 and porl.document_type_code = 'BLANKET'
109 and porl.blanket_po_header_id = poh.po_header_id
110 and pol.po_header_id = poh.po_header_id
111 and trunc(nvl(pol.expiration_date,sysdate+1)) > trunc(sysdate)
112 and nvl(pol.cancel_flag,'N') = 'N'
113 and nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
114 and poh.type_lookup_code = 'BLANKET'
115 and poh.approved_flag = 'Y'
116 and nvl(poh.cancel_flag,'N') = 'N'
117 /* Bug 1128903
118 Modifying the AND condition to accomodate the need_by_date so that
119 documents effective for a future period can be chosen. */
120 and trunc(nvl(porl.need_by_date, sysdate))
121 between trunc(nvl(poh.start_date,nvl(porl.need_by_date, sysdate)))
122 and trunc(nvl(poh.end_date,nvl(porl.need_by_date, sysdate)))
123 and nvl(poh.user_hold_flag,'N') = 'N'
124 and poh.vendor_id = pov.vendor_id
125 and trunc(sysdate) between trunc(nvl(pov.start_date_active,
126 sysdate))
127 and trunc(nvl(pov.end_date_active,sysdate))
128 and not(nvl(psp.enforce_vendor_hold_flag,'N') = 'Y'
129 and nvl(pov.hold_flag,'N') = 'Y')
130 and porl.blanket_po_line_num = pol.line_num
131 and porl.blanket_po_header_id = pad.document_header_id
132 and pol.po_line_id = pad.document_line_id
133 and pad.doc_generation_method in ('CREATE','CREATE_AND_APPROVE')
134 and pad.autosource_rule_id = par.autosource_rule_id
135 /* Bug 1128903
136 Modifying the AND condition to accomodate the need_by_date so that
137 sourcing rules effective for a future period can be chosen. */
138 and trunc(nvl(porl.need_by_date, sysdate))
139 between trunc(par.start_date)
140 and trunc(nvl(par.end_date,nvl(porl.need_by_date, sysdate)))
141 -- Bug 2701147 START
142 -- We should compare the BPA line minimum release amount against the total
143 -- amount of all shipments for that line on the release. This is now done
144 -- in Check 12 of preapproval_checks.
145 -- and (porl.quantity * round(porl.unit_price/nvl(poh.rate,1),5)
146 -- >= nvl(pol.min_release_amount,0))
147 -- Bug 2701147 END
148 order by blanket_po_header_id,
149 doc_generation_method,
150 blanket_po_line_num,
151 /* Supplier PCard FPH */
152 decode(porl.pcard_flag,'N',null,'S',po_pcard_pkg.get_valid_pcard_id(-99999,porl.vendor_id,porl.vendor_site_id),'Y',po_pcard_pkg.get_valid_pcard_id(prh.pcard_id,porl.vendor_id,porl.vendor_site_id)),
153 need_by_date, -- bug 2378110
154 requisition_line_id
155 for update of porl.line_location_id;
156
157 /* Cursor requisition_lines_cursor1 selects requisition lines based on
158 the same criteria as requisition_lines_cursor except that it checks
159 the asl entry to determine the release generation method. The gets
160 the asl entry based on the item/category on the req line and
161 vendor/site on the source document.
162 */
163 /*Bug 1790311:Before fix the following cursor was picking up a record from
164 po_asl_attributes even if there was no blanket attached to the
165 supplier line in the ASL.This caused duplicate shipments to be
166 created in the Release if there are two supplier lines in the
167 ASL.One with a supplier and null supplier site combination and
168 the other with a supplier and a supplier site,attached to the same
169 blanket.Now adding the table po_asl_documents in the cursor and
170 adding a condition which checks whether a blanket has been
171 attached to a supplier line in the ASL.*/
172
173 /*Bug 1916078. Pchintal. Reverted the fix done in bug 1361935, which was a forward port
174 from 11.0 and was causing a performance problem in 11.5. The performance with out this
175 fix is very good and the fix from bug 1361935 was causing a performance problem.
176 */
177 /* Bug 2008371. pchintal. Added the ORDERED hint and changed the order of
178 the tables in the from clause to improve the performance.
179 */
180
181 /*Bug 2005755:
182
183 1.Reverting the fix for bug 1790311.
184 2.Now a shipment will be created for all the valid supplier lines with null
185 supplier site.
186 3.A shipment will be created for a valid supplier line having a supplier site
187 only when there is no other supplier line is existing with the same supplier-
188 item combination having a null supplier site. */
189
190 --bug2880298
191 --pass req_enc_flag and enforce_vendor_hold_flag information from the caller
192 --instead of getting them in the cursor itself to improve performance
193
194 cursor requisition_lines_cursor1
195 ( p_req_enc_flag IN VARCHAR2,
196 p_enforce_vendor_hold_flag IN VARCHAR2
197 ) is
198 select /*+ FIRST_ROWS LEADING(PORL) */
199 porl.requisition_line_id requisition_line_id,
200 poh.agent_id agent_id,
201 porl.blanket_po_header_id blanket_po_header_id,
202 pol.po_line_id blanket_po_line_id,
203 poh.vendor_id vendor_id,
204 poh.vendor_site_id vendor_site_id,
205 nvl(poh.rate,1) rate,
206 nvl(poh.rate_date,sysdate) rate_date,
207 porl.last_updated_by last_updated_by,
208 porl.last_update_login last_update_login,
209 porl.destination_type_code destination_type_code,
210 porl.item_id item_id,
211 porl.unit_price unit_price,
212 porl.quantity quantity,
213 porl.need_by_date need_by_date,
214 --togeorge 09/28/2000
215 --added note to receiver
216 porl.note_to_receiver note_to_receiver,
217 porl.destination_organization_id destination_organization_id,
218 porl.deliver_to_location_id deliver_to_location_id,
219 porl.to_person_id deliver_to_person_id,
220 decode(paa.release_generation_method, 'CREATE_AND_APPROVE',
221 -- decode(pad.doc_generation_method, 'CREATE_AND_APPROVE',
222 DECODE (p_req_enc_flag, 'Y', 'CREATE',
223 'CREATE_AND_APPROVE'),
224 paa.release_generation_method) doc_generation_method,
225 -- pad.doc_generation_method) doc_generation_method,
226 porl.unit_meas_lookup_code req_uom,
227 pol.unit_meas_lookup_code po_uom,
228 prh.requisition_header_id requisition_header_id,
229 porl.secondary_unit_of_measure secondary_unit_of_measure,
230 porl.secondary_quantity secondary_quantity,
231 porl.preferred_grade preferred_grade, /* B1548597 OPM */
232 /* Supplier PCard FPH */
233 decode(porl.pcard_flag,'N',null,'S',po_pcard_pkg.get_valid_pcard_id(-99999,porl.vendor_id,porl.vendor_site_id),'Y',po_pcard_pkg.get_valid_pcard_id(prh.pcard_id,porl.vendor_id,porl.vendor_site_id)) pcard_id,
234 porl.vmi_flag, -- VMI FPH
235 porl.drop_ship_flag, -- <DropShip FPJ>
236 porl.org_id -- <R12 MOAC>
237 from po_requisition_lines porl,
238 po_requisition_headers prh,
239 po_headers poh,
240 po_vendors pov,
241 po_lines pol,
242 po_asl_attributes_val_v paa -- Bug: 1945461
243 --bug 2005755 po_asl_documents pod -- Bug 1790311
244 -- po_autosource_documents pad,
245 -- po_autosource_rules par,
246 where porl.requisition_header_id = prh.requisition_header_id
247 -- <REQINPOOL>: removed parameters made redundant by new
248 -- reqs_in_pool_flag def
249 and porl.reqs_in_pool_flag = 'Y' /* Requisition To Sourcing FPH */
250 -- <REQINPOOL END>
251 and porl.source_type_code = 'VENDOR'
252 and porl.blanket_po_header_id is not null
253 and porl.blanket_po_line_num is not null
254 and porl.unit_price is not null
255 and porl.item_id is not null
256 and porl.document_type_code = 'BLANKET'
257 and porl.blanket_po_header_id = poh.po_header_id
258 and pol.po_header_id = poh.po_header_id
259 and trunc(nvl(pol.expiration_date,sysdate+1)) >= trunc(sysdate) --Bug 5636580 , Modified so that we can Create Releases for a Blanket
260 --Purchase Agreement which has the line level expiration date as Current date
261 and nvl(pol.cancel_flag,'N') = 'N'
262 and nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
263 and poh.type_lookup_code = 'BLANKET'
264 and nvl(poh.global_agreement_flag,'N') = 'N' -- FPI GA
265 and poh.approved_flag = 'Y'
266 and nvl(poh.cancel_flag,'N') = 'N'
267 /* Bug 1128903
268 Modifying the AND condition to accomodate the need_by_date so that
269 documents effective for a future period can be fixed. */
270
271 /* Bug 2402167: In order to allow releases to be created even if the need by date
272 is after the blanket's expiry date, modifying the condition put in by 1128903.
273 Now, it allows release to be created as long as:
274 1. blanket is not ALREADY expired.
275 2. blanket is becoming effective on or before the need by date. */
276 -- and trunc(nvl(porl.need_by_date, sysdate))
277 -- between trunc(nvl(poh.start_date,nvl(porl.need_by_date, sysdate)))
278 -- and trunc(nvl(poh.end_date,nvl(porl.need_by_date, sysdate)))
279
280
281 /* Bug 3397912: Requisition lines without need-by dates were being missed by
282 * this cursor, because one of the need-by dates below was missing an NVL(...,sysdate).
283 * All porl.need_by_date items should now have NVL() around them in this query.
284 */
285
286 and trunc(nvl(poh.end_date, sysdate + 1)) >= trunc(sysdate)
287 and trunc(nvl(poh.start_date, NVL(porl.need_by_date,SYSDATE) - 1))
288 <= trunc(nvl(porl.need_by_date, sysdate))
289 -- Bug 2402167.end
290
291 and nvl(poh.user_hold_flag,'N') = 'N'
292 and poh.vendor_id = pov.vendor_id
293 and trunc(sysdate) between trunc(nvl(pov.start_date_active,
294 sysdate))
295 and trunc(nvl(pov.end_date_active,sysdate))
296 and not(p_enforce_vendor_hold_flag = 'Y' -- bug2880298
297 and nvl(pov.hold_flag,'N') = 'Y')
298 and porl.blanket_po_line_num = pol.line_num
299 and (paa.item_id = porl.item_id
300 or (paa.item_id IS NULL
301 AND porl.category_id = paa.category_id
302 --Bug#2279155 start
303 and not exists
304 (select 'commodity level ASL should be used
305 only if there is no item level ASL'
306 from po_asl_attributes_val_v paa4
307 where paa4.item_id=porl.item_id
308 and paa4.vendor_id=paa.vendor_id
309 and nvl(paa4.vendor_site_id,-1)=nvl(paa.vendor_site_id,-1)
310 AND paa4.using_organization_id in (-1,porl.destination_organization_id))
311 --Bug#2279155 end
312 )
313 )
314 --Bug2005755 and pod.asl_id=paa.asl_id --Bug 1790311
315 --Bug2005755 and porl.blanket_po_header_id=pod.document_header_id --Bug 1790311
316 --Bug2005755 and pol.po_line_id= pod.document_line_id --Bug 1790311
317 --Bug2005755and pod.using_organization_id=paa.using_organization_id--Bug 1790311
318 and paa.vendor_id = poh.vendor_id
319
320 --start of bug Bug2005755
321 and (paa.vendor_site_id is null or
322 ( poh.vendor_site_id = paa.vendor_site_id and
323 not exists
324 (SELECT 'select supplier line with null supplier site'
325 FROM po_asl_attributes_val_v paa3
326 WHERE nvl(paa.item_id, -1) = nvl(paa3.item_id, -1)
327 AND nvl(paa.category_id, -1) = nvl(paa3.category_id, -1)
328 AND paa.vendor_id = paa3.vendor_id
329 AND paa3.vendor_site_id is null
330 AND paa3.using_organization_id in (-1,porl.destination_organization_id)
331 /*
332 Bug 4001367 : Duplicate shipments were created for the requisition line
333 sourced to a source document associated with the supplier and site and
334 the release generation method set to 'Automatic' and also
335 another ASL existed for the same supplier without supplier site and Release
336 Generation method set to 'Automatic Release/Review'. We also need to add
337 a check for release generation method CREATE(Automatic Release/Review)
338 */
339 AND paa3.release_generation_method in ('CREATE_AND_APPROVE','CREATE'))))
340 --end of bug 2005755
341 and paa.using_organization_id =
342 (SELECT max(paa2.using_organization_id)
343 FROM po_asl_attributes_val_v paa2
344 WHERE nvl(paa.item_id, -1) = nvl(paa2.item_id, -1)
345 AND nvl(paa.category_id, -1) = nvl(paa2.category_id, -1)
346 AND paa.vendor_id = paa2.vendor_id
347 AND nvl(paa.vendor_site_id, -1) = nvl(paa2.vendor_site_id, -1)
348 AND paa2.using_organization_id in (-1,porl.destination_organization_id))
349 and paa.release_generation_method in ('CREATE','CREATE_AND_APPROVE')
350 -- and porl.blanket_po_header_id = pad.document_header_id
351 -- and pol.po_line_id = pad.document_line_id
352 -- and pad.doc_generation_method in ('CREATE','CREATE_AND_APPROVE')
353 -- and pad.autosource_rule_id = par.autosource_rule_id
354 -- and trunc(sysdate) between trunc(par.start_date)
355 -- and trunc(nvl(par.end_date,sysdate))
356 -- Bug 2701147 START
357 -- We should compare the BPA line minimum release amount against the total
358 -- amount of all shipments for that line on the release. This is now done
359 -- in Check 12 of preapproval_checks.
360 -- and (porl.quantity * round(porl.unit_price/nvl(poh.rate,1),5)
361 -- >= nvl(pol.min_release_amount,0))
362 -- Bug 2701147 END
363 and nvl(paa.consigned_from_supplier_flag, 'N') = 'N'
364 -- Bug 3411766 START
365 -- We should not select the lines which are created in iProcurement with
366 -- emergency PO number.
367 and prh.emergency_po_num is null
368 -- Bug 3411766 END
369 order by blanket_po_header_id,
370 doc_generation_method,
371 blanket_po_line_num,
372 /* Supplier PCard FPH */
373 decode(porl.pcard_flag,'N',null,'S',po_pcard_pkg.get_valid_pcard_id(-99999,porl.vendor_id,porl.vendor_site_id),'Y',po_pcard_pkg.get_valid_pcard_id(prh.pcard_id,porl.vendor_id,porl.vendor_site_id)),
374 need_by_date, -- bug 2378110
375 requisition_line_id
376 for update of porl.line_location_id;
377
378 /* Declare cursor for the receiving controls */
379
380 TYPE rcv_control_type IS RECORD
381 (inspection_required_flag po_system_parameters.inspection_required_flag%type
382 := null,
383 receipt_required_flag po_system_parameters.receiving_flag%type := null,
384 days_early_receipt_allowed rcv_parameters.days_early_receipt_allowed%type
385 :=null,
386 days_late_receipt_allowed rcv_parameters.days_late_receipt_allowed%type
387 := null,
388 enforce_ship_to_location rcv_parameters.enforce_ship_to_location_code%type
389 := null,
390 receiving_routing_id rcv_parameters.receiving_routing_id%type
391 :=null,
392 qty_rcv_tolerance rcv_parameters.qty_rcv_tolerance%type
393 :=null,
394 receipt_days_exception_code rcv_parameters.receipt_days_exception_code%type
395 :=null,
396 qty_rcv_exception_code rcv_parameters.qty_rcv_exception_code%type
397 :=null,
398 allow_substitute_receipts_flag rcv_parameters.allow_substitute_receipts_flag%type
399 :=null,
400 invoice_close_tolerance po_system_parameters.invoice_close_tolerance%type
401 := null,
402 receipt_close_tolerance po_system_parameters.receive_close_tolerance%type
403 := null);
404
405 /* Declare global variables */
406
407 x_inventory_org_id number := 0;
408 x_expense_accrual_code po_system_parameters.expense_accrual_code%type;
409 x_po_release_id number := 0;
410 x_line_location_id number := 0;
411 x_authorization_status po_releases.authorization_status%type;
412 msgbuf varchar2(200);
413 x_period_name gl_period_statuses.period_name%type;
414
415 /* Declare procedures and associated parameters */
416
417 /* Bug 1834138. pchintal. Added 2 new global variables to calculate the
418 shipment number. This was done as a part of improving the performance of
419 the create releases process.
420 */
421
422 Gpo_release_id_prev number := 0;
423 Gship_num_prev number := 0;
424
425
426 PROCEDURE CREATE_RELEASES;
427
428 PROCEDURE CREATE_RELEASE_HEADER(req_line IN requisition_lines_cursor%rowtype);
429
430 PROCEDURE CREATE_RELEASE_SHIPMENT(req_line IN requisition_lines_cursor%rowtype);
431
432 PROCEDURE OE_DROP_SHIP(req_line IN requisition_lines_cursor%rowtype);
433
434 PROCEDURE MAINTAIN_SUPPLY(req_line IN requisition_lines_cursor%rowtype);
435
436 PROCEDURE GET_RCV_CONTROLS(req_line IN requisition_lines_cursor%rowtype,
437 rcv_controls IN OUT NOCOPY rcv_control_type);
438
439 PROCEDURE GET_INVOICE_MATCH_OPTION(req_line IN requisition_lines_cursor%rowtype,
440 x_invoice_match_option OUT NOCOPY varchar2);
441
442 PROCEDURE WRAPUP(req_line IN requisition_lines_cursor%rowtype);
443
444 FUNCTION GET_BEST_PRICE(req_line IN requisition_lines_cursor%rowtype,
445 x_conversion_rate IN number,
446 x_ship_to_location_id IN number) return number;
447
448 -- <INBOUND LOGISITCS PFJ START>
449 -------------------------------------------------------------------------------
450 --Start of Comments
451 --Name: create_deliveryrecord
452 --Pre-reqs:
453 -- None.
454 --Modifies:
455 -- l_fte_rec
456 --Locks:
457 -- None.
458 --Function:
459 -- Call FTE's API to create delivery record for Approved Blanket Release
460 --Parameters:
461 --IN:
462 --p_release_id
463 -- Corresponding to po_release_id
464 --Testing:
465 -- Pass in po_release_id for an approved release.
466 --End of Comments
467 -------------------------------------------------------------------------------
468
469 PROCEDURE create_deliveryrecord(p_release_id IN NUMBER);
470
471 -- <INBOUND LOGISITCS PFJ END>
472
473 END PO_RELGEN_PKG;
[Home] [Help]