[Home] [Help]
151: PROCEDURE derive_secondary_quantity (
152: p_chg IN PO_CHANGES_REC_TYPE,
153: p_entity_type IN VARCHAR2,
154: p_entity_id IN NUMBER,
155: x_derived_quantity2 OUT NOCOPY PO_LINES.secondary_quantity%TYPE,
156: x_return_status OUT NOCOPY VARCHAR2
157: );
158:
159: PROCEDURE get_release_break_price (
160: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
161: p_quantity IN PO_LINE_LOCATIONS.quantity%TYPE,
162: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
163: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
164: x_price OUT NOCOPY PO_LINES.unit_price%TYPE
165: );
166:
167: PROCEDURE get_po_break_price (
168: p_po_line_id IN PO_LINES.po_line_id%TYPE,
164: x_price OUT NOCOPY PO_LINES.unit_price%TYPE
165: );
166:
167: PROCEDURE get_po_break_price (
168: p_po_line_id IN PO_LINES.po_line_id%TYPE,
169: p_quantity IN PO_LINES.quantity%TYPE,
170: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
171: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
172: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
165: );
166:
167: PROCEDURE get_po_break_price (
168: p_po_line_id IN PO_LINES.po_line_id%TYPE,
169: p_quantity IN PO_LINES.quantity%TYPE,
170: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
171: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
172: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
173: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
169: p_quantity IN PO_LINES.quantity%TYPE,
170: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
171: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
172: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
173: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
174: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
175: --
176: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
177: );
170: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
171: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
172: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
173: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
174: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
175: --
176: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
177: );
178:
172: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
173: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
174: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
175: --
176: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
177: );
178:
179: FUNCTION get_min_shipment_id (
180: p_po_line_id IN PO_LINES.po_line_id%TYPE
176: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
177: );
178:
179: FUNCTION get_min_shipment_id (
180: p_po_line_id IN PO_LINES.po_line_id%TYPE
181: ) RETURN NUMBER;
182:
183: PROCEDURE derive_qty_amt_rollups (
184: p_chg IN OUT NOCOPY PO_CHANGES_REC_TYPE
247: -- resp_appl_id => 201 );
248: --Modifies:
249: -- If all validations are successful, the requested and derived changes
250: -- will be applied to the database tables (ex. PO_HEADERS_ALL,
251: -- PO_LINES_ALL, etc.).
252: -- p_changes will be updated with all of the derived changes, including
253: -- the new LINE_LOCATION_ID and PO_DISTRIBUTION_ID for each split shipment
254: -- and split distribution.
255: --Locks:
783: BEGIN
784: IF (g_po_header_id IS NOT NULL) THEN -- PO / PA
785: SELECT 1
786: INTO l_dummy
787: FROM po_lines
788: WHERE po_header_id = g_po_header_id
789: AND po_line_id = p_chg.line_changes.po_line_id(i);
790: END IF;
791: EXCEPTION
793: add_error (
794: p_api_errors => g_api_errors,
795: x_return_status => x_return_status,
796: p_message_name => 'PO_INVALID_DOC_IDS',
797: p_table_name => 'PO_LINES_ALL',
798: p_column_name => 'PO_LINE_ID',
799: p_entity_type => G_ENTITY_TYPE_LINES,
800: p_entity_id => i
801: );
1179: p_chg.line_changes.c_item_number(i),
1180: --
1181: p_chg.line_changes.t_manual_price_change_flag(i),
1182: p_chg.line_changes.c_preferred_grade(i) --INVCONV
1183: FROM po_lines POL,
1184: po_line_types PLT,
1185: po_headers_all POHA,
1186: mtl_system_items_b MSI,
1187: financials_system_parameters FSP
1336: p_chg.shipment_changes.t_manual_price_change_flag(i),
1337: p_chg.shipment_changes.c_preferred_grade(i) -- INVCONV sschinch 09/07/04
1338: --p_chg.shipment_changes.c_payment_type(i)
1339: FROM po_line_locations PLL,
1340: po_lines POL,
1341: po_line_types PLT,
1342: mtl_system_items_b MSI,
1343: financials_system_parameters FSP
1344: WHERE PLL.line_location_id =
1462: p_chg.distribution_changes.c_distribution_num(i),
1463: p_chg.distribution_changes.c_encumbered_flag(i),
1464: p_chg.distribution_changes.c_req_distribution_id(i),
1465: p_chg.distribution_changes.c_creation_date(i)
1466: FROM po_distributions POD, po_lines POL, po_line_types PLT,po_line_locations_all PLL --
1468: NVL( p_chg.distribution_changes.parent_distribution_id(i), -- split dist
1469: p_chg.distribution_changes.po_distribution_id(i) ) -- existing dist
1470: AND POL.po_line_id = POD.po_line_id -- JOIN
1507: ) IS
1508: l_proc_name CONSTANT VARCHAR2(20) := 'PREPROCESS_CHANGES';
1509: l_progress VARCHAR2(3) := '000';
1510:
1511: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1512: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1513: l_new_qty PO_LINES.quantity%TYPE;
1514: l_converted_qty PO_LINES.quantity%TYPE;
1515: l_item_id PO_LINES.item_id%TYPE;
1508: l_proc_name CONSTANT VARCHAR2(20) := 'PREPROCESS_CHANGES';
1509: l_progress VARCHAR2(3) := '000';
1510:
1511: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1512: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1513: l_new_qty PO_LINES.quantity%TYPE;
1514: l_converted_qty PO_LINES.quantity%TYPE;
1515: l_item_id PO_LINES.item_id%TYPE;
1516: BEGIN
1509: l_progress VARCHAR2(3) := '000';
1510:
1511: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1512: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1513: l_new_qty PO_LINES.quantity%TYPE;
1514: l_converted_qty PO_LINES.quantity%TYPE;
1515: l_item_id PO_LINES.item_id%TYPE;
1516: BEGIN
1517: IF (g_fnd_debug = 'Y') THEN
1510:
1511: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1512: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1513: l_new_qty PO_LINES.quantity%TYPE;
1514: l_converted_qty PO_LINES.quantity%TYPE;
1515: l_item_id PO_LINES.item_id%TYPE;
1516: BEGIN
1517: IF (g_fnd_debug = 'Y') THEN
1518: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1511: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1512: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1513: l_new_qty PO_LINES.quantity%TYPE;
1514: l_converted_qty PO_LINES.quantity%TYPE;
1515: l_item_id PO_LINES.item_id%TYPE;
1516: BEGIN
1517: IF (g_fnd_debug = 'Y') THEN
1518: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1519: FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
1564: WHEN OTHERS THEN
1565: add_error ( p_api_errors => g_api_errors,
1566: x_return_status => x_return_status,
1567: p_message_name => 'PO_CHNG_INVALID_UOM_CONVERSION',
1568: p_table_name => 'PO_LINES_ALL',
1569: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1570: p_entity_type => G_ENTITY_TYPE_LINES,
1571: p_entity_id => i );
1572: RETURN;
1611: WHEN OTHERS THEN
1612: add_error ( p_api_errors => g_api_errors,
1613: x_return_status => x_return_status,
1614: p_message_name => 'PO_CHNG_INVALID_UOM_CONVERSION',
1615: p_table_name => 'PO_LINES_ALL',
1616: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1617: p_entity_type => G_ENTITY_TYPE_SHIPMENTS,
1618: p_entity_id => i );
1619: RETURN;
1658: WHEN OTHERS THEN
1659: add_error ( p_api_errors => g_api_errors,
1660: x_return_status => x_return_status,
1661: p_message_name => 'PO_CHNG_INVALID_UOM_CONVERSION',
1662: p_table_name => 'PO_LINES_ALL',
1663: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1664: p_entity_type => G_ENTITY_TYPE_DISTRIBUTIONS,
1665: p_entity_id => i );
1666: RETURN;
1783: IF (l_shipment_count > 1) THEN -- The line has multiple shipments
1784: add_error ( p_api_errors => g_api_errors,
1785: x_return_status => x_return_status,
1786: p_message_name => 'PO_CHNG_QTY_AMT_MULTI_SHIP',
1787: p_table_name => 'PO_LINES_ALL',
1788: p_column_name => null,
1789: p_entity_type => G_ENTITY_TYPE_LINES,
1790: p_entity_id => i );
1791: END IF; -- l_num_shipments
1803:
1804: add_error ( p_api_errors => g_api_errors,
1805: x_return_status => x_return_status,
1806: p_message_name => 'PO_CHNG_QTY2_NO_UOM2',
1807: p_table_name => 'PO_LINES_ALL',
1808: p_column_name => 'SECONDARY_QUANTITY',
1809: p_entity_type => G_ENTITY_TYPE_LINES,
1810: p_entity_id => i );
1811: END IF;
1822:
1823: add_error ( p_api_errors => g_api_errors,
1824: x_return_status => x_return_status,
1825: p_message_name => 'PO_CHNG_QTY2_NO_QTY',
1826: p_table_name => 'PO_LINES_ALL',
1827: p_column_name => 'SECONDARY_QUANTITY',
1828: p_entity_type => G_ENTITY_TYPE_LINES,
1829: p_entity_id => i );
1830: END IF;
1850: IF l_grade_control_flag = 'N' THEN
1851: add_error ( p_api_errors => g_api_errors,
1852: x_return_status => x_return_status,
1853: p_message_name => 'INV_ITEM_NOT_GRADE_CTRL_EXP',
1854: p_table_name => 'PO_LINES_ALL',
1855: p_column_name => 'PREFERRED_GRADE',
1856: p_entity_type => G_ENTITY_TYPE_LINES,
1857: p_entity_id => i);
1858: END IF;
1877: IF (l_dual_uom_ind = 'P') THEN
1878: add_error ( p_api_errors => g_api_errors,
1879: x_return_status => x_return_status,
1880: p_message_name => 'PO_CHNG_NON_DUAL_ITEM',
1881: p_table_name => 'PO_LINES_ALL',
1882: p_column_name => 'SECONDARY_QUANTITY',
1883: p_entity_type => G_ENTITY_TYPE_LINES,
1884: p_entity_id => i );
1885:
1917: IF (l_dual_uom_ind = 'P') THEN
1918: add_error ( p_api_errors => g_api_errors,
1919: x_return_status => x_return_status,
1920: p_message_name => 'PO_CHNG_NON_DUAL_ITEM',
1921: p_table_name => 'PO_LINES_ALL',
1922: p_column_name => 'SECONDARY_QUANTITY',
1923: p_entity_type => G_ENTITY_TYPE_LINES,
1924: p_entity_id => i );
1925:
2233: OR (p_chg.line_changes.amount(i) IS NOT NULL) THEN
2234: add_error ( p_api_errors => g_api_errors,
2235: x_return_status => x_return_status,
2236: p_message_name => 'PO_CHNG_BLKT_LINE_BAD_FIELD',
2237: p_table_name => 'PO_LINES_ALL',
2238: p_column_name => 'QUANTITY',
2239: p_token_name1 => 'COLUMN_NAME',
2240: p_token_value1 => 'QUANTITY',
2241: p_entity_type => G_ENTITY_TYPE_LINES,
2309: IF (g_document_type = 'RELEASE') AND (p_chg.line_changes.get_count > 0) THEN
2310: add_error ( p_api_errors => g_api_errors,
2311: x_return_status => x_return_status,
2312: p_message_name => 'PO_CHNG_RELEASE_NO_LINES',
2313: p_table_name => 'PO_LINES_ALL',
2314: p_column_name => null,
2315: p_entity_type => G_ENTITY_TYPE_LINES );
2316: END IF;
2317:
2352:
2353: IF (p_chg.line_changes.unit_price(i) IS NOT NULL ) THEN
2354: add_error( p_api_errors => g_api_errors,x_return_status => x_return_status,
2355: p_message_name => 'PO_NO_PRICE_CHNG_FP_LINES',
2356: p_table_name => 'PO_LINES_ALL',p_column_name => 'PRICE',
2357: p_entity_type => g_entity_type_lines,p_entity_id => i);
2358: END IF;
2359: ELSIF (p_chg.line_changes.c_value_basis(i) = 'AMOUNT') THEN
2360: ------------------------------------------------------------------------
2363:
2364: IF (p_chg.line_changes.unit_price(i) IS NOT NULL ) THEN
2365: add_error( p_api_errors => g_api_errors,x_return_status => x_return_status,
2366: p_message_name => 'PO_NO_PRICE_CHNG_AMT_LINES',
2367: p_table_name => 'PO_LINES_ALL',p_column_name => 'PRICE',
2368: p_entity_type => g_entity_type_lines,p_entity_id => i);
2369: END IF;
2370: END IF;
2371: --< BUG 5406211 END >
2381: IF (p_chg.line_changes.quantity(i) IS NOT NULL) THEN
2382: add_error ( p_api_errors => g_api_errors,
2383: x_return_status => x_return_status,
2384: p_message_name => 'PO_SVC_NO_QTY',
2385: p_table_name => 'PO_LINES_ALL',
2386: p_column_name => 'QUANTITY',
2387: p_entity_type => G_ENTITY_TYPE_LINES,
2388: p_entity_id => i );
2389: END IF;
2397: IF (p_chg.line_changes.amount(i) IS NOT NULL) THEN
2398: add_error ( p_api_errors => g_api_errors,
2399: x_return_status => x_return_status,
2400: p_message_name => 'PO_SVC_NO_AMT',
2401: p_table_name => 'PO_LINES_ALL',
2402: p_column_name => 'AMOUNT',
2403: p_entity_type => G_ENTITY_TYPE_LINES,
2404: p_entity_id => i );
2405: END IF;
2418: IF (p_chg.line_changes.start_date(i) IS NOT NULL) THEN
2419: add_error ( p_api_errors => g_api_errors,
2420: x_return_status => x_return_status,
2421: p_message_name => 'PO_SVC_NO_START_END_DATE',
2422: p_table_name => 'PO_LINES_ALL',
2423: p_column_name => 'START_DATE',
2424: p_entity_type => G_ENTITY_TYPE_LINES,
2425: p_entity_id => i );
2426: END IF;
2428: IF (p_chg.line_changes.expiration_date(i) IS NOT NULL) THEN
2429: add_error ( p_api_errors => g_api_errors,
2430: x_return_status => x_return_status,
2431: p_message_name => 'PO_SVC_NO_START_END_DATE',
2432: p_table_name => 'PO_LINES_ALL',
2433: p_column_name => 'EXPIRATION_DATE',
2434: p_entity_type => G_ENTITY_TYPE_LINES,
2435: p_entity_id => i );
2436: END IF;
2648: l_new_quantity := p_chg.line_changes.quantity(i);
2649: l_new_price := p_chg.line_changes.unit_price(i);
2650: l_new_amount := p_chg.line_changes.amount(i);
2651:
2652: l_table_name := 'PO_LINES_ALL';
2653: l_qty_column_name := 'QUANTITY';
2654: l_price_column_name := 'UNIT_PRICE';
2655: l_amt_column_name := 'AMOUNT';
2656: ELSIF (p_entity_type = G_ENTITY_TYPE_SHIPMENTS) THEN
2757: FUNCTION is_split_shipment_num_unique (
2758: p_chg IN PO_CHANGES_REC_TYPE,
2759: i IN NUMBER
2760: ) RETURN BOOLEAN IS
2761: l_po_line_id PO_LINES.po_line_id%TYPE;
2762: l_split_shipment_num PO_LINE_LOCATIONS.shipment_num%TYPE;
2763: l_ship_chg_i NUMBER;
2764: BEGIN
2765: -- Get the PO_LINE_ID and split shipment num for the i-th shipment change.
3009: -- SQL Why: To retrieve the lines on a standard/planned PO that may
3010: -- need to be re-priced from the price breaks.
3011: CURSOR po_line_ref_csr (p_po_header_id PO_HEADERS.po_header_id%TYPE) IS
3012: SELECT po_line_id, manual_price_change_flag
3013: FROM po_lines POL
3014: WHERE POL.po_header_id = p_po_header_id
3015: AND (POL.from_header_id IS NOT NULL OR
3016: --
3017: POL.contract_id IS NOT NULL);
3019: -- SQL What: Retrieves all the standard/planned shipments of the
3020: -- given PO line that are not cancelled or finally closed.
3021: -- SQL Why: To roll down a line price change on a standard/planned PO
3022: -- to the shipments of that line.
3023: CURSOR po_shipment_csr (p_po_line_id PO_LINES.po_line_id%TYPE) IS
3024: SELECT line_location_id
3025: FROM po_line_locations
3026: WHERE po_line_id = p_po_line_id
3027: AND (NVL(cancel_flag,'N') <> 'Y')
3027: AND (NVL(cancel_flag,'N') <> 'Y')
3028: AND (NVL(closed_code,'OPEN') <> 'FINALLY CLOSED')
3029: AND shipment_type IN ('PLANNED','STANDARD');
3030:
3031: l_po_line_id PO_LINES.po_line_id%TYPE;
3032: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
3033: l_new_price PO_LINE_LOCATIONS.price_override%TYPE;
3034: l_new_qty PO_LINE_LOCATIONS.quantity%TYPE;
3035: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3033: l_new_price PO_LINE_LOCATIONS.price_override%TYPE;
3034: l_new_qty PO_LINE_LOCATIONS.quantity%TYPE;
3035: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3036: l_new_ship_to_loc_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
3037: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3038: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3039: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3040: --
3041: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3034: l_new_qty PO_LINE_LOCATIONS.quantity%TYPE;
3035: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3036: l_new_ship_to_loc_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
3037: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3038: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3039: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3040: --
3041: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3042:
3035: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3036: l_new_ship_to_loc_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
3037: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3038: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3039: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3040: --
3041: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3042:
3043: l_ship_chg_i NUMBER;
3037: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3038: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3039: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3040: --
3041: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3042:
3043: l_ship_chg_i NUMBER;
3044: l_line_chg_i NUMBER;
3045:
3045:
3046: l_price_updateable VARCHAR2(1); -- Bug 3337426
3047: l_retro_price_change VARCHAR2(1); -- Bug 3337426
3048: --
3049: l_manual_price_change PO_LINES.manual_price_change_flag%TYPE;
3050: l_current_price PO_LINE_LOCATIONS.price_override%TYPE;
3051: --
3052:
3053:
3059: l_fsp_org_id NUMBER;
3060: x_count NUMBER;
3061: x_data VARCHAR2(100);
3062: l_derived_sec_qty PO_LINE_LOCATIONS.quantity%TYPE;
3063: l_document_uom PO_LINES.secondary_unit_of_measure%TYPE;
3064: l_item_id MTL_SYSTEM_ITEMS_B.inventory_item_id%TYPE;
3065: l_document_sec_uom PO_LINES.secondary_unit_of_measure%TYPE;
3066: l_new_secondary_qty PO_LINE_LOCATIONS.quantity%TYPE;
3067:
3061: x_data VARCHAR2(100);
3062: l_derived_sec_qty PO_LINE_LOCATIONS.quantity%TYPE;
3063: l_document_uom PO_LINES.secondary_unit_of_measure%TYPE;
3064: l_item_id MTL_SYSTEM_ITEMS_B.inventory_item_id%TYPE;
3065: l_document_sec_uom PO_LINES.secondary_unit_of_measure%TYPE;
3066: l_new_secondary_qty PO_LINE_LOCATIONS.quantity%TYPE;
3067:
3068: l_new_amt NUMBER;
3069: l_new_promised_date DATE;
3071: l_new_preferred_grade mtl_grades.grade_code%TYPE;
3072: l_new_preferred_qty PO_LINE_LOCATIONS.quantity%TYPE;
3073: l_shipment_changes po_shipments_rec_type;
3074:
3075: l_new_sec_uom PO_LINES.secondary_unit_of_measure%TYPE;
3076: l_count NUMBER := 0;
3077: /* END INVCONV */
3078:
3079: BEGIN
3352: END IF; -- new quantity, need-by date, or ship-to location
3353:
3354: END IF; -- l_new_price
3355:
3356: END LOOP; -- PO lines with a GA or quotation reference
3357: CLOSE po_line_ref_csr;
3358: END IF; -- document type is standard PO or planned PO
3359:
3360: l_progress := '040';
3873: PROCEDURE derive_secondary_quantity (
3874: p_chg IN PO_CHANGES_REC_TYPE,
3875: p_entity_type IN VARCHAR2,
3876: p_entity_id IN NUMBER,
3877: x_derived_quantity2 OUT NOCOPY PO_LINES.secondary_quantity%TYPE,
3878: x_return_status OUT NOCOPY VARCHAR2
3879: ) IS
3880: l_proc_name VARCHAR2(30) := 'derive_secondary_quantity';
3881: l_progress VARCHAR2(3) := '000';
3880: l_proc_name VARCHAR2(30) := 'derive_secondary_quantity';
3881: l_progress VARCHAR2(3) := '000';
3882:
3883: i NUMBER := p_entity_id;
3884: l_new_quantity PO_LINES.quantity%TYPE;
3885: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3881: l_progress VARCHAR2(3) := '000';
3882:
3883: i NUMBER := p_entity_id;
3884: l_new_quantity PO_LINES.quantity%TYPE;
3885: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3882:
3883: i NUMBER := p_entity_id;
3884: l_new_quantity PO_LINES.quantity%TYPE;
3885: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3890: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3883: i NUMBER := p_entity_id;
3884: l_new_quantity PO_LINES.quantity%TYPE;
3885: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3890: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3891: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3884: l_new_quantity PO_LINES.quantity%TYPE;
3885: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3890: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3891: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3892: l_qty2 PO_LINES.secondary_quantity%TYPE;
3885: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3890: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3891: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3892: l_qty2 PO_LINES.secondary_quantity%TYPE;
3893: l_opm_validate_ind VARCHAR2(1);
3886: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3887: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3890: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3891: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3892: l_qty2 PO_LINES.secondary_quantity%TYPE;
3893: l_opm_validate_ind VARCHAR2(1);
3894:
3888: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3889: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3890: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3891: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3892: l_qty2 PO_LINES.secondary_quantity%TYPE;
3893: l_opm_validate_ind VARCHAR2(1);
3894:
3895: l_last_msg_list_index NUMBER;
3896: l_return_status VARCHAR2(1);
4040: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4041: p_quantity IN PO_LINE_LOCATIONS.quantity%TYPE,
4042: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4043: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4044: x_price OUT NOCOPY PO_LINES.unit_price%TYPE
4045: ) IS
4046: l_quantity PO_LINE_LOCATIONS.quantity%TYPE;
4047: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4048: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4047: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4048: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4049: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
4050: l_po_line_id PO_LINE_LOCATIONS.po_line_id%TYPE;
4051: l_price_break_type PO_LINES.price_break_lookup_code %TYPE;
4052: l_cumulative_flag BOOLEAN;
4053: l_price_break_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4054: l_return_status VARCHAR2(1);
4055:
4068: l_need_by_date,
4069: l_ship_to_org_id,
4070: l_po_line_id,
4071: l_price_break_type
4072: FROM po_line_locations PLL, po_lines POL
4073: WHERE PLL.line_location_id = p_line_location_id
4074: AND PLL.po_line_id = POL.po_line_id; -- JOIN
4075:
4076: -- True if price break type is CUMULATIVE, false otherwise:
4119: -- None.
4120: --End of Comments
4121: -------------------------------------------------------------------------------
4122: PROCEDURE get_po_break_price (
4123: p_po_line_id IN PO_LINES.po_line_id%TYPE,
4124: p_quantity IN PO_LINES.quantity%TYPE,
4125: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4126: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4127: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4120: --End of Comments
4121: -------------------------------------------------------------------------------
4122: PROCEDURE get_po_break_price (
4123: p_po_line_id IN PO_LINES.po_line_id%TYPE,
4124: p_quantity IN PO_LINES.quantity%TYPE,
4125: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4126: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4127: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4128: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4124: p_quantity IN PO_LINES.quantity%TYPE,
4125: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4126: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4127: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4128: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4129: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4130: --
4131: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4132: ) IS
4125: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4126: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4127: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4128: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4129: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4130: --
4131: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4132: ) IS
4133: l_quantity PO_LINES.quantity%TYPE;
4127: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4128: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4129: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4130: --
4131: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4132: ) IS
4133: l_quantity PO_LINES.quantity%TYPE;
4134: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4135: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4129: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4130: --
4131: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4132: ) IS
4133: l_quantity PO_LINES.quantity%TYPE;
4134: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4135: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4136: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
4137: l_from_line_id PO_LINES.from_line_id%TYPE;
4133: l_quantity PO_LINES.quantity%TYPE;
4134: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4135: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4136: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
4137: l_from_line_id PO_LINES.from_line_id%TYPE;
4138: l_return_status VARCHAR2(1);
4139:
4140: --
4141: l_org_id po_lines.org_id%TYPE;
4137: l_from_line_id PO_LINES.from_line_id%TYPE;
4138: l_return_status VARCHAR2(1);
4139:
4140: --
4141: l_org_id po_lines.org_id%TYPE;
4142: l_contract_id po_lines.contract_id%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4138: l_return_status VARCHAR2(1);
4139:
4140: --
4141: l_org_id po_lines.org_id%TYPE;
4142: l_contract_id po_lines.contract_id%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4139:
4140: --
4141: l_org_id po_lines.org_id%TYPE;
4142: l_contract_id po_lines.contract_id%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4140: --
4141: l_org_id po_lines.org_id%TYPE;
4142: l_contract_id po_lines.contract_id%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4141: l_org_id po_lines.org_id%TYPE;
4142: l_contract_id po_lines.contract_id%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4142: l_contract_id po_lines.contract_id%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4143: l_order_header_id po_lines.po_header_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4151: l_vendor_id po_headers.vendor_id%TYPE;
4144: l_order_line_id po_lines.po_line_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4151: l_vendor_id po_headers.vendor_id%TYPE;
4152: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4145: l_creation_date po_lines.creation_date%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4151: l_vendor_id po_headers.vendor_id%TYPE;
4152: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4153: l_uom po_lines.unit_meas_lookup_code%TYPE;
4146: l_item_id po_lines.item_id%TYPE;
4147: l_item_revision po_lines.item_revision%TYPE;
4148: l_category_id po_lines.category_id%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4151: l_vendor_id po_headers.vendor_id%TYPE;
4152: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4153: l_uom po_lines.unit_meas_lookup_code%TYPE;
4154: l_in_unit_price po_lines.unit_price%TYPE;
4149: l_line_type_id po_lines.line_type_id%TYPE;
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4151: l_vendor_id po_headers.vendor_id%TYPE;
4152: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4153: l_uom po_lines.unit_meas_lookup_code%TYPE;
4154: l_in_unit_price po_lines.unit_price%TYPE;
4155: l_base_unit_price po_lines.base_unit_price%TYPE;
4156: l_currency_code po_headers.currency_code%TYPE;
4157: --
4150: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4151: l_vendor_id po_headers.vendor_id%TYPE;
4152: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4153: l_uom po_lines.unit_meas_lookup_code%TYPE;
4154: l_in_unit_price po_lines.unit_price%TYPE;
4155: l_base_unit_price po_lines.base_unit_price%TYPE;
4156: l_currency_code po_headers.currency_code%TYPE;
4157: --
4158:
4151: l_vendor_id po_headers.vendor_id%TYPE;
4152: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4153: l_uom po_lines.unit_meas_lookup_code%TYPE;
4154: l_in_unit_price po_lines.unit_price%TYPE;
4155: l_base_unit_price po_lines.base_unit_price%TYPE;
4156: l_currency_code po_headers.currency_code%TYPE;
4157: --
4158:
4159: BEGIN
4205: l_uom,
4206: l_in_unit_price,
4207: l_currency_code -- Bug 3564863
4208: --
4209: FROM po_line_locations PLL, po_lines POL,
4210: --
4211: po_headers POH
4212: WHERE PLL.line_location_id = p_line_location_id
4213: AND POL.po_line_id = PLL.po_line_id -- JOIN
4275: -- None.
4276: --End of Comments
4277: -------------------------------------------------------------------------------
4278: FUNCTION get_min_shipment_id (
4279: p_po_line_id IN PO_LINES.po_line_id%TYPE
4280: ) RETURN NUMBER IS
4281: l_min_shipment_num NUMBER;
4282: l_line_location_id NUMBER;
4283: BEGIN
4332:
4333: l_ship_rollup_started INDEXED_TBL_NUMBER;
4334: l_line_rollup_started INDEXED_TBL_NUMBER;
4335:
4336: l_po_line_id PO_LINES.po_line_id%TYPE;
4337: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4338:
4339: l_cur_line_qty_amt PO_LINES.quantity%TYPE;
4340: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4335:
4336: l_po_line_id PO_LINES.po_line_id%TYPE;
4337: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4338:
4339: l_cur_line_qty_amt PO_LINES.quantity%TYPE;
4340: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4341: l_cur_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4342: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4343: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4336: l_po_line_id PO_LINES.po_line_id%TYPE;
4337: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4338:
4339: l_cur_line_qty_amt PO_LINES.quantity%TYPE;
4340: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4341: l_cur_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4342: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4343: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4344: l_exist_dist_qty_amt PO_DISTRIBUTIONS.quantity_ordered%TYPE;
4344: l_exist_dist_qty_amt PO_DISTRIBUTIONS.quantity_ordered%TYPE;
4345: l_new_dist_qty_amt PO_DISTRIBUTIONS.quantity_ordered%TYPE;
4346:
4347: --
4348: l_cur_line_amt PO_LINES.amount%type;
4349: l_exist_ship_amt PO_LINE_LOCATIONS.amount%type;
4350: l_new_ship_amt PO_LINE_LOCATIONS.amount%TYPE;
4351: l_new_line_amt PO_LINES.amount%TYPE;
4352: l_new_line_price PO_LINES.unit_price%TYPE;
4347: --
4348: l_cur_line_amt PO_LINES.amount%type;
4349: l_exist_ship_amt PO_LINE_LOCATIONS.amount%type;
4350: l_new_ship_amt PO_LINE_LOCATIONS.amount%TYPE;
4351: l_new_line_amt PO_LINES.amount%TYPE;
4352: l_new_line_price PO_LINES.unit_price%TYPE;
4353:
4354: l_ship_chg_i NUMBER;
4355: l_line_chg_i NUMBER;
4348: l_cur_line_amt PO_LINES.amount%type;
4349: l_exist_ship_amt PO_LINE_LOCATIONS.amount%type;
4350: l_new_ship_amt PO_LINE_LOCATIONS.amount%TYPE;
4351: l_new_line_amt PO_LINES.amount%TYPE;
4352: l_new_line_price PO_LINES.unit_price%TYPE;
4353:
4354: l_ship_chg_i NUMBER;
4355: l_line_chg_i NUMBER;
4356: l_amt_based BOOLEAN;
4657: -- None.
4658: --End of Comments
4659: -------------------------------------------------------------------------------
4660: FUNCTION round_amount (
4661: p_amount IN PO_LINES.amount%TYPE
4662: ) RETURN NUMBER IS
4663: BEGIN
4664: IF (g_min_accountable_unit IS NOT NULL) THEN -- Round to the MAU.
4665: RETURN round (p_amount / g_min_accountable_unit) * g_min_accountable_unit;
4698: FROM po_distributions
4699: WHERE line_location_id = p_line_location_id
4700: ORDER by distribution_num ASC;
4701:
4702: l_po_line_id PO_LINES.po_line_id%TYPE;
4703: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4704: l_parent_line_loc_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4705: l_po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE;
4706:
4703: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4704: l_parent_line_loc_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4705: l_po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE;
4706:
4707: l_exist_line_qty_amt PO_LINES.quantity%TYPE;
4708: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4709: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4710: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4711: l_cum_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4704: l_parent_line_loc_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4705: l_po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE;
4706:
4707: l_exist_line_qty_amt PO_LINES.quantity%TYPE;
4708: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4709: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4710: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4711: l_cum_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4712: l_split_shipment_num PO_LINE_LOCATIONS.shipment_num%TYPE;
5188: WHERE grade_code = p_grade;
5189: /* sschinch 09/08/04 end INVCONV */
5190:
5191: l_has_ga_ref PO_HEADERS.global_agreement_flag%TYPE;
5192: l_po_line_id PO_LINES.po_line_id%TYPE;
5193: l_new_qty PO_LINES.quantity%TYPE;
5194: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
5195: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5196: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5189: /* sschinch 09/08/04 end INVCONV */
5190:
5191: l_has_ga_ref PO_HEADERS.global_agreement_flag%TYPE;
5192: l_po_line_id PO_LINES.po_line_id%TYPE;
5193: l_new_qty PO_LINES.quantity%TYPE;
5194: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
5195: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5196: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5197: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5194: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
5195: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5196: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5197: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5198: l_new_price PO_LINES.unit_price%TYPE;
5199: l_current_price PO_LINES.unit_price%TYPE;
5200: l_new_start_date PO_LINES.start_date%TYPE;
5201: l_new_end_date PO_LINES.expiration_date%TYPE;
5202: l_new_amount PO_LINES.amount%TYPE;
5195: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5196: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5197: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5198: l_new_price PO_LINES.unit_price%TYPE;
5199: l_current_price PO_LINES.unit_price%TYPE;
5200: l_new_start_date PO_LINES.start_date%TYPE;
5201: l_new_end_date PO_LINES.expiration_date%TYPE;
5202: l_new_amount PO_LINES.amount%TYPE;
5203: l_timecard_amount_sum PO_LINES.amount%TYPE;
5196: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5197: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5198: l_new_price PO_LINES.unit_price%TYPE;
5199: l_current_price PO_LINES.unit_price%TYPE;
5200: l_new_start_date PO_LINES.start_date%TYPE;
5201: l_new_end_date PO_LINES.expiration_date%TYPE;
5202: l_new_amount PO_LINES.amount%TYPE;
5203: l_timecard_amount_sum PO_LINES.amount%TYPE;
5204: l_timecard_exists BOOLEAN;
5197: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5198: l_new_price PO_LINES.unit_price%TYPE;
5199: l_current_price PO_LINES.unit_price%TYPE;
5200: l_new_start_date PO_LINES.start_date%TYPE;
5201: l_new_end_date PO_LINES.expiration_date%TYPE;
5202: l_new_amount PO_LINES.amount%TYPE;
5203: l_timecard_amount_sum PO_LINES.amount%TYPE;
5204: l_timecard_exists BOOLEAN;
5205: l_ship_count NUMBER;
5198: l_new_price PO_LINES.unit_price%TYPE;
5199: l_current_price PO_LINES.unit_price%TYPE;
5200: l_new_start_date PO_LINES.start_date%TYPE;
5201: l_new_end_date PO_LINES.expiration_date%TYPE;
5202: l_new_amount PO_LINES.amount%TYPE;
5203: l_timecard_amount_sum PO_LINES.amount%TYPE;
5204: l_timecard_exists BOOLEAN;
5205: l_ship_count NUMBER;
5206: l_last_msg_list_index NUMBER;
5199: l_current_price PO_LINES.unit_price%TYPE;
5200: l_new_start_date PO_LINES.start_date%TYPE;
5201: l_new_end_date PO_LINES.expiration_date%TYPE;
5202: l_new_amount PO_LINES.amount%TYPE;
5203: l_timecard_amount_sum PO_LINES.amount%TYPE;
5204: l_timecard_exists BOOLEAN;
5205: l_ship_count NUMBER;
5206: l_last_msg_list_index NUMBER;
5207: l_return_status VARCHAR2(1);
5209: l_retroactive_price_change VARCHAR2(1);
5210: l_grade MTL_GRADES.grade_code%TYPE ; -- sschinch INVCONV
5211: l_new_preferred_grade MTL_GRADES.grade_code%TYPE; -- sschinch INVCONV
5212: l_advance_amount PO_LINE_LOCATIONS_ALL.AMOUNT%TYPE; /* << Complex work changes for R12 >> */
5213: l_orig_qty PO_LINES.quantity%TYPE; --
5215: BEGIN
5216: IF (g_fnd_debug = 'Y') THEN
5217: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5269: add_error ( p_api_errors => g_api_errors,
5270: x_return_status => x_return_status,
5271: p_message_name => 'PO_CHNG_AMT_LESS_ADV',
5272: --Line amount must be greater than or equal to the Advance amount.
5273: p_table_name => 'PO_LINES_ALL',
5274: p_entity_type => G_ENTITY_TYPE_LINES,
5275: p_entity_id => i );
5276: end if;
5277: END IF;
5286: OR (p_chg.line_changes.c_closed_code(i) = 'FINALLY CLOSED') THEN
5287: add_error ( p_api_errors => g_api_errors,
5288: x_return_status => x_return_status,
5289: p_message_name => 'PO_CHNG_CANNOT_MODIFY_LINE',
5290: p_table_name => 'PO_LINES_ALL',
5291: p_entity_type => G_ENTITY_TYPE_LINES,
5292: p_entity_id => i );
5293: END IF;
5294:
5318: and l_new_qty < l_orig_qty) THEN --
5319: add_error ( p_api_errors => g_api_errors,
5320: x_return_status => x_return_status,
5321: p_message_name => 'PO_CHNG_QTY_RESTRICTED',
5322: p_table_name => 'PO_LINES_ALL',
5323: p_column_name => 'QUANTITY',
5324: p_entity_type => G_ENTITY_TYPE_LINES,
5325: p_entity_id => i );
5326: END IF;
5338: IF (l_new_qty < greatest(l_qty_received, l_qty_billed)) THEN
5339: add_error ( p_api_errors => g_api_errors,
5340: x_return_status => x_return_status,
5341: p_message_name => 'PO_CHNG_QTY_RESTRICTED',
5342: p_table_name => 'PO_LINES_ALL',
5343: p_column_name => 'QUANTITY',
5344: p_entity_type => G_ENTITY_TYPE_LINES,
5345: p_entity_id => i );
5346: END IF;
5389:
5390: IF (l_retroactive_price_change = G_PARAMETER_YES) THEN
5391: -- Remember that this is a retroactive price change.
5392: g_retroactive_price_change := G_PARAMETER_YES;
5393: PO_LINES_SV2.retroactive_change(l_po_line_id);
5394: END IF;
5395:
5396: END IF; -- document type is standard PO
5397: -- Bug 3312906 END
5407:
5408: add_error ( p_api_errors => g_api_errors,
5409: x_return_status => x_return_status,
5410: p_message_name => 'PO_SVC_END_GE_START',
5411: p_table_name => 'PO_LINES_ALL',
5412: p_entity_type => G_ENTITY_TYPE_LINES,
5413: p_entity_id => i );
5414: END IF;
5415:
5437: IF (l_new_amount < greatest(l_amt_received, l_amt_billed)) THEN
5438: add_error ( p_api_errors => g_api_errors,
5439: x_return_status => x_return_status,
5440: p_message_name => 'PO_CHNG_AMT_RESTRICTED',
5441: p_table_name => 'PO_LINES_ALL',
5442: p_column_name => 'AMOUNT',
5443: p_entity_type => G_ENTITY_TYPE_LINES,
5444: p_entity_id => i );
5445: END IF;
5460: IF (l_new_amount < greatest(l_amt_received, l_amt_billed)) THEN
5461: add_error ( p_api_errors => g_api_errors,
5462: x_return_status => x_return_status,
5463: p_message_name => 'PO_CHNG_AMT_RESTRICTED',
5464: p_table_name => 'PO_LINES_ALL',
5465: p_column_name => 'AMOUNT',
5466: p_entity_type => G_ENTITY_TYPE_LINES,
5467: p_entity_id => i );
5468: END IF;
5505: IF (l_timecard_exists) THEN
5506: add_error ( p_api_errors => g_api_errors,
5507: x_return_status => x_return_status,
5508: p_message_name => 'PO_CHNG_OTL_NO_PRICE_CHANGE',
5509: p_table_name => 'PO_LINES_ALL',
5510: p_column_name => 'UNIT_PRICE',
5511: p_entity_type => G_ENTITY_TYPE_LINES,
5512: p_entity_id => i );
5513: END IF; -- l_timecard_exists
5535: IF (l_new_amount < l_timecard_amount_sum) THEN
5536: add_error ( p_api_errors => g_api_errors,
5537: x_return_status => x_return_status,
5538: p_message_name => 'PO_CHNG_OTL_INVALID_AMOUNT',
5539: p_table_name => 'PO_LINES_ALL',
5540: p_column_name => 'AMOUNT',
5541: -- PBWC Message Change Impact: Adding a token.
5542: p_token_name1 => 'TOTAL_AMT',
5543: p_token_value1 => to_char(l_timecard_amount_sum),
5570: IF (l_timecard_exists) THEN
5571: add_error ( p_api_errors => g_api_errors,
5572: x_return_status => x_return_status,
5573: p_message_name => 'PO_CHNG_OTL_INVALID_END_DATE',
5574: p_table_name => 'PO_LINES_ALL',
5575: p_column_name => 'EXPIRATION_DATE',
5576: p_entity_type => G_ENTITY_TYPE_LINES,
5577: p_entity_id => i );
5578: END IF; -- l_timecard_exists
5604:
5605: add_error ( p_api_errors => g_api_errors,
5606: x_return_status => x_return_status,
5607: p_message_name => 'PO_CHNG_OTL_INVALID_START_DATE',
5608: p_table_name => 'PO_LINES_ALL',
5609: p_column_name => 'START_DATE',
5610: p_entity_type => G_ENTITY_TYPE_LINES,
5611: p_entity_id => i );
5612:
5631: IF (g_approved_date IS NOT NULL) THEN
5632: add_error ( p_api_errors => g_api_errors,
5633: x_return_status => x_return_status,
5634: p_message_name => 'PO_PO_USE_CANCEL_ON_APRVD_PO2',
5635: p_table_name => 'PO_LINES_ALL',
5636: p_column_name => NULL,
5637: p_entity_type => G_ENTITY_TYPE_LINES,
5638: p_entity_id => i);
5639: END IF;
5653: IF (l_ship_count > 0) THEN
5654: add_error ( p_api_errors => g_api_errors,
5655: x_return_status => x_return_status,
5656: p_message_name => 'PO_PO_USE_CANCEL_ON_APRVD_PO2',
5657: p_table_name => 'PO_LINES_ALL',
5658: p_column_name => NULL,
5659: p_entity_type => G_ENTITY_TYPE_LINES,
5660: p_entity_id => i);
5661: END IF;
5672: IF (l_ship_count > 0) THEN
5673: add_error ( p_api_errors => g_api_errors,
5674: x_return_status => x_return_status,
5675: p_message_name => 'PO_PO_USE_CANCEL_ON_ENCUMB_PO',
5676: p_table_name => 'PO_LINES_ALL',
5677: p_column_name => NULL,
5678: p_entity_type => G_ENTITY_TYPE_LINES,
5679: p_entity_id => i);
5680: END IF;
5765: l_approved_date PO_LINE_LOCATIONS.approved_date%TYPE;
5766: l_encumbered_flag PO_LINE_LOCATIONS.encumbered_flag%TYPE;
5767: l_shipment_type PO_LINE_LOCATIONS.shipment_type%TYPE;
5768: l_pending_rcv_transactions NUMBER;
5769: l_allow_price_override PO_LINES.allow_price_override_flag%TYPE;
5770: l_ship_to_loc_valid NUMBER;
5771: l_message_name VARCHAR2(30);
5772: l_new_sales_order_update_date PO_LINE_LOCATIONS.sales_order_update_date%TYPE;
5773:
5919:
5920: IF (l_retroactive_price_change = G_PARAMETER_YES) THEN
5921: -- Remember that this is a retroactive price change.
5922: g_retroactive_price_change := G_PARAMETER_YES;
5923: PO_LINES_SV2.retro_change_shipment(l_line_location_id);
5924: END IF;
5925:
5926: END IF; -- document type is release
5927: -- Bug 3312906 END
6602: -------------------------------------------------------------------------------
6603: --Start of Comments
6604: --Name: unreserve
6605: --Function:
6606: -- Unreserves the necessary PO lines or release shipments.
6607: --Pre-reqs:
6608: -- Encumbrance is on.
6609: --Modifies:
6610: -- The lines/shipments are unreserved.
6721: -- Applies the requested and derived changes to the database tables.
6722: --Pre-reqs:
6723: -- None.
6724: --Modifies:
6725: -- Updates PO_HEADERS_ALL, PO_RELEASES_ALL, PO_LINES_ALL,
6726: -- PO_LINE_LOCATIONS_ALL, PO_DISTRIBUTIONS_ALL with the changes.
6727: --Locks:
6728: -- None.
6729: --End of Comments
6741: l_return_status VARCHAR2(1);
6742: l_new_revision_num PO_HEADERS.revision_num%TYPE;
6743: l_acceptance_required_flag PO_HEADERS.acceptance_required_flag%TYPE := NULL;
6744: l_message VARCHAR2(50);
6745: l_new_from_line_loc_id PO_LINES.from_line_location_id%TYPE;
6746: l_ga_entity_type PO_PRICE_DIFFERENTIALS.entity_type%TYPE;
6747: l_ga_entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE;
6748: l_po_entity_type PO_PRICE_DIFFERENTIALS.entity_type%TYPE;
6749: l_po_entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE;
6796:
6797: -- Apply the line changes to the database.
6798: l_progress := '030';
6799: FORALL i IN 1..p_chg.line_changes.get_count
6800: -- SQL What: Update PO_LINES with the requested/derived line changes.
6801: UPDATE po_lines_all /* Changed po_lines to po_lines_all */
6802: SET last_update_date = sysdate,
6803: last_updated_by = g_user_id,
6804: request_id = decode(g_request_id,null,request_id,-1,request_id,g_request_id), /*bug 7278327, update the request_id with the concerned concurrent_request_id*/
6797: -- Apply the line changes to the database.
6798: l_progress := '030';
6799: FORALL i IN 1..p_chg.line_changes.get_count
6800: -- SQL What: Update PO_LINES with the requested/derived line changes.
6801: UPDATE po_lines_all /* Changed po_lines to po_lines_all */
6802: SET last_update_date = sysdate,
6803: last_updated_by = g_user_id,
6804: request_id = decode(g_request_id,null,request_id,-1,request_id,g_request_id), /*bug 7278327, update the request_id with the concerned concurrent_request_id*/
6805: unit_price = nvl(p_chg.line_changes.unit_price(i), unit_price),
7005: -- Bug 3373453 START
7006: FORALL i IN 1..p_chg.line_changes.get_count
7007: -- SQL What: Update the retroactive date for line price changes on
7008: -- non-cumulative lines.
7009: UPDATE po_lines_all POL
7010: SET last_update_date = sysdate,
7011: last_updated_by = g_user_id,
7012: retroactive_date = sysdate
7013: WHERE POL.po_line_id = p_chg.line_changes.po_line_id(i)
7017:
7018: FORALL i IN 1..p_chg.shipment_changes.get_count
7019: -- SQL What: Update the retroactive date for pricing attribute changes
7020: -- on price breaks of non-cumulative lines.
7021: UPDATE po_lines_all POL
7022: SET last_update_date = sysdate,
7023: last_updated_by = g_user_id,
7024: retroactive_date = sysdate
7025: WHERE POL.po_line_id = p_chg.shipment_changes.c_po_line_id(i)
7038: last_updated_by = g_user_id,
7039: price_discount =
7040: (SELECT (POL.unit_price - p_chg.shipment_changes.price_override(i))
7041: * 100 / POL.unit_price
7042: FROM po_lines POL
7043: WHERE POL.po_line_id = PLL.po_line_id
7044: AND POL.unit_price <> 0)
7045: WHERE PLL.line_location_id
7046: = p_chg.shipment_changes.po_line_location_id(i)
7997: -- G_PARAMETER_YES.
7998: --Pre-reqs:
7999: -- None.
8000: --Modifies:
8001: -- PO_LINES, PO_LINE_LOCATIONS, PO_DISTRIBUTIONS
8002: --Locks:
8003: -- None.
8004: --End of Comments
8005: -------------------------------------------------------------------------------
8055: x_doc_header_id => g_po_header_id,
8056: x_shipment_type => p_chg.shipment_changes.c_shipment_type(i)
8057: );
8058:
8059: UPDATE po_lines SET quantity = quantity - l_quantity WHERE po_line_id = l_po_line_id; --bug # 6329352
8060:
8061: END IF;
8062: END LOOP;
8063:
8079: FOR i IN 1..p_chg.line_changes.get_count LOOP
8080: IF (p_chg.line_changes.delete_record(i) = G_PARAMETER_YES) THEN
8081: SELECT rowid
8082: INTO l_rowid
8083: FROM po_lines
8084: WHERE po_line_id = p_chg.line_changes.po_line_id(i);
8085:
8086: PO_LINES_SV.delete_line (
8087: x_type_lookup_code => g_document_subtype,
8082: INTO l_rowid
8083: FROM po_lines
8084: WHERE po_line_id = p_chg.line_changes.po_line_id(i);
8085:
8086: PO_LINES_SV.delete_line (
8087: x_type_lookup_code => g_document_subtype,
8088: x_po_line_id => p_chg.line_changes.po_line_id(i),
8089: x_row_id => l_rowid
8090: );
8400: p_chg IN PO_CHANGES_REC_TYPE,
8401: i IN NUMBER
8402: ) IS
8403: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
8404: l_po_line_id PO_LINES.po_line_id%TYPE;
8405: c NUMBER;
8406: BEGIN
8407: l_line_location_id := p_chg.shipment_changes.po_line_location_id(i);
8408:
8497: -- None.
8498: --End of Comments
8499: -------------------------------------------------------------------------------
8500: FUNCTION get_line_change (
8501: p_po_line_id IN PO_LINES.po_line_id%TYPE
8502: ) RETURN NUMBER IS
8503: i NUMBER;
8504: BEGIN
8505: IF g_line_changes_index.EXISTS(p_po_line_id) THEN
8528: --End of Comments
8529: -------------------------------------------------------------------------------
8530: FUNCTION find_line_change (
8531: p_chg IN OUT NOCOPY PO_CHANGES_REC_TYPE,
8532: p_po_line_id IN PO_LINES.po_line_id%TYPE
8533: ) RETURN NUMBER IS
8534: i NUMBER;
8535: BEGIN
8536: i := get_line_change(p_po_line_id);
8681: --End of Comments
8682: -------------------------------------------------------------------------------
8683: FUNCTION get_split_ship_change (
8684: p_chg IN PO_CHANGES_REC_TYPE,
8685: p_po_line_id IN PO_LINES.po_line_id%TYPE,
8686: p_parent_line_loc_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
8687: p_split_shipment_num IN PO_LINE_LOCATIONS.shipment_num%TYPE
8688: ) RETURN NUMBER IS
8689: l_ship_chg_i NUMBER;
9114: ELSIF p_entity = PO_CORE_S.g_doc_level_LINE
9115: THEN
9116: d_pos := 80;
9117: -- Validates the delete action on the line
9118: PO_LINES_SV.check_line_deletion_allowed(
9119: x_po_line_id => p_po_line_id
9120: ,x_allow_delete => l_allow_delete
9121: ,p_token => 'DOCUMENT_TYPE'
9122: ,p_token_value => l_style_disp_name
9317:
9318: d_pos := 40;
9319: SELECT ROWID
9320: INTO l_entity_row_id
9321: FROM PO_LINES_ALL
9322: WHERE PO_LINE_ID = p_po_line_id;
9323:
9324: d_pos := 45;
9325: PO_LINES_SV.delete_line( X_type_lookup_code => p_doc_subtype
9321: FROM PO_LINES_ALL
9322: WHERE PO_LINE_ID = p_po_line_id;
9323:
9324: d_pos := 45;
9325: PO_LINES_SV.delete_line( X_type_lookup_code => p_doc_subtype
9326: ,X_po_line_id => p_po_line_id
9327: ,X_row_id => l_entity_row_id
9328: ,p_skip_validation => 'Y'); --skip validations as we already have called validate_delete_action
9329: ELSIF p_entity = PO_CORE_S.g_doc_level_SHIPMENT