[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Adjustments
(p_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
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
OE_PRICE_ADJUSTMENTS_S.NEXTVAL
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,NULL
,a.program_application_id
,a.program_id
,a.program_update_date
,a.request_id
,h.header_id
,a.discount_id
,a.discount_line_id
,nvl(a.automatic_flag,ll.automatic_flag)
,a.percent
,l.line_id
,a.context
,a.attribute1
,a.attribute2
,a.attribute3
,a.attribute4
,a.attribute5
,a.attribute6
,a.attribute7
,a.attribute8
,a.attribute9
,a.attribute10
,a.attribute11
,a.attribute12
,a.attribute13
,a.attribute14
,a.attribute15
,a.orig_sys_discount_ref
,a.LIST_HEADER_ID
,a.LIST_LINE_ID
,nvl(a.LIST_LINE_TYPE_CODE,ll.list_line_type_code)
,a.MODIFIER_MECHANISM_TYPE_CODE
,a.MODIFIED_FROM
,a.MODIFIED_TO
,a.UPDATED_FLAG
,nvl(a.UPDATE_ALLOWED,ll.override_flag)
,a.APPLIED_FLAG
,a.CHANGE_REASON_CODE
,a.CHANGE_REASON_TEXT
,nvl(a.operand,ll.operand)
,nvl(a.arithmetic_operator,ll.arithmetic_operator)
,a.COST_ID
,a.TAX_CODE
,NULL -- a.TAX_EXEMPT_FLAG
,NULL -- a.TAX_EXEMPT_NUMBER
,NULL -- a.TAX_EXEMPT_REASON_CODE
,a.PARENT_ADJUSTMENT_ID
,a.INVOICED_FLAG
,nvl(a.ESTIMATED_FLAG
,decode(ll.list_line_type_code,'FREIGHT_CHARGE','Y',NULL))
,a.INC_IN_SALES_PERFORMANCE
,NULL -- a.SPLIT_ACTION_CODE
,a.ADJUSTED_AMOUNT
,nvl(a.PRICING_PHASE_ID,ll.Pricing_phase_id)
,nvl(a.CHARGE_TYPE_CODE,ll.charge_type_code)
,nvl(a.CHARGE_SUBTYPE_CODE,ll.charge_subtype_code)
,nvl(a.list_line_number,ll.list_line_no)
,lh.source_system_code
,ll.benefit_qty
,ll.benefit_uom_code
,ll.print_on_invoice_flag
,ll.expiration_date
,ll.rebate_transaction_type_code
,NULL -- a.rebate_transaction_reference
,NULL -- a.rebate_payment_system_code
,NULL -- a.redeemed_date
,NULL -- a.redeemed_flag
,ll.accrual_flag
,NULL -- a.range_break_quantity
,ll.accrual_conversion_rate
,ll.pricing_group_sequence
,ll.modifier_level_code
,ll.price_break_type_code
,ll.substitution_attribute
,ll.proration_type_code
,a.credit_or_charge_flag
,nvl(a.include_on_returns_flag,ll.include_on_returns_flag)
,a.ac_context
,a.ac_attribute1
,a.ac_attribute2
,a.ac_attribute3
,a.ac_attribute4
,a.ac_attribute5
,a.ac_attribute6
,a.ac_attribute7
,a.ac_attribute8
,a.ac_attribute9
,a.ac_attribute10
,a.ac_attribute11
,a.ac_attribute12
,a.ac_attribute13
,a.ac_attribute14
,a.ac_attribute15
,nvl(a.OPERAND_PER_PQTY,ll.operand)
,a.ADJUSTED_AMOUNT_PER_PQTY
,1
FROM OE_PRICE_ADJS_IFACE_ALL a
, OE_ORDER_HEADERS h
, QP_LIST_HEADERS lh
, QP_LIST_LINES ll
, OE_ORDER_LINES_ALL l -- Changes for SQL Id 14876372
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND lh.list_header_id = a.list_header_id
AND ll.list_line_id = a.list_line_id
AND l.order_source_id(+) = a.order_source_id
AND l.orig_sys_document_ref(+) = a.orig_sys_document_ref
AND l.orig_sys_line_ref(+) = a.orig_sys_line_ref
;
oe_debug_pub.add( 'OTHERS ERROR , INSERT_ADJUSTMENTS' ) ;
,'Insert_Adjustments');
END Insert_Adjustments;
SELECT line_id
,price_list_id
,unit_list_price
,unit_selling_price
FROM OE_ORDER_LINES l
WHERE l.header_id = l_header_id;
SELECT 'Y'
INTO l_book_failed
FROM OE_ORDER_LINES l
WHERE l.header_id = l_header_id
AND (l.price_list_id IS NULL
OR l.unit_list_price IS NULL
OR l.unit_selling_price IS NULL)
AND rownum = 1;
UPDATE oe_order_headers_all
SET booked_flag = p_header_rec.booked_flag(i)
WHERE header_id = p_header_rec.header_id(i);
OE_BULK_TAX_UTIL.Calculate_Tax(p_post_insert => TRUE);
UPDATE oe_order_headers_all
SET booked_flag = p_header_rec.booked_flag(i)
WHERE header_id = p_header_rec.header_id(i);
UPDATE OE_ORDER_LINES
SET booked_flag = 'N'
,flow_status_code = 'ENTERED'
WHERE header_id = p_header_rec.header_id(p_index);
UPDATE OE_ORDER_HEADERS
SET booked_flag = 'N'
,booked_date = NULL
,flow_status_code = 'ENTERED'
WHERE header_id = p_header_rec.header_id(p_index);
DELETE FROM ONT_DBI_CHANGE_LOG
WHERE header_id = p_header_rec.header_id(p_index);
PROCEDURE Update_Pricing_Attributes
(p_line_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE
)
IS
l_last_index binary_integer;
oe_debug_pub.add(' In Update_Pricing_Attributes', 1);
oe_debug_pub.add(' Exit Update_Pricing_Attributes' ,1);
END Update_Pricing_Attributes;