[Home] [Help]
1: PACKAGE BODY PO_PRICE_ADJUSTMENTS_PKG AS
2: -- $Header: PO_PRICE_ADJUSTMENTS_PKG.plb 120.0.12010000.4 2009/07/01 01:34:47 ababujan noship $
3:
4: ---------------------------------------------------------------------------
5: -- Modules for debugging.
1: PACKAGE BODY PO_PRICE_ADJUSTMENTS_PKG AS
2: -- $Header: PO_PRICE_ADJUSTMENTS_PKG.plb 120.0.12010000.4 2009/07/01 01:34:47 ababujan noship $
3:
4: ---------------------------------------------------------------------------
5: -- Modules for debugging.
6: ---------------------------------------------------------------------------
6: ---------------------------------------------------------------------------
7:
8: -- The module base for this package.
9: D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
10: PO_LOG.get_package_base('PO_PRICE_ADJUSTMENTS_PKG');
11:
12: -- The module base for the subprogram.
13: D_popl_manual_overridden_adj CONSTANT VARCHAR2(100) :=
14: PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'popl_manual_overridden_adj');
41: -- Global Constants.
42: ---------------------------------------------------------------------------
43:
44: -- Private package constants
45: g_pkg_name CONSTANT varchar2(30) := 'PO_PRICE_ADJUSTMENTS_PKG';
46: g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
47:
48: -- Debugging
49: g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
98: ,p_log_head IN VARCHAR2
99: );
100:
101: PROCEDURE insert_adj_rec
102: (p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE);
103:
104: PROCEDURE insert_draft_adj_rec
105: (p_draft_id IN NUMBER
106: ,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE);
102: (p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE);
103:
104: PROCEDURE insert_draft_adj_rec
105: (p_draft_id IN NUMBER
106: ,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE);
107:
108: --------------------------------------------------------------------------------
109: -- Procedure definitions
110: --------------------------------------------------------------------------------
151: BEGIN
152: --Check if manual modifiers are applied
153: SELECT COUNT(1)
154: INTO l_man_mod_count
155: FROM PO_PRICE_ADJUSTMENTS_V ADJV
156: WHERE ADJV.po_header_id = p_po_header_id
157: AND ADJV.po_line_id = p_po_line_id
158: AND ADJV.automatic_flag = 'N'
159: --AND ADJ.update_allowed = 'N'
161:
162: --Check if manual overridden modifiers are applied
163: SELECT COUNT(1)
164: INTO l_man_ovr_mod_count
165: FROM PO_PRICE_ADJUSTMENTS_V ADJV
166: WHERE ADJV.po_header_id = p_po_header_id
167: AND ADJV.po_line_id = p_po_line_id
168: AND ADJV.automatic_flag = 'N'
169: AND ADJV.update_allowed = 'Y'
171:
172: --Check if automatic modifiers are applied
173: SELECT COUNT(1)
174: INTO l_auto_mod_count
175: FROM PO_PRICE_ADJUSTMENTS_V ADJV
176: WHERE ADJV.po_header_id = p_po_header_id
177: AND ADJV.po_line_id = p_po_line_id
178: AND ADJV.automatic_flag = 'Y'
179: --AND ADJ.update_allowed = 'N'
181:
182: --Check if automatic overridden modifiers are applied
183: SELECT COUNT(1)
184: INTO l_auto_ovr_mod_count
185: FROM PO_PRICE_ADJUSTMENTS_V ADJV
186: WHERE ADJV.po_header_id = p_po_header_id
187: AND ADJV.po_line_id = p_po_line_id
188: AND ADJV.automatic_flag = 'Y'
189: AND ADJV.update_allowed = 'Y'
571: --E_INVALID_PARAMS EXCEPTION;
572:
573: CURSOR man_ovr_min_adj_cur(p_order_line_id PO_LINES_ALL.po_line_id%TYPE) IS
574: SELECT MIN(ADJV.price_adjustment_id) "MIN_PRICE_ADJ_ID"
575: FROM PO_PRICE_ADJUSTMENTS_V ADJV
576: WHERE ADJV.po_header_id = p_order_header_id --ADJV.draft_id = p_draft_id --sometimes draft_id may be passed as null
577: AND ADJV.po_line_id = p_order_line_id
578: AND NVL(ADJV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
579: AND (ADJV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
611: ,ADJV.price_adjustment_id "PRICE_ADJUSTMENT_ID" --Child price adjustment id
612: ,ADJV.parent_adjustment_id "RLTD_PRICE_ADJUSTMENT_ID" --Parent price adjustment id
613: ,ADJV.parent_list_line_type_code "RELATIONSHIP_TYPE_CODE" --Parent Child relationship type code
614: ,ADJV.parent_list_line_id "RLTD_LIST_LINE_ID" --Parent List Line Id
615: FROM PO_PRICE_ADJUSTMENTS_V ADJV
616: WHERE ADJV.po_header_id = p_order_header_id --ADJV.draft_id = p_draft_id --sometimes draft_id may be passed as null
617: AND ADJV.po_line_id = p_order_line_id
618: AND NVL(ADJV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
619: AND (ADJV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
1349: PO_DEBUG.debug_stmt(l_log_head,l_progress,'UnExpected ERROR IN POPL_MANUAL_OVERRIDDEN_ADJ. SQLERRM at '||l_progress||': '||SQLERRM);
1350: END IF;
1351:
1352: IF g_debug_unexp THEN
1353: PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING PO_PRICE_ADJUSTMENTS_PKG.POPL_MANUAL_OVERRIDDEN_ADJ with rollback');
1354: END IF;
1355: ROLLBACK TO SAVEPOINT POPULATE_QP_TABLES;
1356: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1357: END popl_manual_overridden_adj;
1402: SELECT ADJ.price_adjustment_id
1403: ,LDUP.line_detail_index
1404: FROM QP_LDETS_V LDUP
1405: ,QP_PREQ_LINES_TMP QLUP
1406: ,PO_PRICE_ADJUSTMENTS_DRAFT ADJ
1407: WHERE LDUP.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
1408: AND LDUP.list_line_id = ADJ.list_line_id
1409: AND ADJ.po_line_id = QLUP.line_id
1410: AND ADJ.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1442: IF g_debug_stmt THEN
1443: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Before extracting adjustments from QP temp tables');
1444: END IF;
1445:
1446: --Sync PO PRICE ADJUSTMENTS table with PO PRICE ADJUSTMENTS DRAFT
1447: FOR i IN p_order_line_id_tbl.FIRST .. p_order_line_id_tbl.LAST
1448: LOOP
1449: PO_PRICE_ADJ_DRAFT_PKG.sync_draft_from_txn
1450: ( p_draft_id => p_draft_id
1522: END IF;
1523:
1524: END IF;
1525:
1526: --Merge PO PRICE ADJUSTMENTS DRAFT table with PO PRICE ADJUSTMENTS for retro calls
1527: --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1528: IF (NVL(p_pricing_call_src, 'NULL') = 'RETRO' OR NVL(p_pricing_call_src, 'NULL') = 'AUTO') THEN
1529: --Merge Draft table with Base table
1530: PO_PRICE_ADJ_DRAFT_PKG.merge_changes( p_draft_id => p_draft_id );
1555: PO_DEBUG.debug_stmt(l_log_head,l_progress,'UnExpected ERROR IN EXTRACT_PRICE_ADJUSTMENTS. SQLERRM at '||l_progress||': '||SQLERRM);
1556: END IF;
1557:
1558: IF g_debug_unexp THEN
1559: PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING PO_PRICE_ADJUSTMENTS_PKG.EXTRACT_PRICE_ADJUSTMENTS with rollback');
1560: END IF;
1561: ROLLBACK TO SAVEPOINT EXTRACT_PRICE_ADJUSTMENTS;
1562: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563: END extract_price_adjustments;
1613: IF g_debug_stmt THEN
1614: PO_DEBUG.debug_stmt(p_log_head,l_progress,'The PO Line is not updated by pricing engine');
1615: END IF;
1616: ELSE
1617: --DELETE FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1618: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1619: SET ADJD.delete_flag = 'Y'
1620: WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1621: AND ADJD.po_header_id = p_order_header_id
1614: PO_DEBUG.debug_stmt(p_log_head,l_progress,'The PO Line is not updated by pricing engine');
1615: END IF;
1616: ELSE
1617: --DELETE FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1618: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1619: SET ADJD.delete_flag = 'Y'
1620: WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1621: AND ADJD.po_header_id = p_order_header_id
1622: AND ADJD.po_line_id = l_line_id
1658:
1659: --Only child adjustments are marked for deletion, no need to mark the attributes for deletion. The attributes are deleted along with the adjustments
1660: IF l_adj_id_tbl.count > 0 THEN
1661: FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
1662: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT
1663: SET delete_flag = 'Y'
1664: WHERE draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1665: AND parent_adjustment_id = l_adj_id_tbl(i);
1666:
1720:
1721: BEGIN
1722: SELECT ADJ.price_adjustment_id
1723: INTO l_price_adjustment_id
1724: FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
1725: WHERE ADJ.draft_id = p_draft_id
1726: AND ADJ.price_adjustment_id = p_price_adjustment_id
1727: FOR UPDATE NOWAIT;
1728:
1751: END IF;
1752: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1753: END;
1754:
1755: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1756: SET ( LAST_UPDATE_DATE
1757: , LAST_UPDATED_BY
1758: , LAST_UPDATE_LOGIN
1759: , operand
1821:
1822: --reset the columns used to identify the overridden modifier
1823: --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1824: IF NVL(p_pricing_call_src, 'NULL') = 'RETRO' OR NVL(p_allow_price_override_flag, 'Y') <> 'Y' THEN
1825: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1826: SET ADJD.updated_flag = 'N',
1827: ADJD.change_reason_code = null,
1828: ADJD.change_reason_text = null
1829: WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1852: IF g_debug_stmt THEN
1853: PO_DEBUG.debug_stmt(p_log_head,l_progress,'Insert Adjustments called with Order Header ID: '||p_order_header_id);
1854: END IF;
1855:
1856: INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
1857: (DRAFT_ID
1858: , PRICE_ADJUSTMENT_ID
1859: , ADJ_LINE_NUM
1860: , CREATION_DATE
1953: , LOCK_CONTROL
1954: )
1955: (SELECT
1956: p_draft_id --DRAFT_ID
1957: , po_price_adjustments_s.nextval -- PRICE_ADJUSTMENT_ID
1958: , LDETS.line_detail_index --ADJ_LINE_NUM
1959: , SYSDATE -- CREATION_DATE
1960: , FND_GLOBAL.user_id -- CREATED_BY
1961: , SYSDATE -- LAST_UPDATE_DATE
2067: AND (nvl(LDETS.automatic_flag, 'N') = 'Y')
2068: AND LDETS.created_from_list_type_code NOT IN ('PRL', 'AGR')
2069: AND LDETS.list_line_type_code <> 'PLL'
2070: AND LDETS.list_line_id NOT IN (SELECT ADJ.list_line_id
2071: FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2072: WHERE ADJ.list_line_id = LDETS.list_line_id
2073: AND ADJ.po_line_id = QLINE.line_id)
2074: );
2075: l_progress := '280';
2083: BULK COLLECT INTO l_price_adjustment_id_tbl
2084: ,l_rltd_price_adj_id_tbl
2085: FROM QP_PREQ_RLTD_LINES_TMP RLTD
2086: , QP_PREQ_LINES_TMP QPL
2087: , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2088: , PO_PRICE_ADJUSTMENTS_DRAFT RADJ
2089: WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX
2090: AND QPL.LINE_ID = ADJ.PO_LINE_ID
2091: AND ADJ.draft_id = p_draft_id
2084: ,l_rltd_price_adj_id_tbl
2085: FROM QP_PREQ_RLTD_LINES_TMP RLTD
2086: , QP_PREQ_LINES_TMP QPL
2087: , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2088: , PO_PRICE_ADJUSTMENTS_DRAFT RADJ
2089: WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX
2090: AND QPL.LINE_ID = ADJ.PO_LINE_ID
2091: AND ADJ.draft_id = p_draft_id
2092: AND QPL.LINE_TYPE_CODE = 'LINE'
2099: AND RLTD.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2100:
2101: --Update adjustment table with parent record
2102: FORALL i IN l_rltd_price_adj_id_tbl.FIRST..l_rltd_price_adj_id_tbl.LAST
2103: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2104: SET ADJ.parent_adjustment_id = l_price_adjustment_id_tbl(i)
2105: WHERE ADJ.price_adjustment_id = l_rltd_price_adj_id_tbl(i);
2106: --New Parent Adjustment Id Logic end
2107:
2127:
2128: CURSOR refresh_attribs_cur IS
2129: SELECT ADJ.price_adjustment_id, ldets.line_detail_index
2130: FROM QP_PREQ_LINES_TMP QPL
2131: , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2132: , QP_LDETS_V LDETS
2133: WHERE LDETS.list_line_id = ADJ.list_line_id
2134: AND ADJ.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
2135: AND LDETS.line_index = QPL.line_index
2306: , PO_PRICE_ADJ_ATTRIBS_S.nextval
2307: , 1
2308: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
2309: , QP_LDETS_v LDETS
2310: , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2311: , QP_PREQ_LINES_TMP QPLINE
2312: WHERE ADJ.po_header_id = p_order_header_id
2313: AND QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2314: AND QPLAT.line_detail_index = LDETS.line_detail_index
2358: END IF;
2359:
2360: SELECT price_adjustment_id
2361: BULK COLLECT INTO l_price_adjustment_id_tbl
2362: FROM po_price_adjustments_draft ADJD
2363: WHERE ADJD.draft_id = p_draft_id
2364: AND ADJD.applied_flag IS NULL --this check is needed to ensure if the manual modifier is newly added
2365: AND NVL(ADJD.automatic_flag, 'N') = 'N' --line is not automatic modifier
2366: AND NVL(ADJD.delete_flag, 'N') = 'N' --line is not marked for deletion
2412: , 'QP_ATTR_DEFNS_PRICING' --FLEX_TITLE
2413: , PO_PRICE_ADJ_ATTRIBS_S.nextval
2414: , 1
2415: FROM QP_PRICING_ATTRIBUTES PRA
2416: ,PO_PRICE_ADJUSTMENTS_DRAFT ADJD
2417: WHERE ADJD.draft_id = p_draft_id
2418: AND ADJD.price_adjustment_id = l_price_adjustment_id_tbl(i)
2419: AND ADJD.list_header_id = PRA.list_header_id
2420: AND ADJD.list_line_id = PRA.list_line_id
2472: , 'QP_ATTR_DEFNS_QUALIFIER' --FLEX_TITLE
2473: , PO_PRICE_ADJ_ATTRIBS_S.nextval
2474: , 1
2475: FROM QP_QUALIFIERS QUAL
2476: ,PO_PRICE_ADJUSTMENTS_DRAFT ADJD
2477: WHERE ADJD.draft_id = p_draft_id
2478: AND ADJD.price_adjustment_id = l_price_adjustment_id_tbl(i)
2479: AND ADJD.list_header_id = QUAL.list_header_id
2480: AND ADJD.list_line_id = QUAL.list_line_id
2501: ROLLBACK TO SAVEPOINT COMPLETE_MANUAL_MOD_LOV_MAP;
2502: END complete_manual_mod_lov_map;
2503:
2504: PROCEDURE copy_line_adjustments
2505: ( p_src_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2506: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
2507: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2508: , p_mode IN VARCHAR2
2509: , x_return_status_text OUT NOCOPY VARCHAR2
2502: END complete_manual_mod_lov_map;
2503:
2504: PROCEDURE copy_line_adjustments
2505: ( p_src_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2506: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
2507: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2508: , p_mode IN VARCHAR2
2509: , x_return_status_text OUT NOCOPY VARCHAR2
2510: , x_return_status OUT NOCOPY VARCHAR2
2503:
2504: PROCEDURE copy_line_adjustments
2505: ( p_src_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2506: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
2507: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2508: , p_mode IN VARCHAR2
2509: , x_return_status_text OUT NOCOPY VARCHAR2
2510: , x_return_status OUT NOCOPY VARCHAR2
2511: )
2516: l_progress VARCHAR2(4) := '000';
2517: l_return_status_text VARCHAR2(2000);
2518: COPYDOC_ADJUSTMENT_FAILURE EXCEPTION;
2519:
2520: l_po_price_adjustment_record PO_PRICE_ADJUSTMENTS%ROWTYPE;
2521: l_src_price_adjustment_id PO_PRICE_ADJUSTMENTS.price_adjustment_id%TYPE;
2522:
2523: l_src_adj_count NUMBER;
2524: --l_src_asoc_count NUMBER;
2517: l_return_status_text VARCHAR2(2000);
2518: COPYDOC_ADJUSTMENT_FAILURE EXCEPTION;
2519:
2520: l_po_price_adjustment_record PO_PRICE_ADJUSTMENTS%ROWTYPE;
2521: l_src_price_adjustment_id PO_PRICE_ADJUSTMENTS.price_adjustment_id%TYPE;
2522:
2523: l_src_adj_count NUMBER;
2524: --l_src_asoc_count NUMBER;
2525: l_src_attr_count NUMBER;
2539: l_dest_price_adjustment_id_tbl NUMBER_TYPE;
2540: i PLS_INTEGER;
2541:
2542: --Used to pick only the parent adjustments
2543: CURSOR po_price_adjustments_cur(p_src_line_id PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2544: ,p_auto_manual_flag VARCHAR2
2545: ,p_override_allowed_flag VARCHAR2
2546: ,p_overridden_flag VARCHAR2) IS
2547: SELECT ADJ.*
2544: ,p_auto_manual_flag VARCHAR2
2545: ,p_override_allowed_flag VARCHAR2
2546: ,p_overridden_flag VARCHAR2) IS
2547: SELECT ADJ.*
2548: FROM PO_PRICE_ADJUSTMENTS ADJ
2549: WHERE ADJ.po_line_id = p_src_line_id
2550: AND (p_auto_manual_flag IS NULL OR ADJ.automatic_flag = p_auto_manual_flag)
2551: AND (p_override_allowed_flag IS NULL OR ADJ.update_allowed = p_override_allowed_flag)
2552: AND (p_overridden_flag IS NULL OR ADJ.updated_flag = p_overridden_flag);
2612:
2613: --Check if adjsutments already exist for the destination line id
2614: SELECT COUNT(1)
2615: INTO l_adjustments_exist
2616: FROM PO_PRICE_ADJUSTMENTS ADJ
2617: WHERE ADJ.po_line_id = p_dest_po_line_id
2618: AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2619: AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2620: AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
2633: END IF;
2634:
2635: SELECT COUNT(1)
2636: INTO l_src_adj_count
2637: FROM PO_PRICE_ADJUSTMENTS ADJ
2638: WHERE ADJ.po_line_id = p_src_po_line_id
2639: AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2640: AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2641: AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
2642:
2643: SELECT COUNT(1)
2644: INTO l_src_attr_count
2645: FROM PO_PRICE_ADJ_ATTRIBS ATTR
2646: ,PO_PRICE_ADJUSTMENTS ADJ
2647: WHERE ATTR.price_adjustment_id = ADJ.price_adjustment_id
2648: AND ADJ.po_line_id = p_src_po_line_id
2649: AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2650: AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2661: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Loop through and Copy Adjustments');
2662: END IF;
2663:
2664: i := 0;
2665: OPEN po_price_adjustments_cur( p_src_po_line_id
2666: , l_auto_manual_flag
2667: , l_override_allowed_flag
2668: , l_overridden_flag);
2669: <
2667: , l_override_allowed_flag
2668: , l_overridden_flag);
2669: <
2670: LOOP
2671: FETCH po_price_adjustments_cur INTO l_po_price_adjustment_record;
2672: EXIT ADJUSTMENTS WHEN po_price_adjustments_cur%NOTFOUND;
2673:
2674: -- reset for new record
2675: l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
2668: , l_overridden_flag);
2669: <
2670: LOOP
2671: FETCH po_price_adjustments_cur INTO l_po_price_adjustment_record;
2672: EXIT ADJUSTMENTS WHEN po_price_adjustments_cur%NOTFOUND;
2673:
2674: -- reset for new record
2675: l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
2676: l_po_price_adjustment_record.po_line_id := p_dest_po_line_id;
2675: l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
2676: l_po_price_adjustment_record.po_line_id := p_dest_po_line_id;
2677:
2678: l_src_price_adjustment_id := l_po_price_adjustment_record.price_adjustment_id;
2679: SELECT po_price_adjustments_s.nextval
2680: INTO l_po_price_adjustment_record.price_adjustment_id
2681: FROM SYS.DUAL;
2682:
2683: --reset Standard and other columns
2762: i := i + 1;
2763: l_src_price_adjustment_id_tbl(i) := l_src_price_adjustment_id;
2764: l_dest_price_adjustment_id_tbl(i) := l_po_price_adjustment_record.price_adjustment_id;
2765: END LOOP ADJUSTMENTS;
2766: CLOSE po_price_adjustments_cur;
2767:
2768: l_progress := '100';
2769: IF g_debug_stmt THEN
2770: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
2770: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
2771: END IF;
2772:
2773: FORALL i IN l_src_price_adjustment_id_tbl.FIRST .. l_src_price_adjustment_id_tbl.LAST
2774: UPDATE PO_PRICE_ADJUSTMENTS
2775: SET parent_adjustment_id = l_dest_price_adjustment_id_tbl(i)
2776: WHERE parent_adjustment_id = l_src_price_adjustment_id_tbl(i)
2777: AND po_header_id = p_dest_po_header_id
2778: AND po_line_id = p_dest_po_line_id
2818: END copy_line_adjustments;
2819:
2820:
2821: PROCEDURE copy_draft_line_adjustments
2822: ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
2823: , p_src_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2824: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_header_id%TYPE
2825: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2826: , p_mode IN VARCHAR2
2819:
2820:
2821: PROCEDURE copy_draft_line_adjustments
2822: ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
2823: , p_src_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2824: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_header_id%TYPE
2825: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2826: , p_mode IN VARCHAR2
2827: , x_return_status_text OUT NOCOPY VARCHAR2
2820:
2821: PROCEDURE copy_draft_line_adjustments
2822: ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
2823: , p_src_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2824: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_header_id%TYPE
2825: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2826: , p_mode IN VARCHAR2
2827: , x_return_status_text OUT NOCOPY VARCHAR2
2828: , x_return_status OUT NOCOPY VARCHAR2
2821: PROCEDURE copy_draft_line_adjustments
2822: ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
2823: , p_src_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2824: , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_header_id%TYPE
2825: , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2826: , p_mode IN VARCHAR2
2827: , x_return_status_text OUT NOCOPY VARCHAR2
2828: , x_return_status OUT NOCOPY VARCHAR2
2829: )
2834: l_progress VARCHAR2(4) := '000';
2835: l_return_status_text VARCHAR2(2000);
2836: COPYDOC_ADJUSTMENT_FAILURE EXCEPTION;
2837:
2838: l_po_price_adjustment_record PO_PRICE_ADJUSTMENTS_V%ROWTYPE;
2839: l_src_price_adjustment_id PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE;
2840:
2841: l_src_adj_count NUMBER;
2842: --l_src_asoc_count NUMBER;
2835: l_return_status_text VARCHAR2(2000);
2836: COPYDOC_ADJUSTMENT_FAILURE EXCEPTION;
2837:
2838: l_po_price_adjustment_record PO_PRICE_ADJUSTMENTS_V%ROWTYPE;
2839: l_src_price_adjustment_id PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE;
2840:
2841: l_src_adj_count NUMBER;
2842: --l_src_asoc_count NUMBER;
2843: l_src_attr_count NUMBER;
2857: l_dest_price_adjustment_id_tbl NUMBER_TYPE;
2858: i PLS_INTEGER;
2859:
2860: --Used to pick only the parent adjustments
2861: CURSOR po_price_adjustments_cur(p_src_line_id PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2862: ,p_auto_manual_flag VARCHAR2
2863: ,p_override_allowed_flag VARCHAR2
2864: ,p_overridden_flag VARCHAR2) IS
2865: SELECT ADJV.*
2862: ,p_auto_manual_flag VARCHAR2
2863: ,p_override_allowed_flag VARCHAR2
2864: ,p_overridden_flag VARCHAR2) IS
2865: SELECT ADJV.*
2866: FROM PO_PRICE_ADJUSTMENTS_V ADJV
2867: WHERE ADJV.po_line_id = p_src_line_id --ADJV.draft_id = p_draft_id --the draft id may not have been initialized when copy event was triggered
2868: AND (p_auto_manual_flag IS NULL OR ADJV.automatic_flag = p_auto_manual_flag)
2869: AND (p_override_allowed_flag IS NULL OR ADJV.update_allowed = p_override_allowed_flag)
2870: AND (p_overridden_flag IS NULL OR ADJV.updated_flag = p_overridden_flag);
2941:
2942: --Check if adjsutments already exist for the destination line id
2943: SELECT COUNT(1)
2944: INTO l_adjustments_exist
2945: FROM PO_PRICE_ADJUSTMENTS_V ADJV
2946: WHERE ADJV.draft_id = p_draft_id
2947: AND ADJV.po_line_id = p_dest_po_line_id
2948: AND (l_auto_manual_flag IS NULL OR ADJV.automatic_flag = l_auto_manual_flag)
2949: AND (l_override_allowed_flag IS NULL OR ADJV.update_allowed = l_override_allowed_flag)
2963: END IF;
2964:
2965: SELECT COUNT(1)
2966: INTO l_src_adj_count
2967: FROM PO_PRICE_ADJUSTMENTS_V ADJV
2968: WHERE ADJV.po_line_id = p_src_po_line_id --ADJV.draft_id = p_draft_id --the draft id may not have been initialized when copy event was triggered
2969: AND (l_auto_manual_flag IS NULL OR ADJV.automatic_flag = l_auto_manual_flag)
2970: AND (l_override_allowed_flag IS NULL OR ADJV.update_allowed = l_override_allowed_flag)
2971: AND (l_overridden_flag IS NULL OR ADJV.updated_flag = l_overridden_flag);
2976: FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
2977: WHERE ASOC.draft_id = p_draft_id
2978: AND ASOC.line_id = p_src_po_line_id
2979: AND EXISTS (SELECT 1
2980: FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2981: WHERE ADJ.draft_id = p_draft_id
2982: AND ADJ.line_id = p_src_po_line_id
2983: AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2984: AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
3041: FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3042: WHERE ASOC.draft_id = p_draft_id
3043: AND ASOC.line_id = p_src_po_line_id
3044: AND EXISTS (SELECT 1
3045: FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
3046: WHERE ADJ.draft_id = p_draft_id
3047: AND ADJ.line_id = p_src_po_line_id
3048: AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
3049: AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
3063: IF g_debug_stmt THEN
3064: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Loop through and Copy Adjustments');
3065: END IF;
3066: i := 0;
3067: OPEN po_price_adjustments_cur( p_src_po_line_id
3068: , l_auto_manual_flag
3069: , l_override_allowed_flag
3070: , l_overridden_flag);
3071: <
3069: , l_override_allowed_flag
3070: , l_overridden_flag);
3071: <
3072: LOOP
3073: FETCH po_price_adjustments_cur INTO l_po_price_adjustment_record;
3074: EXIT ADJUSTMENTS WHEN po_price_adjustments_cur%NOTFOUND;
3075:
3076: -- reset for new record
3077: --l_po_price_adjustment_record.draft_id := p_draft_id;
3070: , l_overridden_flag);
3071: <
3072: LOOP
3073: FETCH po_price_adjustments_cur INTO l_po_price_adjustment_record;
3074: EXIT ADJUSTMENTS WHEN po_price_adjustments_cur%NOTFOUND;
3075:
3076: -- reset for new record
3077: --l_po_price_adjustment_record.draft_id := p_draft_id;
3078: l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
3078: l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
3079: l_po_price_adjustment_record.po_line_id := p_dest_po_line_id;
3080:
3081: l_src_price_adjustment_id := l_po_price_adjustment_record.price_adjustment_id;
3082: SELECT po_price_adjustments_s.nextval
3083: INTO l_po_price_adjustment_record.price_adjustment_id
3084: FROM SYS.DUAL;
3085:
3086: --reset Standard columns
3168: i := i + 1;
3169: l_src_price_adjustment_id_tbl(i) := l_src_price_adjustment_id;
3170: l_dest_price_adjustment_id_tbl(i) := l_po_price_adjustment_record.price_adjustment_id;
3171: END LOOP ADJUSTMENTS;
3172: CLOSE po_price_adjustments_cur;
3173:
3174: l_progress := '100';
3175: IF g_debug_stmt THEN
3176: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
3176: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
3177: END IF;
3178:
3179: FORALL i IN l_src_price_adjustment_id_tbl.FIRST .. l_src_price_adjustment_id_tbl.LAST
3180: UPDATE PO_PRICE_ADJUSTMENTS_DRAFT
3181: SET parent_adjustment_id = l_dest_price_adjustment_id_tbl(i)
3182: WHERE parent_adjustment_id = l_src_price_adjustment_id_tbl(i)
3183: AND draft_id = p_draft_id
3184: AND po_header_id = p_dest_po_header_id
3226:
3227:
3228:
3229:
3230: PROCEDURE insert_adj_rec(p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE)
3231: IS
3232: BEGIN
3233: INSERT INTO PO_PRICE_ADJUSTMENTS
3234: (PRICE_ADJUSTMENT_ID
3229:
3230: PROCEDURE insert_adj_rec(p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE)
3231: IS
3232: BEGIN
3233: INSERT INTO PO_PRICE_ADJUSTMENTS
3234: (PRICE_ADJUSTMENT_ID
3235: , CREATION_DATE
3236: , CREATED_BY
3237: , LAST_UPDATE_DATE
3429: --Exception will be caught by the calling procedure
3430: END insert_adj_rec;
3431:
3432: PROCEDURE insert_draft_adj_rec(p_draft_id IN NUMBER
3433: ,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE)
3434: IS
3435: BEGIN
3436: INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
3437: (DRAFT_ID
3432: PROCEDURE insert_draft_adj_rec(p_draft_id IN NUMBER
3433: ,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE)
3434: IS
3435: BEGIN
3436: INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
3437: (DRAFT_ID
3438: , CHANGE_ACCEPTED_FLAG
3439: , DELETE_FLAG
3440: , PRICE_ADJUSTMENT_ID
3644: --Name: delete_price_adjustments
3645: --Pre-reqs:
3646: -- None.
3647: --Modifies:
3648: -- PO_PRICE_ADJUSTMENTS, PO_PRICE_ADJ_ASSOCS, PO_PRICE_ADJ_ATTRIBS
3649: --Locks:
3650: -- None.
3651: --Function:
3652: -- Deletes the price adjustments of a PO/PO Line or GBPA/GBPA Line
3661: --End of Comments
3662: -------------------------------------------------------------------------------
3663: -------------------------------------------------------------------------------
3664: PROCEDURE delete_price_adjustments
3665: ( p_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
3666: , p_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE DEFAULT NULL
3667: )
3668: IS
3669: l_price_adj_tbl NUMBER_TYPE;
3662: -------------------------------------------------------------------------------
3663: -------------------------------------------------------------------------------
3664: PROCEDURE delete_price_adjustments
3665: ( p_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
3666: , p_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE DEFAULT NULL
3667: )
3668: IS
3669: l_price_adj_tbl NUMBER_TYPE;
3670: BEGIN
3669: l_price_adj_tbl NUMBER_TYPE;
3670: BEGIN
3671:
3672: --Delete Price Adjustments
3673: DELETE FROM PO_PRICE_ADJUSTMENTS
3674: WHERE po_header_id = p_po_header_id
3675: AND (po_line_id = p_po_line_id OR p_po_line_id IS NULL)
3676: RETURNING
3677: price_adjustment_id
3694: --Delete dependant fields first
3695: --Delete Price Adjustment Attributes
3696: DELETE FROM PO_PRICE_ADJ_ATTRIBS ATTR
3697: WHERE ATTR.price_adjustment_id IN (SELECT ADJ.price_adjustment_id
3698: FROM PO_PRICE_ADJUSTMENTS ADJ
3699: WHERE ADJ.header_id = p_header_id
3700: AND ADJ.line_id = p_line_id);
3701:
3702: --Delete Price Adjustment Associations
3703: DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
3704: WHERE ASOC.line_id = p_line_id;
3705:
3706: --Delete Price Adjustments
3707: DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
3708: WHERE ADJ.header_id = p_header_id
3709: AND ADJ.line_id = p_line_id;
3710: */
3711:
3710: */
3711:
3712: EXCEPTION
3713: WHEN OTHERS THEN
3714: PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_price_adjustments','000',sqlcode);
3715: RAISE;
3716: END delete_price_adjustments;
3717:
3718:
3722: --Name: delete_adjustment
3723: --Pre-reqs:
3724: -- None.
3725: --Modifies:
3726: -- PO_PRICE_ADJUSTMENTS, PO_PRICE_ADJ_ASSOCS, PO_PRICE_ADJ_ATTRIBS
3727: --Locks:
3728: -- None.
3729: --Function:
3730: -- Deletes the price adjustments of a PO Line or GBPA Line
3737: --End of Comments
3738: -------------------------------------------------------------------------------
3739: -------------------------------------------------------------------------------
3740: PROCEDURE delete_adjustment
3741: ( p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS.price_adjustment_id%TYPE )
3742: IS
3743: BEGIN
3744: --Delete dependant fields first
3745: --Delete Price Adjustment Attributes
3755: DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
3756: WHERE ASOC.rltd_price_adj_id = p_price_adjustment_id;
3757: */
3758: --Delete Price Adjustments
3759: DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
3760: WHERE ADJ.price_adjustment_id = p_price_adjustment_id;
3761: EXCEPTION
3762: WHEN OTHERS THEN
3763: PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment','000',sqlcode);
3759: DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
3760: WHERE ADJ.price_adjustment_id = p_price_adjustment_id;
3761: EXCEPTION
3762: WHEN OTHERS THEN
3763: PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment','000',sqlcode);
3764: RAISE;
3765: END delete_adjustment;
3766:
3767: /*
3786: --End of Comments
3787: -------------------------------------------------------------------------------
3788: -------------------------------------------------------------------------------
3789: PROCEDURE delete_adjustment_dependants
3790: ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
3791: , p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE )
3792: IS
3793: BEGIN
3794: --Delete dependant fields first
3787: -------------------------------------------------------------------------------
3788: -------------------------------------------------------------------------------
3789: PROCEDURE delete_adjustment_dependants
3790: ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
3791: , p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE )
3792: IS
3793: BEGIN
3794: --Delete dependant fields first
3795: --Delete Price Adjustment Attributes
3800: SELECT ASOC.rltd_price_adj_id
3801: FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3802: WHERE ASOC.price_adjustment_id = p_price_adjustment_id);
3803:
3804: DELETE FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
3805: WHERE ADJ.draft_id = p_draft_id
3806: AND ADJ.price_adjustment_id IN (SELECT ASOC.rltd_price_adj_id
3807: FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3808: WHERE ASOC.price_adjustment_id = p_price_adjustment_id);
3818: AND ASOC.rltd_price_adj_id = p_price_adjustment_id;
3819:
3820: EXCEPTION
3821: WHEN OTHERS THEN
3822: PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment_dependants','000',sqlcode);
3823: RAISE;
3824: END delete_adjustment_dependants;
3825: */
3826:
3823: RAISE;
3824: END delete_adjustment_dependants;
3825: */
3826:
3827: END PO_PRICE_ADJUSTMENTS_PKG;