[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (px_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE) Then
OE_Order_Pvt.Header
( p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_control_rec => p_control_rec
, p_x_header_rec => px_header_rec
, p_x_old_header_rec => lx_old_header_rec
, x_return_status => l_return_status);
SELECT concatenated_segments
INTO px_line_rec.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = px_line_rec.inventory_item_id
AND organization_id = l_org_id;
Procedure delete_attribs_for_iue(p_price_adjustment_id in number)
is
l_Line_Adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_line_adj_rec =>l_line_adj_rec,
p_line_adj_id => l_line_adj_rec.price_adjustment_id,
p_old_line_adj_rec =>l_line_adj_rec,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_ADV_PRICE_PVT.DELETE_ATTRIBS_FOR_IUE IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.DELETE_ATTRIBS_FOR_IUE' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ADV_PRICE_PVT.DELETE_ATTRIBS_FOR_IUE' ) ;
oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS' , 1 ) ;
DELETE FROM OE_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = p_price_adjustment_id;
SELECT ldets.line_detail_index,ldets.Line_index,ldets.related_item_id,lines.line_id
FROM QP_LDETS_V ldets,
QP_PREQ_LINES_TMP lines
WHERE ldets.list_line_Type_code /*created_from_list_line_type list_line_Type_code*/= 'IUE'
AND ldets.process_code IN (QP_PREQ_PUB.G_STATUS_NEW,
QP_PREQ_PUB.G_STATUS_UPDATED)
AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status <> 'NOT_VALID'
AND ldets.line_index=lines.line_index;
SELECT adj.line_id, adj.modified_from,adj.price_adjustment_id
FROM OE_PRICE_ADJUSTMENTS adj
WHERE HEADER_ID = oe_order_cache.g_header_rec.header_id
AND list_line_Type_code = 'IUE'
AND PRICING_PHASE_ID IN (select pricing_phase_id from qp_event_phases
where instr(p_pricing_events||',', pricing_event_code||',') >0)
AND LINE_ID IN (select line_id
from qp_preq_lines_tmp where
line_type_code='LINE'
and price_flag IN ('Y','P')
and process_status <> 'NOT_VALID'
and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED, QP_PREQ_PUB.G_STATUS_GSA_VIOLATION, QP_PREQ_PUB.G_STATUS_UNCHANGED))
/* AND list_line_id NOT IN (SELECT list_line_id
from qp_ldets_v where process_code
IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) and line_index=adj.line_id+oe_order_cache.g_header_rec.header_id)*/
AND list_line_id NOT IN (SELECT list_line_id
from qp_ldets_v ld, qp_preq_lines_tmp l
where ld.process_code
IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) and l.line_index = ld.line_index
and l.line_id = adj.line_id);
px_line_tbl(G_PRICE_LINE_ID_TBL(i.line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE PRICE_ADJUSTMENT_ID = i.price_adjustment_id;
delete_attribs_for_iue(i.price_adjustment_id);
px_line_tbl(G_PRICE_LINE_ID_TBL(i.line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
INSERT INTO OE_PRICE_ADJUSTMENTS
( PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HEADER_ID
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, AUTOMATIC_FLAG
, PERCENT
, LINE_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIER_MECHANISM_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, operand
, Arithmetic_operator
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, SPLIT_ACTION_CODE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, list_line_no
, source_system_code
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, rebate_transaction_reference
, rebate_payment_system_code
, redeemed_date
, redeemed_flag
, accrual_flag
, range_break_quantity
, accrual_conversion_rate
, pricing_group_sequence
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
-- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
ldets.price_adjustment_id
, sysdate --p_Line_Adj_rec.creation_date
, fnd_global.user_id --p_Line_Adj_rec.created_by
, sysdate --p_Line_Adj_rec.last_update_date
, fnd_global.user_id --p_Line_Adj_rec.last_updated_by
, fnd_global.login_id --p_Line_Adj_rec.last_update_login
, NULL --p_Line_Adj_rec.program_application_id
, NULL --p_Line_Adj_rec.program_id
, NULL --p_Line_Adj_rec.program_update_date
, NULL --p_Line_Adj_rec.request_id
, oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
, NULL --p_Line_Adj_rec.discount_id
, NULL --p_Line_Adj_rec.discount_line_id
, ldets.automatic_flag
, NULL --p_Line_Adj_rec.percent
, decode(ldets.modifier_level_code,'ORDER',NULL,i.line_id)
, NULL --p_Line_Adj_rec.context
, NULL --p_Line_Adj_rec.attribute1
, NULL --p_Line_Adj_rec.attribute2
, NULL --p_Line_Adj_rec.attribute3
, NULL --p_Line_Adj_rec.attribute4
, NULL --p_Line_Adj_rec.attribute5
, NULL --p_Line_Adj_rec.attribute6
, NULL --p_Line_Adj_rec.attribute7
, NULL --p_Line_Adj_rec.attribute8
, NULL --p_Line_Adj_rec.attribute9
, NULL --p_Line_Adj_rec.attribute10
, NULL --p_Line_Adj_rec.attribute11
, NULL --p_Line_Adj_rec.attribute12
, NULL --p_Line_Adj_rec.attribute13
, NULL --p_Line_Adj_rec.attribute14
, NULL --p_Line_Adj_rec.attribute15
, NULL --p_Line_Adj_rec.orig_sys_discount_ref
, ldets.LIST_HEADER_ID
, ldets.LIST_LINE_ID
, ldets.LIST_LINE_TYPE_CODE
, NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
, to_char(ldets.inventory_item_id)
, to_char(ldets.related_item_id)
, 'N' --p_Line_Adj_rec.UPDATED_FLAG
, ldets.override_flag
, ldets.APPLIED_FLAG
, NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
, NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
, NULL
, ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
, NULl --p_line_Adj_rec.COST_ID
, NULL --p_line_Adj_rec.TAX_CODE
, NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
, NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
, NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
, NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
, NULL --p_line_Adj_rec.INVOICED_FLAG
, NULL --p_line_Adj_rec.ESTIMATED_FLAG
, NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
, NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
, NULL
, ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
, ldets.CHARGE_TYPE_CODE
, ldets.CHARGE_SUBTYPE_CODE
, ldets.list_line_no
, qh.source_system_code
, ldets.benefit_qty
, ldets.benefit_uom_code
, NULL --p_Line_Adj_rec.print_on_invoice_flag
, ldets.expiration_date
, ldets.rebate_transaction_type_code
, NULL --p_Line_Adj_rec.rebate_transaction_reference
, NULL --p_Line_Adj_rec.rebate_payment_system_code
, NULL --p_Line_Adj_rec.redeemed_date
, NULL --p_Line_Adj_rec.redeemed_flag
, ldets.accrual_flag
, ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
, ldets.accrual_conversion_rate
, ldets.pricing_group_sequence
, ldets.modifier_level_code
, ldets.price_break_type_code
, ldets.substitution_attribute
, ldets.proration_type_code
, NULL --p_Line_Adj_rec.credit_or_charge_flag
, ldets.include_on_returns_flag
, NULL -- p_Line_Adj_rec.ac_context
, NULL -- p_Line_Adj_rec.ac_attribute1
, NULL -- p_Line_Adj_rec.ac_attribute2
, NULL -- p_Line_Adj_rec.ac_attribute3
, NULL -- p_Line_Adj_rec.ac_attribute4
, NULL -- p_Line_Adj_rec.ac_attribute5
, NULL -- p_Line_Adj_rec.ac_attribute6
, NULL -- p_Line_Adj_rec.ac_attribute7
, NULL -- p_Line_Adj_rec.ac_attribute8
, NULL -- p_Line_Adj_rec.ac_attribute9
, NULL -- p_Line_Adj_rec.ac_attribute10
, NULL -- p_Line_Adj_rec.ac_attribute11
, NULL -- p_Line_Adj_rec.ac_attribute12
, NULL -- p_Line_Adj_rec.ac_attribute13
, NULL -- p_Line_Adj_rec.ac_attribute14
, NULL -- p_Line_Adj_rec.ac_attribute15
, NULL
, NULL
, 1
FROM
QP_LDETS_v ldets
, QP_LIST_HEADERS_B QH
WHERE
ldets.line_detail_index = i.line_detail_index
and ldets.list_header_id=qh.list_header_id
AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
select price_adjustment_id,LIST_LINE_ID,PRICING_PHASE_ID ,
MODIFIER_LEVEL_CODE
from oe_price_adjustments where
line_id = p_line_id and LIST_LINE_TYPE_CODE = 'TSN';
select line_index,LINE_DETAIL_INDEX,LINE_DETAIL_TYPE_CODE,
CREATED_FROM_LIST_LINE_ID,CREATED_FROM_LIST_LINE_TYPE,
CREATED_FROM_LIST_TYPE_CODE,SUBSTITUTION_TYPE_CODE,
SUBSTITUTION_VALUE_FROM,SUBSTITUTION_VALUE_TO,PROCESSED_FLAG,
PRICING_STATUS_CODE,PRICING_PHASE_ID,APPLIED_FLAG,PROCESS_CODE,
UPDATED_FLAG
from qp_preq_ldets_tmp where
CREATED_FROM_LIST_LINE_TYPE = 'TSN';
select Line_index,substitution_attribute,substitution_value_to,
process_code
FROM QP_LDETS_V
WHERE list_line_type_code = 'TSN';
SELECT ldets.Line_index, ldets.line_detail_index, lines.line_id --bug 4190357 added line_detail_index
, ldets.substitution_attribute, ldets.substitution_value_to
,CHANGE_REASON_CODE,CHANGE_REASON_TEXT
,lines.line_type_code --bug 4190357
FROM QP_LDETS_V ldets, qp_preq_lines_tmp lines
WHERE ldets.list_line_type_code = 'TSN'
AND ldets.process_code IN (QP_PREQ_PUB.G_STATUS_NEW
, QP_PREQ_PUB.G_STATUS_UPDATED
)
AND ldets.Line_index = lines.Line_index
order by ldets.line_index ASC;
oe_debug_pub.add( 'UPDATED_FLAG='||L.UPDATED_FLAG , 1 ) ;
-- for header level term substitution, needs to update all lines
IF (i.line_id = l_header_id and i.line_type_code = 'ORDER') THEN
--bug 4190357
l_count := 0;
SELECT count(*)
INTO l_count
FROM wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = px_header_rec.ship_from_org_id
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh.ship_method_code = px_header_rec.shipping_method_code
AND wsh_org.enabled_flag = 'Y';
px_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
-- LOOP through all lines, update terms on the line
j := px_line_tbl.FIRST;
select count(*) into vcount from oe_price_adjustments where
line_id = px_line_tbl(j).line_id and
LIST_LINE_TYPE_CODE = 'TSN' and MODIFIER_LEVEL_CODE = 'LINE';
px_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
ELSE --for line level term substitution, need to update the line
If i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
px_line_tbl(G_PRICE_LINE_ID_TBL(i.line_id)).payment_term_id := i.Substitution_value_to;
px_line_tbl(G_PRICE_LINE_ID_TBL(i.line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
INSERT INTO OE_PRICE_ADJUSTMENTS
( PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HEADER_ID
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, AUTOMATIC_FLAG
, PERCENT
, LINE_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIER_MECHANISM_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, operand
, Arithmetic_operator
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, SPLIT_ACTION_CODE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, list_line_no
, source_system_code
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, rebate_transaction_reference
, rebate_payment_system_code
, redeemed_date
, redeemed_flag
, accrual_flag
, range_break_quantity
, accrual_conversion_rate
, pricing_group_sequence
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
-- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
ldets.price_adjustment_id
, sysdate --p_Line_Adj_rec.creation_date
, fnd_global.user_id --p_Line_Adj_rec.created_by
, sysdate --p_Line_Adj_rec.last_update_date
, fnd_global.user_id --p_Line_Adj_rec.last_updated_by
, fnd_global.login_id --p_Line_Adj_rec.last_update_login
, NULL --p_Line_Adj_rec.program_application_id
, NULL --p_Line_Adj_rec.program_id
, NULL --p_Line_Adj_rec.program_update_date
, NULL --p_Line_Adj_rec.request_id
, oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
, NULL --p_Line_Adj_rec.discount_id
, NULL --p_Line_Adj_rec.discount_line_id
, ldets.automatic_flag
, NULL --p_Line_Adj_rec.percent
, decode(ldets.modifier_level_code,'ORDER',NULL,i.line_id)
, NULL --p_Line_Adj_rec.context
, NULL --p_Line_Adj_rec.attribute1
, NULL --p_Line_Adj_rec.attribute2
, NULL --p_Line_Adj_rec.attribute3
, NULL --p_Line_Adj_rec.attribute4
, NULL --p_Line_Adj_rec.attribute5
, NULL --p_Line_Adj_rec.attribute6
, NULL --p_Line_Adj_rec.attribute7
, NULL --p_Line_Adj_rec.attribute8
, NULL --p_Line_Adj_rec.attribute9
, NULL --p_Line_Adj_rec.attribute10
, NULL --p_Line_Adj_rec.attribute11
, NULL --p_Line_Adj_rec.attribute12
, NULL --p_Line_Adj_rec.attribute13
, NULL --p_Line_Adj_rec.attribute14
, NULL --p_Line_Adj_rec.attribute15
, NULL --p_Line_Adj_rec.orig_sys_discount_ref
, ldets.LIST_HEADER_ID
, ldets.LIST_LINE_ID
, ldets.LIST_LINE_TYPE_CODE
, NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
, l_old_shipping_method_code
, i.Substitution_value_to
, 'N' --p_Line_Adj_rec.UPDATED_FLAG
, ldets.override_flag
, ldets.APPLIED_FLAG
, NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
, NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
, NULL
, ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
, NULl --p_line_Adj_rec.COST_ID
, NULL --p_line_Adj_rec.TAX_CODE
, NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
, NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
, NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
, NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
, NULL --p_line_Adj_rec.INVOICED_FLAG
, NULL --p_line_Adj_rec.ESTIMATED_FLAG
, NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
, NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
, NULL
, ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
, ldets.CHARGE_TYPE_CODE
, ldets.CHARGE_SUBTYPE_CODE
, ldets.list_line_no
, qh.source_system_code
, ldets.benefit_qty
, ldets.benefit_uom_code
, NULL --p_Line_Adj_rec.print_on_invoice_flag
, ldets.expiration_date
, ldets.rebate_transaction_type_code
, NULL --p_Line_Adj_rec.rebate_transaction_reference
, NULL --p_Line_Adj_rec.rebate_payment_system_code
, NULL --p_Line_Adj_rec.redeemed_date
, NULL --p_Line_Adj_rec.redeemed_flag
, ldets.accrual_flag
, ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
, ldets.accrual_conversion_rate
, ldets.pricing_group_sequence
, ldets.modifier_level_code
, ldets.price_break_type_code
, ldets.substitution_attribute
, ldets.proration_type_code
, NULL --p_Line_Adj_rec.credit_or_charge_flag
, ldets.include_on_returns_flag
, NULL -- p_Line_Adj_rec.ac_context
, NULL -- p_Line_Adj_rec.ac_attribute1
, NULL -- p_Line_Adj_rec.ac_attribute2
, NULL -- p_Line_Adj_rec.ac_attribute3
, NULL -- p_Line_Adj_rec.ac_attribute4
, NULL -- p_Line_Adj_rec.ac_attribute5
, NULL -- p_Line_Adj_rec.ac_attribute6
, NULL -- p_Line_Adj_rec.ac_attribute7
, NULL -- p_Line_Adj_rec.ac_attribute8
, NULL -- p_Line_Adj_rec.ac_attribute9
, NULL -- p_Line_Adj_rec.ac_attribute10
, NULL -- p_Line_Adj_rec.ac_attribute11
, NULL -- p_Line_Adj_rec.ac_attribute12
, NULL -- p_Line_Adj_rec.ac_attribute13
, NULL -- p_Line_Adj_rec.ac_attribute14
, NULL -- p_Line_Adj_rec.ac_attribute15
, NULL
, NULL
, 1
FROM
QP_LDETS_v ldets
, QP_LIST_HEADERS_B QH
WHERE
ldets.line_detail_index = i.line_detail_index
and ldets.list_header_id=qh.list_header_id
AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
IF (l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
begin
select nvl(sum(ordered_quantity),0),nvl(sum(pricing_quantity),0)
into l_tot_qty,l_tot_price_qty
from oe_order_lines
where split_from_line_id = l_line_rec.line_id
and header_id = l_line_rec.header_id;
SELECT attr.value_from
INTO l_line_rec.inventory_item_id
FROM qp_preq_line_attrs_tmp attr
WHERE attr.context = 'ITEM'
AND attr.attribute =
'PRICING_ATTRIBUTE1'
AND attr.line_index = p_line_index;
SELECT concatenated_segments
INTO l_line_rec.ordered_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_org_id;
SELECT QH.NAME, LDET.LIST_LINE_NO
INTO l_modifier
, l_list_line_no
FROM QP_PREQ_RLTD_LINES_TMP RLTD
,QP_LDETS_V LDET
, QP_LIST_HEADERS_TL QH
WHERE RLTD.RELATED_LINE_INDEX = p_line_index
AND RLTD.RELATIONSHIP_TYPE_CODE = 'GENERATED_LINE'
AND RLTD.LINE_DETAIL_INDEX = LDET.LINE_DETAIL_INDEX
AND LDET.LIST_HEADER_ID = QH.LIST_HEADER_ID AND ROWNUM=1;
SELECT ORGANIZATION_NAME
INTO l_org
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = l_org_id;
QP_UTIL_PUB.Update_Lines('MAKE_STATUS_INVALID',l_line_rec.line_id,
NULL,NULL);
ELSIF (l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
if nvl(l_line_rec.booked_flag, 'N') = 'Y' then
l_pricing_event := 'BATCH,BOOK';
oe_debug_pub.add( 'unexp error logging batch event for updated free goods line', 3) ;
oe_debug_pub.add( 'error logging batch event for update free goods line',3);
Procedure Delete_Prg(
px_line_rec IN OUT NOCOPY OE_Order_Pub.line_rec_type
)
IS
l_line_rec OE_Order_Pub.line_rec_type := px_line_rec;
oe_debug_pub.add( 'DELETE UNBOOKED ORDER' ) ;
l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
UPDATE QP_PREQ_LINES_TMP
SET PROCESS_STATUS='NOT_VALID'
WHERE LINE_ID = l_line_rec.line_id;
QP_UTIL_PUB.Update_Lines('MAKE_STATUS_INVALID',l_line_rec.line_id,
NULL,NULL);
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
End Delete_Prg;
select line_id
, line_index
, line_quantity
, line_uom_code
, unit_price
, adjusted_unit_price
, line_unit_price
, order_uom_selling_price
, process_status
, line_category
, priced_quantity
, price_list_header_id
, percent_price
, priced_uom_code
, price_request_code
From qp_preq_lines_tmp
where
--processed_code = 'ENGINE';
, QP_PREQ_PUB.G_STATUS_UPDATED
, QP_PREQ_PUB.G_STATUS_DELETED
, 'FREEGOOD'
);
If (i.Process_status = QP_PREQ_PUB.G_STATUS_DELETED) THEN
l_line_rec := px_line_tbl(px_price_line_id_tbl(i.line_id));
Delete_Prg(l_line_rec);
Elsif (i.Process_Status = QP_PREQ_PUB.G_STATUS_UPDATED
OR i.Process_Status = 'FREEGOOD') THEN
l_line_rec := px_line_tbl(px_price_line_id_tbl(i.line_id));
select nvl(sum(ordered_quantity + nvl(cancelled_quantity,0)),0),nvl(sum(pricing_quantity + nvl(cancelled_quantity,0)),0)
into l_tot_qty,l_tot_price_qty
from oe_order_lines
where split_from_line_id = l_line_rec.line_id
and header_id = l_line_rec.header_id;
oe_line_util.update_adjustment_flags(l_line_rec, l_line_rec);
Delete_Prg(l_line_rec);
UPDATE QP_PREQ_LINES_TMP
SET PRICED_QUANTITY = l_pricing_quantity
where line_id = l_line_rec.line_id;
QP_UTIL_PUB.Update_Lines('UPDATE_PRICED_QUANTITY',l_line_rec.line_id,null,l_pricing_quantity);
l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
UPDATE QP_PREQ_LINES_TMP
SET LINE_ID = l_line_rec.line_id
where line_index = i.line_index;
QP_UTIL_PUB.Update_Lines('UPDATE_LINE_ID', l_line_rec.line_id,
i.line_index, null);
SELECT rltd.line_index
INTO l_parent_line_index
FROM qp_preq_rltd_lines_tmp rltd
WHERE rltd.related_line_index = i.line_index
AND relationship_type_code = 'GENERATED_LINE' and rownum=1;
SELECT line_id
INTO l_parent_line_id
FROM qp_preq_lines_tmp
WHERE line_index = l_parent_line_index;
select set_name INTO l_line_rec.ship_set from oe_sets where set_id= l_buy_line_rec.ship_set_id ;
SELECT line_index, line_id, line_Type_code, hold_code
FROM QP_PREQ_LINES_TMP
WHERE HOLD_CODE IN (QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED);
SELECT ldets.line_index, ldets.limit_text, lines.line_id
FROM QP_LDETS_V ldets, qp_preq_lines_tmp lines
WHERE ldets.LIMIT_CODE IN (QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED, QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED )
AND ldets.line_index = lines.line_index;
select order_source_id, orig_sys_document_ref,
orig_sys_line_ref, orig_sys_shipment_ref,
change_sequence, source_document_type_id,
source_document_id, source_document_line_id
into l_order_source_id, l_orig_sys_document_ref,
l_orig_sys_line_ref, l_orig_sys_shipment_ref,
l_change_sequence, l_source_document_type_id,
l_source_document_id, l_source_document_line_id
from oe_order_lines_all
where line_id = l_line_id;
select oe_msg_request_id_s.nextval into g_request_id from dual;
OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
OE_MSG_PUB.DELETE_MSG(OE_MSG_PUB.COUNT_MSG);
select 1 into l_source_system_code from
oe_order_headers_all oh,
qp_list_headers qh,
-- qp_preq_lines_tmp lines,
qp_preq_ldets_tmp ldets
where qh.currency_code = oh.transactional_curr_code
and oh.header_id = l_header_id
-- and lines.line_index = ldets.line_index
and ldets.CREATED_FROM_LIST_HEADER_ID = qh.LIST_HEADER_ID
and qh.source_system_code = 'AMS'
and qh.active_flag = 'Y'
and rownum = 1;
procedure new_and_updated_notify is
l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
cursor insert_adj_cur is
select price_adjustment_id, ldets.automatic_flag auto_flag,line_id,
modifier_level_code, ldets.LIST_HEADER_ID list_header_id,
LIST_LINE_ID, LIST_LINE_TYPE_CODE,inventory_item_id,
substitution_value_to, related_item_id,process_code,
APPLIED_FLAG, override_flag,operand_calculation_code, operand_value,
lines.priced_quantity priced_quantity,lines.line_quantity lquantity,
adjustment_amount,pricing_phase_id,updated_flag,
order_qty_operand, order_qty_adj_amt,
CHARGE_TYPE_CODE, CHARGE_SUBTYPE_CODE,list_line_no,
source_system_code,benefit_qty, benefit_uom_code,
expiration_date, rebate_transaction_type_code,
accrual_flag, ldets.line_quantity line_quantity,accrual_conversion_rate,
pricing_group_sequence,print_on_invoice_flag,
price_break_type_code, substitution_attribute,
proration_type_code, include_on_returns_flag,lines.line_index line_index
from
QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
, QP_LIST_HEADERS_B QH
where
ldets.LIST_HEADER_ID = qh.list_header_id
AND ldets.process_code IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED)
AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status <> 'NOT_VALID'
AND ldets.line_index=lines.line_index
AND (nvl(ldets.automatic_flag,'N') = 'Y')
-- or
-- (ldets.list_line_type_code = 'FREIGHT_CHARGE')
AND ldets.created_from_list_type_code not in ('PRL','AGR')
AND ldets.list_line_type_code<>'PLL'
AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE');
oe_debug_pub.add('in new_and_updated_notify',1);
FOR l_insert IN insert_adj_cur LOOP
IF l_insert.modifier_level_code <> 'ORDER' THEN
l_Line_Adj_rec.line_id := l_insert.line_id;
l_Line_Adj_rec.price_adjustment_id := l_insert.price_adjustment_id;
l_Line_Adj_rec.last_update_date := sysdate;
l_Line_Adj_rec.last_updated_by := fnd_global.user_id;
l_Line_Adj_rec.last_update_login := fnd_global.user_id;
l_Line_Adj_rec.automatic_flag := l_insert.auto_flag;
l_Line_Adj_rec.LIST_HEADER_ID := l_insert.LIST_HEADER_ID;
l_Line_Adj_rec.LIST_LINE_ID := l_insert.LIST_LINE_ID;
l_Line_Adj_rec.LIST_LINE_TYPE_CODE := l_insert.LIST_LINE_TYPE_CODE;
If l_insert.LIST_LINE_TYPE_CODE = 'TSN' Then
l_Line_Adj_rec.modified_from := l_insert.substitution_attribute;
l_Line_Adj_rec.modified_to := l_insert.substitution_value_to;
Elsif l_insert.LIST_LINE_TYPE_CODE = 'IUE' Then
l_Line_Adj_rec.modified_from := to_char(l_insert.inventory_item_id);
l_Line_Adj_rec.modified_to := to_char(l_insert.related_item_id);
If l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
l_Line_Adj_rec.UPDATED_FLAG := 'N';
Elsif l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
l_Line_Adj_rec.UPDATED_FLAG := l_insert.updated_flag;
l_Line_Adj_rec.print_on_invoice_flag := l_insert.print_on_invoice_flag;
l_Line_Adj_rec.UPDATE_ALLOWED := l_insert.override_flag;
l_Line_Adj_rec.APPLIED_FLAG := l_insert.APPLIED_FLAG;
IF l_insert.operand_calculation_code = '%' or
l_insert.operand_calculation_code = 'LUMPSUM' then
l_Line_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value);
IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
l_Line_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*l_insert.priced_quantity/nvl(l_insert.lquantity,1));
ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
l_Line_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*nvl(l_insert.priced_quantity,l_insert.lquantity)/l_insert.lquantity);
l_Line_Adj_rec.arithmetic_operator := l_insert.operand_calculation_code;
l_Line_Adj_rec.ADJUSTED_AMOUNT := nvl(l_insert.order_qty_adj_amt, l_insert.adjustment_amount*nvl(l_insert.priced_quantity,1)/nvl(l_insert.lquantity,1));
l_Line_Adj_rec.pricing_phase_id := l_insert.pricing_phase_id;
l_Line_Adj_rec.CHARGE_TYPE_CODE := l_insert.CHARGE_TYPE_CODE;
l_Line_Adj_rec.CHARGE_SUBTYPE_CODE := l_insert.CHARGE_SUBTYPE_CODE;
l_Line_Adj_rec.list_line_no := l_insert.list_line_no;
l_Line_Adj_rec.source_system_code := l_insert.source_system_code;
l_Line_Adj_rec.benefit_qty := l_insert.benefit_qty;
l_Line_Adj_rec.benefit_uom_code := l_insert.benefit_uom_code;
l_Line_Adj_rec.expiration_date := l_insert.expiration_date;
l_Line_Adj_rec.rebate_transaction_type_code := l_insert.rebate_transaction_type_code;
l_Line_Adj_rec.accrual_flag := l_insert.accrual_flag;
l_Line_Adj_rec.range_break_quantity := l_insert.line_quantity;
l_Line_Adj_rec.accrual_conversion_rate := l_insert.accrual_conversion_rate;
l_Line_Adj_rec.pricing_group_sequence := l_insert.pricing_group_sequence;
l_Line_Adj_rec.modifier_level_code := l_insert.modifier_level_code;
l_Line_Adj_rec.price_break_type_code := l_insert.price_break_type_code;
l_Line_Adj_rec.substitution_attribute := l_insert.substitution_attribute;
l_Line_Adj_rec.proration_type_code := l_insert.proration_type_code;
l_Line_Adj_rec.include_on_returns_flag := l_insert.include_on_returns_flag;
l_Line_Adj_rec.OPERAND_PER_PQTY := l_insert.OPERAND_value;
l_Line_Adj_rec.ADJUSTED_AMOUNT_PER_PQTY := l_insert.adjustment_amount;
l_Line_Adj_rec.line_index := l_insert.line_index;
IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
l_Line_Adj_rec.db_flag := FND_API.G_FALSE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_old_line_adj_rec => NULL,
p_line_adj_rec =>l_line_adj_rec,
p_line_adj_id => l_line_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
l_Line_Adj_rec.db_flag := FND_API.G_TRUE;
l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_line_adj_rec =>l_line_adj_rec,
p_line_adj_id => l_line_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
l_Header_Adj_rec.price_adjustment_id := l_insert.price_adjustment_id;
l_Header_Adj_rec.last_update_date := sysdate;
l_Header_Adj_rec.last_updated_by := fnd_global.user_id;
l_Header_Adj_rec.last_update_login := fnd_global.user_id;
l_Header_Adj_rec.automatic_flag := l_insert.auto_flag;
l_Header_Adj_rec.LIST_HEADER_ID := l_insert.LIST_HEADER_ID;
l_Header_Adj_rec.LIST_LINE_ID := l_insert.LIST_LINE_ID;
l_Header_Adj_rec.LIST_LINE_TYPE_CODE := l_insert.LIST_LINE_TYPE_CODE;
If l_insert.LIST_LINE_TYPE_CODE = 'TSN' Then
l_Header_Adj_rec.modified_from := l_insert.substitution_attribute;
l_Header_Adj_rec.modified_to := l_insert.substitution_value_to;
Elsif l_insert.LIST_LINE_TYPE_CODE = 'IUE' Then
l_Header_Adj_rec.modified_from := to_char(l_insert.inventory_item_id);
l_Header_Adj_rec.modified_to := to_char(l_insert.related_item_id);
If l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
l_Header_Adj_rec.UPDATED_FLAG := 'N';
Elsif l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
l_Header_Adj_rec.UPDATED_FLAG := l_insert.updated_flag;
l_Header_Adj_rec.print_on_invoice_flag := l_insert.print_on_invoice_flag;
l_Header_Adj_rec.UPDATE_ALLOWED := l_insert.override_flag;
l_Header_Adj_rec.APPLIED_FLAG := l_insert.APPLIED_FLAG;
IF l_insert.operand_calculation_code = '%' or
l_insert.operand_calculation_code = 'LUMPSUM' then
l_Header_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value);
IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
l_Header_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*l_insert.priced_quantity/nvl(l_insert.lquantity,1));
ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
l_Header_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*nvl(l_insert.priced_quantity,l_insert.lquantity)/l_insert.lquantity);
l_Header_Adj_rec.arithmetic_operator := l_insert.operand_calculation_code;
l_Header_Adj_rec.ADJUSTED_AMOUNT := nvl(l_insert.order_qty_adj_amt, l_insert.adjustment_amount*nvl(l_insert.priced_quantity,1)/nvl(l_insert.lquantity,1));
l_Header_Adj_rec.pricing_phase_id := l_insert.pricing_phase_id;
l_Header_Adj_rec.CHARGE_TYPE_CODE := l_insert.CHARGE_TYPE_CODE;
l_Header_Adj_rec.CHARGE_SUBTYPE_CODE := l_insert.CHARGE_SUBTYPE_CODE;
l_Header_Adj_rec.list_line_no := l_insert.list_line_no;
l_Header_Adj_rec.source_system_code := l_insert.source_system_code;
l_Header_Adj_rec.benefit_qty := l_insert.benefit_qty;
l_Header_Adj_rec.benefit_uom_code := l_insert.benefit_uom_code;
l_Header_Adj_rec.expiration_date := l_insert.expiration_date;
l_Header_Adj_rec.rebate_transaction_type_code := l_insert.rebate_transaction_type_code;
l_Header_Adj_rec.accrual_flag := l_insert.accrual_flag;
l_Header_Adj_rec.range_break_quantity := l_insert.line_quantity;
l_Header_Adj_rec.accrual_conversion_rate := l_insert.accrual_conversion_rate;
l_Header_Adj_rec.pricing_group_sequence := l_insert.pricing_group_sequence;
l_Header_Adj_rec.modifier_level_code := l_insert.modifier_level_code;
l_Header_Adj_rec.price_break_type_code := l_insert.price_break_type_code;
l_Header_Adj_rec.substitution_attribute := l_insert.substitution_attribute;
l_Header_Adj_rec.proration_type_code := l_insert.proration_type_code;
l_Header_Adj_rec.include_on_returns_flag := l_insert.include_on_returns_flag;
l_Header_Adj_rec.OPERAND_PER_PQTY := l_insert.OPERAND_value;
l_Header_Adj_rec.ADJUSTED_AMOUNT_PER_PQTY := l_insert.adjustment_amount;
IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
l_Header_Adj_rec.db_flag := FND_API.G_FALSE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_old_hdr_adj_rec => NULL,
p_hdr_adj_rec =>l_header_adj_rec,
p_hdr_adj_id => l_header_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
l_Header_Adj_rec.db_flag := FND_API.G_TRUE;
l_Header_Adj_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_hdr_adj_rec =>l_header_adj_rec,
p_hdr_adj_id => l_header_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);
/*OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_line_adj_rec =>l_line_adj_rec,
p_line_adj_id => l_line_adj_rec.price_adjustment_id,
x_index => l_index,
x_return_status => l_return_status);*/
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY');
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY' ) ;
oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY' , 1 ) ;
Cursor updated_lines_prl IS
SELECT l.LINE_ID,
lines.PRICE_LIST_HEADER_ID,
l.price_list_id
FROM
QP_PREQ_LINES_TMP lines
,OE_ORDER_LINES l
WHERE lines.pricing_status_code IN
( QP_PREQ_GRP.G_STATUS_UPDATED
,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status <> 'NOT_VALID'
AND lines.line_type_code='LINE'
AND nvl(decode(lines.price_list_header_id,-9999,NULL,lines.price_list_header_id),0) <> nvl(l.price_list_id,0)
AND l.line_id = lines.line_id
AND l.ordered_quantity <> 0;
for i in updated_lines_prl loop
IF l_debug_level > 0 THEN
oe_debug_pub.add('Old Price List id : '||i.price_list_id);
px_line_tbl(G_PRICE_LINE_ID_TBL(i.line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
new_and_updated_notify;
Procedure Insert_Adj(p_header_id in Number default null)
IS
l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
oe_debug_pub.add( 'INSIDE OE_ADV_PRICE_PVT.INSERT_ADJ' ) ;
INSERT INTO OE_PRICE_ADJUSTMENTS
( PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HEADER_ID
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, AUTOMATIC_FLAG
, PERCENT
, LINE_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIER_MECHANISM_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, operand
, Arithmetic_operator
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, SPLIT_ACTION_CODE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, list_line_no
, source_system_code
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, rebate_transaction_reference
, rebate_payment_system_code
, redeemed_date
, redeemed_flag
, accrual_flag
, range_break_quantity
, accrual_conversion_rate
, pricing_group_sequence
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
-- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
ldets.price_adjustment_id
, sysdate --p_Line_Adj_rec.creation_date
, fnd_global.user_id --p_Line_Adj_rec.created_by
, sysdate --p_Line_Adj_rec.last_update_date
, fnd_global.user_id --p_Line_Adj_rec.last_updated_by
, fnd_global.login_id --p_Line_Adj_rec.last_update_login
, NULL --p_Line_Adj_rec.program_application_id
, NULL --p_Line_Adj_rec.program_id
, NULL --p_Line_Adj_rec.program_update_date
, NULL --p_Line_Adj_rec.request_id
, decode(p_header_id, NULL, oe_order_pub.g_hdr.header_id, p_header_id) --p_Line_Adj_rec.header_id
, NULL --p_Line_Adj_rec.discount_id
, NULL --p_Line_Adj_rec.discount_line_id
, ldets.automatic_flag
, NULL --p_Line_Adj_rec.percent
, decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id)
, NULL --p_Line_Adj_rec.context
, NULL --p_Line_Adj_rec.attribute1
, NULL --p_Line_Adj_rec.attribute2
, NULL --p_Line_Adj_rec.attribute3
, NULL --p_Line_Adj_rec.attribute4
, NULL --p_Line_Adj_rec.attribute5
, NULL --p_Line_Adj_rec.attribute6
, NULL --p_Line_Adj_rec.attribute7
, NULL --p_Line_Adj_rec.attribute8
, NULL --p_Line_Adj_rec.attribute9
, NULL --p_Line_Adj_rec.attribute10
, NULL --p_Line_Adj_rec.attribute11
, NULL --p_Line_Adj_rec.attribute12
, NULL --p_Line_Adj_rec.attribute13
, NULL --p_Line_Adj_rec.attribute14
, NULL --p_Line_Adj_rec.attribute15
-- Bug 7523118
, 'OE_PRICE_ADJUSTMENTS'||ldets.price_adjustment_id --p_Line_Adj_rec.orig_sys_discount_ref
, ldets.LIST_HEADER_ID
, ldets.LIST_LINE_ID
, ldets.LIST_LINE_TYPE_CODE
, NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
, decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
, 'N' --p_Line_Adj_rec.UPDATED_FLAG
, ldets.override_flag
, ldets.APPLIED_FLAG
, NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
, NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
, nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code,
'%', ldets.operand_value,
'LUMPSUM', ldets.operand_value,
ldets.operand_value*lines.priced_quantity/nvl(lines.line_quantity,1)))
, ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
, NULl --p_line_Adj_rec.COST_ID
, NULL --p_line_Adj_rec.TAX_CODE
, NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
, NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
, NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
, NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
, NULL --p_line_Adj_rec.INVOICED_FLAG
, NULL --p_line_Adj_rec.ESTIMATED_FLAG
, NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
, NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
, nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))
, ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
, ldets.CHARGE_TYPE_CODE
, ldets.CHARGE_SUBTYPE_CODE
, ldets.list_line_no
, qh.source_system_code
, ldets.benefit_qty
, ldets.benefit_uom_code
, NULL --p_Line_Adj_rec.print_on_invoice_flag
, ldets.expiration_date
, ldets.rebate_transaction_type_code
, NULL --p_Line_Adj_rec.rebate_transaction_reference
, NULL --p_Line_Adj_rec.rebate_payment_system_code
, NULL --p_Line_Adj_rec.redeemed_date
, NULL --p_Line_Adj_rec.redeemed_flag
, ldets.accrual_flag
, ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
, ldets.accrual_conversion_rate
, ldets.pricing_group_sequence
, ldets.modifier_level_code
, ldets.price_break_type_code
, ldets.substitution_attribute
, ldets.proration_type_code
, NULL --p_Line_Adj_rec.credit_or_charge_flag
, ldets.include_on_returns_flag
, NULL -- p_Line_Adj_rec.ac_context
, NULL -- p_Line_Adj_rec.ac_attribute1
, NULL -- p_Line_Adj_rec.ac_attribute2
, NULL -- p_Line_Adj_rec.ac_attribute3
, NULL -- p_Line_Adj_rec.ac_attribute4
, NULL -- p_Line_Adj_rec.ac_attribute5
, NULL -- p_Line_Adj_rec.ac_attribute6
, NULL -- p_Line_Adj_rec.ac_attribute7
, NULL -- p_Line_Adj_rec.ac_attribute8
, NULL -- p_Line_Adj_rec.ac_attribute9
, NULL -- p_Line_Adj_rec.ac_attribute10
, NULL -- p_Line_Adj_rec.ac_attribute11
, NULL -- p_Line_Adj_rec.ac_attribute12
, NULL -- p_Line_Adj_rec.ac_attribute13
, NULL -- p_Line_Adj_rec.ac_attribute14
, NULL -- p_Line_Adj_rec.ac_attribute15
, ldets.OPERAND_value
, ldets.adjustment_amount
, 1
FROM
QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
, QP_LIST_HEADERS_B QH
WHERE
ldets.list_header_id=qh.list_header_id
AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
AND lines.process_status <> 'NOT_VALID'
AND ldets.line_index=lines.line_index
--AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND (nvl(ldets.automatic_flag,'N') = 'Y')
-- or
-- (ldets.list_line_type_code = 'FREIGHT_CHARGE'))
AND ldets.created_from_list_type_code not in ('PRL','AGR')
AND ldets.list_line_type_code<>'PLL'
AND ldets.list_line_type_code NOT IN ('IUE', 'TSN') --bug 4190357 excluded TSN
-- AND (p_line_id is null or (p_line_id is not null and lines.line_id = p_line_id and lines.line_type_code = 'LINE'))
-- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
INSERT INTO OE_PRICE_ADJ_ASSOCS
( PRICE_ADJUSTMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICE_ADJ_ASSOC_ID
,LINE_ID
,RLTD_PRICE_ADJ_ID
,LOCK_CONTROL
)
(SELECT /*+ ORDERED USE_NL(QPL ADJ RADJ) */
LDET.price_adjustment_id
,sysdate --p_Line_Adj_Assoc_Rec.creation_date
,fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
,sysdate --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
,fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
,fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
,NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
,OE_PRICE_ADJ_ASSOCS_S.nextval
,NULL
,RLDET.PRICE_ADJUSTMENT_ID
,1
FROM
QP_PREQ_RLTD_LINES_TMP RLTD,
QP_PREQ_LDETS_TMP LDET,
QP_PREQ_LDETS_TMP RLDET,
QP_PREQ_LINES_TMP RLINE
WHERE
LDET.LINE_DETAIL_INDEX = RLTD.LINE_DETAIL_INDEX AND
RLDET.LINE_DETAIL_INDEX = RLTD.RELATED_LINE_DETAIL_INDEX AND
LDET.PRICING_STATUS_CODE = 'N' AND
LDET.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) AND
nvl(LDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
lDET.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR') AND
lDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
RLDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
RLDET.PRICING_STATUS_CODE = 'N' AND
RLDET.PROCESS_CODE = 'N' AND
nvl(RLDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
-- not in might not be needed
RLDET.PRICE_ADJUSTMENT_ID
NOT IN (SELECT RLTD_PRICE_ADJ_ID
FROM OE_PRICE_ADJ_ASSOCS
WHERE PRICE_ADJUSTMENT_ID = LDET.PRICE_ADJUSTMENT_ID ) AND
RLTD.PRICING_STATUS_CODE = 'N'
AND RLINE.LINE_INDEX = RLDET.LINE_INDEX
AND RLINE.PROCESS_STATUS <> 'NOT_VALID' );
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' PRICE ADJ ASSOCS' , 3 ) ;
oe_debug_pub.add('ERROR in inserting adjustments and associations'||sqlerrm);
END Insert_Adj;