[Home] [Help]
87: PARAMETERS:
88: IN : p_auction_header_id NUMBER auction header id
89: IN : p_batch_id NUMBER batch id for which the defaulting will be done.
90:
91: COMMENT : This procedure will default various field in pon_item_prices_interface,
92: pon_auc_attributes_interface and pon_auc_price_elements_int
93: for lines being updated based on various conditions.
94: ======================================================================*/
95: procedure DEFAULT_PREV_ROUND_AMEND_LINES(
130: END IF;
131:
132: -- Update all lines that have line number less than or
133: -- equal to the max line number and have action as update.
134: update pon_item_prices_interface p1
135: set p1.price_and_quantity_apply = 'Y'
136: where
137: p1.batch_id = p_batch_id
138: and p1.action = g_update_action
151: -- the description will be set from the Item as it is
152: -- stored in the database.
153: -- TBD CONSIDER THIS FOR UPDATE STATEMENT.
154: -- TBD ANY VALIDATIONS
155: update pon_item_prices_interface p1
156: set item_description =
157: (select item_description
158: from pon_auction_item_prices_all pal
159: where p1.batch_id = p_batch_id
173:
174:
175: -- Update the line type and the line number of existing
176: -- lines if they are from blanket or requisition.
177: update pon_item_prices_interface p1
178: set line_type = (select tl.line_type
179: from po_line_types_tl tl,
180: pon_auction_item_prices_all pal
181: where p1.batch_id = p_batch_id
203:
204: /*
205: -- Update the Item revision and Unit Of Measure if the
206: -- line is coming from a requisition from
207: update pon_item_prices_interface p1
208: set (item_revision,unit_of_measure) =
209: (select pal.item_revision,pal.unit_of_measure
210: from
211: pon_auction_item_prices_all pal
225: and pal1.line_origination_code = 'REQUISITION');
226:
227: -- If the line order type look up code = 'AMOUNT' and the
228: -- line is from a backing requisition from the then update the quantity
229: update pon_item_prices_interface p1
230: set quantity = (select pal.quantity
231: from
232: pon_auction_item_prices_all pal
233: where p1.batch_id = p_batch_id
252: -- Update the Item revision and Unit Of Measure if the
253: -- line is coming from a requisition from
254: -- If the line order type look up code = 'AMOUNT' and the
255: -- line is from a backing requisition from the then update the quantity
256: update pon_item_prices_interface p1
257: set (item_revision,
258: unit_of_measure,
259: quantity) =
260: (select pal.item_revision,
281: -- Update Ship to location id from the if the negotiation
282: -- outcome is BPA or CPA.
283: if l_is_blanket_agreement <> 'Y' then
284:
285: update pon_item_prices_interface p1
286: set ship_to_location = (select st.location_code
287: from po_ship_to_loc_org_v st,
288: financials_system_params_all fsp,
289: pon_auction_item_prices_all pal
309:
310: -- If this is an amendment then the display target flag and display target
311: -- unit flag will be updated from the last amendment.
312: if l_is_amendment = 'Y' then
313: update pon_item_prices_interface p1
314: set (display_target_flag,unit_display_target_flag) = (select
315: pal.display_target_price_flag,
316: pal.unit_display_target_flag
317: from
341: interface_attribute.batch_id = p_batch_id
342: and exists
343: (select 'x'
344: from
345: pon_item_prices_interface item_interface,
346: pon_auction_attributes auction_attributes
347: where
348: item_interface.batch_id = p_batch_id
349: and item_interface.action = g_update_action
369: (select
370: 'x'
371: from
372: pon_price_elements pe,
373: pon_item_prices_interface paip_int,
374: pon_auction_headers_all pah
375: where
376: paip_int.batch_id = p_batch_id
377: and pe_int.auction_line_number = pe.line_number
704: SELECT
705: auction_item.line_number,
706: auction_item.line_origination_code,
707: auction_item.org_id
708: FROM pon_item_prices_interface interface_line,
709: pon_auction_item_prices_all auction_item
710: WHERE interface_line.BATCH_ID = g_batch_id
711: and interface_line.action = g_delete_action
712: and interface_line.auction_header_id = auction_item.auction_header_id
719: CURSOR delete_pymt_attachments_cursor IS
720: SELECT paps.payment_id,
721: paps.auction_header_id,
722: paps.line_number
723: FROM pon_item_prices_interface p1,
724: pon_auction_item_prices paip,
725: pon_auc_payments_shipments paps,
726: FND_ATTACHED_DOCUMENTS fnd
727: WHERE p1.batch_id = g_batch_id
759: and exists
760: (select
761: 'x'
762: from
763: pon_item_prices_interface p1,
764: pon_auction_item_prices paip
765: where
766: p1.batch_id = g_batch_id
767: and p1.action = g_delete_action
780: and exists
781: (select
782: 'x'
783: from
784: pon_item_prices_interface p1,
785: pon_auction_item_prices paip
786: where
787: p1.batch_id = g_batch_id
788: and p1.action = g_delete_action
801: and exists
802: (select
803: 'x'
804: from
805: pon_item_prices_interface p1,
806: pon_auction_item_prices paip
807: where
808: p1.batch_id = g_batch_id
809: and p1.action = g_delete_action
821: and exists
822: (select
823: 'x'
824: from
825: pon_item_prices_interface p1,
826: pon_auction_item_prices paip
827: where
828: p1.batch_id = g_batch_id
829: and p1.action = g_delete_action
843: and exists
844: (select
845: 'x'
846: from
847: pon_item_prices_interface p1,
848: pon_auction_item_prices paip
849: where
850: p1.batch_id = g_batch_id
851: 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
882: and exists
883: (select
884: 'x'
885: from
886: pon_item_prices_interface p1,
887: pon_auction_item_prices paip
888: where
889: p1.batch_id = g_batch_id
890: and p1.action = g_delete_action
911: and line_number IN
912: (select
913: paip.line_number
914: from
915: pon_item_prices_interface p1,
916: pon_auction_item_prices paip
917: where
918: p1.batch_id = g_batch_id
919: and p1.action = g_delete_action
948: and exists
949: (select
950: 'x'
951: from
952: pon_item_prices_interface p1
953: where
954: p1.batch_id = g_batch_id
955: and p1.action = g_delete_action
956: and item_prices.parent_line_number = p1.auction_line_number
963: and exists
964: (select
965: 'x'
966: from
967: pon_item_prices_interface p1
968: where
969: p1.batch_id = g_batch_id
970: and p1.action = g_delete_action
971: and item_prices.line_number = p1.auction_line_number);
983: PROCEDURE: ADD_LINES PRIVATE
984:
985: PARAMETERS: NONE
986:
987: COMMENT : This procedure will add the new lines from the pon_item_prices_interface
988: interface table to the pon_auction_item_prices_all transaction table.
989: The following sql will be used for the same. This procedure will
990: contain the logic of copying the lines as is present in the
991: copyItemData method of NegItemSpreadsheetAMImpl.
1141: sysdate,
1142: g_user_id,
1143: fnd_global.login_id
1144: from
1145: pon_item_prices_interface p1,
1146: pon_auction_headers_all pah
1147: where
1148: p1.batch_id = g_batch_id
1149: and p1.auction_header_id = pah.auction_header_id
1178: PARAMETERS: NONE
1179:
1180: COMMENT : This procedure will add the supplier price factors in
1181: PON_AUC_PRICE_ELEMENTS_INT corresponding to the new lines
1182: from the PON_ITEM_PRICES_INTERFACE interface table to the
1183: PON_PRICE_ELEMENTS transaction tables. The following sql
1184: will be used for the same. The logic for this is present
1185: in copyPriceElement method in NegItemSpreadsheetAMImpl.
1186: ======================================================================*/
1226: sysdate,
1227: g_user_id
1228: from
1229: pon_auc_price_elements_int pe_int,
1230: pon_item_prices_interface p1
1231: where
1232: pe_int.batch_id = g_batch_id
1233: and p1.batch_id = pe_int.batch_id
1234: and p1.auction_line_number = pe_int.auction_line_number
1288: g_user_id,
1289: fnd_global.login_id
1290: from
1291: pon_auc_price_differ_int pdf_int,
1292: pon_item_prices_interface p1
1293: where
1294: pdf_int.batch_id = g_batch_id
1295: and p1.batch_id = pdf_int.batch_id
1296: and p1.auction_line_number = pdf_int.auction_line_number
1388: g_user_id,
1389: nvl(paa_int.group_name,g_default_section_name)
1390: from
1391: pon_auc_attributes_interface paa_int,
1392: pon_item_prices_interface p1
1393: where
1394: paa_int.batch_id= g_batch_id
1395: and p1.batch_id = paa_int.batch_id
1396: and p1.auction_line_number = paa_int.auction_line_number
1649: (select *
1650: from pon_attribute_scores
1651: where auction_header_id = g_auction_header_id and
1652: line_number in (select paip.line_number
1653: from pon_item_prices_interface p1,
1654: pon_auction_item_prices_all paip
1655: where p1.batch_id = g_batch_id and
1656: p1.auction_header_id = paip.auction_header_id and
1657: p1.auction_line_number = paip.line_number and
1671: where paa.auction_header_id = g_auction_header_id and
1672: paa.ip_category_id is not null and
1673: paa.ip_category_id <> 0 and
1674: paa.line_number in (select paip.line_number
1675: from pon_item_prices_interface p1,
1676: pon_auction_item_prices_all paip
1677: where p1.batch_id = g_batch_id and
1678: p1.auction_header_id = paip.auction_header_id and
1679: p1.auction_line_number = paip.line_number and
1777: l_project_exp_organization_id,
1778: l_project_expenditure_item_dt,
1779: l_work_approver_user_id
1780: from
1781: pon_item_prices_interface p1
1782: where
1783: p1.batch_id = g_batch_id
1784: and p1.action = g_update_action;
1785:
1849: DISTINCT INTERFACE.parent_line_number
1850: BULK COLLECT INTO
1851: l_line_number
1852: FROM
1853: pon_item_prices_interface INTERFACE
1854: WHERE
1855: INTERFACE.batch_id = g_batch_id
1856: AND INTERFACE.parent_line_number <= l_max_internal_line_num
1857: AND INTERFACE.group_type IN ('LOT_LINE','GROUP_LINE');
1872: interface.auction_line_number
1873: BULK COLLECT INTO
1874: l_line_number
1875: FROM
1876: pon_item_prices_interface INTERFACE
1877: WHERE
1878: INTERFACE.batch_id = g_batch_id
1879: AND INTERFACE.auction_line_number <= l_max_internal_line_num;
1880:
1925: LINE_NUMBER =
1926: (select
1927: LINE_NUMBER
1928: from
1929: pon_item_prices_interface paip_int
1930: where
1931: paip_int.batch_id = g_batch_id
1932: and price_differentials.line_number = paip_int.auction_line_number
1933: and paip_int.action = g_update_action);
1959: sysdate,
1960: g_user_id,
1961: fnd_global.login_id
1962: FROM
1963: pon_item_prices_interface paip_int,
1964: pon_auc_price_differ_int price_diff_int
1965: WHERE
1966: paip_int.batch_id = g_batch_id
1967: and price_diff_int.batch_id = paip_int.batch_id
2015: and pe.pf_type = 'SUPPLIER'
2016: and exists (select
2017: 1
2018: from
2019: PON_ITEM_PRICES_INTERFACE line_interface
2020: where
2021: line_interface.action = g_update_action
2022: and line_interface.batch_id = g_batch_id
2023: and line_interface.auction_header_id = pe.auction_header_id
2024: and line_interface.auction_line_number = pe.line_number)
2025: and not exists
2026: (select 1
2027: from
2028: PON_ITEM_PRICES_INTERFACE paip_int,
2029: PON_AUC_PRICE_ELEMENTS_INT pe_int
2030: where
2031: paip_int.action = g_update_action
2032: and paip_int.batch_id = g_batch_id
2054: l_value,
2055: l_display_target_flag
2056: from
2057: PON_AUC_PRICE_ELEMENTS_INT pe_int,
2058: PON_ITEM_PRICES_INTERFACE paip_int
2059: where
2060: paip_int.action = g_update_action
2061: and paip_int.batch_id = g_batch_id
2062: and paip_int.batch_id = pe_int.batch_id
2111: sysdate,
2112: g_user_id
2113: from
2114: pon_auc_price_elements_int pe_int,
2115: pon_item_prices_interface paip_int
2116: where
2117: paip_int.action = g_update_action
2118: and paip_int.batch_id = g_batch_id
2119: and pe_int.batch_id = paip_int.batch_id
2200: l_attribute_seq_number,
2201: l_line_number
2202: from
2203: pon_auction_attributes auction_attributes,
2204: pon_item_prices_interface line_interface
2205: where
2206: line_interface.action = g_update_action
2207: and line_interface.batch_id = g_batch_id
2208: and line_interface.auction_header_id = g_auction_header_id
2239: l_attribute_name,
2240: l_line_number
2241: from
2242: pon_auction_attributes auction_attributes,
2243: pon_item_prices_interface line_interface
2244: where
2245: line_interface.action = g_update_action
2246: and line_interface.batch_id = g_batch_id
2247: and line_interface.auction_header_id = g_auction_header_id
2296: l_attr_level,
2297: l_attr_group_seq_number,
2298: l_attr_disp_seq_number
2299: from
2300: pon_item_prices_interface line_interface,
2301: pon_auc_attributes_interface interface_attributes,
2302: pon_auction_attributes auction_attributes
2303: where
2304: interface_attributes.batch_id = g_batch_id
2398: sysdate,
2399: g_user_id
2400: from
2401: pon_auc_attributes_interface paa_int,
2402: pon_item_prices_interface p1
2403: where
2404: paa_int.batch_id= g_batch_id
2405: and p1.batch_id = paa_int.batch_id
2406: and p1.action = g_update_action
2613: IS
2614: SELECT paip.attachment_desc,
2615: paip.attachment_url,
2616: paip.auction_line_number
2617: FROM pon_item_prices_interface paip
2618: WHERE paip.auction_header_id = g_auction_header_id
2619: AND nvl(paip.action,g_add_action) <> g_delete_action
2620: AND paip.attachment_url IS NOT NULL
2621: AND paip.attachment_desc IS NOT NULL;
2892:
2893:
2894: -- Clear the interface tables
2895: -- What is there is an error? need to clear these always
2896: delete from pon_item_prices_interface where batch_id = g_batch_id;
2897: delete from pon_auc_attributes_interface where batch_id = g_batch_id;
2898: delete from pon_auc_payments_interface where batch_id = g_batch_id;
2899: delete from pon_auc_price_differ_int where batch_id = g_batch_id;
2900: delete from pon_auc_price_elements_int where batch_id = g_batch_id;