[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
4117: WHERE modifier_level_code = 'ORDER'
4118: AND applied_flag = 'YES' -- bug 6628324
4119: AND pricing_status_code = G_STATUS_NEW
4120: AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
4121: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4122: WHERE line.line_type_code = G_LINE_LEVEL
4123: AND line.line_id = adj.line_id
4124: AND adj.applied_flag = G_YES
4125: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4126: WHERE ldet.line_index = line.line_index
4127: AND ldet.pricing_status_code = G_STATUS_NEW
4128: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4129: AND ldet.applied_flag = G_YES))
4130: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4131: WHERE line.line_type_code = G_ORDER_LEVEL
4132: AND line.line_id = adj.header_id
4133: AND adj.applied_flag = G_YES
4134: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4159: WHERE modifier_level_code = 'ORDER'
4160: AND applied_flag = 'YES' -- bug 6628324
4161: AND pricing_status_code = G_STATUS_NEW
4162: AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
4163: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4164: WHERE line.line_type_code = G_LINE_LEVEL
4165: AND line.line_id = adj.line_id
4166: AND adj.applied_flag = G_YES
4167: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_int_ldets ldet
4168: WHERE ldet.line_index = line.line_index
4169: AND ldet.pricing_status_code = G_STATUS_NEW
4170: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4171: AND ldet.applied_flag = G_YES))
4172: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4173: WHERE line.line_type_code = G_ORDER_LEVEL
4174: AND line.line_id = adj.header_id
4175: AND adj.applied_flag = G_YES
4176: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_int_ldets ldet
10909: oldprg.updated_flag
10910: FROM qp_npreq_lines_tmp buyline
10911: , qp_event_phases ev
10912: , qp_pricing_phases ph
10913: , oe_price_adjustments oldprg
10914: , oe_price_adj_assocs oldrltd
10915: , oe_price_adjustments oldfgdis
10916: , qp_npreq_lines_tmp oldfreeline
10917: --where G_REQUEST_TYPE_CODE = 'ONT'
10911: , qp_event_phases ev
10912: , qp_pricing_phases ph
10913: , oe_price_adjustments oldprg
10914: , oe_price_adj_assocs oldrltd
10915: , oe_price_adjustments oldfgdis
10916: , qp_npreq_lines_tmp oldfreeline
10917: --where G_REQUEST_TYPE_CODE = 'ONT'
10918: --bug 3085453 handle pricing availability UI
10919: -- they pass reqtype ONT and insert adj into ldets
10964: -- oldprg.updated_flag
10965: -- FROM qp_npreq_lines_tmp buyline
10966: -- , qp_event_phases ev
10967: -- , qp_pricing_phases ph
10968: -- , oe_price_adjustments oldprg
10969: -- , oe_price_adj_assocs oldrltd
10970: -- , oe_price_adjustments oldfgdis
10971: -- , qp_npreq_lines_tmp oldfreeline
10972: --where G_REQUEST_TYPE_CODE = 'ONT'
10966: -- , qp_event_phases ev
10967: -- , qp_pricing_phases ph
10968: -- , oe_price_adjustments oldprg
10969: -- , oe_price_adj_assocs oldrltd
10970: -- , oe_price_adjustments oldfgdis
10971: -- , qp_npreq_lines_tmp oldfreeline
10972: --where G_REQUEST_TYPE_CODE = 'ONT'
10973: --bug 3085453 handle pricing availability UI
10974: -- they pass reqtype ONT and insert adj into ldets
11067: fgdis.list_line_id fgdis_list_line_id, prg.pricing_phase_id prg_phase_id,
11068: prg.price_adjustment_id prg_price_adj_id,
11069: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
11070: prg.updated_flag
11071: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
11072: oe_price_adj_assocs ass, oe_price_adjustments fgdis
11073: WHERE line.line_type_code = G_LINE_LEVEL
11074: AND prg.line_id = line.line_id
11075: AND prg.list_line_type_code = 'PRG'
11068: prg.price_adjustment_id prg_price_adj_id,
11069: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
11070: prg.updated_flag
11071: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
11072: oe_price_adj_assocs ass, oe_price_adjustments fgdis
11073: WHERE line.line_type_code = G_LINE_LEVEL
11074: AND prg.line_id = line.line_id
11075: AND prg.list_line_type_code = 'PRG'
11076: AND ass.price_adjustment_id = prg.price_adjustment_id
11080: fgdis.list_line_id fgdis_list_line_id, prg.pricing_phase_id prg_phase_id,
11081: prg.price_adjustment_id prg_price_adj_id,
11082: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
11083: prg.updated_flag
11084: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
11085: oe_price_adj_assocs ass, oe_price_adjustments fgdis
11086: WHERE line.line_type_code = G_ORDER_LEVEL
11087: AND prg.header_id = line.line_id
11088: AND prg.line_id IS NULL
11081: prg.price_adjustment_id prg_price_adj_id,
11082: fgdis.price_adjustment_id fg_price_adj_id, fgdis.line_id fg_line_id,
11083: prg.updated_flag
11084: FROM qp_npreq_lines_tmp line, oe_price_adjustments prg,
11085: oe_price_adj_assocs ass, oe_price_adjustments fgdis
11086: WHERE line.line_type_code = G_ORDER_LEVEL
11087: AND prg.header_id = line.line_id
11088: AND prg.line_id IS NULL
11089: AND prg.list_line_type_code = 'PRG'
11122: , oldprg.updated_flag
11123: FROM qp_npreq_lines_tmp buyline
11124: , qp_event_phases ev
11125: , qp_pricing_phases ph
11126: , oe_price_adjustments oldprg
11127: , oe_price_adj_assocs oldrltd
11128: , oe_price_adjustments oldfgdis
11129: -- where G_REQUEST_TYPE_CODE = 'ONT'
11130: --bug 3085453 handle pricing availability UI
11124: , qp_event_phases ev
11125: , qp_pricing_phases ph
11126: , oe_price_adjustments oldprg
11127: , oe_price_adj_assocs oldrltd
11128: , oe_price_adjustments oldfgdis
11129: -- where G_REQUEST_TYPE_CODE = 'ONT'
11130: --bug 3085453 handle pricing availability UI
11131: -- they pass reqtype ONT and insert adj into ldets
11132: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11633: AND oldfreeitem.context = G_ITEM_CONTEXT
11634: AND oldfreeitem.attribute = G_PRIC_ATTRIBUTE1;
11635:
11636: CURSOR l_updated_prg_fg_cur IS
11637: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
11638: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11639: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11640: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11641: --bug 3085453 handle pricing availability UI
11634: AND oldfreeitem.attribute = G_PRIC_ATTRIBUTE1;
11635:
11636: CURSOR l_updated_prg_fg_cur IS
11637: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
11638: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11639: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11640: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11641: --bug 3085453 handle pricing availability UI
11642: -- they pass reqtype ONT and insert adj into ldets
11652: AND rltd.line_index = ldet.line_index
11653: AND rltd.line_detail_index = ldet.line_detail_index
11654: AND rltd.pricing_status_code = G_STATUS_NEW
11655: UNION
11656: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
11657: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11658: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11659: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11660: --bug 3085453 handle pricing availability UI
11653: AND rltd.line_detail_index = ldet.line_detail_index
11654: AND rltd.pricing_status_code = G_STATUS_NEW
11655: UNION
11656: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
11657: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11658: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11659: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11660: --bug 3085453 handle pricing availability UI
11661: -- they pass reqtype ONT and insert adj into ldets
11773: FROM qp_npreq_lines_tmp line
11774: WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED,
11775: G_STATUS_UPDATED, G_STATUS_NEW, G_STATUS_GSA_VIOLATION)
11776: AND EXISTS
11777: (SELECT 'Y' FROM oe_price_adjustments adj
11778: WHERE line.line_type_code = G_LINE_LEVEL
11779: AND adj.line_id = line.line_id
11780: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11781: AND adj.updated_flag = G_YES
11779: AND adj.line_id = line.line_id
11780: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11781: AND adj.updated_flag = G_YES
11782: UNION
11783: SELECT 'Y' FROM oe_price_adjustments adj
11784: WHERE line.line_type_code = G_ORDER_LEVEL
11785: AND adj.header_id = line.line_id
11786: AND adj.line_id IS NULL
11787: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11815: END IF;
11816:
11817: IF l_debug = FND_API.G_TRUE THEN
11818: FOR cl IN
11819: (SELECT adj.list_line_id, adj.updated_flag, line.line_index, --removed index oe_price_adjustments_n1 smbalara 14393164
11820: adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
11821: FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
11822: WHERE line.line_type_code = G_LINE_LEVEL
11823: AND adj.line_id = line.line_id
11817: IF l_debug = FND_API.G_TRUE THEN
11818: FOR cl IN
11819: (SELECT adj.list_line_id, adj.updated_flag, line.line_index, --removed index oe_price_adjustments_n1 smbalara 14393164
11820: adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
11821: FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
11822: WHERE line.line_type_code = G_LINE_LEVEL
11823: AND adj.line_id = line.line_id
11824: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11825: UNION
11824: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11825: UNION
11826: SELECT adj.list_line_id, adj.updated_flag, line.line_index,
11827: adj.pricing_phase_id, adj.price_adjustment_id, adj.line_id
11828: FROM qp_npreq_lines_tmp line, oe_price_adjustments adj
11829: WHERE line.line_type_code = G_ORDER_LEVEL
11830: AND adj.header_id = line.line_id
11831: AND adj.line_id IS NULL
11832: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT)