1 PACKAGE PO_CHG_REQUEST_GRP AUTHID CURRENT_USER AS
2 /* $Header: POXGCHGS.pls 115.2 2004/02/27 23:41:05 jmojnida noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_CHG_REQUEST_GRP';
5 G_FILE_NAME CONSTANT VARCHAR2(30) := 'POXGCHGS.pls';
6
7
8 /*
9 This PL/SQL table will store the supplier request. A new row will be added
10 for each call of the store_supplier_request.
11 */
12 g_po_change_table pos_chg_rec_tbl := null;
13
14 /*
15 g_int_cont_num value will store the internal control number of each request.
16 This will be set in the initialize call and reset on the windup call.
17 Each time the API is called, it will be matched for the integrity purposes.
18 */
19 g_int_cont_num varchar2(256) := null;
20
21 /*
22 g_po_number, g_po_type, g_release_number, g_tp_id, g_tp_site_id is the
23 global variable for the active transaction information set at valid_header.
24 g_requestor is the change requestor's application login; can be 'XML USER'
25 if it is defined in fnd_user.
26 g_request_origin is the origin of the change request, like, XML.
27 This will help in duplicate validations in consequent procedures.
28
29 */
30 g_po_number varchar2(20);
31 g_po_type varchar2(25);
32 g_release_number number;
33 g_tp_id number;
34 g_tp_site_id number;
35 g_requestor varchar2(256);
36 g_request_origin varchar2(30);
37 g_last_upd_date date;
38
39 /*
40 This procedure needs to be called first to initialize an inbound transaction.
41 This will initialize some global variables. This should be called from the
42 pre-process of the root node. No errors should occur here lest there is
43 any weird error, processing should not continue, as it has not been
44 initialized properly.
45 */
46 procedure initialize_chn_ack_inbound (
47 p_requestor IN varchar2, -- Change requester or the acknowledging username
48 p_int_cont_num IN varchar2, -- ECX's ICN. Used for integrity of request
49 p_request_origin IN varchar2, -- XML/OTA/9iAS/OPEN
50 p_tp_id IN number, -- vendor_id
51 p_tp_site_id IN number, -- vendor_site_id
52 x_error_id OUT NOCOPY number, -- The error id will be 2, errors will go to the TP sysadmin
53 x_error_status OUT NOCOPY VARCHAR2 -- Error message
54
55
56 );
57
58 /*
59 This API should be called from the in process of the header level.
60 This will validate the header,
61 if the PO #/Release mentioned belongs to the vendor and vendor site id,
62 */
63 procedure validate_header (
64 p_requestor IN varchar2, -- Change requester or the acknowledging username
65 p_int_cont_num IN varchar2, -- ECX's ICN. Used for integrity of request
66 p_request_origin IN varchar2, -- '9iAS'
67 p_request_type IN varchar2, -- 'CHANGE' or 'ACKNOWLEDGE' or CANCELLATION
68 p_tp_id IN number, -- vendor_id
69 p_tp_site_id IN number, -- vendor_site_id
70 p_po_number IN varchar2, --PO # of the PO being modified or the Blanket's PO #
71 p_release_number IN number, -- Release number if the PO Type is release or null
72 p_po_type IN varchar2, -- PO Type?? -- RELEASE for release, STANDARD for others.
73 p_revision_num IN number, -- Revision number of the PO or the release
74 x_error_id_in IN number, -- The error id will be 2, errors will go to the TP sysadmin
75 x_error_status_in IN VARCHAR2, -- Error message
76 x_error_id_out OUT NOCOPY number, -- The error id will be 2, errors will go to the TP sysadmin
77 x_error_status_out OUT NOCOPY VARCHAR2 -- Error message
78
79
80 );
81
82
83 /*
84 This API should be called from the in process of the lines.
85 This procedure needs to be called in the following scenarios:
86 1. Modifications to a PO at the shipment level
87 2. Modifications to a PO at the line level
88 3. Acknowledgment at the shipment level
89 4. Canceling at the shipment level
90 5. Splitting a shipment (from FPJ)
91 Calls to this API will be stored in a pl/sql table and will not be processed immediately.
92 Call process_supplier_request to process the request.
93 */
94 procedure store_supplier_request (
95 p_requestor IN varchar2, -- Change requester or the acknowledging username
96 p_int_cont_num IN varchar2, -- ECX's ICN. Used for integrity of request
97 p_request_type IN varchar2, -- ??'CHANGE' or 'ACKNOWLEDGE'
98 p_tp_id IN number, -- vendor_id
99 p_tp_site_id IN number, -- vendor_site_id
100 p_level IN varchar2, -- Level at which the api is called. HEADER, LINE, SHIPMENT
101 p_po_number IN varchar2, --PO # of the PO being modified or the Blanket's PO #
102 p_release_number IN number, -- Release number if the PO Type is release or null
103 p_po_type IN varchar2, -- PO Type?? -- RELEASE for release, STANDARD for others.
104 p_revision_num IN number, -- Revision number of the PO or the release
105 p_line_num IN number, -- Line number being modified
106 p_reason IN varchar2, -- Reason for change or acknowledgment (can be null?!)
107 p_shipment_num IN number, -- Shipment number (can be null if the change is at the line)
108 p_quantity IN number, -- The new quantity (can be null)
109 p_quantity_uom IN varchar2, -- The UOM of the new quantity
110 p_price IN number, -- The new price value (can be null)
111 p_price_currency IN varchar2, -- The currency code of the new price (can be null)
112 p_price_uom IN varchar2, -- The UOM code of the new price (can be null)
113 -- Note that the above two are used only for verifying if they are same as the original values.
114 p_promised_date IN date, -- The new promised date (can be null)
115 p_supplier_part_num IN varchar2, -- The new supplier part number (can be null)
116 p_so_number IN varchar2, -- The new sales order number (can be null)
117 p_so_line_number IN varchar2, -- The new sales order line number (can be null)
118 p_ack_type IN varchar2, --'ACCEPT' or 'REJECT' or 'MODIFICATION' or NULL
119
120 /* Note the error messages occurred during multiple calls are concatenated.
121 XML Gateway does not support IN OUT parameters. So, this is a work around.
122 */
123 x_error_id_in IN number, -- Error id from the initialize procedure
124 x_error_status_in IN varchar2, -- Error message from the earlier API calls.
125 x_error_id_out OUT NOCOPY number, -- The error id will be 2; errors will go to the TP sysadmin
126 x_error_status_out OUT NOCOPY varchar2, -- Error message in this call concatenated with the old ones
127 p_parent_shipment_number number default NULL,
128 p_SUPPLIER_DOC_REF varchar2 default NULL,
129 p_SUPPLIER_LINE_REF varchar2 default NULL,
130 p_SUPPLIER_SHIPMENT_REF varchar2 default NULL
131
132
133 );
134
135 /*
136 Call this procedure from the post-process before calling the wind-up API only if the
137 error_id from the earlier calls is 0. This API should not be called if the request is
138 for header level acknowledgment. This API will place the supplier request in the
139 change request table and kicks-off the workflow for the approval of the change request.
140 */
141 procedure process_supplier_request (
142 p_int_cont_num IN varchar2,
143 x_error_id_in IN number,
144 x_error_status_in IN varchar2,
145 x_error_id_out OUT NOCOPY number, -- The error id will be 2, errors will go to the TP sysadmin
146 x_error_status_out OUT NOCOPY VARCHAR2 -- Error message
147
148
149 );
150
151 /*
152 This procedure needs to be called from Acknowledge PO inbound at in_process of the header,
153 only when the PO is acknowledged at the header level. In case of shipment level acknowledgement
154 this procedure should not be called. The acknowledge po request will be processed immediately
155 and only once per transaction. So, no need for error_id_in and error_id_out etc.
156 */
157 procedure acknowledge_po(
158 p_requestor IN varchar2, -- Change requester or the acknowledging username
159 p_int_cont_num IN varchar2, -- ECX's ICN. Used for integrity of request
160 p_request_type IN varchar2, -- ??'ACKNOWLEDGE'
161 p_tp_id IN number, -- vendor_id
162 p_tp_site_id IN number, -- vendor_site_id
163 p_po_number IN varchar2, --PO # of the PO being modified or the Blanket's PO #
164 p_release_number IN number, -- Release number if the PO Type is release or null
165 p_po_type IN varchar2, -- PO Type?? -- RELEASE for release, STANDARD for others.
166 p_revision_num IN number, -- Revision number of the PO or the release
167 p_ack_code IN number, -- 0 for accept 2 reject
168 p_ack_reason IN varchar2, -- comments
169 x_error_id OUT NOCOPY number, -- The error id will be 2, errors will go to the TP sysadmin
170 x_error_status OUT NOCOPY VARCHAR2 -- Error message
171
172 );
173 /*
174 Call this procedure at the post process stage as the last action.
175 At this point the pl/sql table will be 'delete'd. Call this procedure
176 even if there were errors in the earlier calls.
177 */
178 procedure windup_chn_ack_inbound (
179 p_requestor IN varchar2, -- Change requester or the acknowledging username
180 p_int_cont_num IN varchar2, -- ECX's ICN. Used for integrity of request
181 p_request_origin IN varchar2, -- '9iAS'
182 p_tp_id IN number, -- vendor_id
183 p_tp_site_id IN number, -- vendor_site_id
184 x_error_id_in IN number, -- Error id from the initialize procedure
185 x_error_status_in IN varchar2, -- Error message from the earlier API calls.
186 x_error_id_out OUT NOCOPY number, -- The error id will be 2, errors will go to the TP sysadmin
187 x_error_status_out OUT NOCOPY VARCHAR2 --Error message
188
189
190
191 );
192 end PO_CHG_REQUEST_GRP;
193
[Home] [Help]