[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1. Insert original lines_id and retrobill qty, original_ulp, original_usp to temp table.
2a. Use SQL and Cursor, Group lines by sold_to_org, currency and conversion
b. Select original essential line_attributes like price_list_id, nvl (line.bill_to,header.bill_to) into a cursor.
3. For each group (group can be detected when changes by sold_to_org, currency and conversion), process header attributes.
4. For each line in the group, process lines attributes.
a.Set pricing date to be sysdate.
b.Set the line_id to missing.
c.Set return_reason_code.
d.Let the original ulp and usp remain in the line tbl.
e.Copy price list id from old line and set validation_level = G_VALID_PARTIAL_WITH_DEF (which means to redefault is invalid)
5. For each group, call process_order. Process_order api will be called in batch mode. (which eventually call pricing integration price_line).
a. Log copy adjustment in OEXULINB if is a retrobill line
b. In OEXULADB.pls copy adjustment lines to set the retrobill_info_flag
c. Log retrobill event in OEXULINB if it is a retrobill line (retrobill_request_id is not null)
d. Price_line recognizes it is a retrobilling call because of the event and retrobill_request_id
e. Process_adjustment (within the price_line call) will perform post processing for lines and adjustments.
f. If the line has retrobilled before, get the difference by sum ULP, USP, ULPPQTY, USPPQTY.
g. Updated applied_flag = N on any adjustment has
h. If price negative then change line category RETURN, pricing recursion Y oe_order_pvt.lines to redefault the lines.
2. Log this retrobill request into OE_RETROBILL_REQUESTS table.
Elsif EXECUTE previous preview Then
For each retrobill_header_id in p_rectobill_line_tbl call OE_ORDER_BOOK_UTIL.Book_Order.
Elsif PREVIEW AGAIN Then
If;
Select /*+ ORDERED USE_NL(t,l,h) */
LINE_ID
, nvl(l.ORG_ID,h.ORG_ID) org_id
, l.HEADER_ID
, LINE_TYPE_ID
, LINE_NUMBER
, ORDERED_ITEM
, nvl(l.REQUEST_DATE,h.request_date) request_date
, PROMISE_DATE
, SCHEDULE_SHIP_DATE
, ORDER_QUANTITY_UOM
, PRICING_QUANTITY
, PRICING_QUANTITY_UOM
, CANCELLED_QUANTITY
--, SHIPPED_QUANTITY
, ORDERED_QUANTITY
, FULFILLED_QUANTITY
--, SHIPPING_QUANTITY
--, SHIPPING_QUANTITY_UOM
, DELIVERY_LEAD_TIME
, nvl(l.TAX_EXEMPT_FLAG,h.TAX_EXEMPT_FLAG) TAX_EXEMPT_FLAG
, nvl(l.TAX_EXEMPT_NUMBER,h.TAX_EXEMPT_NUMBER) TAX_EXEMPT_NUMBER
, nvl(l.TAX_EXEMPT_REASON_CODE,h.TAX_EXEMPT_REASON_CODE) TAX_EXEMPT_REASON_CODE
, nvl(l.SHIP_FROM_ORG_ID,h.SHIP_FROM_ORG_ID) SHIP_FROM_ORG_ID
, nvl(l.SHIP_TO_ORG_ID,h.SHIP_TO_ORG_ID) SHIP_TO_ORG_ID
, nvl(l.INVOICE_TO_ORG_ID,h.INVOICE_TO_ORG_ID)INVOICE_TO_ORG_ID
, nvl(l.DELIVER_TO_ORG_ID,h.DELIVER_TO_ORG_ID) DELIVER_TO_ORG_ID
, nvl(l.SHIP_TO_CONTACT_ID,h.SHIP_TO_CONTACT_ID) SHIP_TO_CONTACT_ID
, nvl(l.DELIVER_TO_CONTACT_ID,h.DELIVER_TO_CONTACT_ID) DELIVER_TO_CONTACT_ID
, nvl(l.INVOICE_TO_CONTACT_ID,h.INVOICE_TO_CONTACT_ID) INVOICE_TO_CONTACT_ID
, INTMED_SHIP_TO_ORG_ID
, INTMED_SHIP_TO_CONTACT_ID
, nvl(l.SOLD_FROM_ORG_ID,h.SOLD_FROM_ORG_ID) SOLD_FROM_ORG_ID
, nvl(l.SOLD_TO_ORG_ID,h.SOLD_TO_ORG_ID) sold_to_org_id1
, nvl(l.CUST_PO_NUMBER,h.CUST_PO_NUMBER) CUST_PO_NUMBER
, nvl(l.SHIP_TOLERANCE_ABOVE,h.SHIP_TOLERANCE_ABOVE) SHIP_TOLERANCE_ABOVE
, nvl(l.SHIP_TOLERANCE_BELOW,h.SHIP_TOLERANCE_BELOW) SHIP_TOLERANCE_BELOW
--, nvl(l.DEMAND_BUCKET_TYPE_CODE,h.DEMAND_BUCKET_TYPE_CODE)
, VEH_CUS_ITEM_CUM_KEY_ID
, RLA_SCHEDULE_TYPE_CODE
, CUSTOMER_DOCK_CODE
, CUSTOMER_JOB
, CUSTOMER_PRODUCTION_LINE
, CUST_MODEL_SERIAL_NUMBER
, PROJECT_ID
, TASK_ID
, INVENTORY_ITEM_ID
, TAX_DATE
, TAX_CODE
, TAX_RATE
, nvl(l.DEMAND_CLASS_CODE,h.DEMAND_CLASS_CODE) DEMAND_CLASS_CODE
, l.PRICE_LIST_ID
, nvl(l.PRICING_DATE,h.pricing_date) pricing_date
, SHIPMENT_NUMBER
, nvl(l.AGREEMENT_ID,h.agreement_id) agreement_id
, nvl(l.SHIPMENT_PRIORITY_CODE,h.SHIPMENT_PRIORITY_CODE) SHIPMENT_PRIORITY_CODE
, nvl(l.SHIPPING_METHOD_CODE,h.SHIPPING_METHOD_CODE) SHIPPING_METHOD_CODE
, nvl(l.FREIGHT_CARRIER_CODE,h.FREIGHT_CARRIER_CODE) FREIGHT_CARRIER_CODE
, nvl(l.FREIGHT_TERMS_CODE,h.FREIGHT_TERMS_CODE) FREIGHT_TERMS_CODE
, nvl(l.FOB_POINT_CODE,h.FOB_POINT_CODE) FOB_POINT_CODE , nvl(l.TAX_POINT_CODE,h.TAX_POINT_CODE) TAX_POINT_CODE
, nvl(l.PAYMENT_TERM_ID,h.PAYMENT_TERM_ID) PAYMENT_TERM_ID
, nvl(l.INVOICING_RULE_ID,h.INVOICING_RULE_ID) INVOICING_RULE_ID
, nvl(l.ACCOUNTING_RULE_ID,h.ACCOUNTING_RULE_ID) ACCOUNTING_RULE_ID
, nvl(l.SOURCE_DOCUMENT_TYPE_ID,h.SOURCE_DOCUMENT_TYPE_ID) SOURCE_DOCUMENT_TYPE_ID
, l.ORIG_SYS_DOCUMENT_REF
, nvl(l.SOURCE_DOCUMENT_ID,h.SOURCE_DOCUMENT_ID) SOURCE_DOCUMENT_ID
, l.ORIG_SYS_LINE_REF
, l.SOURCE_DOCUMENT_LINE_ID
, ITEM_REVISION
, UNIT_SELLING_PRICE
, UNIT_LIST_PRICE
, TAX_VALUE
, TOP_MODEL_LINE_ID
, LINK_TO_LINE_ID
, COMPONENT_SEQUENCE_ID
, COMPONENT_CODE
, CONFIG_DISPLAY_SEQUENCE
, SORT_ORDER
, ITEM_TYPE_CODE
--, OPTION_NUMBER
, OPTION_FLAG
, DEP_PLAN_REQUIRED_FLAG
, VISIBLE_DEMAND_FLAG
, LINE_CATEGORY_CODE
--, ACTUAL_SHIPMENT_DATE
, CUSTOMER_TRX_LINE_ID
, ACTUAL_ARRIVAL_DATE
, ATO_LINE_ID
, AUTO_SELECTED_QUANTITY
, COMPONENT_NUMBER
, EARLIEST_ACCEPTABLE_DATE
, EXPLOSION_DATE
, LATEST_ACCEPTABLE_DATE
, MODEL_GROUP_NUMBER
, SCHEDULE_ARRIVAL_DATE
, SHIP_MODEL_COMPLETE_FLAG
, SCHEDULE_STATUS_CODE
, SOURCE_TYPE_CODE
, l.CANCELLED_FLAG
, l.OPEN_FLAG
, l.BOOKED_FLAG
, nvl(l.SALESREP_ID,h.SALESREP_ID)SALESREP_ID
, ARRIVAL_SET_ID
, SHIP_SET_ID
, SPLIT_FROM_LINE_ID
, CUST_PRODUCTION_SEQ_NUM
, AUTHORIZED_TO_SHIP_FLAG
--, OVER_SHIP_REASON_CODE
--, OVER_SHIP_RESOLVED_FLAG
, ORDERED_ITEM_ID
, ITEM_IDENTIFIER_TYPE
, CONFIGURATION_ID
, COMMITMENT_ID
--, SHIPPING_INTERFACED_FLAG
, CREDIT_INVOICE_LINE_ID
, l.FIRST_ACK_CODE
, l.FIRST_ACK_DATE
, l.LAST_ACK_CODE
, l.LAST_ACK_DATE
--, PLANNING_PRIORITY
, l.ORDER_SOURCE_ID --Order source id at the line ??
, ORIG_SYS_SHIPMENT_REF
, nvl(l.CHANGE_SEQUENCE,h.CHANGE_SEQUENCE)CHANGE_SEQUENCE
, nvl(l.DROP_SHIP_FLAG,h.DROP_SHIP_FLAG) DROP_SHIP_FLAG
, CUSTOMER_LINE_NUMBER
, CUSTOMER_SHIPMENT_NUMBER
, CUSTOMER_ITEM_NET_PRICE
, nvl(l.CUSTOMER_PAYMENT_TERM_ID,h.CUSTOMER_PAYMENT_TERM_ID) CUSTOMER_PAYMENT_TERM_ID
-- , nvl(l.BLANKET_NUMBER,h.BLANKET_NUMBER) BLANKET_NUMBER --bug8341909 --- bug# 8682469 : Reverted the fix done earlier
, FULFILLED_FLAG
, END_ITEM_UNIT_NUMBER
, CONFIG_HEADER_ID
, CONFIG_REV_NBR
, MFG_COMPONENT_SEQUENCE_ID
--, nvl(l.SHIPPING_INSTRUCTIONS,h.SHIPPING_INSTRUCTIONS) SHIPPING_INSTRUCTIONS
--, nvl(l.PACKING_INSTRUCTIONS,h.PACKING_INSTRUCTIONS) PACKING_INSTRUCTIONS
, INVOICED_QUANTITY
, REFERENCE_CUSTOMER_TRX_LINE_ID
, SPLIT_BY
, LINE_SET_ID
, SERVICE_TXN_REASON_CODE
, SERVICE_TXN_COMMENTS
, SERVICE_DURATION
, SERVICE_START_DATE
, SERVICE_END_DATE
, SERVICE_COTERMINATE_FLAG
, UNIT_LIST_PERCENT
, UNIT_SELLING_PERCENT
, UNIT_PERCENT_BASE_PRICE
, SERVICE_NUMBER
, SERVICE_PERIOD
, SHIPPABLE_FLAG
, MODEL_REMNANT_FLAG
, RE_SOURCE_FLAG
--, FLOW_STATUS_CODE
, FULFILLMENT_METHOD_CODE
, nvl(l.MARKETING_SOURCE_CODE_ID,h.MARKETING_SOURCE_CODE_ID)MARKETING_SOURCE_CODE_ID
, SERVICE_REFERENCE_TYPE_CODE
, SERVICE_REFERENCE_LINE_ID
, SERVICE_REFERENCE_SYSTEM_ID
, CALCULATE_PRICE_FLAG
, l.UPGRADED_FLAG
, REVENUE_AMOUNT
, FULFILLMENT_DATE
, PREFERRED_GRADE
, ORDERED_QUANTITY2
, ORDERED_QUANTITY_UOM2
--,SHIPPING_QUANTITY2
, CANCELLED_QUANTITY2
, SHIPPED_QUANTITY2
--, SHIPPING_QUANTITY_UOM2
, FULFILLED_QUANTITY2
, MFG_LEAD_TIME
--,LOCK_CONTROL
, SUBINVENTORY
, UNIT_LIST_PRICE_PER_PQTY
, UNIT_SELLING_PRICE_PER_PQTY
, nvl(l.PRICE_REQUEST_CODE,h.PRICE_REQUEST_CODE) PRICE_REQUEST_CODE
, ORIGINAL_INVENTORY_ITEM_ID
, ORIGINAL_ORDERED_ITEM_ID
, ORIGINAL_ORDERED_ITEM
, ORIGINAL_ITEM_IDENTIFIER_TYPE
, ITEM_SUBSTITUTION_TYPE_CODE
, OVERRIDE_ATP_DATE_CODE
, LATE_DEMAND_PENALTY_FACTOR
, nvl(l.ACCOUNTING_RULE_DURATION,h.ACCOUNTING_RULE_DURATION)ACCOUNTING_RULE_DURATION
, USER_ITEM_DESCRIPTION
, UNIT_COST
, RETROBILL_REQUEST_ID
--,h.SOLD_TO_ORG_ID
, h.TRANSACTIONAL_CURR_CODE
, h.CONVERSION_TYPE_CODE
, h.Order_Number
, h.Order_Type_Id
, t.value PLSQL_TBL_INDEX
FROM OM_ID_LIST_TMP t,
OE_ORDER_LINES_ALL l,
OE_ORDER_HEADERS_ALL h
WHERE l.line_id = t.key_id
AND l.header_id = h.header_id
ORDER BY sold_to_org_id1,h.transactional_curr_code,h.conversion_type_code;
Select max(header_id)
From OE_ORDER_LINES_ALL l
Where l.order_source_id = 27
And l.orig_sys_document_ref = to_char(p_header_id) -- p_header_id --commented for bug#7665009
And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
Select max(l.line_id)
From OE_ORDER_LINES_ALL l,
OE_ORDER_LINES_ALL b
Where l.order_source_id = 27
And l.orig_sys_document_ref = b.header_id
And l.orig_sys_line_ref = to_char(p_line_id) -- p_line_id --commented for bug#7665009
And l.retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID
AND b.line_id = p_line_id;
SELECT count(*) INTO l_retro_pricing_phase_count
FROM qp_pricing_phases p,
qp_event_phases e
WHERE p.pricing_phase_id=e.pricing_phase_id
AND p.pricing_phase_id <> 1
AND e.pricing_event_code='RETROBILL'
AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate))
AND trunc(nvl(e.end_date_active,sysdate));
select sum(oeol1.unit_selling_price * decode(oeol1.line_category_code,'RETURN',-1,1)),
sum(oeol1.unit_list_price * decode(oeol1.line_category_code,'RETURN',-1,1))
From oe_order_lines_all oeol1
Where oeol1.order_source_id = G_RETROBILL_ORDER_SOURCE_ID
and oeol1.orig_sys_document_ref = to_char(p_header_id) --p_header_id --commented for bug#7665009
and oeol1.orig_sys_line_ref = to_char(p_line_id) --p_line_id --commented for bug#7665009
and nvl(oeol1.retrobill_request_id,-1) <> p_curr_retro_id; --exclude current retrobill line
SELECT 'VALID'
INTO l_dummy
FROM ZX_OUTPUT_CLASSIFICATIONS_V
WHERE LOOKUP_CODE = p_tax_code
AND ORG_ID IN (p_org_id, -99)
AND TRUNC(p_tax_date)
BETWEEN TRUNC(START_DATE_ACTIVE) AND
TRUNC(NVL(END_DATE_ACTIVE, p_tax_date))
AND ROWNUM = 1;
SELECT order_number INTO l_order_number
FROM oe_order_headers_all
WHERE header_id=p_header_id;
Select max(line_id),
max(header_id)
From OE_ORDER_LINES_ALL l
Where l.order_source_id = 27
And l.orig_sys_document_ref = to_char(p_key_header_id) --p_key_header_id --commented for bug#7665009
And l.orig_sys_line_ref = to_char(p_key_line_id) --p_key_line_id --commented for bug#7665009
And retrobill_request_id <> G_CURRENT_RETROBILL_REQUEST_ID;
Select customer_trx_line_id --customer_trx_line_id is the invoice_id
From ra_customer_trx_lines_all a,
oe_transaction_types_tl b
Where INTERFACE_LINE_CONTEXT= 'ORDER ENTRY'
and INTERFACE_LINE_ATTRIBUTE1=to_char(p_order_number) ----bug5138249
and INTERFACE_LINE_ATTRIBUTE2=b.name
and INTERFACE_LINE_ATTRIBUTE6=to_char(p_line_id) --bug5138249
and INTERFACE_LINE_ATTRIBUTE11 = '0'
and a.org_id = (select org_id from oe_order_lines_all where line_id = p_line_id) --bug# 8448816
and b.transaction_type_id = p_order_type_id
and b.language = (select language_code from fnd_languages where installed_flag = 'B');
SELECT price_adjustment_id, list_line_id
FROM oe_price_adjustments
WHERE line_id = p_line_id
AND retrobill_request_id IS NOT NULL
AND list_line_type_code IN ('DIS', 'SUR', 'PBH');
SELECT list_line_id
FROM oe_price_adjustments
WHERE line_id = p_line_id
AND retrobill_request_id IS NOT NULL
AND list_line_type_code IN ('DIS', 'SUR', 'PBH')
AND list_line_id = p_list_line_id
AND line_id = p_line_id;
DELETE From OE_PRICE_ADJUSTMENTS
WHERE line_id = p_line_id
AND retrobill_request_id IS NULL
AND list_line_type_code <> 'TAX';
UPDATE OE_PRICE_ADJUSTMENTS
SET operand = l_line_adj_tbl(i).operand,
operand_per_pqty = l_line_adj_tbl(i).operand_per_pqty,
adjusted_amount = l_line_adj_tbl(i).adjusted_amount,
adjusted_amount_per_pqty = l_line_adj_tbl(i).adjusted_amount_per_pqty,
applied_flag = 'Y'
WHERE line_id = p_line_id
AND retrobill_request_id IS NOT NULL
AND list_line_id = l_line_adj_tbl(i).list_line_id;
oe_debug_pub.add('PVIPRANA: Inserting Invalid list_line_id ' || l_line_adj_tbl(i).list_line_id);
SELECT Oe_Price_Adjustments_S.Nextval
INTO l_line_adj_tbl(i).price_adjustment_id
FROM dual;
OE_LINE_ADJ_UTIL.Insert_Row(l_line_adj_tbl(i));
DELETE FROM oe_price_adjustments
WHERE price_adjustment_id=l_new_price_adj_id_tbl(i);
Select sum(ordered_quantity)
From Oe_Order_Lines_All
Where reference_line_id = p_line_id
And line_category_code = 'RETURN';
This procdures update oe_retrobill_requests table
*************************************************************/
Procedure Update_Row(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
Begin
oe_debug_pub.add('Retro:Entering Update retrobill request,request_id:'||p_retrobill_request_rec.retrobill_request_id);
UPDATE OE_RETROBILL_REQUESTS
SET
NAME = p_retrobill_request_rec.name
, DESCRIPTION = p_retrobill_request_rec.description
, EXECUTION_MODE= p_retrobill_request_rec.execution_mode
, ORDER_TYPE_ID = p_retrobill_request_rec.order_type_id
, RETROBILL_REASON_CODE= p_retrobill_request_rec.retrobill_reason_code
, EXECUTION_DATE= nvl(p_retrobill_request_rec.execution_date,SYSDATE)
, INVENTORY_ITEM_ID= p_retrobill_request_rec.inventory_item_id
, SOLD_TO_ORG_ID = p_retrobill_request_rec.sold_to_org_id
, CREATION_DATE = nvl(p_retrobill_request_rec.creation_date,SYSDATE)
, CREATED_BY = nvl(p_retrobill_request_rec.created_by,fnd_global.user_id)
, LAST_UPDATE_DATE = nvl(p_retrobill_request_rec.last_update_date,SYSDATE)
, LAST_UPDATED_BY = nvl(p_retrobill_request_rec.last_updated_by,fnd_global.user_id)
, LAST_UPDATE_LOGIN= nvl(p_retrobill_request_rec.last_update_login,fnd_global.login_id)
, REQUEST_ID = p_retrobill_request_rec.request_id
, PROGRAM_APPLICATION_ID= nvl(p_retrobill_request_rec.program_application_id,fnd_global.prog_appl_id)
, PROGRAM_ID = p_retrobill_request_rec.program_id
, PROGRAM_UPDATED_DATE= p_retrobill_request_rec.program_updated_date
Where retrobill_request_id = p_retrobill_request_rec.retrobill_request_id;
oe_debug_pub.add('Retro:Leaving Update retrobill request:'||SQL%ROWCOUNT||' updated');
oe_debug_pub.add('Retro:Update_Row:'||SQLERRM);
PROCEDURE Insert_Id(p_retrobill_tbl IN RETROBILL_TBL_TYPE) As
l_id_tbl OE_GLOBALS.NUMBER_TBL_TYPE;
DELETE FROM OM_ID_LIST_TMP;
INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
VALUES (l_id_tbl(j),l_value_tbl(j));
oe_debug_pub.add('Execption occured in Oe_Retrobill_Pvt.Insert_Id:'||SQLERRM);
SELECT
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
pricing_attribute16,
pricing_attribute17,
pricing_attribute18,
pricing_attribute19,
pricing_attribute20,
pricing_attribute21,
pricing_attribute22,
pricing_attribute23,
pricing_attribute24,
pricing_attribute25,
pricing_attribute26,
pricing_attribute27,
pricing_attribute28,
pricing_attribute29,
pricing_attribute30,
pricing_attribute31,
pricing_attribute32,
pricing_attribute33,
pricing_attribute34,
pricing_attribute35,
pricing_attribute36,
pricing_attribute37,
pricing_attribute38,
pricing_attribute39,
pricing_attribute40,
pricing_attribute41,
pricing_attribute42,
pricing_attribute43,
pricing_attribute44,
pricing_attribute45,
pricing_attribute46,
pricing_attribute47,
pricing_attribute48,
pricing_attribute49,
pricing_attribute50,
pricing_attribute51,
pricing_attribute52,
pricing_attribute53,
pricing_attribute54,
pricing_attribute55,
pricing_attribute56,
pricing_attribute57,
pricing_attribute58,
pricing_attribute59,
pricing_attribute60,
pricing_attribute61,
pricing_attribute62,
pricing_attribute63,
pricing_attribute64,
pricing_attribute65,
pricing_attribute66,
pricing_attribute67,
pricing_attribute68,
pricing_attribute69,
pricing_attribute70,
pricing_attribute71,
pricing_attribute72,
pricing_attribute73,
pricing_attribute74,
pricing_attribute75,
pricing_attribute76,
pricing_attribute77,
pricing_attribute78,
pricing_attribute79,
pricing_attribute80,
pricing_attribute81,
pricing_attribute82,
pricing_attribute83,
pricing_attribute84,
pricing_attribute85,
pricing_attribute86,
pricing_attribute87,
pricing_attribute88,
pricing_attribute89,
pricing_attribute90,
pricing_attribute91,
pricing_attribute92,
pricing_attribute93,
pricing_attribute94,
pricing_attribute95,
pricing_attribute96,
pricing_attribute97,
pricing_attribute98,
pricing_attribute99,
pricing_attribute100,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
flex_title ,
override_flag,
lock_control,
orig_sys_atts_ref
INTO
x_Line_price_Att_rec.pricing_context,
x_Line_price_Att_rec.pricing_attribute1,
x_Line_price_Att_rec.pricing_attribute2,
x_Line_price_Att_rec.pricing_attribute3,
x_Line_price_Att_rec.pricing_attribute4,
x_Line_price_Att_rec.pricing_attribute5,
x_Line_price_Att_rec.pricing_attribute6,
x_Line_price_Att_rec.pricing_attribute7,
x_Line_price_Att_rec.pricing_attribute8,
x_Line_price_Att_rec.pricing_attribute9,
x_Line_price_Att_rec.pricing_attribute10,
x_Line_price_Att_rec.pricing_attribute11,
x_Line_price_Att_rec.pricing_attribute12,
x_Line_price_Att_rec.pricing_attribute13,
x_Line_price_Att_rec.pricing_attribute14,
x_Line_price_Att_rec.pricing_attribute15,
x_Line_price_Att_rec.pricing_attribute16,
x_Line_price_Att_rec.pricing_attribute17,
x_Line_price_Att_rec.pricing_attribute18,
x_Line_price_Att_rec.pricing_attribute19,
x_Line_price_Att_rec.pricing_attribute20,
x_Line_price_Att_rec.pricing_attribute21,
x_Line_price_Att_rec.pricing_attribute22,
x_Line_price_Att_rec.pricing_attribute23,
x_Line_price_Att_rec.pricing_attribute24,
x_Line_price_Att_rec.pricing_attribute25,
x_Line_price_Att_rec.pricing_attribute26,
x_Line_price_Att_rec.pricing_attribute27,
x_Line_price_Att_rec.pricing_attribute28,
x_Line_price_Att_rec.pricing_attribute29,
x_Line_price_Att_rec.pricing_attribute30,
x_Line_price_Att_rec.pricing_attribute31,
x_Line_price_Att_rec.pricing_attribute32,
x_Line_price_Att_rec.pricing_attribute33,
x_Line_price_Att_rec.pricing_attribute34,
x_Line_price_Att_rec.pricing_attribute35,
x_Line_price_Att_rec.pricing_attribute36,
x_Line_price_Att_rec.pricing_attribute37,
x_Line_price_Att_rec.pricing_attribute38,
x_Line_price_Att_rec.pricing_attribute39,
x_Line_price_Att_rec.pricing_attribute40,
x_Line_price_Att_rec.pricing_attribute41,
x_Line_price_Att_rec.pricing_attribute42,
x_Line_price_Att_rec.pricing_attribute43,
x_Line_price_Att_rec.pricing_attribute44,
x_Line_price_Att_rec.pricing_attribute45,
x_Line_price_Att_rec.pricing_attribute46,
x_Line_price_Att_rec.pricing_attribute47,
x_Line_price_Att_rec.pricing_attribute48,
x_Line_price_Att_rec.pricing_attribute49,
x_Line_price_Att_rec.pricing_attribute50,
x_Line_price_Att_rec.pricing_attribute51,
x_Line_price_Att_rec.pricing_attribute52,
x_Line_price_Att_rec.pricing_attribute53,
x_Line_price_Att_rec.pricing_attribute54,
x_Line_price_Att_rec.pricing_attribute55,
x_Line_price_Att_rec.pricing_attribute56,
x_Line_price_Att_rec.pricing_attribute57,
x_Line_price_Att_rec.pricing_attribute58,
x_Line_price_Att_rec.pricing_attribute59,
x_Line_price_Att_rec.pricing_attribute60,
x_Line_price_Att_rec.pricing_attribute61,
x_Line_price_Att_rec.pricing_attribute62,
x_Line_price_Att_rec.pricing_attribute63,
x_Line_price_Att_rec.pricing_attribute64,
x_Line_price_Att_rec.pricing_attribute65,
x_Line_price_Att_rec.pricing_attribute66,
x_Line_price_Att_rec.pricing_attribute67,
x_Line_price_Att_rec.pricing_attribute68,
x_Line_price_Att_rec.pricing_attribute69,
x_Line_price_Att_rec.pricing_attribute70,
x_Line_price_Att_rec.pricing_attribute71,
x_Line_price_Att_rec.pricing_attribute72,
x_Line_price_Att_rec.pricing_attribute73,
x_Line_price_Att_rec.pricing_attribute74,
x_Line_price_Att_rec.pricing_attribute75,
x_Line_price_Att_rec.pricing_attribute76,
x_Line_price_Att_rec.pricing_attribute77,
x_Line_price_Att_rec.pricing_attribute78,
x_Line_price_Att_rec.pricing_attribute79,
x_Line_price_Att_rec.pricing_attribute80,
x_Line_price_Att_rec.pricing_attribute81,
x_Line_price_Att_rec.pricing_attribute82,
x_Line_price_Att_rec.pricing_attribute83,
x_Line_price_Att_rec.pricing_attribute84,
x_Line_price_Att_rec.pricing_attribute85,
x_Line_price_Att_rec.pricing_attribute86,
x_Line_price_Att_rec.pricing_attribute87,
x_Line_price_Att_rec.pricing_attribute88,
x_Line_price_Att_rec.pricing_attribute89,
x_Line_price_Att_rec.pricing_attribute90,
x_Line_price_Att_rec.pricing_attribute91,
x_Line_price_Att_rec.pricing_attribute92,
x_Line_price_Att_rec.pricing_attribute93,
x_Line_price_Att_rec.pricing_attribute94,
x_Line_price_Att_rec.pricing_attribute95,
x_Line_price_Att_rec.pricing_attribute96,
x_Line_price_Att_rec.pricing_attribute97,
x_Line_price_Att_rec.pricing_attribute98,
x_Line_price_Att_rec.pricing_attribute99,
x_Line_price_Att_rec.pricing_attribute100,
x_Line_price_Att_rec.context,
x_Line_price_Att_rec.attribute1,
x_Line_price_Att_rec.attribute2,
x_Line_price_Att_rec.attribute3,
x_Line_price_Att_rec.attribute4,
x_Line_price_Att_rec.attribute5,
x_Line_price_Att_rec.attribute6,
x_Line_price_Att_rec.attribute7,
x_Line_price_Att_rec.attribute8,
x_Line_price_Att_rec.attribute9,
x_Line_price_Att_rec.attribute10,
x_Line_price_Att_rec.attribute11,
x_Line_price_Att_rec.attribute12,
x_Line_price_Att_rec.attribute13,
x_Line_price_Att_rec.attribute14,
x_Line_price_Att_rec.attribute15,
x_Line_price_Att_rec.flex_title ,
x_Line_price_Att_rec.override_flag,
x_Line_price_Att_rec.lock_control,
x_Line_price_Att_rec.orig_sys_atts_ref
FROM oe_order_price_attribs
WHERE line_id =p_line_id;
PROCEDURE INSERT_RETROBILL_REQUEST(p_retrobill_request_rec IN OE_RETROBILL_REQUESTS%ROWTYPE) AS
Begin
INSERT INTO OE_RETROBILL_REQUESTS
( RETROBILL_REQUEST_ID
,NAME
, DESCRIPTION
, EXECUTION_MODE
, ORDER_TYPE_ID
, RETROBILL_REASON_CODE
, EXECUTION_DATE
, INVENTORY_ITEM_ID
, SOLD_TO_ORG_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATED_DATE)
VALUES
(p_retrobill_request_rec.retrobill_request_id,
nvl(p_retrobill_request_rec.name,'RETRO TEST '||p_retrobill_request_rec.retrobill_request_id),
p_retrobill_request_rec.description,
p_retrobill_request_rec.execution_mode,
p_retrobill_request_rec.order_type_id,
p_retrobill_request_rec.retrobill_reason_code,
nvl(p_retrobill_request_rec.execution_date,SYSDATE),
p_retrobill_request_rec.inventory_item_id,
p_retrobill_request_rec.sold_to_org_id,
nvl(p_retrobill_request_rec.creation_date,SYSDATE),
nvl(p_retrobill_request_rec.created_by,fnd_global.user_id),
nvl(p_retrobill_request_rec.last_update_date,SYSDATE),
nvl(p_retrobill_request_rec.last_updated_by,fnd_global.user_id),
nvl(p_retrobill_request_rec.last_update_login,fnd_global.login_id),
p_retrobill_request_rec.request_id,
nvl(p_retrobill_request_rec.program_application_id,fnd_global.prog_appl_id),
p_retrobill_request_rec.program_id,
p_retrobill_request_rec.program_updated_date);
oe_debug_pub.add('RETRO:INSERT_RETROBILL_REQUEST:'||SQLERRM);
Called by OE_ORDER_PRICE_PVT, Update retrobill lines
based on the end results. New prie > old --> ORDER then redefault line type
If New price < old --> RETURN existing line type is fine (we always assume return) is
the most commom case
***************************************************************/
Procedure Update_Retrobill_Lines(p_operation IN VARCHAR2)
As
Cursor priced_lines IS
select lines.ADJUSTED_UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.
ordered_quantity NEW_UNIT_SELLING_PRICE
, lines.UNIT_PRICE*nvl(lines.priced_quantity,l.ordered_quantity)/l.ordered_quantity NEW_UNIT_LIST_PRICE
, lines.ADJUSTED_UNIT_PRICE UNIT_SELLING_PRICE_PER_PQTY
, lines.UNIT_PRICE UNIT_LIST_PRICE_PER_PQTY
, decode(lines.priced_quantity,-99999,l.ordered_quantity,lines.priced_quantity) PRICING_QUANTITY
, decode(lines.priced_quantity,-99999,l.order_quantity_uom,lines.priced_uom_code)PRICING_QUANTITY_UOM
, lines.price_list_header_id PRICE_LIST_ID
, lines.price_request_code PRICE_REQUEST_CODE
, nvl(lines.percent_price, NULL) UNIT_LIST_PERCENT
, nvl(lines.parent_price, NULL) UNIT_PERCENT_BASE_PRICE
, decode(lines.parent_price, NULL, 0, 0, 0,
lines.adjusted_unit_price/lines.parent_price) UNIT_SELLING_PERCENT
, l.unit_selling_price OLD_UNIT_SELLING_PRICE
, l.unit_list_price OLD_UNIT_LIST_PRICE
, l.line_id
, l.header_id
, l.retrobill_request_id
, l.order_source_id --source_id,orig_sys_document_ref and sys_line_ref forms an index
, l.orig_sys_document_ref
, l.orig_sys_line_ref
, l.inventory_item_id --For identifying unique item
, l.sold_to_org_id --For identifying unique customer
, l.line_number
, l.lock_control + 1 LOCK_CONTROL
from qp_preq_lines_tmp lines,
oe_order_lines_all l
where lines.line_id=l.line_id
and lines.line_type_code='LINE'
and lines.pricing_status_code in(QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION);
select sum(oeol1.unit_selling_price * decode(oeol1.line_category_code,'RETURN',-1,1)),
sum(oeol1.unit_list_price * decode(oeol1.line_category_code,'RETURN',-1,1))
From oe_order_lines_all oeol1
Where oeol1.order_source_id = p_order_source_id
and oeol1.orig_sys_document_ref = to_char(p_orig_sys_document_ref) --p_orig_sys_document_ref --commented for bug#7665009
and oeol1.orig_sys_line_ref = to_char(p_orig_sys_line_ref) --p_orig_sys_line_ref --commented for bug#7665009
and oeol1.retrobill_request_id <> p_curr_retro_id; --exclude current retrobill line
Select unit_selling_price,unit_list_price
From oe_order_lines_all
Where line_id = p_orig_sys_line_ref;
Select line_id from oe_order_lines_all
Where header_id = p_header_id
Order by line_id;
l_deleted_line NUMBER :=NULL;
l_deleted_line_hdr NUMBER :=NULL;
l_current_line_deleted VARCHAR2(1);
oe_debug_pub.add('Entering update retrobill lines : p_operation = '||p_operation);
l_current_line_deleted := 'N';
select line_category_code into l_line_category_code
from oe_order_lines_all
where line_id = to_number(I.orig_sys_line_ref);
UPDATE OE_ORDER_LINES_all l
SET UNIT_SELLING_PRICE =l_retrobill_selling_price
,UNIT_LIST_PRICE =l_retrobill_list_price
,UNIT_SELLING_PRICE_PER_PQTY=I.UNIT_SELLING_PRICE_PER_PQTY
,UNIT_LIST_PRICE_PER_PQTY =I.UNIT_LIST_PRICE_PER_PQTY
,PRICING_QUANTITY =I.PRICING_QUANTITY
,PRICING_QUANTITY_UOM =I.PRICING_QUANTITY_UOM
,PRICE_LIST_ID =I.PRICE_LIST_ID
,PRICE_REQUEST_CODE =I.PRICE_REQUEST_CODE
,UNIT_LIST_PERCENT =I.UNIT_LIST_PERCENT
,UNIT_PERCENT_BASE_PRICE =I.UNIT_PERCENT_BASE_PRICE
,UNIT_SELLING_PERCENT =I.UNIT_SELLING_PERCENT
,CALCULATE_PRICE_FLAG ='N'
,LOCK_CONTROL =I.LOCK_CONTROL
WHERE l.line_id = I.line_id;
oe_debug_pub.add('retro:updated row number:'|| SQL%ROWCOUNT||'line_id:'|| I.line_id);
SELECT DEFAULT_OUTBOUND_LINE_TYPE_ID INTO l_line_rec.line_type_id
FROM OE_TRANSACTION_TYPES_all
WHERE TRANSACTION_TYPE_ID = g_retrobill_request_rec.order_type_id ; -- bug# 8751523 : Added the SELECT sql
l_line_rec.operation :=OE_GLOBALS.G_OPR_UPDATE;
l_current_line_deleted := 'Y';
oe_debug_pub.add('Retro:No price difference,delete line_id:'||I.line_id);
l_deleted_line_hdr := I.header_id;
G_FIRST_LINE_DELETED := 'Y';
Oe_Line_Util.Delete_Row(I.line_id);
IF( l_current_line_deleted = 'N') THEN
g_retrobill_request_rec.inventory_item_id := I.inventory_item_id;
l_current_line_deleted := 'N';
IF l_deleted_line_hdr IS NOT NULL THEN
oe_debug_pub.add('Retro:before line_num');
FOR N IN line_number(l_deleted_line_hdr) LOOP
l_line_num_tbl(k):= k;
IF(G_FIRST_LINE_DELETED='Y' and k=1) THEN
SELECT price_list_id INTO G_FIRST_LINE_PRICE_LIST_ID
FROM oe_order_lines_all
WHERE line_id=N.LINE_ID;
oe_debug_pub.add('Retro:before update line_num');
UPDATE OE_ORDER_LINES_ALL
SET LINE_NUMBER = l_line_num_tbl(K)
WHERE LINE_ID = l_line_id_tbl(K);
oe_debug_pub.add('Retro:after update line_num'||SQL%ROWCOUNT);
oe_debug_pub.add('Customer id before inserting'||g_retrobill_request_rec.sold_to_org_id);
oe_debug_pub.add('Inventory item id before inserting'||g_retrobill_request_rec.inventory_item_id);
PROCEDURE Insert_diff_Adj As
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
select operand,operand_per_pqty,list_line_id,applied_flag,arithmetic_operator,updated_flag,adjusted_amount,adjusted_amount_per_pqty
from oe_price_adjustments
where retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID;
oe_debug_pub.add('arithmetic_operator:'||k.arithmetic_operator||'updated_flag:'||k.updated_flag||'adjusted_amount:'||k.adjusted_amount||'adjusted_amount_per_pqty:'||k.adjusted_amount_per_pqty||' retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
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
, retrobill_request_id
)
( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
oe_price_adjustments_s.nextval -- p_Line_Adj_rec.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
, oepj.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
--bug3654144 Changing the list_line_type_code to 'DIS' if ldets.list_line_type_code is 'PBH'
, decode(ldets.LIST_LINE_TYPE_CODE, 'PBH', 'DIS', 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
, 'N' --bug3896248 override_allowed
--bug3590893 setting the applied_flag to 'N' if the adjusted_amount on the modifier that is inserted for the retrobill line is 0
, decode((abs(abs(oepj.adjusted_amount) - abs(ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))) * decode(ldets.list_line_type_code,'DIS',-1,1)),0,'N',ldets.APPLIED_FLAG)
, NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
, NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
--below is operand
--bug3654144 modifying the calculation so that the operand is opposite in sign to adjusted_amount if the list_line_type_code = 'DIS' and same as adjusted_amount otherwise ('PBH' is considered as 'DIS' in diff adjs)
, (oepj.adjusted_amount - ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1)) * decode(oeol.line_category_code, 'RETURN', 1, -1) * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
, 'AMT' --arithmetic_operator hardcoded to AMT
, 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
--below is adjusted amount
--bug3654144 commenting the following and adding a new calculation for adjusted_amount
-- , abs(abs(oepj.adjusted_amount) - abs(ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))) * decode(ldets.list_line_type_code,'DIS',-1,1)
, (oepj.adjusted_amount - ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1)) * decode(oeol.line_category_code, 'RETURN', 1, -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
--bug3654144 commenting the following and adding a new calculation of operand_per_pqty
-- , decode(sign(ldets.OPERAND_value),-1,oepj.operand_per_pqty-ldets.OPERAND_value, abs(oepj.operand_per_pqty - ldets.OPERAND_value))
, (oepj.adjusted_amount_per_pqty - ldets.adjustment_amount) * decode(oeol.line_category_code, 'RETURN', 1, -1) * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
--bug3654144 Multiplying by -1 if the line_category_code = 'ORDER'
, (oepj.adjusted_amount_per_pqty - ldets.adjustment_amount) * decode(oeol.line_category_code, 'RETURN', 1, -1)
, 1
,null --this offset adjustment should not have retrobill request id
FROM QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
, QP_LIST_HEADERS_B QH
, OE_PRICE_ADJUSTMENTS oepj
, OE_ORDER_LINES_ALL oeol
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 oepj.line_id=oeol.line_id
--AND (nvl(ldets.automatic_flag,'N') = 'Y')
AND nvl(ldets.created_from_list_type_code,'xxx') not in ('PRL','AGR')
AND ldets.list_line_type_code<>'PLL'
AND ldets.list_line_type_code<>'IUE'
AND ldets.price_adjustment_id = oepj.price_adjustment_id
AND ldets.list_line_id = oepj.list_line_id
AND oepj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
);
oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' inserted, Retrobill id:'||G_CURRENT_RETROBILL_REQUEST_ID);
END Insert_diff_Adj;
Procedure Update_Diff_Adj AS
Begin
/*UPDATE OE_PRICE_ADJUSTMENTS oepj
SET (operand,
operand_per_pqty,
adjusted_amount,
adjusted_amount_per_pqty)
=(SELECT oepj.adjusted_amount - ldets.adjustment_amount* nvl(lines.priced_quantity,1/nvl(lines.line_quantity,1))
,oepj.adjusted_amount_per_pqty - ldets.adjustment_amount
adjusted_amount - ldets.adjustment_amount* nvl(lines.priced_quantity,1/nvl(lines.line_quantity,1))
adjusted_amont_per_pqty - ldets.adjustment_amount
From QP_LDETS_V ldets
Where ldets.list_line_id = oepj.list_line_id
--AND ldets.price_adjustment_id = oepj.price_adjustment_id
AND oepj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
--AND ldets.process_code = QP_PREQ_GRP.G_STATUS_UPDATED
)
WHERE retrobill_request_id IS NULL --offset adj does not have retrobill_request_id
AND */
null;
Procedure Update_Existing_Retro_Adj AS
Begin
UPDATE OE_PRICE_ADJUSTMENTS adj
SET ( operand
, operand_per_pqty
, adjusted_amount
, adjusted_amount_per_pqty
, arithmetic_operator
, pricing_phase_id
, pricing_group_sequence
, automatic_flag
, list_line_type_code
, applied_flag
, modified_from
, modified_to
, update_allowed
, updated_flag
, charge_type_code
, charge_subtype_code
, range_break_quantity
, accrual_conversion_rate
, accrual_flag
, list_line_no
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, include_on_returns_flag
, lock_control
)
=
(select
decode(ldets.operand_calculation_code,
'%',ldets.operand_value,
'LUMPSUM', ldets.operand_value,
ldets.operand_value*nvl(lines.priced_quantity,lines.line_quantity)/lines.line_quantity)
, ldets.operand_value
--bug4583857
, ldets.adjustment_amount* nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1)
, ldets.adjustment_amount
, ldets.operand_calculation_code
, ldets.pricing_phase_id
, ldets.pricing_group_sequence
, ldets.automatic_flag
, ldets.list_line_type_code
, 'N' --ldets.applied_flag
, 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)
, ldets.override_flag
, ldets.updated_flag
, ldets.charge_type_code
, ldets.charge_subtype_code
, ldets.line_quantity --range_break_quantity (?)
, ldets.accrual_conversion_rate
, ldets.accrual_flag
, ldets.list_line_no
, ldets.benefit_qty
, ldets.benefit_uom_code
, ldets.print_on_invoice_flag
, ldets.expiration_date
, ldets.rebate_transaction_type_code
, ldets.modifier_level_code
, ldets.price_break_type_code
, ldets.substitution_attribute
, ldets.proration_type_code
, ldets.include_on_returns_flag
, adj.lock_control + 1
from
QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
WHERE
lines.line_index = ldets.line_index
--and lines.process_status <> 'NOT_VALID'
and ldets.list_line_id = adj.list_line_id
--bug3417428
--and ldets.line_index = adj.header_id+nvl(adj.line_id,0)
and lines.line_id=adj.line_id
--and ldets.process_code = QP_PREQ_GRP.G_STATUS_UPDATED
and adj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID
)
WHERE header_id=oe_order_pub.g_hdr.header_id
and list_line_id in
(select list_line_id
from qp_ldets_v ldets2, QP_PREQ_LINES_TMP lines2
where --lines2.process_status <> 'NOT_VALID'
--and ldets2.process_code=QP_PREQ_GRP.G_STATUS_UPDATED
lines2.line_index = ldets2.line_index
and ldets2.list_line_id = adj.list_line_id
--bug3417428
--and ldets2.line_index = adj.header_id+nvl(adj.line_id,0))
and lines2.line_id=adj.line_id)
and adj.retrobill_request_id = G_CURRENT_RETROBILL_REQUEST_ID;
oe_debug_pub.add('Retro:'||SQL%ROWCOUNT||' updated');
PROCEDURE Insert_New_Adj As
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
, RETROBILL_REQUEST_ID
)
( 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,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
, 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
, 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
, 'N' --bug3896248 --override_allowed
, 'N' -- 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
, G_CURRENT_RETROBILL_REQUEST_ID
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<>'IUE'
AND ldets.list_line_type_code NOT IN ('TAX','FREIGHT_CHARGE')
-- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
);
oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW 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
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');
oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' NEW PRICE ADJ ASSOCS' , 3 ) ;
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
, RETROBILL_REQUEST_ID
)
( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
oe_price_adjustments_s.nextval -- p_Line_Adj_rec.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,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
, NULL --p_Line_Adj_rec.orig_sys_discount_ref
, ldets.LIST_HEADER_ID
, ldets.LIST_LINE_ID
, decode(ldets.list_line_type_code,'PBH','DIS',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
--below is operand
, decode(oeol.line_category_code, 'RETURN', -1, 1) * ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1) * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
, 'AMT' --hardcoded to 'AMT'
, 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
, decode(oeol.line_category_code, 'RETURN', -1, 1) * 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
, decode(oeol.line_category_code, 'RETURN', -1, 1) * ldets.adjustment_amount * decode(ldets.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
, decode(oeol.line_category_code, 'RETURN', -1, 1) * ldets.adjustment_amount
, 1
, null --retrobill_request_id
FROM
QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
, QP_LIST_HEADERS_B QH
, OE_ORDER_LINES_ALL oeol
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 lines.line_id=oeol.line_id
--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<>'IUE'
AND ldets.list_line_type_code NOT IN ('TAX','FREIGHT_CHARGE')
-- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
);
oe_debug_pub.add( 'pviprana INSERTED '||SQL%ROWCOUNT||' new DIFF ADJUSTMENTS' ) ;
END Insert_New_Adj;
PROCEDURE Update_Invalid_Diff_Adj AS
CURSOR invalid_price_adjs IS
SELECT price_adjustment_id, list_line_id, list_line_type_code, line_id
FROM oe_price_adjustments adj
WHERE adj.list_line_id NOT IN
(SELECT list_line_id
FROM
QP_LDETS_v ldets
, QP_PREQ_LINES_TMP lines
WHERE
lines.line_index = ldets.line_index
and lines.line_id= adj.line_id
and adj.list_line_id=ldets.list_line_id)
AND header_id=oe_order_pub.g_hdr.header_id
AND nvl(applied_flag,'N') = 'Y'
AND nvl(automatic_flag,'N') = 'Y'
AND nvl(list_line_type_code,'TAX') NOT IN ('TAX', 'FREIGHT_CHARGE')
ORDER BY line_id;
l_updated_inv_adj_count PLS_INTEGER := 0;
SELECT line_category_code INTO l_line_category_code
FROM oe_order_lines_all
WHERE line_id = invalid_price_adj.line_id;
UPDATE OE_PRICE_ADJUSTMENTS
SET ( LAST_UPDATE_DATE
, LIST_LINE_TYPE_CODE
, UPDATED_FLAG
, UPDATE_ALLOWED --bug3896248
, APPLIED_FLAG
, operand
, Arithmetic_operator
, ADJUSTED_AMOUNT
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
, retrobill_request_id
)
= (SELECT SYSDATE
, decode(adj.list_line_type_code,'PBH','DIS',adj.list_line_type_code)
, 'N' --p_Line_Adj_rec.UPDATED_FLAG
, 'N' --bug3896248 update_allowed
, adj.applied_flag
--below is operand
, nvl(adj.adjusted_amount,0) * decode(l_line_category_code, 'ORDER', -1, 1) * decode(adj.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
, 'AMT'
--below is adjusted amount
, nvl(adj.adjusted_amount,0) * decode(l_line_category_code, 'ORDER', -1, 1)
, nvl(adj.adjusted_amount_per_pqty,0) * decode(l_line_category_code, 'ORDER', -1, 1) * decode(adj.list_line_type_code, 'DIS', -1, 'PBH', -1, 1)
, nvl(adj.adjusted_amount_per_pqty,0) * decode(l_line_category_code, 'ORDER', -1, 1)
, 1
, null --this offset adjustment should not have retrobill request id
FROM oe_price_adjustments adj
, oe_order_lines_all oeol
WHERE adj.price_adjustment_id = l_price_adjustment_id
AND oeol.line_id=adj.line_id
)
WHERE price_adjustment_id=l_price_adjustment_id;
l_updated_inv_adj_count := l_updated_inv_adj_count + 1;
oe_debug_pub.add( 'pviprana UPDATED '|| l_updated_inv_adj_count ||' INVALID DIFF ADJUSTMENTS' ) ;
Insert_Diff_Adj;
Update_Existing_Retro_Adj;
Insert_New_Adj;
l_delete_tbl RETROBILL_TBL_TYPE;
l_update_tbl RETROBILL_TBL_TYPE;
SELECT DISTINCT KEY_ID Header_Id
FROM OM_ID_LIST_TMP;
SELECT line_id
FROM oe_order_lines_all
WHERE header_id=p_header_id;
IF nvl(p_retrobill_tbl(i).operation,'NULL') = 'DELETE' THEN
l_delete_tbl(del_index):=p_retrobill_tbl(i);
ELSIF nvl(p_retrobill_tbl(i).operation,'NULL') IN ('UPDATE','NULL') THEN
l_header_id_tbl(n_index):=p_retrobill_tbl(i).retrobill_header_id;
IF nvl(p_retrobill_tbl(i).operation,'NULL') = 'UPDATE' THEN
l_line_id_tbl(upd_index):=p_retrobill_tbl(i).retrobill_line_id;
i:=l_delete_tbl.first;
oe_debug_pub.add('Retro:Operation DELETE:deleting line id:'||l_delete_tbl(i).retrobill_line_id);
Oe_Line_Util.Delete_Row(p_line_id=>l_delete_tbl(i).retrobill_line_id);
i:=l_delete_tbl.next(i);
DELETE FROM OM_ID_LIST_TMP;
j := l_delete_tbl.FIRST;
INSERT INTO OM_ID_LIST_TMP(KEY_ID)
VALUES (l_delete_tbl(j).retrobill_header_id);
j:=l_delete_tbl.next(j);
oe_debug_pub.add('Retro:before update line_num');
UPDATE OE_ORDER_LINES_ALL
SET LINE_NUMBER = l_arrange_line_num_tbl(i)
WHERE LINE_ID = l_arrange_line_id_tbl(i);
oe_debug_pub.add('Retro:after update line_num');
DELETE FROM OM_ID_LIST_TMP;
INSERT INTO OM_ID_LIST_TMP(KEY_ID,VALUE)
VALUES (l_line_id_tbl(j),l_retrobill_qty_tbl(j));
UPDATE OE_ORDER_LINES_ALL oeol
SET (ordered_quantity,
pricing_quantity
)
= (Select value,
(value * oeol.unit_selling_price)/decode(nvl(oeol.unit_selling_price_per_pqty,0),0,1,oeol.unit_selling_price_per_pqty)
From OM_ID_LIST_TMP idt
Where idt.key_id = oeol.line_id)
WHERE oeol.retrobill_request_id IS NOT NULL
AND line_id in (SELECT key_id
FROM OM_ID_LIST_TMP);
DELETE FROM OM_ID_LIST_TMP;
INSERT INTO OM_ID_LIST_TMP(KEY_ID)
VALUES (l_header_id_tbl(j));
SELECT count(*) INTO l_book_line_count
FROM oe_order_lines_all
WHERE header_id = k.header_id;
UPDATE OE_ORDER_LINES_ALL
SET calculate_price_flag='Y'
WHERE header_id=k.header_id;
UPDATE OE_ORDER_LINES_ALL
SET calculate_price_flag='N'
WHERE header_id=k.header_id;
PROCEDURE Delete_Order
( p_header_id in NUMBER,
--bug5003256
x_header_deleted out nocopy BOOLEAN)
AS
l_line_count NUMBER := 0;
oe_debug_pub.add('Entering procedure Delete_Order');
x_header_deleted := FALSE;
select count(*) into l_line_count from oe_order_lines_all where header_id=p_header_id;
OE_Header_Util.Delete_Row(p_header_id);
x_header_deleted := TRUE;
oe_debug_pub.add('Exception occured in Delete_Order:'||SQLERRM);
l_update_price_list Boolean:=FALSE;
l_header_deleted BOOLEAN;
G_FIRST_LINE_DELETED := 'N';
Select OE_RETROBILL_REQUEST_S.NEXTVAL
Into l_retrobill_request_rec.retrobill_request_id
From dual;
Insert_Id(p_retrobill_tbl);
Oe_retrobill_Pvt.Delete_Order(lx_header_id, l_header_deleted);
IF l_header_deleted THEN
oe_debug_pub.ADD('Retro Order Header created has been deleted');
oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
SELECT price_list_id INTO l_header_price_list_id
FROM oe_order_headers_all
WHERE header_id=lx_header_id;
IF ((G_FIRST_LINE_DELETED='Y' AND
G_FIRST_LINE_PL_ASSIGNED='Y' AND
G_FIRST_LINE_PRICE_LIST_ID IS NOT NULL) OR
(l_header_price_list_id IS NULL)) THEN
BEGIN
UPDATE oe_order_headers_all
SET price_list_id=G_FIRST_LINE_PRICE_LIST_ID
WHERE header_id=lx_header_id;
NOT l_header_deleted Then --bug5003256
l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
l_line_tbl.delete;
L_Line_price_Att_tbl.delete; --8736629
G_FIRST_LINE_DELETED:='N';
oe_retrobill_pvt.delete_order(lx_header_id, l_header_deleted); --bug5003256
IF l_header_deleted THEN
oe_debug_pub.ADD('Retro Order Header created has been deleted');
oe_debug_pub.add('Praveen: First Line Deleted' || G_FIRST_LINE_DELETED);
NOT l_header_deleted Then --bug5003256
l_to_be_exe_hdr_id_tbl(i):=lx_header_id;
SELECT price_list_id INTO l_header_price_list_id
FROM oe_order_headers_all
WHERE header_id=lx_header_id;
IF ((G_FIRST_LINE_DELETED='Y' AND
G_FIRST_LINE_PL_ASSIGNED='Y' AND
G_FIRST_LINE_PRICE_LIST_ID IS NOT NULL) OR
(l_header_price_list_id IS NULL)) THEN
BEGIN
UPDATE oe_order_headers_all
SET price_list_id=G_FIRST_LINE_PRICE_LIST_ID
WHERE header_id=lx_header_id;
SELECT count(*) INTO l_book_line_count
FROM oe_order_lines_all
WHERE header_id = l_to_be_exe_hdr_id_tbl(i);
Insert_Retrobill_Request(l_retrobill_request_rec);
Update_Row(p_retrobill_request_rec=>l_retrobill_request_rec);
select line_id,
header_id,
numeric_attribute1,
numeric_attribute2,
numeric_attribute3,
char_attribute1
from oe_conc_request_iface
where request_id = p_request_session_id
FOR UPDATE NOWAIT;
select name
from oe_retrobill_requests
where
retrobill_request_id
in
(select retrobill_request_id from oe_order_lines_all
where
order_source_id=27 and
orig_sys_document_ref=to_char(c_header_id) and
orig_sys_line_ref=to_char(c_line_id) and
invoiced_quantity is NULL
)
group by name;
Delete from oe_conc_request_iface where request_id =p_request_session_id;
SELECT org_id INTO l_org_id
FROM oe_transaction_types_all
WHERE transaction_type_id = p_order_type_id;
select /* MOAC_SQL_CHANGE */ retro_line.line_id
, retro_line.header_id
, retro_line.order_source_id
, retro_line.unit_list_price
, retro_line.unit_selling_price
, retro_line.invoiced_quantity
, retro_line.line_category_code
, retro_line.retrobill_request_id
, retro_header.order_number
, retro_header.order_type_id
from
oe_order_lines_all retro_line
, oe_order_lines orig_line
, oe_order_headers_all retro_header
where orig_line.line_id = p_line_rec.reference_line_id
and retro_line.order_source_id=27
and retro_line.orig_sys_document_ref = to_char(orig_line.header_id) --bug5553346
and retro_line.orig_sys_line_ref = to_char(p_line_rec.reference_line_id) --bug5553346
and retro_line.header_id = retro_header.header_id
and retro_line.creation_date < p_line_rec.creation_date;
SELECT header_id
,unit_list_price
,unit_selling_price INTO
l_orig_header_id
,l_orig_list_price
,l_orig_selling_price
FROM oe_order_lines_all where line_id=p_line_rec.reference_line_id;
G_Retro_Bill_Only_Line_Tbl.delete;
select rct.trx_number
from ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
oe_transaction_types_tl ott
where rctl.INTERFACE_LINE_CONTEXT='ORDER ENTRY' and
rctl.INTERFACE_LINE_ATTRIBUTE1= to_char(l_order_number) and -- Added to_char for bug 9323027
rctl.INTERFACE_LINE_ATTRIBUTE2=ott.name and
rctl. INTERFACE_LINE_ATTRIBUTE6= to_char(l_line_id) and -- Added to_char for bug 9323027
rctl.customer_trx_id = rct.customer_trx_id and
ott.transaction_type_id=l_order_type_id
and ott.language = (select language_code from fnd_languages
where installed_flag = 'B') ;
select count(*) into l_count from
oe_order_lines_all where
order_source_id=27 and
orig_sys_document_ref=to_char(l_header_id) and
orig_sys_line_ref = to_char(l_line_id)
and l_creation_date < creation_date;
PROCEDURE Delete_Retrobill_Orders
( p_purge_preview_orders IN VARCHAR2,
p_retrobill_request_id IN VARCHAR2,
p_request_name IN VARCHAR2,
x_requests_processed OUT NOCOPY NUMBER,
x_headers_processed OUT NOCOPY NUMBER
)
AS
cursor retrobill_header(p_retrobill_request_id in VARCHAR2) is
select header_id,booked_flag from oe_order_headers_all
where orig_sys_document_ref=to_char(p_retrobill_request_id) --p_retrobill_request_id --commented for bug#7665009
and order_source_id=27;
--rt moac directly calling oe_order_pvt.process_order with delete operation since the context has already been set in procedure oe_retrobill_purge
/*
Oe_Order_Pub.Delete_Order
(
p_header_id =>l_retrobill_header_rec.header_id,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data
);
l_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
oe_debug_pub.add('Deleted Header:'||l_retrobill_header_rec.header_id);
select count(*) into l_header_count from oe_order_headers_all
where orig_sys_document_ref=to_char(p_retrobill_request_id) -- p_retrobill_request_id --commented for bug#7665009
and order_source_id=27;
Delete from oe_retrobill_requests where
retrobill_request_id=to_number(p_retrobill_request_id);
oe_debug_pub.add('Deleted Request'||p_retrobill_request_id);
select retrobill_request_id,retro.name,ord_typ.org_id,retro.execution_mode from oe_retrobill_requests retro,oe_order_types_v ord_typ --rt moac selecting the org_id
where trunc(retro.creation_date) >= nvl(trunc(l_creation_date_from),trunc(retro.creation_date))
and trunc(retro.creation_date) <= nvl(trunc(l_creation_date_to),trunc(retro.creation_date))
and trunc(execution_date) >= nvl(trunc(l_execution_date_from),trunc(execution_date))
and trunc(execution_date) <= nvl(trunc(l_execution_date_to),trunc(execution_date))
-- and execution_mode='PREVIEW'
and retro.order_type_id = ord_typ.order_type_id
order by org_id; --rt moac
select retrobill_request_id,retro.name,ord_typ.org_id,retro.execution_mode from oe_retrobill_requests retro,oe_order_types_v ord_typ --rt moac selecting the org_id
where retrobill_request_id = to_number(p_retrobill_request_id)
and trunc(retro.creation_date) >= nvl(trunc(l_creation_date_from),trunc(retro.creation_date))
and trunc(retro.creation_date) <= nvl(trunc(l_creation_date_to),trunc(retro.creation_date))
and trunc(execution_date) >= nvl(trunc(l_execution_date_from),trunc(execution_date))
and trunc(execution_date) <= nvl(trunc(l_execution_date_to),trunc(execution_date))
-- and execution_mode='PREVIEW'
and retro.order_type_id = ord_typ.order_type_id;
SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
USE_NL (a b) */
a.order_source_id
, a.original_sys_document_ref
, a.change_sequence
, a.original_sys_document_line_ref
, b.message_text
FROM oe_processing_msgs a, oe_processing_msgs_tl b
WHERE a.request_id = l_concurrent_id
AND a.transaction_id = b.transaction_id
AND b.language = oe_globals.g_lang
ORDER BY a.order_source_id, a.original_sys_document_ref, a.change_sequence;
delete_retrobill_orders(p_purge_preview_orders,to_char(l_retrobill_request_rec.retrobill_request_id),l_retrobill_request_rec.name,l_requests_processed_per_call,l_headers_processed_per_call);
delete_retrobill_orders(p_purge_preview_orders,to_char(l_retrobill_request_rec.retrobill_request_id),l_retrobill_request_rec.name,l_requests_processed_per_call,l_headers_processed_per_call);
select list_line_id into l_list_line_id
from oe_price_adjustments
where price_adjustment_id = p_price_adjustment_id;
SELECT adjusted_amount into l_adjusted_amount from oe_price_adjustments where
header_id = G_Retro_Bill_Only_Line_Tbl(i).header_id and
line_id = G_Retro_Bill_Only_Line_Tbl(i).line_id and
list_line_id = l_list_line_id and
applied_flag = 'Y' and
retrobill_request_id is NULL;