[Home] [Help]
89: PARAMETERS:
90: IN : p_auction_header_id NUMBER auction header id
91: IN : p_batch_id NUMBER batch id for which the defaulting will be done.
92:
93: COMMENT : This procedure will default various field in pon_item_prices_interface,
94: pon_auc_attributes_interface and pon_auc_price_elements_int
95: for lines being updated based on various conditions.
96: ======================================================================*/
97: procedure DEFAULT_PREV_ROUND_AMEND_LINES(
132: END IF;
133:
134: -- Update all lines that have line number less than or
135: -- equal to the max line number and have action as update.
136: update pon_item_prices_interface p1
137: set p1.price_and_quantity_apply = 'Y'
138: where
139: p1.batch_id = p_batch_id
140: and p1.action = g_update_action
153: -- the description will be set from the Item as it is
154: -- stored in the database.
155: -- TBD CONSIDER THIS FOR UPDATE STATEMENT.
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
175:
176:
177: -- Update the line type and the line number of existing
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
206:
207: /*
208: -- Update the Item revision and Unit Of Measure if the
209: -- line is coming from a requisition from
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
228: and pal1.line_origination_code = 'REQUISITION');
229:
230: -- If the line order type look up code = 'AMOUNT' and the
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
255: -- Update the Item revision and Unit Of Measure if the
256: -- line is coming from a requisition from
257: -- If the line order type look up code = 'AMOUNT' and the
258: -- line is from a backing requisition from the then update the quantity
259: update pon_item_prices_interface p1
260: set (item_revision,
261: unit_of_measure,
262: quantity) =
263: (select pal.item_revision,
284: -- Update Ship to location id from the if the negotiation
285: -- outcome is BPA or CPA.
286: if l_is_blanket_agreement <> 'Y' then
287:
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
314:
315: -- If this is an amendment then the display target flag and display target
316: -- unit flag will be updated from the last amendment.
317: if l_is_amendment = 'Y' then
318: update pon_item_prices_interface p1
319: set (display_target_flag,unit_display_target_flag) = (select
320: pal.display_target_price_flag,
321: pal.unit_display_target_flag
322: from
346: interface_attribute.batch_id = p_batch_id
347: and exists
348: (select 'x'
349: from
350: pon_item_prices_interface item_interface,
351: pon_auction_attributes auction_attributes
352: where
353: item_interface.batch_id = p_batch_id
354: and item_interface.action = g_update_action
374: (select
375: 'x'
376: from
377: pon_price_elements pe,
378: pon_item_prices_interface paip_int,
379: pon_auction_headers_all pah
380: where
381: paip_int.batch_id = p_batch_id
382: and pe_int.auction_line_number = pe.line_number
709: SELECT
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
727: CURSOR delete_pymt_attachments_cursor IS
728: SELECT paps.payment_id,
729: paps.auction_header_id,
730: paps.line_number
731: FROM pon_item_prices_interface p1,
732: pon_auction_item_prices paip,
733: pon_auc_payments_shipments paps,
734: FND_ATTACHED_DOCUMENTS fnd
735: WHERE p1.batch_id = g_batch_id
771: and exists
772: (select
773: 'x'
774: from
775: pon_item_prices_interface p1,
776: pon_auction_item_prices paip
777: where
778: p1.batch_id = g_batch_id
779: and p1.action = g_delete_action
794: and exists
795: (select
796: 'x'
797: from
798: pon_item_prices_interface p1,
799: pon_auction_item_prices paip
800: where
801: p1.batch_id = g_batch_id
802: and p1.action = g_delete_action
817: and exists
818: (select
819: 'x'
820: from
821: pon_item_prices_interface p1,
822: pon_auction_item_prices paip
823: where
824: p1.batch_id = g_batch_id
825: and p1.action = g_delete_action
839: and exists
840: (select
841: 'x'
842: from
843: pon_item_prices_interface p1,
844: pon_auction_item_prices paip
845: where
846: p1.batch_id = g_batch_id
847: and p1.action = g_delete_action
863: and exists
864: (select
865: 'x'
866: from
867: pon_item_prices_interface p1,
868: pon_auction_item_prices paip
869: where
870: p1.batch_id = g_batch_id
871: and p1.action = g_delete_action
885: and exists
886: (select
887: 'x'
888: from
889: pon_item_prices_interface p1,
890: pon_auction_item_prices paip
891: where
892: p1.batch_id = g_batch_id
893: and p1.action = g_delete_action
906: and exists
907: (select
908: 'x'
909: from
910: pon_item_prices_interface p1,
911: pon_auction_item_prices paip
912: where
913: p1.batch_id = g_batch_id
914: and p1.action = g_delete_action
939: and line_number IN
940: (select
941: paip.line_number
942: from
943: pon_item_prices_interface p1,
944: pon_auction_item_prices paip
945: where
946: p1.batch_id = g_batch_id
947: and p1.action = g_delete_action
978: and exists
979: (select
980: 'x'
981: from
982: pon_item_prices_interface p1
983: where
984: p1.batch_id = g_batch_id
985: and p1.action = g_delete_action
986: and (item_prices.parent_line_number = p1.auction_line_number
995: and exists
996: (select
997: 'x'
998: from
999: pon_item_prices_interface p1
1000: where
1001: p1.batch_id = g_batch_id
1002: and p1.action = g_delete_action
1003: and item_prices.line_number = p1.auction_line_number);
1016: PROCEDURE: ADD_LINES PRIVATE
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.
1037: x_result VARCHAR2(10);
1038:
1039: CURSOR update_clin_num_cursor IS
1040: SELECT AUCTION_LINE_NUMBER,group_line_id,
1041: clm_info_flag,auction_header_id FROM pon_item_prices_interface
1042: WHERE batch_id = g_batch_id
1043: AND group_Line_id IS null
1044: AND Nvl(action,g_ADD_action) = g_ADD_action
1045: ORDER BY interface_line_id;
1257: Decode(l_is_fed,'Y', decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.CLM_DELIVERY_PERIOD_UOM_CODE), NULL),
1258: Decode(l_is_fed,'Y', decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.CLM_POP_DURATION), NULL),
1259: Decode(l_is_fed,'Y', decode(p1.group_type,'GROUP',NULL,'LOT_LINE',NULL,p1.CLM_POP_DURATION_UOM_CODE), NULL)
1260: from
1261: pon_item_prices_interface p1,
1262: pon_auction_headers_all pah
1263: where
1264: p1.batch_id = g_batch_id
1265: and p1.auction_header_id = pah.auction_header_id
1399: PARAMETERS: NONE
1400:
1401: COMMENT : This procedure will add the supplier price factors in
1402: PON_AUC_PRICE_ELEMENTS_INT corresponding to the new lines
1403: from the PON_ITEM_PRICES_INTERFACE interface table to the
1404: PON_PRICE_ELEMENTS transaction tables. The following sql
1405: will be used for the same. The logic for this is present
1406: in copyPriceElement method in NegItemSpreadsheetAMImpl.
1407: ======================================================================*/
1447: sysdate,
1448: g_user_id
1449: from
1450: pon_auc_price_elements_int pe_int,
1451: pon_item_prices_interface p1
1452: where
1453: pe_int.batch_id = g_batch_id
1454: and p1.batch_id = pe_int.batch_id
1455: and p1.auction_line_number = pe_int.auction_line_number
1509: g_user_id,
1510: fnd_global.login_id
1511: from
1512: pon_auc_price_differ_int pdf_int,
1513: pon_item_prices_interface p1
1514: where
1515: pdf_int.batch_id = g_batch_id
1516: and p1.batch_id = pdf_int.batch_id
1517: and p1.auction_line_number = pdf_int.auction_line_number
1610: g_user_id,
1611: nvl(paa_int.group_name,g_default_section_name)
1612: from
1613: pon_auc_attributes_interface paa_int,
1614: pon_item_prices_interface p1
1615: where
1616: paa_int.batch_id= g_batch_id
1617: and p1.batch_id = paa_int.batch_id
1618: and p1.auction_line_number = paa_int.auction_line_number
1882: (select *
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
1904: where paa.auction_header_id = g_auction_header_id 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
2028: l_clm_delivery_period_uom,
2029: l_clm_pop_duration,
2030: l_clm_pop_duration_uom
2031: from
2032: pon_item_prices_interface p1
2033: where
2034: p1.batch_id = g_batch_id
2035: and p1.action = g_update_action;
2036:
2109: DISTINCT INTERFACE.parent_line_number
2110: BULK COLLECT INTO
2111: l_line_number
2112: FROM
2113: pon_item_prices_interface INTERFACE
2114: WHERE
2115: INTERFACE.batch_id = g_batch_id
2116: AND INTERFACE.parent_line_number <= l_max_internal_line_num
2117: AND INTERFACE.group_type IN ('LOT_LINE','GROUP_LINE');
2132: interface.auction_line_number
2133: BULK COLLECT INTO
2134: l_line_number
2135: FROM
2136: pon_item_prices_interface INTERFACE
2137: WHERE
2138: INTERFACE.batch_id = g_batch_id
2139: AND INTERFACE.auction_line_number <= l_max_internal_line_num;
2140:
2185: LINE_NUMBER =
2186: (select
2187: LINE_NUMBER
2188: from
2189: pon_item_prices_interface paip_int
2190: where
2191: paip_int.batch_id = g_batch_id
2192: and price_differentials.line_number = paip_int.auction_line_number
2193: and paip_int.action = g_update_action);
2219: sysdate,
2220: g_user_id,
2221: fnd_global.login_id
2222: FROM
2223: pon_item_prices_interface paip_int,
2224: pon_auc_price_differ_int price_diff_int
2225: WHERE
2226: paip_int.batch_id = g_batch_id
2227: and price_diff_int.batch_id = paip_int.batch_id
2276: and pe.pf_type = 'SUPPLIER'
2277: and exists (select
2278: 1
2279: from
2280: PON_ITEM_PRICES_INTERFACE line_interface
2281: where
2282: line_interface.action = g_update_action
2283: and line_interface.batch_id = g_batch_id
2284: and line_interface.auction_header_id = pe.auction_header_id
2285: and line_interface.auction_line_number = pe.line_number)
2286: and not exists
2287: (select 1
2288: from
2289: PON_ITEM_PRICES_INTERFACE paip_int,
2290: PON_AUC_PRICE_ELEMENTS_INT pe_int
2291: where
2292: paip_int.action = g_update_action
2293: and paip_int.batch_id = g_batch_id
2315: l_value,
2316: l_display_target_flag
2317: from
2318: PON_AUC_PRICE_ELEMENTS_INT pe_int,
2319: PON_ITEM_PRICES_INTERFACE paip_int
2320: where
2321: paip_int.action = g_update_action
2322: and paip_int.batch_id = g_batch_id
2323: and paip_int.batch_id = pe_int.batch_id
2372: sysdate,
2373: g_user_id
2374: from
2375: pon_auc_price_elements_int pe_int,
2376: pon_item_prices_interface paip_int
2377: where
2378: paip_int.action = g_update_action
2379: and paip_int.batch_id = g_batch_id
2380: and pe_int.batch_id = paip_int.batch_id
2461: l_attribute_seq_number,
2462: l_line_number
2463: from
2464: pon_auction_attributes auction_attributes,
2465: pon_item_prices_interface line_interface
2466: where
2467: line_interface.action = g_update_action
2468: and line_interface.batch_id = g_batch_id
2469: and line_interface.auction_header_id = g_auction_header_id
2500: l_attribute_name,
2501: l_line_number
2502: from
2503: pon_auction_attributes auction_attributes,
2504: pon_item_prices_interface line_interface
2505: where
2506: line_interface.action = g_update_action
2507: and line_interface.batch_id = g_batch_id
2508: and line_interface.auction_header_id = g_auction_header_id
2557: l_attr_level,
2558: l_attr_group_seq_number,
2559: l_attr_disp_seq_number
2560: from
2561: pon_item_prices_interface line_interface,
2562: pon_auc_attributes_interface interface_attributes,
2563: pon_auction_attributes auction_attributes
2564: where
2565: interface_attributes.batch_id = g_batch_id
2659: sysdate,
2660: g_user_id
2661: from
2662: pon_auc_attributes_interface paa_int,
2663: pon_item_prices_interface p1
2664: where
2665: paa_int.batch_id= g_batch_id
2666: and p1.batch_id = paa_int.batch_id
2667: and p1.action = g_update_action
2874: IS
2875: SELECT paip.attachment_desc,
2876: paip.attachment_url,
2877: paip.auction_line_number
2878: FROM pon_item_prices_interface paip
2879: WHERE paip.auction_header_id = g_auction_header_id
2880: AND nvl(paip.action,g_add_action) <> g_delete_action
2881: AND paip.attachment_url IS NOT NULL
2882: AND paip.attachment_desc IS NOT NULL;
3154:
3155: -- Clear the interface tables
3156: -- What is there is an error? need to clear these always
3157: IF(Nvl(p_commit,'Y') = 'Y') then
3158: delete from pon_item_prices_interface where batch_id = g_batch_id;
3159: delete from pon_auc_attributes_interface where batch_id = g_batch_id;
3160: delete from pon_auc_payments_interface where batch_id = g_batch_id;
3161: delete from pon_auc_price_differ_int where batch_id = g_batch_id;
3162: delete from pon_auc_price_elements_int where batch_id = g_batch_id;