[Home] [Help]
2415: , x_return_status_text
2416: , l_LIST_LINE_ID
2417: , l_RLTD_LIST_LINE_ID
2418: , l_pricing_status_text);
2419: /* delete from qp_npreq_rltd_lines_tmp
2420: where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2421: and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
2422: and pricing_status_text = G_CALC_INSERT;
2423: */ --need to delete them later
2739: --looks at to evaluate the breaks
2740: --This procedure will be used in the performance code path when
2741: --the public API is called by non-OM applications
2742: -- Ravi -- Added code to update the line detail type code which will be used
2743: -- in l_calculate_cur to not fetch the child lines as they are already in the qp_npreq_rltd_lines_tmp table
2744: -- Bug was adjustment amt was being calculated for child lines where it is not satisfied also
2745: PROCEDURE Update_passed_in_pbh(x_return_status OUT NOCOPY VARCHAR2,
2746: x_return_status_text OUT NOCOPY VARCHAR2) IS
2747:
2750: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2751:
2752: --when any changes are made to this, the similar updated stmt
2753: --in Update_Related_Line_Info procedure in this file also needs to be changed
2754: UPDATE qp_npreq_rltd_lines_tmp rltd
2755: SET (setup_value_from,
2756: setup_value_to,
2757: relationship_type_detail,
2758: list_line_id,
2806:
2807: UPDATE qp_npreq_ldets_tmp
2808: SET line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
2809: WHERE line_detail_index IN (SELECT related_line_detail_index
2810: FROM qp_npreq_rltd_lines_tmp
2811: WHERE relationship_type_code = G_PBH_LINE
2812: AND pricing_status_code = G_STATUS_NEW);
2813:
2814: IF (l_debug = FND_API.G_TRUE) THEN
3129: AND ldet.override_flag = G_YES
3130: AND ldet.pricing_group_sequence IS NULL --only return null bucket manual modifiers
3131: AND ldet.line_detail_index NOT IN
3132: (SELECT rltd.related_line_detail_index
3133: FROM qp_npreq_rltd_lines_tmp rltd
3134: WHERE rltd.relationship_type_code = G_PBH_LINE
3135: AND rltd.pricing_status_code = G_STATUS_NEW)
3136: --ORDER BY ldet.calculation_code,ldet.created_from_list_line_type,ldet.applied_flag desc;
3137: --ORDER BY ldet.created_from_list_line_type, precedence desc , ldet.applied_flag desc;
4214: pricing_group_sequence,
4215: setup_value_from,
4216: setup_value_to,
4217: qualifier_value
4218: FROM qp_npreq_rltd_lines_tmp
4219: WHERE pricing_status_code = G_STATUS_NEW;
4220:
4221: CURSOR l_net_amount_flag_cur (p_list_line_id NUMBER) IS
4222: SELECT net_amount_flag
7260: AND nvl(ldet.created_from_list_type_code, 'NULL') NOT IN
7261: (G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
7262: AND ldet.line_detail_index NOT IN
7263: (SELECT rltd.related_line_detail_index
7264: FROM qp_npreq_rltd_lines_tmp rltd
7265: WHERE rltd.pricing_status_code = G_STATUS_NEW
7266: AND rltd.relationship_type_code = G_PBH_LINE)
7267: -- next 4 conditions added for 3435240
7268: AND line.line_type_code = G_LINE_LEVEL
7322: AND nvl(ldet.created_from_list_type_code, 'NULL') NOT IN
7323: (G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
7324: AND ldet.line_detail_index NOT IN
7325: (SELECT rltd.related_line_detail_index
7326: FROM qp_npreq_rltd_lines_tmp rltd
7327: WHERE rltd.pricing_status_code = G_STATUS_NEW
7328: AND rltd.relationship_type_code = G_PBH_LINE)
7329: -- next 4 conditions added for 3435240
7330: AND line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
9361: rltd.related_line_detail_index,
9362: rltd.list_line_id,
9363: rltd.related_list_line_id,
9364: rltd.pricing_status_code
9365: FROM qp_npreq_rltd_lines_tmp rltd
9366: WHERE rltd.relationship_type_code = G_PBH_LINE;
9367: BEGIN
9368: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
9369: IF l_debug = FND_API.G_TRUE
9380: END IF;
9381:
9382: --mark the engine passed relationships of manual PBH as deleted
9383: --if the parent PBH line is deleted
9384: UPDATE qp_npreq_rltd_lines_tmp SET pricing_status_code = G_STATUS_DELETED
9385: WHERE line_detail_index IN (SELECT line_detail_index
9386: FROM qp_npreq_ldets_tmp ldet
9387: WHERE (ldet.process_code = G_STATUS_DELETED
9388: OR ldet.pricing_status_code = G_STATUS_DELETED))
9394: --mark the engine returned manual child PBH lines as deleted
9395: UPDATE qp_npreq_ldets_tmp ldet SET pricing_status_code = G_STATUS_DELETED
9396: WHERE ldet.line_detail_index IN
9397: (SELECT rltd.related_line_detail_index
9398: FROM qp_npreq_rltd_lines_tmp rltd
9399: WHERE rltd.relationship_type_code = G_PBH_LINE
9400: AND rltd.pricing_status_code = G_STATUS_DELETED);
9401:
9402: IF l_debug = FND_API.G_TRUE THEN
9438: nvl(ldet.line_quantity,
9439: nvl(line.priced_quantity, line.line_quantity))
9440: qualifier_value
9441: FROM
9442: qp_npreq_rltd_lines_tmp rltd,
9443: qp_npreq_lines_tmp line,
9444: qp_npreq_ldets_tmp ldet,
9445: qp_npreq_ldets_tmp ldet_pbh,
9446: qp_pricing_attributes qpa
9466: operand_calculation_code,
9467: operand,
9468: pricing_group_sequence,
9469: qualifier_value
9470: FROM qp_npreq_rltd_lines_tmp rltd
9471: WHERE rltd.pricing_status_code = G_STATUS_NEW
9472: AND rltd.relationship_type_code = G_PBH_LINE;
9473:
9474: BEGIN
9492: END IF;
9493:
9494: --when any changes are made to this, the similar updated stmt
9495: --in update_passed_in_pbh procedure in this file also needs to be changed
9496: UPDATE qp_npreq_rltd_lines_tmp rltd
9497: SET (setup_value_from,
9498: setup_value_to,
9499: relationship_type_detail,
9500: list_line_id,
9728: -- or (ldet.updated_flag = G_YES
9729: -- and nvl(ldet.automatic_flag, G_NO) = G_NO
9730: -- and ldet.line_detail_index in
9731: -- (select rltd.related_line_detail_index
9732: -- from qp_npreq_rltd_lines_tmp rltd
9733: -- where rltd.relationship_type_code = G_PBH_LINE)));
9734:
9735: IF l_debug = FND_API.G_TRUE THEN
9736: QP_PREQ_GRP.ENGINE_DEBUG('PROCESS ADJUSTMENTS: Step1.5');
9984: IF l_debug = FND_API.G_TRUE THEN
9985: QP_PREQ_GRP.engine_debug('Begin UPDATE_QUALIFIER_VALUE routine');
9986:
9987: END IF;
9988: UPDATE qp_npreq_rltd_lines_tmp rltd
9989: SET rltd.qualifier_value = (SELECT
9990: decode(qpa.pricing_attribute,
9991: G_LINE_AMT_ATTRIBUTE,
9992: (nvl(line.priced_quantity, line.line_quantity)
10026: --[julin/pbperf] tuned to use QP_PREQ_LDETS_TMP_U1
10027: CURSOR l_child_break_cur IS
10028: SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_U1) */ldet.process_code,
10029: rltd.related_line_detail_index
10030: FROM qp_npreq_rltd_lines_tmp rltd,
10031: -- qp_npreq_ldets_tmp ldet1,
10032: qp_npreq_ldets_tmp ldet
10033: WHERE rltd.relationship_type_code = G_PBH_LINE
10034: AND rltd.pricing_status_code = G_STATUS_NEW
10146: ldet.operand_value,
10147: ldet.group_amount,
10148: line.parent_price
10149: FROM qp_npreq_lines_tmp line,
10150: qp_npreq_rltd_lines_tmp rltd,
10151: qp_npreq_ldets_tmp ldet,
10152: qp_pricing_attributes qpa,
10153: qp_npreq_line_attrs_tmp attr
10154: WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED, G_STATUS_UPDATED)
10512: rltd.related_line_index,
10513: line.unit_price,
10514: line.priced_quantity,
10515: line.priced_uom_code
10516: FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
10517: WHERE rltd.line_index IS NOT NULL
10518: AND rltd.related_line_index IS NOT NULL
10519: AND rltd.relationship_type_code = G_SERVICE_LINE
10520: AND line.line_index = rltd.line_index;
11016: FROM qp_npreq_lines_tmp buyline
11017: , qp_event_phases ev
11018: , qp_pricing_phases ph
11019: , qp_npreq_ldets_tmp oldprg
11020: , qp_npreq_rltd_lines_tmp oldrltd
11021: , qp_npreq_ldets_tmp oldfgdis
11022: , qp_npreq_lines_tmp oldfreeline
11023: --where G_REQUEST_TYPE_CODE <> 'ONT'
11024: --bug 3085453 handle pricing availability UI
11166: SELECT 1
11167: INTO l_fgdline_exist
11168: FROM dual
11169: WHERE EXISTS (SELECT 1
11170: FROM qp_npreq_rltd_lines_tmp oldrltd,
11171: qp_npreq_ldets_tmp oldprg,
11172: qp_event_phases ev
11173: WHERE oldrltd.relationship_type_code = G_GENERATED_LINE
11174: AND oldrltd.line_detail_index = oldprg.line_detail_index
11283: PROCEDURE Update_prg_pricing_status(x_return_status OUT NOCOPY VARCHAR2,
11284: x_return_status_text OUT NOCOPY VARCHAR2) IS
11285: CURSOR l_mark_prg_delete_cur IS
11286: SELECT rltd.line_detail_index, rltd.related_line_detail_index
11287: FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
11288: WHERE rltd.pricing_status_code = G_STATUS_NEW
11289: AND rltd.relationship_type_code = G_GENERATED_LINE
11290: AND line.line_index = rltd.related_line_index
11291: AND line.process_status IN (G_NOT_VALID, G_STATUS_DELETED); --bug 3126969
11480: newfgdis.operand_value || newfgdis.operand_calculation_code newfgdis_operand,
11481: oldfreeline.SERVICE_DURATION old_SERVICE_DURATION, -- service project
11482: oldfreeline.SERVICE_PERIOD old_SERVICE_PERIOD -- service project
11483: FROM qp_npreq_lines_tmp oldfreeline
11484: , qp_npreq_rltd_lines_tmp newrltd
11485: , qp_npreq_ldets_tmp newfgdis
11486: , qp_npreq_lines_tmp newfgline
11487: , qp_npreq_line_attrs_tmp oldfreeitem
11488: , qp_npreq_line_attrs_tmp newfgitem
11604: newfgdis.operand_value || newfgdis.operand_calculation_code newfgdis_operand,
11605: oldfreeline.SERVICE_DURATION old_SERVICE_DURATION, -- service project
11606: oldfreeline.SERVICE_PERIOD old_SERVICE_PERIOD -- service project
11607: FROM qp_npreq_lines_tmp oldfreeline
11608: , qp_npreq_rltd_lines_tmp newrltd
11609: , qp_npreq_ldets_tmp newfgdis
11610: , qp_npreq_lines_tmp newfgline
11611: , qp_npreq_line_attrs_tmp newfgitem
11612: , qp_npreq_line_attrs_tmp oldfreeitem
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
11643: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
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
11662: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11675: UNION
11676: -- hint added for 5575718
11677: SELECT /*+ ORDERED */ rltd.related_line_index
11678: FROM qp_npreq_lines_tmp buyline, qp_npreq_ldets_tmp prg,
11679: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11680: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11681: --bug 3085453 handle pricing availability UI
11682: -- they pass reqtype ONT and insert adj into ldets
11683: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11835: ||'prg listlineid '|| cl.list_line_id ||' updatedflag '|| cl.updated_flag
11836: ||' buylineid '|| cl.line_id ||' prgphaseid '|| cl.pricing_phase_id);
11837: FOR fg IN
11838: (SELECT rltd.related_line_index, ldet.created_from_list_line_id
11839: FROM qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11840: WHERE ldet.line_index = cl.line_index
11841: AND ldet.pricing_status_code = G_STATUS_NEW
11842: AND ldet.applied_flag = G_YES
11843: AND ldet.created_from_list_line_id = cl.list_line_id
12273: -- 3493716: added prg.line_detail_index to cursor
12274: CURSOR l_buyline_price_flag_cur IS
12275: SELECT /*+ ORDERED USE_NL(buyline prg dis)*/
12276: dis.created_from_list_line_id, prg.line_detail_index, buyline.price_flag, 'Y' is_ldet, dis.line_index
12277: FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp buyline
12278: , qp_npreq_ldets_tmp prg, qp_npreq_ldets_tmp dis
12279: WHERE rltd.pricing_status_code = G_STATUS_NEW
12280: AND rltd.relationship_type_code = G_GENERATED_LINE
12281: AND buyline.line_index = rltd.line_index
12297: G_BUYLINE_INDEXES_FOR_LINE_ID.DELETE;
12298:
12299: IF l_debug = FND_API.G_TRUE THEN
12300: FOR c1 IN (SELECT line_detail_index, related_line_detail_index, line_index
12301: FROM qp_npreq_rltd_lines_tmp WHERE pricing_status_code = G_STATUS_NEW
12302: AND relationship_type_code = G_GENERATED_LINE)
12303: LOOP
12304: IF l_debug = FND_API.G_TRUE THEN
12305: QP_PREQ_GRP.engine_debug('loop linedtl '|| c1.line_detail_index ||' rltddtl '
12353: G_buyline_list_line_id, G_buyline_price_flag;
12354: CLOSE l_buyline_price_flag_cur;
12355: UPDATE qp_npreq_ldets_tmp freegood_dis set buy_line_price_flag =
12356: (select buyline.price_flag
12357: from qp_npreq_rltd_lines_tmp rl, qp_npreq_lines_tmp buyline
12358: where rl.pricing_status_code = G_STATUS_NEW
12359: and rl.relationship_type_code = G_GENERATED_LINE
12360: and rl.related_line_detail_index = freegood_dis.line_detail_index
12361: and buyline.line_index = rl.line_index)
12363: and freegood_dis.applied_flag = G_YES
12364: and freegood_dis.created_from_list_line_type in (G_OTHER_ITEM_DISCOUNT,
12365: G_PROMO_GOODS_DISCOUNT)
12366: and freegood_dis.line_detail_index in (select rltd.related_line_detail_index
12367: from qp_npreq_rltd_lines_tmp rltd
12368: where rltd.pricing_status_code = G_STATUS_NEW
12369: and rltd.relationship_type_code = G_GENERATED_LINE
12370: and rltd.line_detail_index = freegood_dis.line_detail_index);
12371: */
14041: pricing_group_sequence,
14042: setup_value_from,
14043: setup_value_to,
14044: qualifier_value
14045: FROM qp_npreq_rltd_lines_tmp
14046: WHERE pricing_status_code = G_STATUS_NEW)
14047: LOOP
14048: IF l_debug = FND_API.G_TRUE THEN
14049: QP_PREQ_GRP.engine_debug('related records with status N '