[Home] [Help]
1551: , NULL BREAK_UOM_CODE
1552: , NULL BREAK_UOM_CONTEXT
1553: , NULL BREAK_UOM_ATTRIBUTE
1554: , NULL PROCESS_CODE -- 3215497
1555: FROM qp_int_lines line, oe_price_adjustments adj,
1556: qp_list_headers_b qplh
1557: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1668: , NULL BREAK_UOM_CODE
1669: , NULL BREAK_UOM_CONTEXT
1670: , NULL BREAK_UOM_ATTRIBUTE
1671: , NULL PROCESS_CODE -- 3215497
1672: FROM qp_int_lines line, oe_price_adjustments adj,
1673: qp_list_headers_b qplh
1674: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1715: adj_pbh.list_line_id list_line_id,
1716: adj.list_line_id related_list_line_id,
1717: 'INSERTED FOR CALCULATION' pricing_status_text
1718: FROM qp_int_lines line
1719: , oe_price_adjustments adj_pbh
1720: , oe_price_adj_assocs ass
1721: , oe_price_adjustments adj
1722: , qp_pricing_attributes attr
1723: WHERE pbh_exist_flag = 'Y'
1717: 'INSERTED FOR CALCULATION' pricing_status_text
1718: FROM qp_int_lines line
1719: , oe_price_adjustments adj_pbh
1720: , oe_price_adj_assocs ass
1721: , oe_price_adjustments adj
1722: , qp_pricing_attributes attr
1723: WHERE pbh_exist_flag = 'Y'
1724: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1725: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1760: adj_prg.list_line_id list_line_id,
1761: adj.list_line_id related_list_line_id,
1762: 'INSERTED FOR CALCULATION' pricing_status_text
1763: FROM qp_int_lines line
1764: , oe_price_adjustments adj_prg
1765: , oe_price_adj_assocs ass
1766: , oe_price_adjustments adj
1767: , qp_int_lines getline
1768: WHERE prg_exist_flag = 'Y'
1762: 'INSERTED FOR CALCULATION' pricing_status_text
1763: FROM qp_int_lines line
1764: , oe_price_adjustments adj_prg
1765: , oe_price_adj_assocs ass
1766: , oe_price_adjustments adj
1767: , qp_int_lines getline
1768: WHERE prg_exist_flag = 'Y'
1769: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1770: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1787: AND getline.line_id = adj.line_id;
1788: --and getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE);
1789:
1790: CURSOR l_pbh_adj_exists_cur IS
1791: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1792: FROM
1793: qp_int_lines line,
1794: oe_price_adjustments adj
1795: WHERE adj.line_id = line.line_id
1790: CURSOR l_pbh_adj_exists_cur IS
1791: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1792: FROM
1793: qp_int_lines line,
1794: oe_price_adjustments adj
1795: WHERE adj.line_id = line.line_id
1796: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1797: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1798: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1813: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1814: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1815: QP_PREQ_PUB.G_YES))))
1816: UNION
1817: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N1) */ 'Y'
1818: FROM
1819: qp_int_lines line,
1820: oe_price_adjustments adj
1821: WHERE adj.header_id = line.line_id
1816: UNION
1817: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N1) */ 'Y'
1818: FROM
1819: qp_int_lines line,
1820: oe_price_adjustments adj
1821: WHERE adj.header_id = line.line_id
1822: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1823: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1824: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1840: QP_PREQ_PUB.G_YES))));
1841:
1842:
1843: CURSOR l_prg_adj_exists_cur IS
1844: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1845: FROM qp_int_lines line,
1846: oe_price_adjustments adj
1847: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1848: AND adj.line_id = line.line_id
1842:
1843: CURSOR l_prg_adj_exists_cur IS
1844: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1845: FROM qp_int_lines line,
1846: oe_price_adjustments adj
1847: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1848: AND adj.line_id = line.line_id
1849: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1850: AND adj.list_line_type_code = 'PRG'
1921: , NULL BREAK_UOM_CODE
1922: , NULL BREAK_UOM_CONTEXT
1923: , NULL BREAK_UOM_ATTRIBUTE
1924: , NULL PROCESS_CODE -- 3215497
1925: FROM qp_int_lines getline, oe_price_adjustments adj,
1926: qp_int_ldets ldet, oe_price_adj_assocs ass
1927: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1928: AND ldet.CREATED_FROM_LIST_LINE_TYPE = 'PRG'
1929: AND ass.price_adjustment_id = ldet.price_adjustment_id
4098: WHERE modifier_level_code = 'ORDER'
4099: AND applied_flag = 'YES' -- bug 6628324
4100: AND pricing_status_code = G_STATUS_NEW
4101: AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
4102: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4103: WHERE line.line_type_code = G_LINE_LEVEL
4104: AND line.line_id = adj.line_id
4105: AND adj.applied_flag = G_YES
4106: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4107: WHERE ldet.line_index = line.line_index
4108: AND ldet.pricing_status_code = G_STATUS_NEW
4109: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4110: AND ldet.applied_flag = G_YES))
4111: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4112: WHERE line.line_type_code = G_ORDER_LEVEL
4113: AND line.line_id = adj.header_id
4114: AND adj.applied_flag = G_YES
4115: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4140: WHERE modifier_level_code = 'ORDER'
4141: AND applied_flag = 'YES' -- bug 6628324
4142: AND pricing_status_code = G_STATUS_NEW
4143: AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
4144: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4145: WHERE line.line_type_code = G_LINE_LEVEL
4146: AND line.line_id = adj.line_id
4147: AND adj.applied_flag = G_YES
4148: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_int_ldets ldet
4149: WHERE ldet.line_index = line.line_index
4150: AND ldet.pricing_status_code = G_STATUS_NEW
4151: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4152: AND ldet.applied_flag = G_YES))
4153: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4154: WHERE line.line_type_code = G_ORDER_LEVEL
4155: AND line.line_id = adj.header_id
4156: AND adj.applied_flag = G_YES
4157: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_int_ldets ldet
10830: oldprg.updated_flag
10831: FROM qp_npreq_lines_tmp buyline
10832: , qp_event_phases ev
10833: , qp_pricing_phases ph
10834: , oe_price_adjustments oldprg
10835: , oe_price_adj_assocs oldrltd
10836: , oe_price_adjustments oldfgdis
10837: , qp_npreq_lines_tmp oldfreeline
10838: --where G_REQUEST_TYPE_CODE = 'ONT'
10832: , qp_event_phases ev
10833: , qp_pricing_phases ph
10834: , oe_price_adjustments oldprg
10835: , oe_price_adj_assocs oldrltd
10836: , oe_price_adjustments oldfgdis
10837: , qp_npreq_lines_tmp oldfreeline
10838: --where G_REQUEST_TYPE_CODE = 'ONT'
10839: --bug 3085453 handle pricing availability UI
10840: -- they pass reqtype ONT and insert adj into ldets
10881: oldprg.updated_flag
10882: FROM qp_npreq_lines_tmp buyline
10883: , qp_event_phases ev
10884: , qp_pricing_phases ph
10885: , oe_price_adjustments oldprg
10886: , oe_price_adj_assocs oldrltd
10887: , oe_price_adjustments oldfgdis
10888: , qp_npreq_lines_tmp oldfreeline
10889: --where G_REQUEST_TYPE_CODE = 'ONT'
10883: , qp_event_phases ev
10884: , qp_pricing_phases ph
10885: , oe_price_adjustments oldprg
10886: , oe_price_adj_assocs oldrltd
10887: , oe_price_adjustments oldfgdis
10888: , qp_npreq_lines_tmp oldfreeline
10889: --where G_REQUEST_TYPE_CODE = 'ONT'
10890: --bug 3085453 handle pricing availability UI
10891: -- they pass reqtype ONT and insert adj into ldets
10981: fgdis.list_line_id fgdis_list_line_id, prg.pricing_phase_id prg_phase_id,
10982: prg.price_adjustment_id prg_price_adj_id,
10983: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
10984: prg.updated_flag
10985: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
10986: oe_price_adj_assocs ass, oe_price_adjustments fgdis
10987: WHERE line.line_type_code = G_LINE_LEVEL
10988: AND prg.line_id = line.line_id
10989: AND prg.list_line_type_code = 'PRG'
10982: prg.price_adjustment_id prg_price_adj_id,
10983: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
10984: prg.updated_flag
10985: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
10986: oe_price_adj_assocs ass, oe_price_adjustments fgdis
10987: WHERE line.line_type_code = G_LINE_LEVEL
10988: AND prg.line_id = line.line_id
10989: AND prg.list_line_type_code = 'PRG'
10990: AND ass.price_adjustment_id = prg.price_adjustment_id
10994: fgdis.list_line_id fgdis_list_line_id, prg.pricing_phase_id prg_phase_id,
10995: prg.price_adjustment_id prg_price_adj_id,
10996: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
10997: prg.updated_flag
10998: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
10999: oe_price_adj_assocs ass, oe_price_adjustments fgdis
11000: WHERE line.line_type_code = G_ORDER_LEVEL
11001: AND prg.header_id = line.line_id
11002: AND prg.line_id IS NULL
10995: prg.price_adjustment_id prg_price_adj_id,
10996: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
10997: prg.updated_flag
10998: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
10999: oe_price_adj_assocs ass, oe_price_adjustments fgdis
11000: WHERE line.line_type_code = G_ORDER_LEVEL
11001: AND prg.header_id = line.line_id
11002: AND prg.line_id IS NULL
11003: AND prg.list_line_type_code = 'PRG'
11035: , oldprg.updated_flag
11036: FROM qp_npreq_lines_tmp buyline
11037: , qp_event_phases ev
11038: , qp_pricing_phases ph
11039: , oe_price_adjustments oldprg
11040: , oe_price_adj_assocs oldrltd
11041: , oe_price_adjustments oldfgdis
11042: -- where G_REQUEST_TYPE_CODE = 'ONT'
11043: --bug 3085453 handle pricing availability UI
11037: , qp_event_phases ev
11038: , qp_pricing_phases ph
11039: , oe_price_adjustments oldprg
11040: , oe_price_adj_assocs oldrltd
11041: , oe_price_adjustments oldfgdis
11042: -- where G_REQUEST_TYPE_CODE = 'ONT'
11043: --bug 3085453 handle pricing availability UI
11044: -- they pass reqtype ONT and insert adj into ldets
11045: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11497: AND oldfreeitem.context = G_ITEM_CONTEXT
11498: AND oldfreeitem.attribute = G_PRIC_ATTRIBUTE1;
11499:
11500: CURSOR l_updated_prg_fg_cur IS
11501: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
11502: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11503: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11504: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11505: --bug 3085453 handle pricing availability UI
11498: AND oldfreeitem.attribute = G_PRIC_ATTRIBUTE1;
11499:
11500: CURSOR l_updated_prg_fg_cur IS
11501: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
11502: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11503: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11504: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11505: --bug 3085453 handle pricing availability UI
11506: -- they pass reqtype ONT and insert adj into ldets
11516: AND rltd.line_index = ldet.line_index
11517: AND rltd.line_detail_index = ldet.line_detail_index
11518: AND rltd.pricing_status_code = G_STATUS_NEW
11519: UNION
11520: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
11521: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11522: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11523: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11524: --bug 3085453 handle pricing availability UI
11517: AND rltd.line_detail_index = ldet.line_detail_index
11518: AND rltd.pricing_status_code = G_STATUS_NEW
11519: UNION
11520: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
11521: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11522: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11523: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11524: --bug 3085453 handle pricing availability UI
11525: -- they pass reqtype ONT and insert adj into ldets
11637: FROM qp_npreq_lines_tmp line
11638: WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED,
11639: G_STATUS_UPDATED, G_STATUS_NEW, G_STATUS_GSA_VIOLATION)
11640: AND EXISTS
11641: (SELECT 'Y' FROM oe_price_adjustments adj
11642: WHERE line.line_type_code = G_LINE_LEVEL
11643: AND adj.line_id = line.line_id
11644: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11645: AND adj.updated_flag = G_YES
11643: AND adj.line_id = line.line_id
11644: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11645: AND adj.updated_flag = G_YES
11646: UNION
11647: SELECT 'Y' FROM oe_price_adjustments adj
11648: WHERE line.line_type_code = G_ORDER_LEVEL
11649: AND adj.header_id = line.line_id
11650: AND adj.line_id IS NULL
11651: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11681: IF l_debug = FND_API.G_TRUE THEN
11682: FOR cl IN
11683: (SELECT adj.list_line_id, adj.updated_flag, line.line_index,
11684: adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
11685: FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
11686: WHERE line.line_type_code = G_LINE_LEVEL
11687: AND adj.line_id = line.line_id
11688: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11689: UNION
11688: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11689: UNION
11690: SELECT adj.list_line_id, adj.updated_flag, line.line_index,
11691: adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
11692: FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
11693: WHERE line.line_type_code = G_ORDER_LEVEL
11694: AND adj.header_id = line.line_id
11695: AND adj.line_id IS NULL
11696: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT)