[Home] [Help]
641: ,ATTRV.pricing_attr_value_to "VALUE_TO"
642: ,ATTRV.comparison_operator "COMPARISON_OPERATOR_TYPE"
643: ,DECODE(ATTRV.flex_title
644: ,'QP_ATTR_DEFNS_QUALIFIER', 'Y', 'N') "VALIDATED_FLAG"
645: FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
646: WHERE ATTRV.po_header_id = p_order_header_id --ATTRV.draft_id = p_draft_id --sometimes draft_id may be passed as null
647: AND ATTRV.po_line_id = p_order_line_id
648: AND NVL(ATTRV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
649: AND (ATTRV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
1671: END IF;
1672: /*
1673: IF l_adj_id_tbl.count > 0 THEN
1674: FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
1675: DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i);
1676: l_progress := '100';
1677: IF g_debug_stmt THEN
1678: PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ATTRIBS');
1679: END IF;
2157: FETCH refresh_attribs_cur BULK COLLECT INTO l_adj_id_tbl, l_line_detail_index_tbl;
2158:
2159: IF l_adj_id_tbl.count > 0 THEN
2160: FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
2161: DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i)
2162: AND ( pricing_context
2163: , pricing_attribute
2164: , pricing_attr_value_from
2165: , pricing_attr_value_to
2179: PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ATTRIBUTES');
2180: END IF;
2181:
2182: FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
2183: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2184: (DRAFT_ID
2185: , PRICE_ADJUSTMENT_ID
2186: , PRICING_CONTEXT
2187: , PRICING_ATTRIBUTE
2220: , QPLAT.comparison_operator_type_code
2221: , decode(QPLAT.attribute_type,
2222: 'QUALIFIER', 'QP_ATTR_DEFNS_QUALIFIER',
2223: 'QP_ATTR_DEFNS_PRICING')
2224: , PO_PRICE_ADJ_ATTRIBS_S.nextval
2225: , 1
2226: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
2227: WHERE QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2228: AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
2235: (SELECT pricing_context
2236: , pricing_attribute
2237: , pricing_attr_value_from
2238: , pricing_attr_value_to
2239: FROM PO_PRICE_ADJ_ATTRIBS_DRAFT
2240: WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i))
2241: );
2242:
2243: l_progress := '390';
2262: PO_DEBUG.debug_var(p_log_head,l_progress,'p_draft_id',p_draft_id);
2263: PO_DEBUG.debug_var(p_log_head,l_progress,'p_order_header_id',p_order_header_id);
2264: END IF;
2265:
2266: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2267: (DRAFT_ID
2268: , PRICE_ADJUSTMENT_ID
2269: , PRICING_CONTEXT
2270: , PRICING_ATTRIBUTE
2302: , QPLAT.comparison_operator_type_code
2303: , decode(QPLAT.attribute_type,
2304: 'QUALIFIER', 'QP_ATTR_DEFNS_QUALIFIER',
2305: 'QP_ATTR_DEFNS_PRICING')
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
2367: AND NVL(ADJD.change_accepted_flag, 'Y') = 'Y'; --change is accepted for the line. Do we need this condition??
2368:
2369: d_position := 120;
2370: IF PO_LOG.d_stmt THEN
2371: PO_LOG.stmt(d_mod, d_position, 'Populate PO_PRICE_ADJ_ATTRIBS table with pricing attribute details');
2372: END IF;
2373:
2374: FORALL i IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
2375: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2371: PO_LOG.stmt(d_mod, d_position, 'Populate PO_PRICE_ADJ_ATTRIBS table with pricing attribute details');
2372: END IF;
2373:
2374: FORALL i IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
2375: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2376: (DRAFT_ID
2377: , PRICE_ADJUSTMENT_ID
2378: , PRICING_CONTEXT
2379: , PRICING_ATTRIBUTE
2409: , PRA.pricing_attr_value_from --VALUE_FROM
2410: , PRA.pricing_attr_value_to --VALUE_TO
2411: , PRA.comparison_operator_code
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
2427: END IF;
2428:
2429: d_position := 160;
2430: IF PO_LOG.d_stmt THEN
2431: PO_LOG.stmt(d_mod, d_position, 'Populate PO_PRICE_ADJ_ATTRIBS table with qualifier attribute details');
2432: END IF;
2433:
2434: FORALL i IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
2435: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2431: PO_LOG.stmt(d_mod, d_position, 'Populate PO_PRICE_ADJ_ATTRIBS table with qualifier attribute details');
2432: END IF;
2433:
2434: FORALL i IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
2435: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2436: (DRAFT_ID
2437: , PRICE_ADJUSTMENT_ID
2438: , PRICING_CONTEXT
2439: , PRICING_ATTRIBUTE
2469: , QUAL.qualifier_attr_value --VALUE_FROM
2470: , QUAL.qualifier_attr_value_to --VALUE_TO
2471: , QUAL.comparison_operator_code
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
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)
2714: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Insert attributes corresponding to the adjustment');
2715: END IF;
2716:
2717: --copy attribute lines for the adjustment
2718: INSERT INTO PO_PRICE_ADJ_ATTRIBS
2719: (PRICE_ADJUSTMENT_ID
2720: , PRICING_CONTEXT
2721: , PRICING_ATTRIBUTE
2722: , CREATION_DATE
2751: , ATTR.pricing_attr_value_from
2752: , ATTR.pricing_attr_value_to
2753: , ATTR.comparison_operator
2754: , ATTR.FLEX_TITLE
2755: , PO_PRICE_ADJ_ATTRIBS_S.nextval
2756: , 1
2757: FROM PO_PRICE_ADJ_ATTRIBS ATTR
2758: WHERE ATTR.price_adjustment_id = l_src_price_adjustment_id);
2759: l_dest_attr_count := l_dest_attr_count + SQL%ROWCOUNT;
2753: , ATTR.comparison_operator
2754: , ATTR.FLEX_TITLE
2755: , PO_PRICE_ADJ_ATTRIBS_S.nextval
2756: , 1
2757: FROM PO_PRICE_ADJ_ATTRIBS ATTR
2758: WHERE ATTR.price_adjustment_id = l_src_price_adjustment_id);
2759: l_dest_attr_count := l_dest_attr_count + SQL%ROWCOUNT;
2760:
2761: --Get the source and dest price adjustment id mapping, will be used later to update the parent adjustment ids
2986: */
2987:
2988: SELECT COUNT(1)
2989: INTO l_src_attr_count
2990: FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
2991: WHERE ATTRV.po_line_id = p_src_po_line_id --ADJV.draft_id = p_draft_id AND ATTRV.draft_id = ADJV.draft_id --the draft id may not have been initialized when copy event was triggered
2992: AND (l_auto_manual_flag IS NULL OR ATTRV.automatic_flag = l_auto_manual_flag)
2993: AND (l_override_allowed_flag IS NULL OR ATTRV.update_allowed = l_override_allowed_flag)
2994: AND (l_overridden_flag IS NULL OR ATTRV.updated_flag = l_overridden_flag);
3118: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Insert attributes corresponding to the adjustment');
3119: END IF;
3120:
3121: --copy attribute lines for the adjustment
3122: INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
3123: (DRAFT_ID
3124: , PRICE_ADJUSTMENT_ID
3125: , PRICING_CONTEXT
3126: , PRICING_ATTRIBUTE
3157: , ATTRV.pricing_attr_value_from
3158: , ATTRV.pricing_attr_value_to
3159: , ATTRV.comparison_operator
3160: , ATTRV.FLEX_TITLE
3161: , PO_PRICE_ADJ_ATTRIBS_S.nextval
3162: , 1
3163: FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
3164: WHERE ATTRV.price_adjustment_id = l_src_price_adjustment_id); --ATTRV.draft_id = p_draft_id --draft_id may not have been initialized when copy event was triggered
3165: l_dest_attr_count := l_dest_attr_count + SQL%ROWCOUNT;
3159: , ATTRV.comparison_operator
3160: , ATTRV.FLEX_TITLE
3161: , PO_PRICE_ADJ_ATTRIBS_S.nextval
3162: , 1
3163: FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
3164: WHERE ATTRV.price_adjustment_id = l_src_price_adjustment_id); --ATTRV.draft_id = p_draft_id --draft_id may not have been initialized when copy event was triggered
3165: l_dest_attr_count := l_dest_attr_count + SQL%ROWCOUNT;
3166:
3167: --Get the source and dest price adjustment id mapping, will be used later to update the parent adjustment ids
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
3686: */
3687:
3688: --Delete Price Adjustment Attributes
3689: FORALL i IN l_price_adj_tbl.FIRST..l_price_adj_tbl.LAST
3690: DELETE FROM PO_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = l_price_adj_tbl(i);
3691: END IF;
3692:
3693: /*
3694: --Delete dependant fields first
3692:
3693: /*
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);
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
3742: IS
3743: BEGIN
3744: --Delete dependant fields first
3745: --Delete Price Adjustment Attributes
3746: DELETE FROM PO_PRICE_ADJ_ATTRIBS ATTR
3747: WHERE ATTR.price_adjustment_id = p_price_adjustment_id;
3748: /*
3749: --Delete Price Adjustment Associations
3750: DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
3771: --Name: delete_adjustment_dependants
3772: --Pre-reqs:
3773: -- None.
3774: --Modifies:
3775: -- PO_PRICE_ADJ_ASSOCS, PO_PRICE_ADJ_ATTRIBS
3776: --Locks:
3777: -- None.
3778: --Function:
3779: -- Deletes the price adjustments dependants of a PO Line or GBPA Line
3792: IS
3793: BEGIN
3794: --Delete dependant fields first
3795: --Delete Price Adjustment Attributes
3796: DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT ATTR
3797: WHERE ATTR.draft_id = p_draft_id
3798: AND ATTR.price_adjustment_id IN (SELECT p_price_adjustment_id FROM DUAL
3799: UNION
3800: SELECT ASOC.rltd_price_adj_id