1 PACKAGE po_autocreate_mainproc_pvt AUTHID CURRENT_USER AS
2 /* $Header: PO_AUTOCREATE_MAINPROC_PVT.pls 120.0 2010/02/28 14:08:30 ssreekum noship $ */
3
4 /* -------------------------------------------------------
5 ---------------- PRIVATE PROCEDURES -------------------
6 ------------------------------------------------------- */
7
8 /* ============================================================================
9 NAME: process_headers
10 DESC: Handle the logic to derive, default, validate records from
11 po_headers_interface table and insert records into
12 po_headers_draft_all table.
13
14 PO_AUTO_HEADER_PROCESS_PVT.fetch_headers
15 PO_AUTO_HEADER_PROCESS_PVT.derive_and_default_headers
16 PO_AUTO_HEADER_PROCESS_PVT.validate_headers
17 PO_AUTO_HEADER_PROCESS_PVT.merge_to_headers_draft
18 PO_AUTO_HEADER_PROCESS_PVT.merge_to_org_assign_draft
19
20 When the autocreate processing starts we could be in any of the following
21 scenarios.
22 I. The process will create a new document.
23 II. The process will add lines to an already exisitng document.
24 The document might not have been approved yet.
25 III. The process might add lines lines to an already approved document.
26 There might not be any draft.
27 IV. The process might add lines lines to an already approved document.
28 There might be a draft already existing.
29 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
30 ============================================================================== */
31
32 PROCEDURE process_headers;
33
34 /* ============================================================================
35 NAME: group_interface_records
36 DESC: Group the interface lines
37
38 RULES: When this procedure is called we might be in the following
39 scenario's
40
41 * Depending on whether the flow is called from Automatic Mode or
42 Manual Mode, the line number may or may not exist in the
43 interface. if it exists, it must be honored.
44
45 * The expected grouping could be REQUISITION meaning there is no
46 grouping at-all required OR it could be DEFAULT expecting the
47 defaulting grouping to take place.
48
49 * The document being created might be a NEW DOCUMENT in which case,
50 the interface lines should be grouped with itself. It could be
51 ADD TO DOCUMENT in which case the interface line must be grouped
52 with the main document AND also with the other interface lines.
53
54 * Grouping rules
55 For Line:
56 1. For Complex Purchase Order, do not group the lines
57 2. If the grouping is REQUISITION do not group the lines.
58 3. Group the req line with an EXISTING PO line base on the
59 following attributes:
60
61 - ITEM_ID
62 - ITEM_DESCRIPTION
63 - ITEM_REVISION
64 - UNIT_MEAS_LOOKUP_CODE
65 - PREFERRED_GRADE
66 - FROM_HEADER_ID
67 - FROM_LINE_ID
68 - NEED_BY_DATE (IF "PO: Use Need-by Date for Default Autocreate Grouping" is set to Yes)
69 - SHIP_TO_LOCATION (IF "PO: Use Ship-to for Default Autocreate Grouping" is set to Yes)
70 - TRANSACTION_REASON_CODE
71 - CONTRACT_ID
72 - SUPPLIER_REF_NUMBER
73 - VENDOR_PRODUCT_NUM
74 - OKE_CONTRACT_HEADER_ID
75 - OKE_CONTRACT_VERSION_ID
76 - BID_NUMBER
77 - BID_LINE_NUMBER
78
79 4. Group the req line with other group lines in the INTERFACE
80 table base on the following attributes:
81
82 - ITEM_ID
83 - ITEM_DESCRIPTION
84 - ITEM_REVISION
85 - UNIT_MEAS_LOOKUP_CODE
86 - PREFERRED_GRADE
87 - FROM_HEADER_ID
88 - FROM_LINE_ID
89 - NEED_BY_DATE (IF "PO: Use Need-by Date for Default Autocreate Grouping" is set to Yes)
90 - SHIP_TO_LOCATION (IF "PO: Use Ship-to for Default Autocreate Grouping" is set to Yes)
91 - CONSIGNED_FLAG *** (Possibly a bug ???)
92 - TRANSACTION_REASON_CODE
93 - CONTRACT_ID
94 - SUPPLIER_REF_NUMBER
95 - VENDOR_PRODUCT_NUM
96 - OKE_CONTRACT_HEADER_ID
97 - OKE_CONTRACT_VERSION_ID
98 - BID_NUMBER
99 - BID_LINE_NUMBER
100
101
102 For Shipments:
103 1. For Complex Purchase Order, do not group the pay items.
104 2. Group shipments based on the following attribytes:
105
106 - NEED_BY_DATE
107 - SHIP_TO_LOCATION
108 - CONSIGNED_FLAG
109
110 If those values match, then we will create a new shipment
111 based on the following attributes:
112
113 - NOTE_TO_RECEIVER
114 - SHIPMENT_TYPE
115 - ENCUMBERED_FLAG
116 - PREFERRED_GRADE
117 - VMI_FLAG
118
119 * After matching the lines the interface record is updated with the
120 line number and the shipment number. Rest of the processing is
121 deferred to the line processing and shipment processing routines.
122
123 ALGM:
124 IF the mode is 'ADD'
125 Next_Line_Num := Get the latest line number from document + 1
126 ELSE
127 Next_Line_Num := 1;
128 END IF;
129
130 LOOP for all the lines interface records with LINE NUM as NULL
131 IF mod is 'ADD'
132 Try to match the interface record with document.
133 IF matching line found
134 LINE_NUM := matching line number
135 END IF;
136 END IF;
137
138 IF the LINE_NUM is still null
139 --This means there is no matching line found on the document
140 --OR this is not an ADD TO case.
141 Try to match the already processed interface records.
142
143 IF matching line found
144 LINE_NUM := matching interface line number
145 ELSE
146 LINE_NUM := Next_Line_Num;
147 Next_Line_Num := Next_Line_Num+1;
148 END IF;
149 END IF;
150
151 UPDATE the interface with the LINE_NUM;
152
153 END LOOP
154
155 * Matching logic should take care of all the rules mentioned above.
156
157 Repeat the same steps for even shipments. But try match the
158 shipments ONLY for the stamped LINE_NUM.
159
160 ============================================================================== */
161
162 PROCEDURE group_interface_records;
163
164 /* ============================================================================
165 NAME: process_lines
166
167 DESC: Handle the logic to derive, default, validate records from po_lines_interface
168 table and insert records into po_lines_draft_all table.
169
170 PO_AUTO_LINE_PROCESS_PVT.fetch_lines
171 PO_AUTO_LINE_PROCESS_PVT.derive_and_default_lines
172 PO_AUTO_LINE_PROCESS_PVT.validate_lines (Is this required??)
173 PO_AUTO_LINE_PROCESS_PVT.merge_to_lines_draft
174 PO_AUTO_LINE_PROCESS_PVT.merge_to_price_diff_draft
175 PO_AUTO_LINE_PROCESS_PVT.merge_to_attributes_draft
176 (process attribute values and attribute tlp)
177 PO_AUTO_LINE_PROCESS_PVT.hanlde_line_attachments
178
179 EXMP: After the grouping the document being processed might in the
180 following state. The Line number is followed by the quantity
181 on that record.
182
183 DOCUMENT DRAFT INTERFACE
184 ======== ===== =========
185 1 (10) .......1 (12) A - 1 (5)
186 2 (10) B - 2 (5)
187 3 (10) .......3 (13) C - 3 (5)
188 4 (10) D - 2 (5)
189 5 (10) E - 6 (5)
190 F - 1 (5)
191 G - 6 (5)
192
193 - We are in ADD TO document flow.
194 - The document is already approved document.
195 - Each of the line in the document has 10 qty.
196 - A Draft is created for the document already. The Line #1 qty is changed
197 to 12 and the Line #3 qty is changed to 13.
198 - The interface has six requisition lines 5 qty each.
199 - The Line A and F matches to the existing document line 1 (which is in draft)
200 - The Line B and D matches to the existing document line 2 (which is NOT in draft)
201 - The Line C matches to the existing document line 3 (which is in draft)
202 - The Line E does not match any line in the document.
203 - The line G matches to the line E in the interface.
204 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
205 ============================================================================== */
206
207 PROCEDURE process_lines;
208
209 /* ============================================================================
210 NAME: process_line_locations
211 DESC: Handle the logic to derive, default, validate records from po_line_locations_interface
212 table and insert records into po_line_locations_draft_all table.
213
214 PO_AUTO_LINE_LOC_PROCESS_PVT.fetch_line_loc
215 PO_AUTO_LINE_LOC_PROCESS_PVT.derive_and_default_line_loc : most for standard po / complex order
216 (Country of Origin,Inspection Required,Receipt required,match option)
217 PO_AUTO_LINE_LOC_PROCESS_PVT.validate_line_loc (??)
218 PO_AUTO_LINE_LOC_PROCESS_PVT.merge_to_line_loc_draft
222
219 (Shipments, Pay Items, Price Breaks they can be handled separetly);
220 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
221 ============================================================================== */
223 PROCEDURE process_line_locations;
224
225 /* ============================================================================
226 NAME: process_distributions
227 DESC: The line locations processing also needs the derive default happened
228 in process lines. So either at the end of process lines we should
229 update the changed values back to the interface and read it from
230 interface here OR we should use the same record itself to even process
231 the line locations. Thus we can avoid doing the same currency
232 conversion and UOM conversion steps here again.
233
234 This process is called to process the Shipments, Pay Items and Price Breaks.
235 Handle the logic to derive, default, validate records from po_distributions_interface
236 table and insert records into po_distributions_draft_all table.
237
238 PO_AUTO_DIST_PROCESS_PVT.fetch_dists
239 PO_AUTO_DIST_PROCESS_PVT.derive_and_default_dists
240 PO_AUTO_LINE_LOC_PROCESS_PVT.validate_dists (??)
241 PO_AUTO_LINE_LOC_PROCESS_PVT.merge_to_dists_draft
242 CALLER : PO_AUTOCREATE_MAINPROC_PVT.process
243 ==============================================================================*/
244
245 PROCEDURE process_distributions;
246
247 /* ----------------------------------------------------
248 ----------------- PUBLIC PROCEDURES ----------------
249 ---------------------------------------------------- */
250
251 /* ============================================================================
252 NAME: process
253 DESC: Main Procedure for the AutoCreate Main Processing logic which does the following
254 - process_headers
255 - group_interface_records
256 - process_lines
257 - process_line_locations
258 - process_distributions
259 Caller : PO_AUTOCREATE_PVT.create_po
260 ==============================================================================*/
261
262 PROCEDURE process;
263
264 /**
265 * Function: has_one_time_location
266 * Effects: Checks if the requisition line p_req_line_id has a one-time
267 * location.
268 * Returns: TRUE if the requisition line has a one-time location
269 * FALSE otherwise
270 */
271
272 FUNCTION has_one_time_location (p_req_line_id IN NUMBER)
273 RETURN BOOLEAN;
274
275
276 /* ============================================================================
277 NAME: get_ship_to_loc
278 DESC: This function returns the exact HR/HZ Ship to location for the
279 given location.
280 ARGS: p_deliver_to_loc_id IN Deliver to Location
281 ============================================================================ */
282
283 FUNCTION get_ship_to_loc(p_deliver_to_loc_id IN NUMBER)
284 RETURN NUMBER;
285
286 END PO_AUTOCREATE_MAINPROC_PVT;