[Home] [Help]
140: and p1.action = g_update_action
141: and exists
142: (select 'x'
143: from
144: pon_auction_item_prices_all prev_round_item
145: where
146: prev_round_item.line_number <= l_prev_max_line_number
147: and prev_round_item.auction_header_id = p_auction_header_id
148: and nvl(prev_round_item.quantity_disabled_flag,'N') = 'Y'
156: -- TBD ANY VALIDATIONS
157: update pon_item_prices_interface p1
158: set item_description =
159: (select item_description
160: from pon_auction_item_prices_all pal
161: where p1.batch_id = p_batch_id
162: and p1.action = g_update_action
163: and p1.auction_header_id = pal.auction_header_id
164: and p1.auction_line_number = pal.line_number)
166: p1.batch_id = p_batch_id
167: and p1.action = g_update_action
168: and exists
169: (select 'x'
170: from pon_auction_item_prices_all pal1
171: where
172: p1.auction_header_id = pal1.auction_header_id
173: and p1.auction_line_number = pal1.line_number
174: and (pal1.line_origination_code ='BLANKET' or p1.item_id is not null));
178: -- lines if they are from blanket or requisition.
179: update pon_item_prices_interface p1
180: set line_type = (select tl.line_type
181: from po_line_types_tl tl,
182: pon_auction_item_prices_all pal
183: where p1.batch_id = p_batch_id
184: and p1.action = g_update_action
185: and pal.auction_header_id = p1.auction_header_id
186: and p1.auction_line_number = pal.line_number
187: and pal.line_type_id = tl.line_type_id
188: AND tl.LANGUAGE = UserEnv('Lang')),
189: item_number = (select pal.item_number
190: from
191: pon_auction_item_prices_all pal
192: where p1.batch_id = p_batch_id
193: and p1.action = g_update_action
194: and p1.auction_header_id = pal.auction_header_id
195: and p1.auction_line_number = pal.line_number)
197: p1.batch_id = p_batch_id
198: and p1.action = g_update_action
199: and exists
200: (select 'x'
201: from pon_auction_item_prices_all pal1
202: where
203: p1.auction_header_id = pal1.auction_header_id
204: and p1.auction_line_number = pal1.line_number
205: and pal1.line_origination_code in ('BLANKET','REQUISITION'));
210: update pon_item_prices_interface p1
211: set (item_revision,unit_of_measure) =
212: (select pal.item_revision,pal.unit_of_measure
213: from
214: pon_auction_item_prices_all pal
215: where p1.batch_id = p_batch_id
216: and p1.action=g_update_action
217: and p1.auction_header_id = pal.auction_header_id
218: and p1.auction_line_number = pal.line_number)
220: p1.batch_id = p_batch_id
221: and p1.action = g_update_action
222: and exists
223: (select 'x'
224: from pon_auction_item_prices_all pal1
225: where
226: p1.auction_header_id = pal1.auction_header_id
227: and p1.auction_line_number = pal1.line_number
228: and pal1.line_origination_code = 'REQUISITION');
231: -- line is from a backing requisition from the then update the quantity
232: update pon_item_prices_interface p1
233: set quantity = (select pal.quantity
234: from
235: pon_auction_item_prices_all pal
236: where p1.batch_id = p_batch_id
237: and p1.action = g_update_action
238: and pal.auction_header_id = p1.auction_header_id
239: and pal.line_number = p1.auction_line_number)
241: p1.batch_id = p_batch_id
242: and p1.action = g_update_action
243: and exists
244: (select 'x'
245: from pon_auction_item_prices_all pal1
246: where
247: p1.auction_header_id = pal1.auction_header_id
248: and p1.auction_line_number = pal1.line_number
249: and pal1.order_type_lookup_code = 'AMOUNT'
263: (select pal.item_revision,
264: pal.unit_of_measure,
265: decode(pal.order_type_lookup_code,'AMOUNT',pal.quantity,p1.quantity)
266: from
267: pon_auction_item_prices_all pal
268: where p1.batch_id = p_batch_id
269: and p1.action=g_update_action
270: and p1.auction_header_id = pal.auction_header_id
271: and p1.auction_line_number = pal.line_number)
273: p1.batch_id = p_batch_id
274: and p1.action = g_update_action
275: and exists
276: (select 'x'
277: from pon_auction_item_prices_all pal1
278: where
279: p1.auction_header_id = pal1.auction_header_id
280: and p1.auction_line_number = pal1.line_number
281: and pal1.line_origination_code ='REQUISITION');
288: update pon_item_prices_interface p1
289: set ship_to_location = (select st.location_code
290: from po_ship_to_loc_org_v st,
291: financials_system_params_all fsp,
292: pon_auction_item_prices_all pal
293: where p1.batch_id = p_batch_id
294: and p1.action = g_update_action
295: and p1.auction_header_id = pal.auction_header_id
296: and p1.auction_line_number = pal.line_number
303: p1.batch_id = p_batch_id
304: and p1.action = g_update_action
305: and exists
306: (select 'x'
307: from pon_auction_item_prices_all pal1
308: where
309: p1.auction_header_id = pal1.auction_header_id
310: and p1.auction_line_number = pal1.line_number
311: and pal1.line_origination_code in ('BLANKET','REQUISITION'));
319: set (display_target_flag,unit_display_target_flag) = (select
320: pal.display_target_price_flag,
321: pal.unit_display_target_flag
322: from
323: pon_auction_item_prices_all pal
324: where p1.batch_id = p_batch_id
325: and p1.action = g_update_action
326: and p1.auction_line_number = pal.line_number
327: and pal.auction_header_id = p1.auction_header_id)
697: PON_PARTY_LINE_EXCLUSIONS
698: PON_AUC_PAYMENTS_SHIPMENTS
699: Attachments
700: Update backing requisitions for lines being deleted.
701: PON_AUCTION_ITEM_PRICES_ALL
702: ======================================================================*/
703: PROCEDURE DELETE_LINES_WITH_CHILDREN is
704:
705: l_module CONSTANT VARCHAR2(26) := 'DELETE_LINES_WITH_CHILDREN';
710: auction_item.line_number,
711: auction_item.line_origination_code,
712: auction_item.org_id
713: FROM pon_item_prices_interface interface_line,
714: pon_auction_item_prices_all auction_item
715: WHERE interface_line.BATCH_ID = g_batch_id
716: and interface_line.action = g_delete_action
717: and interface_line.auction_header_id = auction_item.auction_header_id
718: and interface_line.auction_line_number = auction_item.line_number
956: --Delete all attachments and update backing requisitions if they exist.
957: FOR delete_line_record IN delete_line_cursor LOOP
958:
959: FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
960: (x_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
961: x_pk1_value => g_auction_header_id,
962: x_pk2_value => delete_line_record.line_number);
963:
964: if delete_line_record.line_origination_code is not null then
971:
972: end loop;
973:
974:
975: delete from pon_auction_item_prices_all item_prices
976: where
977: auction_header_id = g_auction_header_id
978: and exists
979: (select
988: OR item_prices.group_line_id = p1.auction_line_number
989: OR item_prices.clm_base_line_num = p1.auction_line_number));
990:
991:
992: delete from pon_auction_item_prices_all item_prices
993: where
994: auction_header_id = g_auction_header_id
995: and exists
996: (select
1017:
1018: PARAMETERS: NONE
1019:
1020: COMMENT : This procedure will add the new lines from the pon_item_prices_interface
1021: interface table to the pon_auction_item_prices_all transaction table.
1022: The following sql will be used for the same. This procedure will
1023: contain the logic of copying the lines as is present in the
1024: copyItemData method of NegItemSpreadsheetAMImpl.
1025: ======================================================================*/
1026: PROCEDURE ADD_LINES is
1027:
1028: l_module CONSTANT VARCHAR2(9) := 'ADD_LINES';
1029:
1030: l_price_break_type pon_auction_item_prices_all.price_break_type%type;
1031: l_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
1032:
1033: -- clm clin slin changes
1034: l_is_fed VARCHAR2(1) := 'N';
1027:
1028: l_module CONSTANT VARCHAR2(9) := 'ADD_LINES';
1029:
1030: l_price_break_type pon_auction_item_prices_all.price_break_type%type;
1031: l_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
1032:
1033: -- clm clin slin changes
1034: l_is_fed VARCHAR2(1) := 'N';
1035: l_doctype_id NUMBER;
1052: -- uda template id
1053: l_uda_template_date pon_auction_headers_all.uda_template_date%TYPE;
1054: l_template_return_status VARCHAR2(10);
1055: l_template_err_msg VARCHAR2(50);
1056: l_uda_template_id pon_auction_item_prices_all.uda_template_id%TYPE;
1057:
1058:
1059:
1060: BEGIN
1090: l_price_break_neg_flag);
1091:
1092:
1093:
1094: insert into pon_auction_item_prices_all
1095: fields
1096: (AUCTION_HEADER_ID,
1097: LINE_NUMBER,
1098: SUB_LINE_SEQUENCE_NUMBER,
1278: will be used for this. This would be called after lined have been updated
1279: added or deleted.
1280: */
1281: IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1282: print_debug_log(l_module,'Inserted records into pon_auction_item_prices_all');
1283: END IF;
1284:
1285: --clm clin/slin changes
1286: IF(l_is_fed = 'Y') THEN
1287: l_max_clin := '0000';
1288:
1289: /* Bug 9378884 - Start
1290: * When the last batch runs i.e., from 7501-9999 the below query will retrun
1291: * 9999 as the table pon_auction_item_prices_all will have 9999 records. When
1292: * the same value is passed to pon_clo_renumber_pkg.next_clin_num only one line
1293: * will be processed as per the algorithm written in that procedure. Hence added
1294: * line_num_display is not null condition to the query so that processing of the
1295: * next batch will start from the last row.
1300: */
1301:
1302: BEGIN
1303: SELECT line_num_display BULK COLLECT INTO clin_num_tbl
1304: FROM pon_auction_item_prices_all
1305: WHERE auctioN_header_id = g_auction_header_id
1306: AND group_line_id IS NULL
1307: AND line_num_display IS NOT NULL
1308: order by line_num_display;
1317: BEGIN
1318: FOR upd_row IN update_clin_num_cursor LOOP
1319: next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
1320:
1321: UPDATE pon_auction_item_prices_all
1322: SET line_num_display = next_clin_num
1323: WHERE auction_header_id = upd_row.auction_header_id
1324: AND line_number = upd_row.auction_line_number;
1325:
1335: NULL;
1336: END;
1337:
1338: BEGIN
1339: UPDATE pon_auction_item_prices_all
1340: SET CLM_OPTION_INDICATOR = 'B'
1341: WHERE
1342: auction_header_id = g_auction_header_id
1343: AND line_number IN (SELECT DISTINCT paip1.line_number FROM pon_auction_item_prices_all paip1, pon_auction_item_prices_all paip2
1339: UPDATE pon_auction_item_prices_all
1340: SET CLM_OPTION_INDICATOR = 'B'
1341: WHERE
1342: auction_header_id = g_auction_header_id
1343: AND line_number IN (SELECT DISTINCT paip1.line_number FROM pon_auction_item_prices_all paip1, pon_auction_item_prices_all paip2
1344: WHERE
1345: paip1.auction_header_id = g_auction_header_id
1346: AND paip1.auction_header_id = paip2.auction_header_id
1347: AND paip2.CLM_OPTION_INDICATOR = 'O'
1369: x_err_msg => l_template_err_msg);
1370:
1371: IF( l_template_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1372:
1373: UPDATE pon_auction_item_prices_all
1374: SET uda_template_id = l_uda_template_id
1375: WHERE auction_header_id = g_auction_header_id;
1376:
1377: END IF;
1883: from pon_attribute_scores
1884: where auction_header_id = g_auction_header_id and
1885: line_number in (select paip.line_number
1886: from pon_item_prices_interface p1,
1887: pon_auction_item_prices_all paip
1888: where p1.batch_id = g_batch_id and
1889: p1.auction_header_id = paip.auction_header_id and
1890: p1.auction_line_number = paip.line_number and
1891: paip.ip_category_id is not null and
1905: paa.ip_category_id is not null and
1906: paa.ip_category_id <> 0 and
1907: paa.line_number in (select paip.line_number
1908: from pon_item_prices_interface p1,
1909: pon_auction_item_prices_all paip
1910: where p1.batch_id = g_batch_id and
1911: p1.auction_header_id = paip.auction_header_id and
1912: p1.auction_line_number = paip.line_number and
1913: paip.ip_category_id is not null and
2034: p1.batch_id = g_batch_id
2035: and p1.action = g_update_action;
2036:
2037: FORALL x in 1..l_line_number.COUNT
2038: UPDATE PON_AUCTION_ITEM_PRICES_ALL
2039: SET
2040: GROUP_TYPE = l_group_type(x),
2041: ITEM_DESCRIPTION = l_item_description(x),
2042: CATEGORY_ID = l_category_id(x),
2117: AND INTERFACE.group_type IN ('LOT_LINE','GROUP_LINE');
2118:
2119:
2120: FORALL x in 1..l_line_number.COUNT
2121: UPDATE PON_AUCTION_ITEM_PRICES_ALL
2122: SET
2123: LAST_AMENDMENT_UPDATE = decode(l_last_amendment_update,0,LAST_AMENDMENT_UPDATE,l_last_amendment_update),
2124: MODIFIED_DATE = sysdate,
2125: MODIFIED_FLAG = 'Y'
2138: INTERFACE.batch_id = g_batch_id
2139: AND INTERFACE.auction_line_number <= l_max_internal_line_num;
2140:
2141: FORALL x in 1..l_line_number.COUNT
2142: UPDATE PON_AUCTION_ITEM_PRICES_ALL
2143: SET
2144: LAST_AMENDMENT_UPDATE = decode(l_last_amendment_update,0,LAST_AMENDMENT_UPDATE,l_last_amendment_update),
2145: MODIFIED_DATE = sysdate,
2146: MODIFIED_FLAG = 'Y'
2853: and auction header id passed as a parameter to the procedure.
2854: This will also set the global variables g_batch_id and
2855: g_auction_header_id.
2856: This will also re-number all the lines and set flags in
2857: pon_auction_item_prices_all.
2858: ======================================================================*/
2859: PROCEDURE SYNCH_FROM_INTERFACE(
2860: p_batch_id IN NUMBER,
2861: p_auction_header_id IN NUMBER,
3048: p_category_name => 'Vendor',
3049: p_document_description => l_attachment_record.attachment_desc,
3050: p_datatype_id => 5,
3051: p_url => l_attachment_record.attachment_url,
3052: p_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
3053: p_pk1_value => g_auction_header_id,
3054: p_pk2_value => l_attachment_record.auction_line_number,
3055: p_pk3_value => NULL,
3056: p_pk4_value => NULL,
3060: IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3061: l_progress := 'Data copied for p_auction_header_id = '||p_auction_header_id;
3062: END if;
3063:
3064: /* Set the following fields for all records in pon_auction_item_prices all
3065: HAS_ATTRIBUTES_FLAG,
3066: HAS_SHIPMENTS_FLAG
3067: HAS_PRICE_ELEMENTS_FLAG
3068: HAS_BUYER_PFS_FLAG
3117:
3118: -- Determine total number of lines
3119: SELECT count(line_number)
3120: INTO l_number_of_lines
3121: FROM pon_auction_item_prices_all
3122: WHERE auction_header_id = g_auction_header_id;
3123:
3124: else
3125:
3140: into
3141: l_number_of_lines,
3142: l_max_display_number
3143: FROM
3144: pon_auction_item_prices_all paip
3145: where
3146: paip.auction_header_id = p_auction_header_id;
3147: end if;
3148:
3168: END IF;
3169:
3170: SELECT count(line_number)
3171: INTO l_number_of_lines
3172: FROM pon_auction_item_prices_all
3173: WHERE auction_header_id = g_auction_header_id;
3174:
3175: IF (g_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3176: l_progress := 'delete completed g_batch_id = '||g_batch_id ||
3267:
3268: l_module CONSTANT VARCHAR2(32) := 'SYNCH_PAYMENTS_FROM_INTERFACE';
3269: l_progress varchar2(200);
3270: l_sequence NUMBER :=0;
3271: l_previous_line_number pon_auction_item_prices_all.document_disp_line_number%TYPE;
3272: l_prev_amend_auc_id pon_auction_headers_all.auction_header_id_prev_amend%TYPE;
3273: l_supplier_modify_flag pon_auction_headers_all.supplier_enterable_pymt_flag%TYPE;
3274:
3275:
3281: paps.auction_header_id,
3282: paps.line_number,
3283: papi.document_disp_line_number
3284: FROM pon_auc_payments_interface papi,
3285: pon_auction_item_prices_all pai,
3286: pon_auc_payments_shipments paps
3287: WHERE papi.auction_header_id = pai.auction_header_id
3288: AND papi.document_disp_line_number = pai.document_disp_line_number
3289: AND paps.auction_header_id = pai.auction_header_id
3327: DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, ppi.PROJECT_EXPENDITURE_TYPE)) PROJECT_EXPENDITURE_TYPE,
3328: DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, porg.organization_id)) PROJECT_EXP_ORGANIZATION_ID,
3329: DECODE(pai.LINE_ORIGINATION_CODE, 'REQUISITION', NULL, DECODE(pah.SUPPLIER_ENTERABLE_PYMT_FLAG,'Y', NULL, ppi.PROJECT_EXPENDITURE_ITEM_DATE)) PROJECT_EXPENDITURE_ITEM_DATE
3330: FROM PON_AUC_PAYMENTS_INTERFACE ppi,
3331: PON_AUCTION_ITEM_PRICES_ALL pai,
3332: PON_AUCTION_HEADERS_ALL pah,
3333: FND_USER fu,
3334: HR_LOCATIONS_ALL hrl,
3335: MTL_UNITS_OF_MEASURE uom,
3473: FROM PON_AUCTION_HEADERS_ALL pah
3474: WHERE auction_header_id = p_auction_header_id;
3475:
3476: IF l_prev_amend_auc_id IS NOT NULL THEN
3477: --Update pon_auction_item_prices_all to set lastupdatedate
3478: -- for changed payments
3479: UPDATE pon_auction_item_prices_all al
3480: SET modified_flag = 'Y'
3481: , modified_date = SYSDATE
3475:
3476: IF l_prev_amend_auc_id IS NOT NULL THEN
3477: --Update pon_auction_item_prices_all to set lastupdatedate
3478: -- for changed payments
3479: UPDATE pon_auction_item_prices_all al
3480: SET modified_flag = 'Y'
3481: , modified_date = SYSDATE
3482: , last_update_date = SYSDATE
3483: , last_updated_by = fnd_global.user_id