1 PACKAGE PO_SOURCING_RULES_SV AS
2 /* $Header: POXPISRS.pls 120.0 2005/06/01 19:23:37 appldev noship $ */
3
4 /*==================================================================
5 PROCEDURE NAME: create_update_sourcing_rule()
6
7 DESCRIPTION: This API inserts row into mrp_sr_assignments,
8 mrp_sourcing_rules,mrp_sr_receipt_org,
9 mrp_sr_source_org depending on the create or update
10 flag from the approval flag.
11 Validations of start_date,end_date not being null and
12 the approved_status being approved are done here and
13 then create__sourcing_rule is called. If update flag
14 is checked in the approval window then
15 update__sourcing_rule is called.
16
17 PARAMETERS: X_interface_header_id,X_interface_line_id - Sequence generated
18 numbers to insert into po_interface_errors.
19 X_item_id,X_vendor_id,X_po_header_id,
20 X_po_line_id,X_document_type,X_approval_status -
21 Values of the document for which the sourcing rule is created
22 or updated.
23 X_rule_name - Sourcing rule name that is to be created or updated
24 X_rule_name_prefix - Prefix that we get from the workflow attibute.
25 When we create a new sourcing rule the name is
26 X_rule_name_prefix_<SR Sequence number);
27 X_start_date,X_end_date -
28 The effective_date and disable_date for the sourcing rule.
29 X_create_update_code -
30 "CREATE" if create flag in the Approval Window is checked.
31 "CREATE_UPDATE" if both create and update flag is checked.
32 X_return_value - Set to N in exception.
33 X_header_processable_flag - Value is N if there was any
34 error encountered. Set in the procedure
35 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
36 X_po_interface_error_code - This is the code used to populate interface_type
37 field in po_interface_errors table.
38
39
40 =======================================================================*/
41 PROCEDURE create_update_sourcing_rule (
42 p_interface_header_id IN NUMBER,
43 p_interface_line_id IN NUMBER,
44 p_item_id IN NUMBER,
45 p_vendor_id IN NUMBER,
46 p_po_header_id IN NUMBER,
47 p_po_line_id IN NUMBER,
48 p_document_type IN VARCHAR2,
49 p_approval_status IN VARCHAR2,
50 p_rule_name IN VARCHAR2,
51 p_rule_name_prefix IN VARCHAR2,
52 p_start_date IN DATE,
53 p_end_date IN DATE,
54 p_create_update_code IN VARCHAR2,
55 p_organization_id IN NUMBER,
56 p_assignment_type_id IN NUMBER,
57 p_po_interface_error_code IN VARCHAR2,
58 x_header_processable_flag IN OUT NOCOPY VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2,
60 ----<LOCAL SR/ASL PROJECT 11i11 START>
61 p_assignment_set_id IN NUMBER DEFAULT NULL,
62 p_vendor_site_id IN NUMBER DEFAULT NULL
63 ----<LOCAL SR/ASL PROJECT 11i11 END>
64
65 );
66
67 /*==================================================================
68 PROCEDURE NAME: create_sourcing_rule()
69
70
71 DESCRIPTION: This API inserts row into mrp_sr_assignments,
72 mrp_sourcing_rules,mrp_sr_receipt_org,
73 mrp_sr_source_org when the create flag in the
74 Approval Window is true.
75
76 PARAMETERS: X_interface_header_id,X_interface_line_id - Sequence generated
77 numbers to insert into po_interface_errors.
78 X_item_id,X_vendor_id,X_po_header_id,
79 X_po_line_id,X_document_type,X_approval_status -
80 Values of the document for which the sourcing rule is created
81 or updated.
82 X_rule_name - Sourcing rule name that is to be created or updated
83 X_rule_name_prefix - Prefix that we get from the workflow attibute.
84 X_organization_id - We create global sourcing rules as default. But
85 if the workflow attribute is set with any org_id, then we use that
86 to create a local sourcing rule.
87 X_assignment_tye - Default is item_level(3) but if this is given then
88 we set the assignment type to this value
89 X_start_date,X_end_date -
90 The effective_date and disable_date for the sourcing rule.
91 X_return_value - Set to N in exception.
92 X_header_processable_flag - Value is N if there was any
93 error encountered. Set in the procedure
94 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
95
96
97 =======================================================================*/
98 PROCEDURE create_sourcing_rule (X_interface_header_id IN NUMBER,
99 X_interface_line_id IN NUMBER,
100 X_item_id IN NUMBER,
101 X_vendor_id IN NUMBER,
102 X_po_header_id IN NUMBER,
103 X_po_line_id IN NUMBER,
104 X_document_type IN VARCHAR2,
105 X_rule_name IN VARCHAR2,
106 X_rule_name_prefix IN VARCHAR2,
107 X_start_date IN DATE,
108 X_end_date IN DATE,
109 X_organization_id IN NUMBER,
110 X_assignment_type_id IN NUMBER,
111 x_assignment_set_id IN NUMBER,
112 x_sourcing_rule_id IN OUT NOCOPY NUMBER,
113 x_temp_sourcing_rule_id IN OUT NOCOPY NUMBER,
114 x_process_flag IN OUT NOCOPY VARCHAR2,
115 x_running_status IN OUT NOCOPY VARCHAR2,
116 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
117 ----<LOCAL SR/ASL PROJECT 11i11 START>
118 p_vendor_site_id IN NUMBER DEFAULT NULL
119 ----<LOCAL SR/ASL PROJECT 11i11 END>
120 );
121
122 /*==================================================================
123 PROCEDURE NAME: update_sourcing_rule()
124
125 DESCRIPTION: This API inserts row into mrp_sr_assignments,
126 mrp_sourcing_rules,mrp_sr_receipt_org,
127 mrp_sr_source_org when the update flag is true
128 in the Approval Window.
129
130 PARAMETERS: X_interface_header_id,X_interface_line_id - Sequence generated
131 numbers to insert into po_interface_errors.
132 X_item_id,X_vendor_id,X_po_header_id,
133 X_po_line_id,X_document_type,X_approval_status -
134 Values of the document for which the sourcing rule is created
135 or updated.
136 x_sourcing_rule_id-Sourcing rule id from the create_sourcing_rule.
137 X_start_date,X_end_date -
138 The effective_date and disable_date for the sourcing rule.
139 X_return_value - Set to N in exception.
140 X_header_processable_flag - Value is N if there was any
141 error encountered. Set in the procedure
142 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
143
144
145 =======================================================================*/
146 PROCEDURE update_sourcing_rule (X_interface_header_id IN NUMBER,
147 X_interface_line_id IN NUMBER,
148 X_item_id IN NUMBER,
149 X_vendor_id IN NUMBER,
150 X_po_header_id IN NUMBER,
151 X_po_line_id IN NUMBER,
152 X_document_type IN VARCHAR2,
153 x_sourcing_rule_id IN NUMBER,
154 x_temp_sourcing_rule_id IN NUMBER,
155 X_start_date IN DATE,
156 X_end_date IN DATE,
157 X_organization_id IN NUMBER,
158 X_assignment_type_id IN NUMBER,
159 x_assignment_set_id IN NUMBER,
160 x_running_status IN OUT NOCOPY VARCHAR2,
161 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
162 X_po_interface_error_code IN VARCHAR2,
163 ----<LOCAL SR/ASL PROJECT 11i11 START>
164 p_vendor_site_id IN NUMBER DEFAULT NULL
165 ----<LOCAL SR/ASL PROJECT 11i11 END>
166
167 );
168
169
170 /*==================================================================
171 PROCEDURE NAME: validate_sourcing_rule()
172
173 DESCRIPTION: This API validates whether the start_date and end_date
174 have the correct value and whether there is a default
175 assignment set in the profile and whether the document
176 is Approved.
177
178
179 PARAMETERS: X_interface_header_id,X_interface_line_id - Values from the
180 po_headers_interface and po_lines_interface tables.
181 X_item_id,X_vendor_id,X_po_header_id,
182 X_approval_status - Approval status
183 X_rule_name - Sourcing rule name from the po_lines_interface.
184 X_start_date,X_end_date -
185 The effective_date and disable_date for the sourcing rule.
186 X_process_flag - OUTparameter which is set to N if error occurs.
187 X_return_value - Set to N in exception.
188 X_header_processable_flag - Value is N if there was any
189 error encountered. Set in the procedure
190 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
191
192
193 =======================================================================*/
194 PROCEDURE validate_sourcing_rule (X_interface_header_id IN NUMBER,
195 X_interface_line_id IN NUMBER,
196 X_approval_status IN VARCHAR2,
197 X_rule_name IN VARCHAR2,
198 X_start_date IN DATE,
199 X_end_date IN DATE,
200 x_assignment_type_id IN NUMBER,
201 X_organization_id IN NUMBER,
202 x_assignment_set_id IN OUT NOCOPY NUMBER,
203 X_process_flag IN OUT NOCOPY VARCHAR2,
204 x_running_status IN OUT NOCOPY VARCHAR2,
205 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
206 X_po_interface_error_code IN VARCHAR2);
207
208
209 /*==================================================================
210 PROCEDURE NAME: validate_update_sourcing_rule()
211
212 DESCRIPTION: This API validates if there is any overlap for a given sourcing rule
213 before allowing update to continue.
214
215 PARAMETERS:
216 X_interface_header_id,X_interface_line_id - Values from the
217 po_headers_interface and po_lines_interface tables.
218 X_start_date,X_end_date -
219 The effective_date and disable_date for the sourcing rule.
220 X_process_flag - OUTparameter which is set to N if error occurs.i.e. logic conditions are not met
221 X_return_value - Set to N in exception.
222 X_header_processable_flag - Value is N if there are any error encountered.
223 Set in the procedure PO_INTERFACE_ERRORS_SV1.handle_interface_errors
224 =======================================================================*/
225 PROCEDURE validate_update_sourcing_rule (X_interface_header_id IN NUMBER,
226 X_interface_line_id IN NUMBER,
227 X_sourcing_rule_id IN NUMBER,
228 X_start_date IN DATE,
229 X_end_date IN DATE,
230 X_assignment_type_id IN NUMBER,
231 X_organization_id IN NUMBER,
232 x_assignment_set_id IN OUT NOCOPY NUMBER,
233 X_process_flag IN OUT NOCOPY VARCHAR2,
234 X_running_status IN OUT NOCOPY VARCHAR2,
235 X_header_processable_flag IN OUT NOCOPY VARCHAR2,
236 X_po_interface_error_code IN VARCHAR2);
237
238 --<Shared Proc FPJ>
239 PROCEDURE get_vendor_site_id (
240 p_po_header_id IN NUMBER,
241 x_vendor_site_id OUT NOCOPY NUMBER);
242
243 END PO_SOURCING_RULES_SV;
[Home] [Help]