[Home] [Help]
147: PROCEDURE derive_secondary_quantity (
148: p_chg IN PO_CHANGES_REC_TYPE,
149: p_entity_type IN VARCHAR2,
150: p_entity_id IN NUMBER,
151: x_derived_quantity2 OUT NOCOPY PO_LINES.secondary_quantity%TYPE,
152: x_return_status OUT NOCOPY VARCHAR2
153: );
154:
155: PROCEDURE get_release_break_price (
156: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
157: p_quantity IN PO_LINE_LOCATIONS.quantity%TYPE,
158: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
159: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
160: x_price OUT NOCOPY PO_LINES.unit_price%TYPE
161: );
162:
163: PROCEDURE get_po_break_price (
164: p_po_line_id IN PO_LINES.po_line_id%TYPE,
160: x_price OUT NOCOPY PO_LINES.unit_price%TYPE
161: );
162:
163: PROCEDURE get_po_break_price (
164: p_po_line_id IN PO_LINES.po_line_id%TYPE,
165: p_quantity IN PO_LINES.quantity%TYPE,
166: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
167: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
168: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
161: );
162:
163: PROCEDURE get_po_break_price (
164: p_po_line_id IN PO_LINES.po_line_id%TYPE,
165: p_quantity IN PO_LINES.quantity%TYPE,
166: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
167: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
168: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
169: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
165: p_quantity IN PO_LINES.quantity%TYPE,
166: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
167: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
168: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
169: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
170: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
171: --
172: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
173: );
166: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
167: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
168: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
169: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
170: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
171: --
172: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
173: );
174:
168: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
169: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
170: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
171: --
172: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
173: );
174:
175: FUNCTION get_min_shipment_id (
176: p_po_line_id IN PO_LINES.po_line_id%TYPE
172: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
173: );
174:
175: FUNCTION get_min_shipment_id (
176: p_po_line_id IN PO_LINES.po_line_id%TYPE
177: ) RETURN NUMBER;
178:
179: PROCEDURE derive_qty_amt_rollups (
180: p_chg IN OUT NOCOPY PO_CHANGES_REC_TYPE
242: -- resp_appl_id => 201 );
243: --Modifies:
244: -- If all validations are successful, the requested and derived changes
245: -- will be applied to the database tables (ex. PO_HEADERS_ALL,
246: -- PO_LINES_ALL, etc.).
247: -- p_changes will be updated with all of the derived changes, including
248: -- the new LINE_LOCATION_ID and PO_DISTRIBUTION_ID for each split shipment
249: -- and split distribution.
250: --Locks:
769: BEGIN
770: IF (g_po_header_id IS NOT NULL) THEN -- PO / PA
771: SELECT 1
772: INTO l_dummy
773: FROM po_lines
774: WHERE po_header_id = g_po_header_id
775: AND po_line_id = p_chg.line_changes.po_line_id(i);
776: END IF;
777: EXCEPTION
779: add_error (
780: p_api_errors => g_api_errors,
781: x_return_status => x_return_status,
782: p_message_name => 'PO_INVALID_DOC_IDS',
783: p_table_name => 'PO_LINES_ALL',
784: p_column_name => 'PO_LINE_ID',
785: p_entity_type => G_ENTITY_TYPE_LINES,
786: p_entity_id => i
787: );
1156: p_chg.line_changes.c_item_number(i),
1157: --
1158: p_chg.line_changes.t_manual_price_change_flag(i),
1159: p_chg.line_changes.c_preferred_grade(i) --INVCONV
1160: FROM po_lines POL,
1161: po_line_types PLT,
1162: po_headers_all POHA,
1163: mtl_system_items_b MSI,
1164: financials_system_parameters FSP
1313: p_chg.shipment_changes.t_manual_price_change_flag(i),
1314: p_chg.shipment_changes.c_preferred_grade(i) -- INVCONV sschinch 09/07/04
1315: --p_chg.shipment_changes.c_payment_type(i)
1316: FROM po_line_locations PLL,
1317: po_lines POL,
1318: po_line_types PLT,
1319: mtl_system_items_b MSI,
1320: financials_system_parameters FSP
1321: WHERE PLL.line_location_id =
1439: p_chg.distribution_changes.c_distribution_num(i),
1440: p_chg.distribution_changes.c_encumbered_flag(i),
1441: p_chg.distribution_changes.c_req_distribution_id(i),
1442: p_chg.distribution_changes.c_creation_date(i)
1443: FROM po_distributions POD, po_lines POL, po_line_types PLT,po_line_locations_all PLL --
1445: NVL( p_chg.distribution_changes.parent_distribution_id(i), -- split dist
1446: p_chg.distribution_changes.po_distribution_id(i) ) -- existing dist
1447: AND POL.po_line_id = POD.po_line_id -- JOIN
1484: ) IS
1485: l_proc_name CONSTANT VARCHAR2(20) := 'PREPROCESS_CHANGES';
1486: l_progress VARCHAR2(3) := '000';
1487:
1488: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1489: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1490: l_new_qty PO_LINES.quantity%TYPE;
1491: l_converted_qty PO_LINES.quantity%TYPE;
1492: l_item_id PO_LINES.item_id%TYPE;
1485: l_proc_name CONSTANT VARCHAR2(20) := 'PREPROCESS_CHANGES';
1486: l_progress VARCHAR2(3) := '000';
1487:
1488: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1489: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1490: l_new_qty PO_LINES.quantity%TYPE;
1491: l_converted_qty PO_LINES.quantity%TYPE;
1492: l_item_id PO_LINES.item_id%TYPE;
1493: BEGIN
1486: l_progress VARCHAR2(3) := '000';
1487:
1488: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1489: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1490: l_new_qty PO_LINES.quantity%TYPE;
1491: l_converted_qty PO_LINES.quantity%TYPE;
1492: l_item_id PO_LINES.item_id%TYPE;
1493: BEGIN
1494: IF (g_fnd_debug = 'Y') THEN
1487:
1488: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1489: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1490: l_new_qty PO_LINES.quantity%TYPE;
1491: l_converted_qty PO_LINES.quantity%TYPE;
1492: l_item_id PO_LINES.item_id%TYPE;
1493: BEGIN
1494: IF (g_fnd_debug = 'Y') THEN
1495: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1488: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
1489: l_document_uom PO_LINES.unit_meas_lookup_code%TYPE;
1490: l_new_qty PO_LINES.quantity%TYPE;
1491: l_converted_qty PO_LINES.quantity%TYPE;
1492: l_item_id PO_LINES.item_id%TYPE;
1493: BEGIN
1494: IF (g_fnd_debug = 'Y') THEN
1495: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1496: FND_LOG.string( log_level => FND_LOG.LEVEL_PROCEDURE,
1541: WHEN OTHERS THEN
1542: add_error ( p_api_errors => g_api_errors,
1543: x_return_status => x_return_status,
1544: p_message_name => 'PO_CHNG_INVALID_UOM_CONVERSION',
1545: p_table_name => 'PO_LINES_ALL',
1546: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1547: p_entity_type => G_ENTITY_TYPE_LINES,
1548: p_entity_id => i );
1549: RETURN;
1588: WHEN OTHERS THEN
1589: add_error ( p_api_errors => g_api_errors,
1590: x_return_status => x_return_status,
1591: p_message_name => 'PO_CHNG_INVALID_UOM_CONVERSION',
1592: p_table_name => 'PO_LINES_ALL',
1593: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1594: p_entity_type => G_ENTITY_TYPE_SHIPMENTS,
1595: p_entity_id => i );
1596: RETURN;
1635: WHEN OTHERS THEN
1636: add_error ( p_api_errors => g_api_errors,
1637: x_return_status => x_return_status,
1638: p_message_name => 'PO_CHNG_INVALID_UOM_CONVERSION',
1639: p_table_name => 'PO_LINES_ALL',
1640: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1641: p_entity_type => G_ENTITY_TYPE_DISTRIBUTIONS,
1642: p_entity_id => i );
1643: RETURN;
1760: IF (l_shipment_count > 1) THEN -- The line has multiple shipments
1761: add_error ( p_api_errors => g_api_errors,
1762: x_return_status => x_return_status,
1763: p_message_name => 'PO_CHNG_QTY_AMT_MULTI_SHIP',
1764: p_table_name => 'PO_LINES_ALL',
1765: p_column_name => null,
1766: p_entity_type => G_ENTITY_TYPE_LINES,
1767: p_entity_id => i );
1768: END IF; -- l_num_shipments
1780:
1781: add_error ( p_api_errors => g_api_errors,
1782: x_return_status => x_return_status,
1783: p_message_name => 'PO_CHNG_QTY2_NO_UOM2',
1784: p_table_name => 'PO_LINES_ALL',
1785: p_column_name => 'SECONDARY_QUANTITY',
1786: p_entity_type => G_ENTITY_TYPE_LINES,
1787: p_entity_id => i );
1788: END IF;
1799:
1800: add_error ( p_api_errors => g_api_errors,
1801: x_return_status => x_return_status,
1802: p_message_name => 'PO_CHNG_QTY2_NO_QTY',
1803: p_table_name => 'PO_LINES_ALL',
1804: p_column_name => 'SECONDARY_QUANTITY',
1805: p_entity_type => G_ENTITY_TYPE_LINES,
1806: p_entity_id => i );
1807: END IF;
1827: IF l_grade_control_flag = 'N' THEN
1828: add_error ( p_api_errors => g_api_errors,
1829: x_return_status => x_return_status,
1830: p_message_name => 'INV_ITEM_NOT_GRADE_CTRL_EXP',
1831: p_table_name => 'PO_LINES_ALL',
1832: p_column_name => 'PREFERRED_GRADE',
1833: p_entity_type => G_ENTITY_TYPE_LINES,
1834: p_entity_id => i);
1835: END IF;
1854: IF (l_dual_uom_ind = 'P') THEN
1855: add_error ( p_api_errors => g_api_errors,
1856: x_return_status => x_return_status,
1857: p_message_name => 'PO_CHNG_NON_DUAL_ITEM',
1858: p_table_name => 'PO_LINES_ALL',
1859: p_column_name => 'SECONDARY_QUANTITY',
1860: p_entity_type => G_ENTITY_TYPE_LINES,
1861: p_entity_id => i );
1862:
1894: IF (l_dual_uom_ind = 'P') THEN
1895: add_error ( p_api_errors => g_api_errors,
1896: x_return_status => x_return_status,
1897: p_message_name => 'PO_CHNG_NON_DUAL_ITEM',
1898: p_table_name => 'PO_LINES_ALL',
1899: p_column_name => 'SECONDARY_QUANTITY',
1900: p_entity_type => G_ENTITY_TYPE_LINES,
1901: p_entity_id => i );
1902:
2210: OR (p_chg.line_changes.amount(i) IS NOT NULL) THEN
2211: add_error ( p_api_errors => g_api_errors,
2212: x_return_status => x_return_status,
2213: p_message_name => 'PO_CHNG_BLKT_LINE_BAD_FIELD',
2214: p_table_name => 'PO_LINES_ALL',
2215: p_column_name => 'QUANTITY',
2216: p_token_name1 => 'COLUMN_NAME',
2217: p_token_value1 => 'QUANTITY',
2218: p_entity_type => G_ENTITY_TYPE_LINES,
2286: IF (g_document_type = 'RELEASE') AND (p_chg.line_changes.get_count > 0) THEN
2287: add_error ( p_api_errors => g_api_errors,
2288: x_return_status => x_return_status,
2289: p_message_name => 'PO_CHNG_RELEASE_NO_LINES',
2290: p_table_name => 'PO_LINES_ALL',
2291: p_column_name => null,
2292: p_entity_type => G_ENTITY_TYPE_LINES );
2293: END IF;
2294:
2329:
2330: IF (p_chg.line_changes.unit_price(i) IS NOT NULL ) THEN
2331: add_error( p_api_errors => g_api_errors,x_return_status => x_return_status,
2332: p_message_name => 'PO_NO_PRICE_CHNG_FP_LINES',
2333: p_table_name => 'PO_LINES_ALL',p_column_name => 'PRICE',
2334: p_entity_type => g_entity_type_lines,p_entity_id => i);
2335: END IF;
2336: ELSIF (p_chg.line_changes.c_value_basis(i) = 'AMOUNT') THEN
2337: ------------------------------------------------------------------------
2340:
2341: IF (p_chg.line_changes.unit_price(i) IS NOT NULL ) THEN
2342: add_error( p_api_errors => g_api_errors,x_return_status => x_return_status,
2343: p_message_name => 'PO_NO_PRICE_CHNG_AMT_LINES',
2344: p_table_name => 'PO_LINES_ALL',p_column_name => 'PRICE',
2345: p_entity_type => g_entity_type_lines,p_entity_id => i);
2346: END IF;
2347: END IF;
2348: --< BUG 5406211 END >
2358: IF (p_chg.line_changes.quantity(i) IS NOT NULL) THEN
2359: add_error ( p_api_errors => g_api_errors,
2360: x_return_status => x_return_status,
2361: p_message_name => 'PO_SVC_NO_QTY',
2362: p_table_name => 'PO_LINES_ALL',
2363: p_column_name => 'QUANTITY',
2364: p_entity_type => G_ENTITY_TYPE_LINES,
2365: p_entity_id => i );
2366: END IF;
2374: IF (p_chg.line_changes.amount(i) IS NOT NULL) THEN
2375: add_error ( p_api_errors => g_api_errors,
2376: x_return_status => x_return_status,
2377: p_message_name => 'PO_SVC_NO_AMT',
2378: p_table_name => 'PO_LINES_ALL',
2379: p_column_name => 'AMOUNT',
2380: p_entity_type => G_ENTITY_TYPE_LINES,
2381: p_entity_id => i );
2382: END IF;
2395: IF (p_chg.line_changes.start_date(i) IS NOT NULL) THEN
2396: add_error ( p_api_errors => g_api_errors,
2397: x_return_status => x_return_status,
2398: p_message_name => 'PO_SVC_NO_START_END_DATE',
2399: p_table_name => 'PO_LINES_ALL',
2400: p_column_name => 'START_DATE',
2401: p_entity_type => G_ENTITY_TYPE_LINES,
2402: p_entity_id => i );
2403: END IF;
2405: IF (p_chg.line_changes.expiration_date(i) IS NOT NULL) THEN
2406: add_error ( p_api_errors => g_api_errors,
2407: x_return_status => x_return_status,
2408: p_message_name => 'PO_SVC_NO_START_END_DATE',
2409: p_table_name => 'PO_LINES_ALL',
2410: p_column_name => 'EXPIRATION_DATE',
2411: p_entity_type => G_ENTITY_TYPE_LINES,
2412: p_entity_id => i );
2413: END IF;
2625: l_new_quantity := p_chg.line_changes.quantity(i);
2626: l_new_price := p_chg.line_changes.unit_price(i);
2627: l_new_amount := p_chg.line_changes.amount(i);
2628:
2629: l_table_name := 'PO_LINES_ALL';
2630: l_qty_column_name := 'QUANTITY';
2631: l_price_column_name := 'UNIT_PRICE';
2632: l_amt_column_name := 'AMOUNT';
2633: ELSIF (p_entity_type = G_ENTITY_TYPE_SHIPMENTS) THEN
2734: FUNCTION is_split_shipment_num_unique (
2735: p_chg IN PO_CHANGES_REC_TYPE,
2736: i IN NUMBER
2737: ) RETURN BOOLEAN IS
2738: l_po_line_id PO_LINES.po_line_id%TYPE;
2739: l_split_shipment_num PO_LINE_LOCATIONS.shipment_num%TYPE;
2740: l_ship_chg_i NUMBER;
2741: BEGIN
2742: -- Get the PO_LINE_ID and split shipment num for the i-th shipment change.
2986: -- SQL Why: To retrieve the lines on a standard/planned PO that may
2987: -- need to be re-priced from the price breaks.
2988: CURSOR po_line_ref_csr (p_po_header_id PO_HEADERS.po_header_id%TYPE) IS
2989: SELECT po_line_id, manual_price_change_flag
2990: FROM po_lines POL
2991: WHERE POL.po_header_id = p_po_header_id
2992: AND (POL.from_header_id IS NOT NULL OR
2993: --
2994: POL.contract_id IS NOT NULL);
2996: -- SQL What: Retrieves all the standard/planned shipments of the
2997: -- given PO line that are not cancelled or finally closed.
2998: -- SQL Why: To roll down a line price change on a standard/planned PO
2999: -- to the shipments of that line.
3000: CURSOR po_shipment_csr (p_po_line_id PO_LINES.po_line_id%TYPE) IS
3001: SELECT line_location_id
3002: FROM po_line_locations
3003: WHERE po_line_id = p_po_line_id
3004: AND (NVL(cancel_flag,'N') <> 'Y')
3004: AND (NVL(cancel_flag,'N') <> 'Y')
3005: AND (NVL(closed_code,'OPEN') <> 'FINALLY CLOSED')
3006: AND shipment_type IN ('PLANNED','STANDARD');
3007:
3008: l_po_line_id PO_LINES.po_line_id%TYPE;
3009: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
3010: l_new_price PO_LINE_LOCATIONS.price_override%TYPE;
3011: l_new_qty PO_LINE_LOCATIONS.quantity%TYPE;
3012: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3010: l_new_price PO_LINE_LOCATIONS.price_override%TYPE;
3011: l_new_qty PO_LINE_LOCATIONS.quantity%TYPE;
3012: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3013: l_new_ship_to_loc_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
3014: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3015: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3016: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3017: --
3018: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3011: l_new_qty PO_LINE_LOCATIONS.quantity%TYPE;
3012: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3013: l_new_ship_to_loc_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
3014: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3015: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3016: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3017: --
3018: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3019:
3012: l_new_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
3013: l_new_ship_to_loc_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
3014: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3015: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3016: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3017: --
3018: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3019:
3020: l_ship_chg_i NUMBER;
3014: l_price_break_id PO_LINES.from_line_location_id%TYPE;
3015: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3016: l_derived_qty2 PO_LINES.secondary_quantity%TYPE;
3017: --
3018: l_base_unit_price PO_LINES.base_unit_price%TYPE;
3019:
3020: l_ship_chg_i NUMBER;
3021: l_line_chg_i NUMBER;
3022:
3022:
3023: l_price_updateable VARCHAR2(1); -- Bug 3337426
3024: l_retro_price_change VARCHAR2(1); -- Bug 3337426
3025: --
3026: l_manual_price_change PO_LINES.manual_price_change_flag%TYPE;
3027: l_current_price PO_LINE_LOCATIONS.price_override%TYPE;
3028: --
3029:
3030:
3036: l_fsp_org_id NUMBER;
3037: x_count NUMBER;
3038: x_data VARCHAR2(100);
3039: l_derived_sec_qty PO_LINE_LOCATIONS.quantity%TYPE;
3040: l_document_uom PO_LINES.secondary_unit_of_measure%TYPE;
3041: l_item_id MTL_SYSTEM_ITEMS_B.inventory_item_id%TYPE;
3042: l_document_sec_uom PO_LINES.secondary_unit_of_measure%TYPE;
3043: l_new_secondary_qty PO_LINE_LOCATIONS.quantity%TYPE;
3044:
3038: x_data VARCHAR2(100);
3039: l_derived_sec_qty PO_LINE_LOCATIONS.quantity%TYPE;
3040: l_document_uom PO_LINES.secondary_unit_of_measure%TYPE;
3041: l_item_id MTL_SYSTEM_ITEMS_B.inventory_item_id%TYPE;
3042: l_document_sec_uom PO_LINES.secondary_unit_of_measure%TYPE;
3043: l_new_secondary_qty PO_LINE_LOCATIONS.quantity%TYPE;
3044:
3045: l_new_amt NUMBER;
3046: l_new_promised_date DATE;
3048: l_new_preferred_grade mtl_grades.grade_code%TYPE;
3049: l_new_preferred_qty PO_LINE_LOCATIONS.quantity%TYPE;
3050: l_shipment_changes po_shipments_rec_type;
3051:
3052: l_new_sec_uom PO_LINES.secondary_unit_of_measure%TYPE;
3053: l_count NUMBER := 0;
3054: /* END INVCONV */
3055:
3056: BEGIN
3329: END IF; -- new quantity, need-by date, or ship-to location
3330:
3331: END IF; -- l_new_price
3332:
3333: END LOOP; -- PO lines with a GA or quotation reference
3334: CLOSE po_line_ref_csr;
3335: END IF; -- document type is standard PO or planned PO
3336:
3337: l_progress := '040';
3836: PROCEDURE derive_secondary_quantity (
3837: p_chg IN PO_CHANGES_REC_TYPE,
3838: p_entity_type IN VARCHAR2,
3839: p_entity_id IN NUMBER,
3840: x_derived_quantity2 OUT NOCOPY PO_LINES.secondary_quantity%TYPE,
3841: x_return_status OUT NOCOPY VARCHAR2
3842: ) IS
3843: l_proc_name VARCHAR2(30) := 'derive_secondary_quantity';
3844: l_progress VARCHAR2(3) := '000';
3843: l_proc_name VARCHAR2(30) := 'derive_secondary_quantity';
3844: l_progress VARCHAR2(3) := '000';
3845:
3846: i NUMBER := p_entity_id;
3847: l_new_quantity PO_LINES.quantity%TYPE;
3848: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3844: l_progress VARCHAR2(3) := '000';
3845:
3846: i NUMBER := p_entity_id;
3847: l_new_quantity PO_LINES.quantity%TYPE;
3848: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3845:
3846: i NUMBER := p_entity_id;
3847: l_new_quantity PO_LINES.quantity%TYPE;
3848: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3853: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3846: i NUMBER := p_entity_id;
3847: l_new_quantity PO_LINES.quantity%TYPE;
3848: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3853: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3854: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3847: l_new_quantity PO_LINES.quantity%TYPE;
3848: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3853: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3854: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3855: l_qty2 PO_LINES.secondary_quantity%TYPE;
3848: l_new_quantity2 PO_LINES.secondary_quantity%TYPE;
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3853: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3854: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3855: l_qty2 PO_LINES.secondary_quantity%TYPE;
3856: l_opm_validate_ind VARCHAR2(1);
3849: l_cur_quantity2 PO_LINES.secondary_quantity%TYPE;
3850: l_request_uom PO_LINES.unit_meas_lookup_code%TYPE;
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3853: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3854: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3855: l_qty2 PO_LINES.secondary_quantity%TYPE;
3856: l_opm_validate_ind VARCHAR2(1);
3857:
3851: l_cur_uom PO_LINES.unit_meas_lookup_code%TYPE;
3852: l_request_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3853: l_cur_uom2 PO_LINES.secondary_unit_of_measure%TYPE;
3854: l_item_number MTL_SYSTEM_ITEMS.segment1%TYPE;
3855: l_qty2 PO_LINES.secondary_quantity%TYPE;
3856: l_opm_validate_ind VARCHAR2(1);
3857:
3858: l_last_msg_list_index NUMBER;
3859: l_return_status VARCHAR2(1);
4003: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4004: p_quantity IN PO_LINE_LOCATIONS.quantity%TYPE,
4005: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4006: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4007: x_price OUT NOCOPY PO_LINES.unit_price%TYPE
4008: ) IS
4009: l_quantity PO_LINE_LOCATIONS.quantity%TYPE;
4010: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4011: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4010: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4011: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4012: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
4013: l_po_line_id PO_LINE_LOCATIONS.po_line_id%TYPE;
4014: l_price_break_type PO_LINES.price_break_lookup_code %TYPE;
4015: l_cumulative_flag BOOLEAN;
4016: l_price_break_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4017: l_return_status VARCHAR2(1);
4018:
4031: l_need_by_date,
4032: l_ship_to_org_id,
4033: l_po_line_id,
4034: l_price_break_type
4035: FROM po_line_locations PLL, po_lines POL
4036: WHERE PLL.line_location_id = p_line_location_id
4037: AND PLL.po_line_id = POL.po_line_id; -- JOIN
4038:
4039: -- True if price break type is CUMULATIVE, false otherwise:
4082: -- None.
4083: --End of Comments
4084: -------------------------------------------------------------------------------
4085: PROCEDURE get_po_break_price (
4086: p_po_line_id IN PO_LINES.po_line_id%TYPE,
4087: p_quantity IN PO_LINES.quantity%TYPE,
4088: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4089: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4090: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4083: --End of Comments
4084: -------------------------------------------------------------------------------
4085: PROCEDURE get_po_break_price (
4086: p_po_line_id IN PO_LINES.po_line_id%TYPE,
4087: p_quantity IN PO_LINES.quantity%TYPE,
4088: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4089: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4090: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4091: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4087: p_quantity IN PO_LINES.quantity%TYPE,
4088: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4089: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4090: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4091: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4092: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4093: --
4094: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4095: ) IS
4088: p_line_location_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
4089: p_ship_to_location_id IN PO_LINE_LOCATIONS.ship_to_location_id%TYPE,
4090: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4091: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4092: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4093: --
4094: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4095: ) IS
4096: l_quantity PO_LINES.quantity%TYPE;
4090: p_need_by_date IN PO_LINE_LOCATIONS.need_by_date%TYPE,
4091: x_price_break_id OUT NOCOPY PO_LINES.from_line_location_id%TYPE,
4092: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4093: --
4094: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4095: ) IS
4096: l_quantity PO_LINES.quantity%TYPE;
4097: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4098: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4092: x_price OUT NOCOPY PO_LINES.unit_price%TYPE,
4093: --
4094: x_base_unit_price OUT NOCOPY PO_LINES.base_unit_price%TYPE
4095: ) IS
4096: l_quantity PO_LINES.quantity%TYPE;
4097: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4098: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4099: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
4100: l_from_line_id PO_LINES.from_line_id%TYPE;
4096: l_quantity PO_LINES.quantity%TYPE;
4097: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
4098: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4099: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
4100: l_from_line_id PO_LINES.from_line_id%TYPE;
4101: l_return_status VARCHAR2(1);
4102:
4103: --
4104: l_org_id po_lines.org_id%TYPE;
4100: l_from_line_id PO_LINES.from_line_id%TYPE;
4101: l_return_status VARCHAR2(1);
4102:
4103: --
4104: l_org_id po_lines.org_id%TYPE;
4105: l_contract_id po_lines.contract_id%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4101: l_return_status VARCHAR2(1);
4102:
4103: --
4104: l_org_id po_lines.org_id%TYPE;
4105: l_contract_id po_lines.contract_id%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4102:
4103: --
4104: l_org_id po_lines.org_id%TYPE;
4105: l_contract_id po_lines.contract_id%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4103: --
4104: l_org_id po_lines.org_id%TYPE;
4105: l_contract_id po_lines.contract_id%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4104: l_org_id po_lines.org_id%TYPE;
4105: l_contract_id po_lines.contract_id%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4105: l_contract_id po_lines.contract_id%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4106: l_order_header_id po_lines.po_header_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4114: l_vendor_id po_headers.vendor_id%TYPE;
4107: l_order_line_id po_lines.po_line_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4114: l_vendor_id po_headers.vendor_id%TYPE;
4115: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4108: l_creation_date po_lines.creation_date%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4114: l_vendor_id po_headers.vendor_id%TYPE;
4115: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4116: l_uom po_lines.unit_meas_lookup_code%TYPE;
4109: l_item_id po_lines.item_id%TYPE;
4110: l_item_revision po_lines.item_revision%TYPE;
4111: l_category_id po_lines.category_id%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4114: l_vendor_id po_headers.vendor_id%TYPE;
4115: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4116: l_uom po_lines.unit_meas_lookup_code%TYPE;
4117: l_in_unit_price po_lines.unit_price%TYPE;
4112: l_line_type_id po_lines.line_type_id%TYPE;
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4114: l_vendor_id po_headers.vendor_id%TYPE;
4115: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4116: l_uom po_lines.unit_meas_lookup_code%TYPE;
4117: l_in_unit_price po_lines.unit_price%TYPE;
4118: l_base_unit_price po_lines.base_unit_price%TYPE;
4119: l_currency_code po_headers.currency_code%TYPE;
4120: --
4113: l_vendor_product_num po_lines.vendor_product_num%TYPE;
4114: l_vendor_id po_headers.vendor_id%TYPE;
4115: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4116: l_uom po_lines.unit_meas_lookup_code%TYPE;
4117: l_in_unit_price po_lines.unit_price%TYPE;
4118: l_base_unit_price po_lines.base_unit_price%TYPE;
4119: l_currency_code po_headers.currency_code%TYPE;
4120: --
4121:
4114: l_vendor_id po_headers.vendor_id%TYPE;
4115: l_vendor_site_id po_headers.vendor_site_id%TYPE;
4116: l_uom po_lines.unit_meas_lookup_code%TYPE;
4117: l_in_unit_price po_lines.unit_price%TYPE;
4118: l_base_unit_price po_lines.base_unit_price%TYPE;
4119: l_currency_code po_headers.currency_code%TYPE;
4120: --
4121:
4122: BEGIN
4168: l_uom,
4169: l_in_unit_price,
4170: l_currency_code -- Bug 3564863
4171: --
4172: FROM po_line_locations PLL, po_lines POL,
4173: --
4174: po_headers POH
4175: WHERE PLL.line_location_id = p_line_location_id
4176: AND POL.po_line_id = PLL.po_line_id -- JOIN
4238: -- None.
4239: --End of Comments
4240: -------------------------------------------------------------------------------
4241: FUNCTION get_min_shipment_id (
4242: p_po_line_id IN PO_LINES.po_line_id%TYPE
4243: ) RETURN NUMBER IS
4244: l_min_shipment_num NUMBER;
4245: l_line_location_id NUMBER;
4246: BEGIN
4295:
4296: l_ship_rollup_started INDEXED_TBL_NUMBER;
4297: l_line_rollup_started INDEXED_TBL_NUMBER;
4298:
4299: l_po_line_id PO_LINES.po_line_id%TYPE;
4300: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4301:
4302: l_cur_line_qty_amt PO_LINES.quantity%TYPE;
4303: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4298:
4299: l_po_line_id PO_LINES.po_line_id%TYPE;
4300: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4301:
4302: l_cur_line_qty_amt PO_LINES.quantity%TYPE;
4303: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4304: l_cur_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4305: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4306: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4299: l_po_line_id PO_LINES.po_line_id%TYPE;
4300: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4301:
4302: l_cur_line_qty_amt PO_LINES.quantity%TYPE;
4303: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4304: l_cur_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4305: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4306: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4307: l_exist_dist_qty_amt PO_DISTRIBUTIONS.quantity_ordered%TYPE;
4307: l_exist_dist_qty_amt PO_DISTRIBUTIONS.quantity_ordered%TYPE;
4308: l_new_dist_qty_amt PO_DISTRIBUTIONS.quantity_ordered%TYPE;
4309:
4310: --
4311: l_cur_line_amt PO_LINES.amount%type;
4312: l_exist_ship_amt PO_LINE_LOCATIONS.amount%type;
4313: l_new_ship_amt PO_LINE_LOCATIONS.amount%TYPE;
4314: l_new_line_amt PO_LINES.amount%TYPE;
4315: l_new_line_price PO_LINES.unit_price%TYPE;
4310: --
4311: l_cur_line_amt PO_LINES.amount%type;
4312: l_exist_ship_amt PO_LINE_LOCATIONS.amount%type;
4313: l_new_ship_amt PO_LINE_LOCATIONS.amount%TYPE;
4314: l_new_line_amt PO_LINES.amount%TYPE;
4315: l_new_line_price PO_LINES.unit_price%TYPE;
4316:
4317: l_ship_chg_i NUMBER;
4318: l_line_chg_i NUMBER;
4311: l_cur_line_amt PO_LINES.amount%type;
4312: l_exist_ship_amt PO_LINE_LOCATIONS.amount%type;
4313: l_new_ship_amt PO_LINE_LOCATIONS.amount%TYPE;
4314: l_new_line_amt PO_LINES.amount%TYPE;
4315: l_new_line_price PO_LINES.unit_price%TYPE;
4316:
4317: l_ship_chg_i NUMBER;
4318: l_line_chg_i NUMBER;
4319: l_amt_based BOOLEAN;
4620: -- None.
4621: --End of Comments
4622: -------------------------------------------------------------------------------
4623: FUNCTION round_amount (
4624: p_amount IN PO_LINES.amount%TYPE
4625: ) RETURN NUMBER IS
4626: BEGIN
4627: IF (g_min_accountable_unit IS NOT NULL) THEN -- Round to the MAU.
4628: RETURN round (p_amount / g_min_accountable_unit) * g_min_accountable_unit;
4661: FROM po_distributions
4662: WHERE line_location_id = p_line_location_id
4663: ORDER by distribution_num ASC;
4664:
4665: l_po_line_id PO_LINES.po_line_id%TYPE;
4666: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4667: l_parent_line_loc_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4668: l_po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE;
4669:
4666: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4667: l_parent_line_loc_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4668: l_po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE;
4669:
4670: l_exist_line_qty_amt PO_LINES.quantity%TYPE;
4671: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4672: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4673: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4674: l_cum_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4667: l_parent_line_loc_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4668: l_po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE;
4669:
4670: l_exist_line_qty_amt PO_LINES.quantity%TYPE;
4671: l_new_line_qty_amt PO_LINES.quantity%TYPE;
4672: l_exist_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4673: l_new_ship_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4674: l_cum_qty_amt PO_LINE_LOCATIONS.quantity%TYPE;
4675: l_split_shipment_num PO_LINE_LOCATIONS.shipment_num%TYPE;
5149: WHERE grade_code = p_grade;
5150: /* sschinch 09/08/04 end INVCONV */
5151:
5152: l_has_ga_ref PO_HEADERS.global_agreement_flag%TYPE;
5153: l_po_line_id PO_LINES.po_line_id%TYPE;
5154: l_new_qty PO_LINES.quantity%TYPE;
5155: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
5156: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5157: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5150: /* sschinch 09/08/04 end INVCONV */
5151:
5152: l_has_ga_ref PO_HEADERS.global_agreement_flag%TYPE;
5153: l_po_line_id PO_LINES.po_line_id%TYPE;
5154: l_new_qty PO_LINES.quantity%TYPE;
5155: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
5156: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5157: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5158: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5155: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
5156: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5157: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5158: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5159: l_new_price PO_LINES.unit_price%TYPE;
5160: l_current_price PO_LINES.unit_price%TYPE;
5161: l_new_start_date PO_LINES.start_date%TYPE;
5162: l_new_end_date PO_LINES.expiration_date%TYPE;
5163: l_new_amount PO_LINES.amount%TYPE;
5156: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
5157: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5158: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5159: l_new_price PO_LINES.unit_price%TYPE;
5160: l_current_price PO_LINES.unit_price%TYPE;
5161: l_new_start_date PO_LINES.start_date%TYPE;
5162: l_new_end_date PO_LINES.expiration_date%TYPE;
5163: l_new_amount PO_LINES.amount%TYPE;
5164: l_timecard_amount_sum PO_LINES.amount%TYPE;
5157: l_amt_received PO_LINE_LOCATIONS.amount_received%TYPE;
5158: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5159: l_new_price PO_LINES.unit_price%TYPE;
5160: l_current_price PO_LINES.unit_price%TYPE;
5161: l_new_start_date PO_LINES.start_date%TYPE;
5162: l_new_end_date PO_LINES.expiration_date%TYPE;
5163: l_new_amount PO_LINES.amount%TYPE;
5164: l_timecard_amount_sum PO_LINES.amount%TYPE;
5165: l_timecard_exists BOOLEAN;
5158: l_amt_billed PO_LINE_LOCATIONS.amount_billed%TYPE;
5159: l_new_price PO_LINES.unit_price%TYPE;
5160: l_current_price PO_LINES.unit_price%TYPE;
5161: l_new_start_date PO_LINES.start_date%TYPE;
5162: l_new_end_date PO_LINES.expiration_date%TYPE;
5163: l_new_amount PO_LINES.amount%TYPE;
5164: l_timecard_amount_sum PO_LINES.amount%TYPE;
5165: l_timecard_exists BOOLEAN;
5166: l_ship_count NUMBER;
5159: l_new_price PO_LINES.unit_price%TYPE;
5160: l_current_price PO_LINES.unit_price%TYPE;
5161: l_new_start_date PO_LINES.start_date%TYPE;
5162: l_new_end_date PO_LINES.expiration_date%TYPE;
5163: l_new_amount PO_LINES.amount%TYPE;
5164: l_timecard_amount_sum PO_LINES.amount%TYPE;
5165: l_timecard_exists BOOLEAN;
5166: l_ship_count NUMBER;
5167: l_last_msg_list_index NUMBER;
5160: l_current_price PO_LINES.unit_price%TYPE;
5161: l_new_start_date PO_LINES.start_date%TYPE;
5162: l_new_end_date PO_LINES.expiration_date%TYPE;
5163: l_new_amount PO_LINES.amount%TYPE;
5164: l_timecard_amount_sum PO_LINES.amount%TYPE;
5165: l_timecard_exists BOOLEAN;
5166: l_ship_count NUMBER;
5167: l_last_msg_list_index NUMBER;
5168: l_return_status VARCHAR2(1);
5227: add_error ( p_api_errors => g_api_errors,
5228: x_return_status => x_return_status,
5229: p_message_name => 'PO_CHNG_AMT_LESS_ADV',
5230: --Line amount must be greater than or equal to the Advance amount.
5231: p_table_name => 'PO_LINES_ALL',
5232: p_entity_type => G_ENTITY_TYPE_LINES,
5233: p_entity_id => i );
5234: end if;
5235: END IF;
5244: OR (p_chg.line_changes.c_closed_code(i) = 'FINALLY CLOSED') THEN
5245: add_error ( p_api_errors => g_api_errors,
5246: x_return_status => x_return_status,
5247: p_message_name => 'PO_CHNG_CANNOT_MODIFY_LINE',
5248: p_table_name => 'PO_LINES_ALL',
5249: p_entity_type => G_ENTITY_TYPE_LINES,
5250: p_entity_id => i );
5251: END IF;
5252:
5273: IF (l_new_qty < greatest(l_qty_received, l_qty_billed)) THEN
5274: add_error ( p_api_errors => g_api_errors,
5275: x_return_status => x_return_status,
5276: p_message_name => 'PO_CHNG_QTY_RESTRICTED',
5277: p_table_name => 'PO_LINES_ALL',
5278: p_column_name => 'QUANTITY',
5279: p_entity_type => G_ENTITY_TYPE_LINES,
5280: p_entity_id => i );
5281: END IF;
5293: IF (l_new_qty < greatest(l_qty_received, l_qty_billed)) THEN
5294: add_error ( p_api_errors => g_api_errors,
5295: x_return_status => x_return_status,
5296: p_message_name => 'PO_CHNG_QTY_RESTRICTED',
5297: p_table_name => 'PO_LINES_ALL',
5298: p_column_name => 'QUANTITY',
5299: p_entity_type => G_ENTITY_TYPE_LINES,
5300: p_entity_id => i );
5301: END IF;
5344:
5345: IF (l_retroactive_price_change = G_PARAMETER_YES) THEN
5346: -- Remember that this is a retroactive price change.
5347: g_retroactive_price_change := G_PARAMETER_YES;
5348: PO_LINES_SV2.retroactive_change(l_po_line_id);
5349: END IF;
5350:
5351: END IF; -- document type is standard PO
5352: -- Bug 3312906 END
5362:
5363: add_error ( p_api_errors => g_api_errors,
5364: x_return_status => x_return_status,
5365: p_message_name => 'PO_SVC_END_GE_START',
5366: p_table_name => 'PO_LINES_ALL',
5367: p_entity_type => G_ENTITY_TYPE_LINES,
5368: p_entity_id => i );
5369: END IF;
5370:
5392: IF (l_new_amount < greatest(l_amt_received, l_amt_billed)) THEN
5393: add_error ( p_api_errors => g_api_errors,
5394: x_return_status => x_return_status,
5395: p_message_name => 'PO_CHNG_AMT_RESTRICTED',
5396: p_table_name => 'PO_LINES_ALL',
5397: p_column_name => 'AMOUNT',
5398: p_entity_type => G_ENTITY_TYPE_LINES,
5399: p_entity_id => i );
5400: END IF;
5415: IF (l_new_amount < greatest(l_amt_received, l_amt_billed)) THEN
5416: add_error ( p_api_errors => g_api_errors,
5417: x_return_status => x_return_status,
5418: p_message_name => 'PO_CHNG_AMT_RESTRICTED',
5419: p_table_name => 'PO_LINES_ALL',
5420: p_column_name => 'AMOUNT',
5421: p_entity_type => G_ENTITY_TYPE_LINES,
5422: p_entity_id => i );
5423: END IF;
5460: IF (l_timecard_exists) THEN
5461: add_error ( p_api_errors => g_api_errors,
5462: x_return_status => x_return_status,
5463: p_message_name => 'PO_CHNG_OTL_NO_PRICE_CHANGE',
5464: p_table_name => 'PO_LINES_ALL',
5465: p_column_name => 'UNIT_PRICE',
5466: p_entity_type => G_ENTITY_TYPE_LINES,
5467: p_entity_id => i );
5468: END IF; -- l_timecard_exists
5490: IF (l_new_amount < l_timecard_amount_sum) THEN
5491: add_error ( p_api_errors => g_api_errors,
5492: x_return_status => x_return_status,
5493: p_message_name => 'PO_CHNG_OTL_INVALID_AMOUNT',
5494: p_table_name => 'PO_LINES_ALL',
5495: p_column_name => 'AMOUNT',
5496: -- PBWC Message Change Impact: Adding a token.
5497: p_token_name1 => 'TOTAL_AMT',
5498: p_token_value1 => to_char(l_timecard_amount_sum),
5525: IF (l_timecard_exists) THEN
5526: add_error ( p_api_errors => g_api_errors,
5527: x_return_status => x_return_status,
5528: p_message_name => 'PO_CHNG_OTL_INVALID_END_DATE',
5529: p_table_name => 'PO_LINES_ALL',
5530: p_column_name => 'EXPIRATION_DATE',
5531: p_entity_type => G_ENTITY_TYPE_LINES,
5532: p_entity_id => i );
5533: END IF; -- l_timecard_exists
5559:
5560: add_error ( p_api_errors => g_api_errors,
5561: x_return_status => x_return_status,
5562: p_message_name => 'PO_CHNG_OTL_INVALID_START_DATE',
5563: p_table_name => 'PO_LINES_ALL',
5564: p_column_name => 'START_DATE',
5565: p_entity_type => G_ENTITY_TYPE_LINES,
5566: p_entity_id => i );
5567:
5586: IF (g_approved_date IS NOT NULL) THEN
5587: add_error ( p_api_errors => g_api_errors,
5588: x_return_status => x_return_status,
5589: p_message_name => 'PO_PO_USE_CANCEL_ON_APRVD_PO2',
5590: p_table_name => 'PO_LINES_ALL',
5591: p_column_name => NULL,
5592: p_entity_type => G_ENTITY_TYPE_LINES,
5593: p_entity_id => i);
5594: END IF;
5608: IF (l_ship_count > 0) THEN
5609: add_error ( p_api_errors => g_api_errors,
5610: x_return_status => x_return_status,
5611: p_message_name => 'PO_PO_USE_CANCEL_ON_APRVD_PO2',
5612: p_table_name => 'PO_LINES_ALL',
5613: p_column_name => NULL,
5614: p_entity_type => G_ENTITY_TYPE_LINES,
5615: p_entity_id => i);
5616: END IF;
5627: IF (l_ship_count > 0) THEN
5628: add_error ( p_api_errors => g_api_errors,
5629: x_return_status => x_return_status,
5630: p_message_name => 'PO_PO_USE_CANCEL_ON_ENCUMB_PO',
5631: p_table_name => 'PO_LINES_ALL',
5632: p_column_name => NULL,
5633: p_entity_type => G_ENTITY_TYPE_LINES,
5634: p_entity_id => i);
5635: END IF;
5720: l_approved_date PO_LINE_LOCATIONS.approved_date%TYPE;
5721: l_encumbered_flag PO_LINE_LOCATIONS.encumbered_flag%TYPE;
5722: l_shipment_type PO_LINE_LOCATIONS.shipment_type%TYPE;
5723: l_pending_rcv_transactions NUMBER;
5724: l_allow_price_override PO_LINES.allow_price_override_flag%TYPE;
5725: l_ship_to_loc_valid NUMBER;
5726: l_message_name VARCHAR2(30);
5727: l_new_sales_order_update_date PO_LINE_LOCATIONS.sales_order_update_date%TYPE;
5728:
5869:
5870: IF (l_retroactive_price_change = G_PARAMETER_YES) THEN
5871: -- Remember that this is a retroactive price change.
5872: g_retroactive_price_change := G_PARAMETER_YES;
5873: PO_LINES_SV2.retro_change_shipment(l_line_location_id);
5874: END IF;
5875:
5876: END IF; -- document type is release
5877: -- Bug 3312906 END
6546: -------------------------------------------------------------------------------
6547: --Start of Comments
6548: --Name: unreserve
6549: --Function:
6550: -- Unreserves the necessary PO lines or release shipments.
6551: --Pre-reqs:
6552: -- Encumbrance is on.
6553: --Modifies:
6554: -- The lines/shipments are unreserved.
6665: -- Applies the requested and derived changes to the database tables.
6666: --Pre-reqs:
6667: -- None.
6668: --Modifies:
6669: -- Updates PO_HEADERS_ALL, PO_RELEASES_ALL, PO_LINES_ALL,
6670: -- PO_LINE_LOCATIONS_ALL, PO_DISTRIBUTIONS_ALL with the changes.
6671: --Locks:
6672: -- None.
6673: --End of Comments
6685: l_return_status VARCHAR2(1);
6686: l_new_revision_num PO_HEADERS.revision_num%TYPE;
6687: l_acceptance_required_flag PO_HEADERS.acceptance_required_flag%TYPE := NULL;
6688: l_message VARCHAR2(50);
6689: l_new_from_line_loc_id PO_LINES.from_line_location_id%TYPE;
6690: l_ga_entity_type PO_PRICE_DIFFERENTIALS.entity_type%TYPE;
6691: l_ga_entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE;
6692: l_po_entity_type PO_PRICE_DIFFERENTIALS.entity_type%TYPE;
6693: l_po_entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE;
6728:
6729: -- Apply the line changes to the database.
6730: l_progress := '030';
6731: FORALL i IN 1..p_chg.line_changes.get_count
6732: -- SQL What: Update PO_LINES with the requested/derived line changes.
6733: UPDATE po_lines
6734: SET last_update_date = sysdate,
6735: last_updated_by = g_user_id,
6736: 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*/
6729: -- Apply the line changes to the database.
6730: l_progress := '030';
6731: FORALL i IN 1..p_chg.line_changes.get_count
6732: -- SQL What: Update PO_LINES with the requested/derived line changes.
6733: UPDATE po_lines
6734: SET last_update_date = sysdate,
6735: last_updated_by = g_user_id,
6736: 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*/
6737: unit_price = nvl(p_chg.line_changes.unit_price(i), unit_price),
6893: -- Bug 3373453 START
6894: FORALL i IN 1..p_chg.line_changes.get_count
6895: -- SQL What: Update the retroactive date for line price changes on
6896: -- non-cumulative lines.
6897: UPDATE po_lines POL
6898: SET last_update_date = sysdate,
6899: last_updated_by = g_user_id,
6900: retroactive_date = sysdate
6901: WHERE POL.po_line_id = p_chg.line_changes.po_line_id(i)
6905:
6906: FORALL i IN 1..p_chg.shipment_changes.get_count
6907: -- SQL What: Update the retroactive date for pricing attribute changes
6908: -- on price breaks of non-cumulative lines.
6909: UPDATE po_lines POL
6910: SET last_update_date = sysdate,
6911: last_updated_by = g_user_id,
6912: retroactive_date = sysdate
6913: WHERE POL.po_line_id = p_chg.shipment_changes.c_po_line_id(i)
6926: last_updated_by = g_user_id,
6927: price_discount =
6928: (SELECT (POL.unit_price - p_chg.shipment_changes.price_override(i))
6929: * 100 / POL.unit_price
6930: FROM po_lines POL
6931: WHERE POL.po_line_id = PLL.po_line_id
6932: AND POL.unit_price <> 0)
6933: WHERE PLL.line_location_id
6934: = p_chg.shipment_changes.po_line_location_id(i)
7885: -- G_PARAMETER_YES.
7886: --Pre-reqs:
7887: -- None.
7888: --Modifies:
7889: -- PO_LINES, PO_LINE_LOCATIONS, PO_DISTRIBUTIONS
7890: --Locks:
7891: -- None.
7892: --End of Comments
7893: -------------------------------------------------------------------------------
7944: FOR i IN 1..p_chg.line_changes.get_count LOOP
7945: IF (p_chg.line_changes.delete_record(i) = G_PARAMETER_YES) THEN
7946: SELECT rowid
7947: INTO l_rowid
7948: FROM po_lines
7949: WHERE po_line_id = p_chg.line_changes.po_line_id(i);
7950:
7951: PO_LINES_SV.delete_line (
7952: x_type_lookup_code => g_document_subtype,
7947: INTO l_rowid
7948: FROM po_lines
7949: WHERE po_line_id = p_chg.line_changes.po_line_id(i);
7950:
7951: PO_LINES_SV.delete_line (
7952: x_type_lookup_code => g_document_subtype,
7953: x_po_line_id => p_chg.line_changes.po_line_id(i),
7954: x_row_id => l_rowid
7955: );
8265: p_chg IN PO_CHANGES_REC_TYPE,
8266: i IN NUMBER
8267: ) IS
8268: l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
8269: l_po_line_id PO_LINES.po_line_id%TYPE;
8270: c NUMBER;
8271: BEGIN
8272: l_line_location_id := p_chg.shipment_changes.po_line_location_id(i);
8273:
8362: -- None.
8363: --End of Comments
8364: -------------------------------------------------------------------------------
8365: FUNCTION get_line_change (
8366: p_po_line_id IN PO_LINES.po_line_id%TYPE
8367: ) RETURN NUMBER IS
8368: i NUMBER;
8369: BEGIN
8370: IF g_line_changes_index.EXISTS(p_po_line_id) THEN
8393: --End of Comments
8394: -------------------------------------------------------------------------------
8395: FUNCTION find_line_change (
8396: p_chg IN OUT NOCOPY PO_CHANGES_REC_TYPE,
8397: p_po_line_id IN PO_LINES.po_line_id%TYPE
8398: ) RETURN NUMBER IS
8399: i NUMBER;
8400: BEGIN
8401: i := get_line_change(p_po_line_id);
8546: --End of Comments
8547: -------------------------------------------------------------------------------
8548: FUNCTION get_split_ship_change (
8549: p_chg IN PO_CHANGES_REC_TYPE,
8550: p_po_line_id IN PO_LINES.po_line_id%TYPE,
8551: p_parent_line_loc_id IN PO_LINE_LOCATIONS.line_location_id%TYPE,
8552: p_split_shipment_num IN PO_LINE_LOCATIONS.shipment_num%TYPE
8553: ) RETURN NUMBER IS
8554: l_ship_chg_i NUMBER;
8979: ELSIF p_entity = PO_CORE_S.g_doc_level_LINE
8980: THEN
8981: d_pos := 80;
8982: -- Validates the delete action on the line
8983: PO_LINES_SV.check_line_deletion_allowed(
8984: x_po_line_id => p_po_line_id
8985: ,x_allow_delete => l_allow_delete
8986: ,p_token => 'DOCUMENT_TYPE'
8987: ,p_token_value => l_style_disp_name
9182:
9183: d_pos := 40;
9184: SELECT ROWID
9185: INTO l_entity_row_id
9186: FROM PO_LINES_ALL
9187: WHERE PO_LINE_ID = p_po_line_id;
9188:
9189: d_pos := 45;
9190: PO_LINES_SV.delete_line( X_type_lookup_code => p_doc_subtype
9186: FROM PO_LINES_ALL
9187: WHERE PO_LINE_ID = p_po_line_id;
9188:
9189: d_pos := 45;
9190: PO_LINES_SV.delete_line( X_type_lookup_code => p_doc_subtype
9191: ,X_po_line_id => p_po_line_id
9192: ,X_row_id => l_entity_row_id
9193: ,p_skip_validation => 'Y'); --skip validations as we already have called validate_delete_action
9194: ELSIF p_entity = PO_CORE_S.g_doc_level_SHIPMENT