[Home] [Help]
PACKAGE: APPS.PON_AUCTION_CREATE_PO_PKG
Source
1 PACKAGE pon_auction_create_po_pkg AUTHID CURRENT_USER as
2 /* $Header: PONCRPOS.pls 120.14.12020000.3 2013/02/09 06:10:33 hvutukur ship $ */
3
4 TYPE SumOfReqLineAllocQuantities is TABLE OF NUMBER
5 INDEX BY BINARY_INTEGER;
6
7 /* Bug 9891996 - Added External IDV field to the record type */
8
9 TYPE PDOIheader IS RECORD (
10 auction_header_id pon_auction_headers_all.auction_header_id%TYPE,
11 document_number pon_auction_headers_all.document_number%TYPE,
12 org_id pon_auction_headers_all.org_id%TYPE,
13 contract_type pon_auction_headers_all.contract_type%TYPE,
14 language_code pon_auction_headers_all.language_code%TYPE,
15 po_start_date pon_auction_headers_all.po_start_date%TYPE,
16 po_end_date pon_auction_headers_all.po_end_date%TYPE,
17 currency_code pon_auction_headers_all.currency_code%TYPE,
18 fob_code pon_auction_headers_all.fob_code%TYPE,
19 freight_terms_code pon_auction_headers_all.freight_terms_code%TYPE,
20 carrier_code pon_auction_headers_all.carrier_code%TYPE,
21 payment_terms_id pon_auction_headers_all.payment_terms_id%TYPE,
22 ship_to_location_id pon_auction_headers_all.ship_to_location_id%TYPE,
23 bill_to_location_id pon_auction_headers_all.bill_to_location_id%TYPE,
24 auction_origination_code pon_auction_headers_all.auction_origination_code%TYPE,
28 vendor_id pon_bid_headers.vendor_id%TYPE,
25 source_reqs_flag pon_auction_headers_all.source_reqs_flag%TYPE,
26 bid_number pon_bid_headers.bid_number%TYPE,
27 order_number pon_bid_headers.order_number%TYPE,
29 vendor_site_id pon_bid_headers.vendor_site_id%TYPE,
30 vendor_contact_id pon_bid_headers.trading_partner_contact_id%TYPE,
31 agent_id pon_bid_headers.agent_id%TYPE,
32 global_agreement_flag pon_auction_headers_all.global_agreement_flag%TYPE,
33 po_min_rel_amount pon_auction_headers_all.po_min_rel_amount%TYPE,
34 po_agreed_amount pon_bid_headers.po_agreed_amount%TYPE,
35 bid_currency_code pon_bid_headers.bid_currency_code%TYPE,
36 rate_type pon_auction_headers_all.rate_type%TYPE,
37 rate_date pon_auction_headers_all.rate_date%TYPE,
38 rate_dsp pon_bid_headers.rate_dsp%TYPE,
39 create_sourcing_rules pon_bid_headers.create_sourcing_rules%TYPE,
40 update_sourcing_rules pon_bid_headers.update_sourcing_rules%TYPE,
41 release_method pon_bid_headers.release_method%TYPE,
42 initiate_approval pon_bid_headers.initiate_approval%TYPE,
43 acceptance_required_flag pon_bid_headers.acceptance_required_flag%TYPE,
44 po_style_id pon_auction_headers_all.po_style_id%TYPE,
45 progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE,
46 supplier_enterable_pymt_flag pon_auction_headers_all.supplier_enterable_pymt_flag%TYPE,
47 idv_header_id pon_bid_headers.idv_header_id%TYPE,
48 clm_external_idv pon_bid_headers.external_idv%TYPE,
49 umbrella_program_id pon_auction_headers_all.umbrella_program_id%TYPE,
50 fair_opp_notice_flag pon_auction_headers_all.fair_opp_notice_flag%TYPE
51 );
52
53 TYPE PDOIline IS RECORD (
54 line_number pon_auction_item_prices_all.line_number%TYPE,
55 line_type_id pon_auction_item_prices_all.line_type_id%TYPE,
56 order_type_lookup_code pon_auction_item_prices_all.order_type_lookup_code%TYPE,
57 line_origination_code pon_auction_item_prices_all.line_origination_code%TYPE,
58 item_id pon_auction_item_prices_all.item_id%TYPE,
59 item_revision pon_auction_item_prices_all.item_revision%TYPE,
60 category_id pon_auction_item_prices_all.category_id%TYPE,
61 item_description pon_auction_item_prices_all.item_description%TYPE,
62 unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE,
63 ship_to_location_id pon_auction_item_prices_all.ship_to_location_id%TYPE,
64 need_by_date pon_auction_item_prices_all.clm_need_by_date%TYPE,
65 pop_start_date pon_auction_item_prices_all.need_by_start_date%TYPE,
66 pop_end_date pon_auction_item_prices_all.need_by_date%TYPE,
67 award_quantity pon_bid_item_prices.award_quantity%TYPE,
68 po_min_rel_amount pon_auction_item_prices_all.po_min_rel_amount%TYPE,
69 has_price_elements_flag pon_auction_item_prices_all.has_price_elements_flag%TYPE,
70 bid_currency_unit_price pon_bid_item_prices.bid_currency_unit_price%TYPE,
71 promised_date pon_bid_item_prices.promised_date%TYPE,
72 job_id pon_auction_item_prices_all.job_id%TYPE,
73 po_agreed_amount pon_auction_item_prices_all.po_agreed_amount%TYPE,
74 purchase_basis pon_auction_item_prices_all.purchase_basis%TYPE,
75 bid_curr_advance_amount pon_bid_item_prices.bid_curr_advance_amount%TYPE,
76 recoupment_rate_percent pon_bid_item_prices.recoupment_rate_percent%TYPE,
77 progress_pymt_rate_percent pon_bid_item_prices.progress_pymt_rate_percent%TYPE,
78 retainage_rate_percent pon_bid_item_prices.retainage_rate_percent%TYPE,
79 bid_curr_max_retainage_amt pon_bid_item_prices.bid_curr_max_retainage_amt%TYPE,
80 has_bid_payments_flag pon_bid_item_prices.has_bid_payments_flag%TYPE,
81 award_shipment_number pon_bid_item_prices.award_shipment_number%TYPE,
82 line_num_display pon_auction_item_prices_all.line_num_display%TYPE,
83 group_line_id pon_auction_item_prices_all.group_line_id%TYPE,
84 clm_info_flag pon_auction_item_prices_all.clm_info_flag%TYPE,
85 clm_option_indicator pon_auction_item_prices_all.clm_option_indicator%TYPE,
86 clm_option_num pon_auction_item_prices_all.clm_option_num%TYPE,
87 clm_option_from_date pon_auction_item_prices_all.clm_option_from_date%TYPE,
88 clm_option_to_date pon_auction_item_prices_all.clm_option_to_date%TYPE,
89 clm_funded_flag pon_auction_item_prices_all.clm_funded_flag%TYPE,
90 clm_base_line_num pon_auction_item_prices_all.clm_base_line_num%TYPE,
91 CONTRACT_TYPE pon_auction_item_prices_all.CLM_CONTRACT_TYPE%TYPE,
92 COST_CONSTRAINT pon_auction_item_prices_all.CLM_COST_CONSTRAINT%TYPE,
93 CLM_IDC_TYPE pon_auction_item_prices_all.CLM_IDC_TYPE%TYPE,
94 IDV_LINE_ID pon_bid_item_prices.idv_line_id%TYPE,
95 EXHIBIT_NUMBER pon_auction_item_prices_all.exhibit_number%TYPE, --ELINs project
96 -- Event Based Delivery Project
97 CLM_DELIVERY_EVENT_CODE pon_auction_item_prices_all.CLM_DELIVERY_EVENT_CODE%TYPE,
98 CLM_DELIVERY_PERIOD pon_auction_item_prices_all.CLM_DELIVERY_PERIOD%TYPE,
99 CLM_DELIVERY_PERIOD_UOM pon_auction_item_prices_all.CLM_DELIVERY_PERIOD_UOM%TYPE,
100 CLM_PROMISE_PERIOD pon_bid_item_prices.CLM_PROMISE_PERIOD%TYPE,
101 CLM_PROMISE_PERIOD_UOM pon_bid_item_prices.CLM_PROMISE_PERIOD_UOM%TYPE,
102 CLM_PROMISE_POP_DURATION pon_bid_item_prices.CLM_PROMISE_POP_DURATION%TYPE,
103 CLM_PROMISE_POP_DURATION_UOM pon_bid_item_prices.CLM_PROMISE_POP_DURATION_UOM%TYPE
104
105 );
106
110 p_formatted_name IN VARCHAR2, -- 4
107 procedure AUTO_ALLOC_AND_SPLIT_REQ(p_auction_header_id IN NUMBER, -- 1
108 p_user_name IN VARCHAR2, -- 2
109 p_user_id IN NUMBER, -- 3
111 p_auction_title IN VARCHAR2, -- 5
112 p_organization_name IN VARCHAR2,
113 p_resultout OUT NOCOPY VARCHAR2,
114 x_allocation_error OUT NOCOPY VARCHAR2,
115 x_line_number OUT NOCOPY NUMBER,
116 x_item_number OUT NOCOPY VARCHAR2,
117 x_item_description OUT NOCOPY VARCHAR2,
118 x_item_revision OUT NOCOPY VARCHAR2,
119 x_requisition_number OUT NOCOPY VARCHAR2,
120 x_job_name OUT NOCOPY VARCHAR2,
121 x_document_disp_line_number OUT NOCOPY VARCHAR2);
122
123
124 procedure ALLOC_ALL_UNALLOC_ITEMS(p_auction_header_id IN NUMBER,
125 p_allocation_result OUT NOCOPY VARCHAR2,
126 p_failure_reason OUT NOCOPY VARCHAR2,
127 p_item_line_number OUT NOCOPY NUMBER,
128 p_item_number OUT NOCOPY VARCHAR2,
129 p_item_description OUT NOCOPY VARCHAR2,
130 p_item_revision OUT NOCOPY VARCHAR2,
131 p_requisition_number OUT NOCOPY VARCHAR2,
132 p_job_name OUT NOCOPY VARCHAR2,
133 p_document_disp_line_number OUT NOCOPY VARCHAR2);
134
135 PROCEDURE SPLIT_REQ_LINES(p_auction_header_id IN NUMBER,
136 p_split_result OUT NOCOPY VARCHAR2,
137 p_split_failure_reason OUT NOCOPY VARCHAR2,
138 p_item_line_number OUT NOCOPY NUMBER,
139 p_item_number OUT NOCOPY VARCHAR2,
140 p_item_description OUT NOCOPY VARCHAR2,
141 p_item_revision OUT NOCOPY VARCHAR2,
142 p_requisition_number OUT NOCOPY VARCHAR2,
143 p_job_name OUT NOCOPY VARCHAR2);
144
145 PROCEDURE Auto_Req_Allocation(p_auctionID IN NUMBER,
146 p_line_number IN NUMBER,
147 p_result OUT NOCOPY VARCHAR2,
148 p_error_message OUT NOCOPY VARCHAR2);
149
150
151 PROCEDURE START_PO_WORKFLOW(p_auction_header_id IN NUMBER, -- 1
152 p_user_name IN VARCHAR2, -- 2
153 p_user_id IN NUMBER, -- 3
154 p_formatted_name IN VARCHAR2, -- 4
155 p_auction_title IN VARCHAR2, -- 5
156 p_organization_name IN VARCHAR2,
157 p_email_type IN VARCHAR2,
158 p_itemkey IN VARCHAR2,
159 x_allocation_error OUT NOCOPY VARCHAR2,
160 x_line_number OUT NOCOPY NUMBER,
161 x_item_number OUT NOCOPY VARCHAR2,
162 x_item_description OUT NOCOPY VARCHAR2,
163 x_item_revision OUT NOCOPY VARCHAR2,
164 x_requisition_number OUT NOCOPY VARCHAR2,
165 x_job_name OUT NOCOPY VARCHAR2,
166 x_document_disp_line_number OUT NOCOPY VARCHAR2);
167
168 PROCEDURE START_PO_CREATION(EFFBUF OUT NOCOPY VARCHAR2, -- std. out param for concurrent program
169 RETCODE OUT NOCOPY VARCHAR2, -- std. out param for concurrent program
170 p_auction_header_id IN NUMBER, -- 1
171 p_user_name IN VARCHAR2, -- 2
172 p_user_id IN NUMBER, -- 3
173 p_formatted_name IN VARCHAR2, -- 4
174 p_auction_title IN VARCHAR2, -- 5
175 p_organization_name IN VARCHAR2, -- 6
176 p_resultout OUT NOCOPY VARCHAR2); -- 7
177
178 procedure GENERATE_POS(p_auction_header_id IN NUMBER, -- 1
179 p_user_name IN VARCHAR2, -- 2
180 p_user_id IN NUMBER, -- 3
181 p_resultout OUT NOCOPY VARCHAR2);
182
183
184 PROCEDURE CREATE_PO_STRUCTURE(p_auction_header_id IN NUMBER,
185 p_bid_number IN NUMBER,
186 p_user_id IN NUMBER,
187 p_interface_header_id OUT NOCOPY NUMBER,
188 p_pdoi_header OUT NOCOPY PDOIheader,
189 p_error_code OUT NOCOPY VARCHAR2,
190 p_error_message OUT NOCOPY VARCHAR2);
191
192
193 PROCEDURE LAUNCH_PO_APPROVAL (p_po_header_id IN NUMBER,
194 p_pdoi_header IN PDOIheader,
195 p_user_id IN NUMBER
196 );
197
198 PROCEDURE CHECK_PO_STATUS(itemtype IN VARCHAR2,
199 itemkey IN VARCHAR2,
200 actid IN NUMBER,
201 uncmode IN VARCHAR2,
202 resultout OUT NOCOPY VARCHAR2);
203
204
205 PROCEDURE GENERATE_PO_SUCCESS_EMAIL(document_id IN VARCHAR2,
206 display_type IN VARCHAR2,
207 document IN OUT NOCOPY VARCHAR2,
208 document_type IN OUT NOCOPY VARCHAR2);
209
210 PROCEDURE GENERATE_PO_FAILURE_EMAIL(document_id IN VARCHAR2,
211 display_type IN VARCHAR2,
212 document IN OUT NOCOPY VARCHAR2,
213 document_type IN OUT NOCOPY VARCHAR2);
214
215
216 procedure CHECK_PO_EMAIL_TYPE (itemtype IN VARCHAR2,
217 itemkey IN VARCHAR2,
218 actid IN NUMBER,
219 uncmode IN VARCHAR2,
220 resultout OUT NOCOPY VARCHAR2);
221
222 PROCEDURE INSERT_IP_DESCRIPTORS(p_auction_header_id IN NUMBER,
223 p_bid_number IN NUMBER,
224 p_interface_header_id IN NUMBER,
225 p_user_id IN NUMBER,
226 p_login_id IN NUMBER,
227 p_batch_start IN NUMBER,
228 p_batch_end IN NUMBER);
229
230 /*Added for Line Type and Structure Changes Project*/
231 PROCEDURE create_pon_back_req_alloc(p_auction_header_id IN NUMBER, x_resultout in out nocopy varchar2 );
232
233 FUNCTION get_vendor_contact_id(
234 p_trading_partner_contact_id IN NUMBER,
235 p_vendor_site_id IN NUMBER,
236 p_vendor_id IN NUMBER)
237 RETURN NUMBER;
238
239
240 END PON_AUCTION_CREATE_PO_PKG;