[Home] [Help]
1974: l_Line_Rec OE_Order_Pub.Line_Rec_Type;
1975: line_Tbl_Index pls_integer;
1976: Cursor prg_lines is
1977: select adj1.line_id prg_line_id, assoc.rltd_price_adj_id
1978: from oe_price_adjustments adj1,
1979: oe_price_adj_assocs assoc,
1980: oe_price_adjustments adj2
1981: where adj1.price_adjustment_id = assoc.rltd_price_adj_id AND
1982: assoc.price_adjustment_id = adj2.price_adjustment_id AND
1976: Cursor prg_lines is
1977: select adj1.line_id prg_line_id, assoc.rltd_price_adj_id
1978: from oe_price_adjustments adj1,
1979: oe_price_adj_assocs assoc,
1980: oe_price_adjustments adj2
1981: where adj1.price_adjustment_id = assoc.rltd_price_adj_id AND
1982: assoc.price_adjustment_id = adj2.price_adjustment_id AND
1983: adj2.list_line_type_code = 'PRG' AND
1984: adj2.line_id = p_line_id;
2076: l_org_id Number:= OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
2077: /*l_org_id NUMBER := FND_PROFILE.Value('OE_ORGANIZATION_ID');*/
2078: l_ordered_item varchar2(300);
2079: cursor adj_cur is
2080: select modified_from from oe_price_adjustments
2081: where line_id=px_line_rec.line_id
2082: and list_line_type_code='IUE';
2083: --
2084: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3571: (QP_PREQ_PUB.G_STATUS_UPDATED,
3572: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
3573: )
3574: THEN
3575: DELETE FROM OE_PRICE_ADJUSTMENTS
3576: WHERE HEADER_ID=oe_order_pub.g_hdr.header_id
3577: AND LINE_ID IS NULL
3578: AND LIST_LINE_TYPE_CODE NOT IN ('TAX')
3579: AND NVL(UPDATED_FLAG, 'N')='N'
3644: BEGIN
3645:
3646: l_line_type_code := 'LINE';
3647: For one_line in updated_order_lines(l_line_type_code) loop
3648: DELETE /*+ index (adj oe_price_adjustments_n2) */
3649: FROM OE_PRICE_ADJUSTMENTS adj
3650: WHERE --HEADER_ID=oe_order_pub.g_hdr.header_id
3651: LINE_ID = one_line.line_id
3652: AND LIST_LINE_TYPE_CODE NOT IN ('TAX','IUE') --bug 2858712
3645:
3646: l_line_type_code := 'LINE';
3647: For one_line in updated_order_lines(l_line_type_code) loop
3648: DELETE /*+ index (adj oe_price_adjustments_n2) */
3649: FROM OE_PRICE_ADJUSTMENTS adj
3650: WHERE --HEADER_ID=oe_order_pub.g_hdr.header_id
3651: LINE_ID = one_line.line_id
3652: AND LIST_LINE_TYPE_CODE NOT IN ('TAX','IUE') --bug 2858712
3653: AND NVL(UPDATED_FLAG, 'N')='N'
3727: and process_status <> 'NOT_VALID'
3728: and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED,
3729: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION);
3730:
3731: DELETE FROM OE_PRICE_ADJUSTMENTS
3732: WHERE HEADER_ID=oe_order_pub.g_hdr.header_id
3733: AND LINE_ID=p_line_id
3734: AND NVL(UPDATED_FLAG, 'N')='N'
3735: AND PRICING_PHASE_ID IN (select b.pricing_phase_id
3805: BEGIN
3806:
3807: SELECT price_adjustment_id
3808: INTO l_price_adjustment_id
3809: FROM oe_price_adjustments
3810: WHERE price_adjustment_id = p_price_adjustment_id
3811: FOR UPDATE NOWAIT;
3812:
3813: IF l_debug_level > 0 Then
3837:
3838: END;
3839: --bug 5497035
3840:
3841: UPDATE OE_PRICE_ADJUSTMENTS adj
3842: SET ( operand
3843: , operand_per_pqty
3844: , adjusted_amount
3845: , adjusted_amount_per_pqty
3957: i number;
3958: l_pricing_events varchar2(40) := p_pricing_events||',';
3959: l_stmt number:=0;
3960: cursor del_attribs1 Is
3961: Select /*+ ORDERED USE_NL(ADJ LDETS) index(QPLINES QP_PREQ_LINES_TMP_N2) index(ADJ OE_PRICE_ADJUSTMENTS_N2)*/
3962: adj.price_adjustment_id, ldets.line_detail_index
3963: From QP_PREQ_LINES_TMP QPLINES
3964: ,OE_PRICE_ADJUSTMENTS ADJ
3965: ,QP_LDETS_V LDETS
3960: cursor del_attribs1 Is
3961: Select /*+ ORDERED USE_NL(ADJ LDETS) index(QPLINES QP_PREQ_LINES_TMP_N2) index(ADJ OE_PRICE_ADJUSTMENTS_N2)*/
3962: adj.price_adjustment_id, ldets.line_detail_index
3963: From QP_PREQ_LINES_TMP QPLINES
3964: ,OE_PRICE_ADJUSTMENTS ADJ
3965: ,QP_LDETS_V LDETS
3966: Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
3967: AND LDETS.LINE_INDEX = QPLINES.LINE_INDEX
3968: AND ADJ.PRICING_PHASE_ID in (select pricing_phase_id from qp_event_phases
3978:
3979: cursor del_attribs2(p_hdr_line_index in number, p_hdr_line_id in number) Is
3980: Select ADJ.PRICE_ADJUSTMENT_ID, LDETS.LINE_DETAIL_INDEX
3981: From
3982: OE_PRICE_ADJUSTMENTS ADJ
3983: ,QP_LDETS_V LDETS
3984: Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
3985: AND LDETS.LINE_INDEX = p_hdr_line_index
3986: AND ADJ.PRICING_PHASE_ID in (select pricing_phase_id from qp_event_phases
4027: ldets.price_break_type_code,
4028: ldets.proration_type_code,
4029: ldets.include_on_returns_flag,
4030: adj.lock_control + 1 adj_lock_control
4031: from OE_PRICE_ADJUSTMENTS adj
4032: , QP_LDETS_v ldets
4033: , QP_PREQ_LINES_TMP lines
4034: WHERE
4035: adj.header_id=oe_order_pub.g_hdr.header_id
4073: ldets.price_break_type_code,
4074: ldets.proration_type_code,
4075: ldets.include_on_returns_flag,
4076: adj.lock_control + 1 adj_lock_control
4077: from OE_PRICE_ADJUSTMENTS adj
4078: , QP_LDETS_v ldets
4079: , QP_PREQ_LINES_TMP lines
4080: WHERE
4081: adj.header_id=oe_order_pub.g_hdr.header_id
4254: l_stmt:=7;
4255: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
4256: OE_ADV_PRICE_PVT.Insert_Adj(p_header_id);
4257: ELSE
4258: INSERT INTO OE_PRICE_ADJUSTMENTS
4259: ( PRICE_ADJUSTMENT_ID
4260: , CREATION_DATE
4261: , CREATED_BY
4262: , LAST_UPDATE_DATE
4357: , ADJUSTED_AMOUNT_PER_PQTY
4358: , LOCK_CONTROL
4359: )
4360: ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
4361: oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
4362: , sysdate --p_Line_Adj_rec.creation_date
4363: , fnd_global.user_id --p_Line_Adj_rec.created_by
4364: , sysdate --p_Line_Adj_rec.last_update_date
4365: , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
4518: ,ADJ.LINE_ID
4519: ,RADJ.PRICE_ADJUSTMENT_ID
4520: ,1
4521: FROM QP_PREQ_RLTD_LINES_TMP RLTD ,
4522: OE_PRICE_ADJUSTMENTS ADJ,
4523: OE_PRICE_ADJUSTMENTS RADJ,
4524: QP_PREQ_LINES_TMP QPL
4525: WHERE
4526: ADJ.HEADER_ID = RLTD.LINE_INDEX AND
4519: ,RADJ.PRICE_ADJUSTMENT_ID
4520: ,1
4521: FROM QP_PREQ_RLTD_LINES_TMP RLTD ,
4522: OE_PRICE_ADJUSTMENTS ADJ,
4523: OE_PRICE_ADJUSTMENTS RADJ,
4524: QP_PREQ_LINES_TMP QPL
4525: WHERE
4526: ADJ.HEADER_ID = RLTD.LINE_INDEX AND
4527: ADJ.LIST_LINE_ID = RLTD.LIST_LINE_ID AND
4566: ,1
4567: FROM
4568: QP_PREQ_RLTD_LINES_TMP RLTD,
4569: QP_PREQ_LINES_TMP QPL,
4570: OE_PRICE_ADJUSTMENTS ADJ,
4571: OE_PRICE_ADJUSTMENTS RADJ
4572: WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX AND
4573: QPL.LINE_ID = ADJ.LINE_ID AND
4574: QPL.LINE_TYPE_CODE = 'LINE' AND
4567: FROM
4568: QP_PREQ_RLTD_LINES_TMP RLTD,
4569: QP_PREQ_LINES_TMP QPL,
4570: OE_PRICE_ADJUSTMENTS ADJ,
4571: OE_PRICE_ADJUSTMENTS RADJ
4572: WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX AND
4573: QPL.LINE_ID = ADJ.LINE_ID AND
4574: QPL.LINE_TYPE_CODE = 'LINE' AND
4575: QPL.PROCESS_STATUS <> 'NOT_VALID' AND
4608: , qplat.setup_value_from
4609: , qplat.setup_value_to
4610: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
4611: -- , QP_PREQ_LDETS_TMP LDETS
4612: -- , OE_PRICE_ADJUSTMENTS ADJ
4613: WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
4614: --AND QPLAT.LINE_INDEX = ADJ.HEADER_ID + nvl(ADJ.LINE_ID, 0)
4615: --AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
4616: AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
4670: ,OE_PRICE_ADJ_ATTRIBS_S.nextval
4671: ,1
4672: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
4673: -- , QP_LDETS_v LDETS
4674: -- , OE_PRICE_ADJUSTMENTS ADJ
4675: WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
4676: --AND LDETS.LINE_INDEX = ADJ.HEADER_ID + ADJ.LINE_ID
4677: --AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
4678: AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
4716: , qplat.setup_value_from
4717: , qplat.setup_value_to
4718: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
4719: -- , QP_LDETS_v LDETS
4720: -- , OE_PRICE_ADJUSTMENTS ADJ
4721: WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
4722: --AND QPLAT.LINE_INDEX = ADJ.HEADER_ID + nvl(ADJ.LINE_ID, 0)
4723: --AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
4724: AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
4776: ,OE_PRICE_ADJ_ATTRIBS_S.nextval
4777: ,1
4778: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
4779: --, QP_LDETS_v LDETS
4780: --, OE_PRICE_ADJUSTMENTS ADJ
4781: --, QP_PREQ_LINES_TMP QPL
4782: WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
4783: --AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
4784: AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
4806: /*DELETE FROM Oe_Price_Adj_Attribs adjattrs
4807: WHERE exists
4808: (Select ADJ.PRICE_ADJUSTMENT_ID
4809: From QP_LDETS_V LDETS
4810: ,OE_PRICE_ADJUSTMENTS ADJ
4811: ,QP_PREQ_LINES_TMP QPLINES
4812: Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
4813: AND LDETS.LINE_INDEX = QPLINES.LINE_INDEX
4814: AND QPLINES.LINE_TYPE_CODE = 'ORDER'
4819: /*DELETE FROM Oe_Price_Adj_Attribs adjattrs
4820: WHERE exists
4821: (Select ADJ.PRICE_ADJUSTMENT_ID
4822: From QP_LDETS_V LDETS
4823: ,OE_PRICE_ADJUSTMENTS ADJ
4824: ,QP_PREQ_LINES_TMP QPLINES
4825: Where LDETS.LIST_LINE_ID = ADJ.LIST_LINE_ID
4826: AND LDETS.LINE_INDEX = QPLINES.LINE_INDEX
4827: AND QPLINES.LINE_ID = ADJ.LINE_ID
4870: ,OE_PRICE_ADJ_ATTRIBS_S.nextval
4871: ,1
4872: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
4873: , QP_LDETS_v LDETS
4874: , OE_PRICE_ADJUSTMENTS ADJ
4875: WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
4876: -- AND LDETS.LINE_INDEX = ADJ.HEADER_ID
4877: AND ADJ.LINE_ID IS NULL
4878: AND ADJ.HEADER_ID = oe_order_pub.g_hdr.header_id
4927: ,OE_PRICE_ADJ_ATTRIBS_S.nextval
4928: ,1
4929: FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
4930: , QP_LDETS_v LDETS
4931: , OE_PRICE_ADJUSTMENTS ADJ
4932: , QP_PREQ_LINES_TMP QPLINE
4933: WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
4934: -- AND QPLAT.LINE_INDEX = ADJ.HEADER_ID+nvl(ADJ.LINE_ID,0)
4935: AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX