1 PACKAGE PO_RESCHEDULE_PKG AUTHID CURRENT_USER AS
2 /* $Header: POXRSCHS.pls 120.4 2010/10/13 07:38:13 lswamina ship $ */
3 /**
4 * Parameter:
5 * (1)X_need_by_date_old: The old need_by_date for the shipment, should not be
6 * null.
7 * (2)X_need_by_date: The new need_by_date for the shipment, should not be
8 * null.
9 * (3)X_po_header_id: The header id on which the change should be made.
10 * MRP should pass in SUPPLY_HEADER_ID from view MRP_PO_SUPPLY_VIEW
11 * (4)X_po_line_id: The line id on which the change should be made. For
12 * releases, null should be passed in.
13 * MRP should pass in SUPPLY_LINE_ID from view MRP_PO_SUPPLY_VIEW
14 * (5) X_supply_number:
15 * MRP should pass in SUPPLY_NUMBER from view MRP_PO_SUPPLY_VIEW
16 * The format of it is:
17 * decode(MS.PO_RELEASE_ID, NULL,PH.SEGMENT1,
18 * PH.SEGMENT1||'('||TO_CHAR(PR.RELEASE_NUM)'||')')
19 *
20 * Algorithm:
21 * The program will first validate the reschedule using existing PO
22 * form logic, if it passes the validation, then the function will
23 * change in the
24 * a) shipment level (For Standard, Planned PO).
25 * b) shipment level. (For blanket, scheduled release).
26 * Update corresponding tables, archive the changes, and launch the
27 * PO reapproval workflow process.
28 *
29 * Assumption:
30 * For each po line , or release passed in by MRP, if there're more
31 * than one shipment, 'need-by-date' of all these shipments with
32 * the old need_by_date will be updated.
33 */
34
35 /** Bug 922852
36 * bgu, July 01, 1999
37 * User should not be able to reschedule encumbered PO shipments
38 */
39 /*Bug2187544
40 The need by date check should be nvl(promised_date,need_by_date) instead
41 of pll.need_by_date bcos in mtl supply table PO inserts the need by
42 date using the same logic i.e nvl(poll.promised_date,pol.need_by_date)
43 */
44
45 -- <PO_CHANGE_API FPJ>
46 -- In 115.8, changed the cursor to select only the LINE_LOCATION_ID,
47 -- because the other fields are unnecessary when calling the PO Change API.
48 -- Also, removed the restriction on encumbered shipments, because the
49 -- PO Change API is capable of unreserving and re-reserving shipments.
50
51 /* Bug3066274 fixed. Added trunc to the date fields in the below
52 select statement.
53 */
54
55 cursor po_shipment_rel_cursor (X_po_release_id number,
56 X_need_by_date_old date,
57 X_shipment_num number) is --2279541
58 select poll.line_location_id
59 from po_line_locations_all poll, po_releases_all por
60 where por.po_release_id = X_po_release_id
61 and por.po_release_id = poll.po_release_id
62 and poll.shipment_num = nvl(X_shipment_num,poll.shipment_num) --2279541
63 -- <NBD TZ/Timestamp FPJ START>
64 -- Remove TRUNC() on both sides
65 and nvl(poll.promised_date,poll.need_by_date) = X_need_by_date_old
66 -- <NBD TZ/Timestamp FPJ END>
67 for update of poll.need_by_date;
68
69 /** Bug 885536
70 * bgu, May 07, 1999
71 * The previous definition of cursor will pick up scheduled releases too,
72 * resulting in workflow item type cannot be found for the wrong
73 * combination of document type 'PO' and document subtype 'SCHEDULED'
74 */
75 /*Bug2187544
76 The need by date check should be nvl(promised_date,need_by_date) instead
77 of pll.need_by_date bcos in mtl supply table PO inserts the need by
78 date using the same logic i.e nvl(poll.promised_date,pol.need_by_date)
79 */
80
81 -- <PO_CHANGE_API FPJ>
82 -- In 115.8, changed the cursor to select only the LINE_LOCATION_ID,
83 -- because the other fields are unnecessary when calling the PO Change API.
84 -- Also, removed the restriction on encumbered shipments, because the
85 -- PO Change API is capable of unreserving and re-reserving shipments.
86
87 /* Bug3066274 fixed. Added trunc to the date fields in the below
88 select statement.
89 */
90 cursor po_shipment_cursor (X_po_line_id number,
91 X_need_by_date_old date,
92 X_shipment_num number) is -- 2279541
93 select poll.line_location_id
94 from po_line_locations_all poll, po_lines_all pol
95 where pol.po_line_id = X_po_line_id
96 and poll.po_line_id = pol.po_line_id
97 and poll.shipment_num = nvl(X_shipment_num,poll.shipment_num) -- 2279541
98 -- <NBD TZ/Timestamp FPJ START>
99 -- Remove TRUNC() on both sides
100 and nvl(poll.promised_date,poll.need_by_date) = X_need_by_date_old
101 -- <NBD TZ/Timestamp FPJ END>
102 and poll.shipment_type in ('STANDARD', 'PLANNED') --885536
103 for update of poll.need_by_date;
104
105 Function RESCHEDULE( X_need_by_date_old date,
106 X_need_by_date date,
107 X_po_header_id number,
108 X_po_line_id number,
109 X_supply_number varchar2,
110 X_shipment_num number default null,
111 p_estimated_pickup_date DATE DEFAULT NULL, -- <APS FPJ>
112 p_ship_method VARCHAR2 DEFAULT NULL -- <APS FPJ>
113 ) return boolean; --2279541
114
115 -- bug 5255550 : Reschedule API rewrite
116 Function RESCHEDULE ( X_need_by_dates_old po_tbl_date,
117 X_need_by_dates po_tbl_date,
118 X_po_header_id number,
119 X_po_line_ids po_tbl_number,
120 X_supply_number varchar2,
121 X_shipment_nums po_tbl_number Default NULL,
122 p_estimated_pickup_dates po_tbl_date Default NULL,
123 p_ship_methods po_tbl_varchar30 Default NULL
124 ) Return BOOLEAN;
125
126 --Bug 9693292<START> , error message required.
127 --Adding new parameter X_error_message
128 Function RESCHEDULE ( X_need_by_dates_old po_tbl_date,
129 X_need_by_dates po_tbl_date,
130 X_po_header_id number,
131 X_po_line_ids po_tbl_number,
132 X_supply_number varchar2,
133 X_shipment_nums po_tbl_number Default NULL,
134 p_estimated_pickup_dates po_tbl_date Default NULL,
135 p_ship_methods po_tbl_varchar30 Default NULL,
136 X_error_message OUT NOCOPY po_tbl_varchar2000
137 ) Return BOOLEAN;
138
139 --Bug 9693292<END>
140
141 END PO_RESCHEDULE_PKG;
142
143
[Home] [Help]