The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Set_CC_Selected_From_Lov (p_CC_selected_from_LOV IN VARCHAR2)
IS
BEGIN
g_is_cc_selected_from_LOV := p_CC_selected_from_LOV;
END Set_CC_Selected_From_Lov;
AND nvl(g_is_cc_selected_from_LOV,'N') <> 'Y') --bug 5083663
) -- AND condition added to fix 3098878
THEN
p_x_header_rec.credit_card_expiration_date := FND_API.G_MISS_DATE;
AND nvl(g_is_cc_selected_from_LOV,'N') <> 'Y') --bug 5083663
) -- AND condition added to fix 3098878
THEN
p_x_header_rec.credit_card_holder_name := FND_API.G_MISS_CHAR;
l_rowtype_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
l_rowtype_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
l_rowtype_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
l_rowtype_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
x_rowtype_rec.LAST_UPDATED_BY := p_header_rec.LAST_UPDATED_BY;
x_rowtype_rec.LAST_UPDATE_DATE := p_header_rec.LAST_UPDATE_DATE;
x_rowtype_rec.LAST_UPDATE_LOGIN := p_header_rec.LAST_UPDATE_LOGIN;
x_rowtype_rec.PROGRAM_UPDATE_DATE := p_header_rec.PROGRAM_UPDATE_DATE;
x_api_rec.LAST_UPDATED_BY := p_record.LAST_UPDATED_BY;
x_api_rec.LAST_UPDATE_DATE := p_record.LAST_UPDATE_DATE;
x_api_rec.LAST_UPDATE_LOGIN := p_record.LAST_UPDATE_LOGIN;
x_api_rec.PROGRAM_UPDATE_DATE := p_record.PROGRAM_UPDATE_DATE;
--can be cleared during the future calls when the cc is not selected
--from the LOV.
g_is_cc_selected_from_LOV := 'N';
select count(payment_type_code)
from oe_payments
where header_id = p_x_header_rec.header_id
and line_id is null;
cursor delete_payment_count_cur is
select count(payment_type_code)
from oe_payments
where header_id = p_x_header_rec.header_id
and line_id is null
and payment_type_code in ('CREDIT_CARD','ACH','DIRECT_DEBIT');
select line_id from oe_order_lines_all
where header_id = p_x_header_rec.header_id;
l_delete_payment_count NUMBER :=0;
l_log_delete_payment_req VARCHAR2(1) := 'N';
oe_debug_pub.ADD('Update Line Process Order return UNEXP_ERROR');
oe_debug_pub.ADD('Update Line Process Order return RET_STS_ERROR');
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
--R12 CC Encryption
--Delayed request for deleting the payments when
--invoice to changes
IF p_x_header_rec.payment_type_code in ('ACH','DIRECT_DEBIT','CREDIT_CARD') OR
p_x_header_rec.payment_type_code IS NULL THEN --null for prepayments
OPEN delete_payment_count_cur;
FETCH delete_payment_count_cur into l_delete_payment_count;
close delete_payment_count_cur;
IF l_delete_payment_count > 0 THEN
l_log_delete_payment_req := 'Y';
oe_debug_pub.add('Delete Payment Count count'||l_delete_payment_count);
oe_debug_pub.add('Log delete payment req Flag'||l_log_delete_payment_req);
IF l_log_delete_payment_req = 'Y' THEN
OE_delayed_requests_Pvt.log_request
(p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_PAYMENT,
p_entity_id => p_x_Header_rec.header_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
p_requesting_entity_id => p_x_Header_rec.header_id,
p_request_type => OE_GLOBALS.G_DELETE_PAYMENTS,
p_param1 => to_char(p_old_header_rec.invoice_to_org_id),
x_return_status => l_return_status);
( p_calling_action => 'UPDATE'
, p_order_type_id =>
p_x_header_rec.order_type_id
) ;
END IF;--Operation - Update
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in Order Type');
OE_DELAYED_REQUESTS_PVT.Delete_Request
(
p_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
p_entity_id => p_x_header_rec.header_id,
p_request_type => OE_GLOBALS.G_DFLT_HSCREDIT_FOR_SREP,
x_return_status => l_return_status
);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in Sold To',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
-- Log request to evaluate Holds.
oe_debug_pub.ADD('Customer update: logging request for eval_hold_source',1);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND l_credithold_cust ='BTH' THEN
-- Log request to evaluate Holds.
oe_debug_pub.ADD('Bill to Customer update: logging request for eval_hold_source',1);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
IF p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in Curr Code',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
--R12 CC Encryption
--Credit card processing would
--now be handled in pre_write_process procedure
IF p_x_header_rec.payment_type_code IS NOT NULL
AND p_x_header_rec.payment_type_code <> 'CREDIT_CARD' THEN
-- Set Flag Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in Payment Type',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code in ('CASH', 'CHECK')
THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Update Payments Delayed',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code = 'CHECK'
THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Update Payments Delayed',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in CC Holder',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in CC Number',3);
select last_day(p_x_header_rec.credit_card_expiration_date)
into p_x_header_rec.credit_card_expiration_date from dual;
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in CC Exp Date',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.payment_type_code = 'CREDIT_CARD' THEN
-- Set Flag to Log a request for Verify Payment
oe_debug_pub.add('Log Verify Payment Delayed Request in CC Approval',3);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
If nvl(p_x_header_rec.booked_flag, 'N') <> 'Y' Then
OE_delayed_requests_Pvt.log_request(
p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_x_header_rec.header_id,
p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_x_header_rec.header_id,
p_param1 => p_x_header_rec.header_id,
p_param2 => 'BATCH',
p_request_unique_key1 => 'BATCH',
p_param3 => 'PRICE_ORDER',
p_request_type => OE_GLOBALS.G_PRICE_ORDER,
x_return_status => l_return_status);
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
OE_Holds_PUB.G_PAYMENT_HOLD_APPLIED := 'N';
oe_debug_pub.ADD('payment type update: logging request for eval_hold_source',1);
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
oe_debug_pub.ADD('Currency update: logging request for eval_hold_source',1);
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
oe_debug_pub.ADD('sales channel update: logging request for eval_hold_source',1);
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
oe_debug_pub.ADD('price list update: logging request for eval_hold_source',1);
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
oe_debug_pub.ADD('Order type update: logging request for eval_hold_source',1);
p_request_type => OE_GLOBALS.G_UPDATE_HDR_PAYMENT,
p_param1 => 'UPDATE_PAYMENT',
x_return_status => l_return_status);
IF p_x_header_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_header_rec.last_updated_by := p_old_header_rec.last_updated_by;
IF p_x_header_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_header_rec.last_update_date := p_old_header_rec.last_update_date;
IF p_x_header_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_header_rec.last_update_login := p_old_header_rec.last_update_login;
IF p_x_header_rec.program_update_date = FND_API.G_MISS_DATE THEN
p_x_header_rec.program_update_date := p_old_header_rec.program_update_date;
IF p_x_header_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_header_rec.last_updated_by := NULL;
IF p_x_header_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_header_rec.last_update_date := NULL;
IF p_x_header_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_header_rec.last_update_login := NULL;
IF p_x_header_rec.program_update_date = FND_API.G_MISS_DATE THEN
p_x_header_rec.program_update_date := NULL;
PROCEDURE Update_Row
( p_header_rec IN OUT NOCOPY OE_Order_PUB.Header_Rec_Type
)
IS
l_org_id NUMBER;
oe_debug_pub.add('Entering OE_HEADER_UTIL.UPDATE_ROW', 1);
SELECT lock_control
INTO l_lock_control
FROM OE_ORDER_HEADERS
WHERE header_id = p_header_rec.header_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Header_rec =>p_header_rec,
p_header_id => p_header_rec.header_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_HEADER_UTIL.update_row is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_HEADER_UTIL.Update_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_HEADER_UTIL.Update_row');
OE_DEBUG_PUB.ADD('Exiting OE_HEADER_UTIL.Update_ROW', 1);
UPDATE OE_ORDER_HEADERS
SET ACCOUNTING_RULE_ID = p_header_rec.accounting_rule_id
, ACCOUNTING_RULE_DURATION = p_header_rec.accounting_rule_duration
, AGREEMENT_ID = p_header_rec.agreement_id
, ATTRIBUTE1 = p_header_rec.attribute1
, ATTRIBUTE10 = p_header_rec.attribute10
, ATTRIBUTE11 = p_header_rec.attribute11
, ATTRIBUTE12 = p_header_rec.attribute12
, ATTRIBUTE13 = p_header_rec.attribute13
, ATTRIBUTE14 = p_header_rec.attribute14
, ATTRIBUTE15 = p_header_rec.attribute15
, ATTRIBUTE16 = p_header_rec.attribute16 --For bug 2184255
, ATTRIBUTE17 = p_header_rec.attribute17
, ATTRIBUTE18 = p_header_rec.attribute18
, ATTRIBUTE19 = p_header_rec.attribute19
, ATTRIBUTE2 = p_header_rec.attribute2
, ATTRIBUTE20 = p_header_rec.attribute20
, ATTRIBUTE3 = p_header_rec.attribute3
, ATTRIBUTE4 = p_header_rec.attribute4
, ATTRIBUTE5 = p_header_rec.attribute5
, ATTRIBUTE6 = p_header_rec.attribute6
, ATTRIBUTE7 = p_header_rec.attribute7
, ATTRIBUTE8 = p_header_rec.attribute8
, ATTRIBUTE9 = p_header_rec.attribute9
, BLANKET_NUMBER = p_header_rec.Blanket_Number
, BOOKED_FLAG = p_header_rec.booked_flag
, BOOKED_DATE = p_header_rec.booked_date
, CANCELLED_FLAG = p_header_rec.cancelled_flag
, CONTEXT = p_header_rec.context
, CONVERSION_RATE = p_header_rec.conversion_rate
, CONVERSION_RATE_DATE = p_header_rec.conversion_rate_date
, CONVERSION_TYPE_CODE = p_header_rec.conversion_type_code
, CUSTOMER_PREFERENCE_SET_CODE = p_header_rec.CUSTOMER_PREFERENCE_SET_CODE
, CREATED_BY = p_header_rec.created_by
, CREATION_DATE = p_header_rec.creation_date
, CUST_PO_NUMBER = p_header_rec.cust_po_number
, DELIVER_TO_CONTACT_ID = p_header_rec.deliver_to_contact_id
, DELIVER_TO_ORG_ID = p_header_rec.deliver_to_org_id
, DEMAND_CLASS_CODE = p_header_rec.demand_class_code
, EXPIRATION_DATE = p_header_rec.expiration_date
, EARLIEST_SCHEDULE_LIMIT = p_header_rec.earliest_schedule_limit
, FIRST_ACK_CODE = p_header_rec.first_ack_code
, FIRST_ACK_DATE = p_header_rec.first_ack_date
, FOB_POINT_CODE = p_header_rec.fob_point_code
, FREIGHT_CARRIER_CODE = p_header_rec.freight_carrier_code
, FREIGHT_TERMS_CODE = p_header_rec.freight_terms_code
, GLOBAL_ATTRIBUTE1 = p_header_rec.global_attribute1
, GLOBAL_ATTRIBUTE10 = p_header_rec.global_attribute10
, GLOBAL_ATTRIBUTE11 = p_header_rec.global_attribute11
, GLOBAL_ATTRIBUTE12 = p_header_rec.global_attribute12
, GLOBAL_ATTRIBUTE13 = p_header_rec.global_attribute13
, GLOBAL_ATTRIBUTE14 = p_header_rec.global_attribute14
, GLOBAL_ATTRIBUTE15 = p_header_rec.global_attribute15
, GLOBAL_ATTRIBUTE16 = p_header_rec.global_attribute16
, GLOBAL_ATTRIBUTE17 = p_header_rec.global_attribute17
, GLOBAL_ATTRIBUTE18 = p_header_rec.global_attribute18
, GLOBAL_ATTRIBUTE19 = p_header_rec.global_attribute19
, GLOBAL_ATTRIBUTE2 = p_header_rec.global_attribute2
, GLOBAL_ATTRIBUTE20 = p_header_rec.global_attribute20
, GLOBAL_ATTRIBUTE3 = p_header_rec.global_attribute3
, GLOBAL_ATTRIBUTE4 = p_header_rec.global_attribute4
, GLOBAL_ATTRIBUTE5 = p_header_rec.global_attribute5
, GLOBAL_ATTRIBUTE6 = p_header_rec.global_attribute6
, GLOBAL_ATTRIBUTE7 = p_header_rec.global_attribute7
, GLOBAL_ATTRIBUTE8 = p_header_rec.global_attribute8
, GLOBAL_ATTRIBUTE9 = p_header_rec.global_attribute9
, GLOBAL_ATTRIBUTE_CATEGORY = p_header_rec.global_attribute_category
--, HEADER_ID = p_header_rec.header_id
, INVOICE_TO_CONTACT_ID = p_header_rec.invoice_to_contact_id
, INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
, INVOICING_RULE_ID = p_header_rec.invoicing_rule_id
, LAST_ACK_CODE = p_header_rec.last_ack_code
, LAST_ACK_DATE = p_header_rec.last_ack_date
, LAST_UPDATED_BY = p_header_rec.last_updated_by
, LAST_UPDATE_DATE = p_header_rec.last_update_date
, LAST_UPDATE_LOGIN = p_header_rec.last_update_login
, LATEST_SCHEDULE_LIMIT = p_header_rec.latest_schedule_limit
, OPEN_FLAG = p_header_rec.open_flag
, ORDERED_DATE = p_header_rec.ordered_date
, ORDER_DATE_TYPE_CODE = p_header_rec.order_date_type_code
, ORDER_NUMBER = p_header_rec.order_number
, ORDER_SOURCE_ID = p_header_rec.order_source_id
, ORDER_TYPE_ID = p_header_rec.order_type_id
, ORDER_CATEGORY_CODE = p_header_rec.order_category_code
-- Org_id should not allowed to update
-- , ORG_ID = p_header_rec.org_id
, ORIG_SYS_DOCUMENT_REF = p_header_rec.orig_sys_document_ref
, SOURCE_DOCUMENT_ID = p_header_rec.source_document_id
, SOURCE_DOCUMENT_TYPE_ID = p_header_rec.source_document_type_id
, PARTIAL_SHIPMENTS_ALLOWED = p_header_rec.partial_shipments_allowed
, PAYMENT_TERM_ID = p_header_rec.payment_term_id
, PRICE_LIST_ID = p_header_rec.price_list_id
, PRICE_REQUEST_CODE = p_header_rec.price_request_code -- PROMOTIONS SEP/01
, PRICING_DATE = p_header_rec.pricing_date
, PROGRAM_APPLICATION_ID = p_header_rec.program_application_id
, PROGRAM_ID = p_header_rec.program_id
, PROGRAM_UPDATE_DATE = p_header_rec.program_update_date
, REQUEST_DATE = p_header_rec.request_date
, REQUEST_ID = p_header_rec.request_id
, RETURN_REASON_CODE = p_header_rec.return_reason_code
, salesrep_id = p_header_rec.salesrep_id
, SALES_CHANNEL_CODE = p_header_rec.sales_channel_code
, SHIPMENT_PRIORITY_CODE = p_header_rec.shipment_priority_code
, SHIPPING_METHOD_CODE = p_header_rec.shipping_method_code
, SHIP_FROM_ORG_ID = p_header_rec.ship_from_org_id
, SHIP_TOLERANCE_ABOVE = p_header_rec.ship_tolerance_above
, SHIP_TOLERANCE_BELOW = p_header_rec.ship_tolerance_below
, SHIP_TO_CONTACT_ID = p_header_rec.ship_to_contact_id
, SHIP_TO_ORG_ID = p_header_rec.ship_to_org_id
--, SOLD_FROM_ORG_ID = p_header_rec.sold_from_org_id
, SOLD_TO_CONTACT_ID = p_header_rec.sold_to_contact_id
, SOLD_TO_ORG_ID = p_header_rec.sold_to_org_id
, SOLD_TO_PHONE_ID = p_header_rec.sold_to_phone_id
, TAX_EXEMPT_FLAG = p_header_rec.tax_exempt_flag
, TAX_EXEMPT_NUMBER = p_header_rec.tax_exempt_number
, TAX_EXEMPT_REASON_CODE = p_header_rec.tax_exempt_reason_code
, TAX_POINT_CODE = p_header_rec.tax_point_code
, TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
, VERSION_NUMBER = p_header_rec.version_number
, PAYMENT_TYPE_CODE = p_header_rec.payment_type_code
, PAYMENT_AMOUNT = p_header_rec.payment_amount
, CHECK_NUMBER = p_header_rec.check_number
/*, CREDIT_CARD_CODE = p_header_rec.credit_card_code --R12 CC Encryption
, CREDIT_CARD_HOLDER_NAME = p_header_rec.credit_card_holder_name
, CREDIT_CARD_NUMBER = p_header_rec.credit_card_number
, CREDIT_CARD_EXPIRATION_DATE = p_header_rec.credit_card_expiration_date
, CREDIT_CARD_APPROVAL_DATE = p_header_rec.credit_card_approval_date
, CREDIT_CARD_APPROVAL_CODE = p_header_rec.credit_card_approval_code*/ --R12 CC Encryption
, CHANGE_SEQUENCE = p_header_rec.change_sequence
--, DROP_SHIP_FLAG = p_header_rec.drop_ship_flag
--, CUSTOMER_PAYMENT_TERM_ID = p_header_rec.customer_payment_term_id
, SHIPPING_INSTRUCTIONS = p_header_rec.shipping_instructions
, PACKING_INSTRUCTIONS = p_header_rec.packing_instructions
, FLOW_STATUS_CODE = p_header_rec.flow_status_code
, MARKETING_SOURCE_CODE_ID = p_header_rec.marketing_source_code_id
, DEFAULT_FULFILLMENT_SET = p_header_rec.default_fulfillment_set
, FULFILLMENT_SET_NAME = p_header_rec.fulfillment_set_name
, LINE_SET_NAME = p_header_rec.line_set_name
, LOCK_CONTROL = l_lock_control
, TP_CONTEXT = p_header_rec.TP_context --added for bug 3977624
, TP_ATTRIBUTE1 = p_header_rec.TP_attribute1
, TP_ATTRIBUTE10 = p_header_rec.TP_attribute10
, TP_ATTRIBUTE11 = p_header_rec.TP_attribute11
, TP_ATTRIBUTE12 = p_header_rec.TP_attribute12
, TP_ATTRIBUTE13 = p_header_rec.TP_attribute13
, TP_ATTRIBUTE14 = p_header_rec.TP_attribute14
, TP_ATTRIBUTE15 = p_header_rec.TP_attribute15
, TP_ATTRIBUTE2 = p_header_rec.TP_attribute2
, TP_ATTRIBUTE3 = p_header_rec.TP_attribute3
, TP_ATTRIBUTE4 = p_header_rec.TP_attribute4
, TP_ATTRIBUTE5 = p_header_rec.TP_attribute5
, TP_ATTRIBUTE6 = p_header_rec.TP_attribute6
, TP_ATTRIBUTE7 = p_header_rec.TP_attribute7
, TP_ATTRIBUTE8 = p_header_rec.TP_attribute8
, TP_ATTRIBUTE9 = p_header_rec.TP_attribute9
, XML_MESSAGE_ID = p_header_rec.xml_message_id
-- QUOTING changes
, quote_date = p_header_rec.quote_date
, quote_number = p_header_rec.quote_number
, sales_document_name = p_header_rec.sales_document_name
, transaction_phase_code = p_header_rec.transaction_phase_code
, user_status_code = p_header_rec.user_status_code
, draft_submitted_flag = p_header_rec.draft_submitted_flag
, source_document_version_number = p_header_rec.source_document_version_number
, sold_to_site_use_id = p_header_rec.sold_to_site_use_id
-- QUOTING changes END
, Minisite_Id = p_header_rec.minisite_id
, End_customer_Id = p_header_rec.End_customer_id
, End_customer_contact_Id = p_header_rec.End_customer_contact_id
, End_customer_site_use_Id = p_header_rec.End_customer_site_use_id
, Ib_owner = p_header_rec.Ib_owner
, Ib_installed_at_location = p_header_rec.Ib_installed_at_location
, Ib_current_location = p_header_rec.Ib_current_location
, supplier_signature = p_header_rec.supplier_signature
, supplier_signature_date = p_header_rec.supplier_signature_date
, customer_signature = p_header_rec.customer_signature
, customer_signature_date = p_header_rec.customer_signature_date
, order_firmed_date = p_header_rec.order_firmed_date --key transaction dates
WHERE HEADER_ID = p_header_rec.header_id;
oe_debug_pub.add('Exiting OE_HEADER_UTIL.UPDATE_ROW', 1);
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_header_rec IN OUT NOCOPY OE_Order_PUB.Header_Rec_Type
)
IS
l_org_id NUMBER;
oe_debug_pub.add('Entering OE_HEADER_UTIL.INSERT_ROW', 1);
INSERT INTO OE_ORDER_HEADERS
( ACCOUNTING_RULE_ID
, ACCOUNTING_RULE_DURATION
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16 --For bug 2184255
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE2
, ATTRIBUTE20
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, BLANKET_NUMBER
, BOOKED_FLAG
, BOOKED_DATE
, CANCELLED_FLAG
, CONTEXT
, CONVERSION_RATE
, CONVERSION_RATE_DATE
, CONVERSION_TYPE_CODE
, CUSTOMER_PREFERENCE_SET_CODE
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_CONTACT_ID
, DELIVER_TO_ORG_ID
, DEMAND_CLASS_CODE
, FIRST_ACK_CODE
, FIRST_ACK_DATE
, EXPIRATION_DATE
, EARLIEST_SCHEDULE_LIMIT
, FOB_POINT_CODE
, FREIGHT_CARRIER_CODE
, FREIGHT_TERMS_CODE
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE_CATEGORY
, HEADER_ID
, INVOICE_TO_CONTACT_ID
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, LAST_ACK_CODE
, LAST_ACK_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LATEST_SCHEDULE_LIMIT
, OPEN_FLAG
, ORDERED_DATE
, ORDER_DATE_TYPE_CODE
, ORDER_NUMBER
, ORDER_SOURCE_ID
, ORDER_TYPE_ID
, ORDER_CATEGORY_CODE
, ORG_ID --MOAC change. Remove the comment out
, ORIG_SYS_DOCUMENT_REF
, PARTIAL_SHIPMENTS_ALLOWED
, PAYMENT_TERM_ID
, PRICE_LIST_ID
, PRICE_REQUEST_CODE -- PROMOTIONS SEP/01
, PRICING_DATE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_DATE
, REQUEST_ID
, RETURN_REASON_CODE
, SALESREP_ID
, SALES_CHANNEL_CODE
, SHIPMENT_PRIORITY_CODE
, SHIPPING_METHOD_CODE
, SHIP_FROM_ORG_ID
, SHIP_TOLERANCE_ABOVE
, SHIP_TOLERANCE_BELOW
, SHIP_TO_CONTACT_ID
, SHIP_TO_ORG_ID
, SOLD_FROM_ORG_ID
, SOLD_TO_CONTACT_ID
, SOLD_TO_ORG_ID
, SOLD_TO_PHONE_ID
, SOURCE_DOCUMENT_ID
, SOURCE_DOCUMENT_TYPE_ID
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, TAX_POINT_CODE
, TRANSACTIONAL_CURR_CODE
, VERSION_NUMBER
, PAYMENT_TYPE_CODE
, PAYMENT_AMOUNT
, CHECK_NUMBER
/*, CREDIT_CARD_CODE --R12 CC Encryption
, CREDIT_CARD_HOLDER_NAME
, CREDIT_CARD_NUMBER
, CREDIT_CARD_EXPIRATION_DATE
, CREDIT_CARD_APPROVAL_DATE
, CREDIT_CARD_APPROVAL_CODE*/ --R12 CC Encryption
, CHANGE_SEQUENCE
-- , DROP_SHIP_FLAG
-- , CUSTOMER_PAYMENT_TERM_ID
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, FLOW_STATUS_CODE
, MARKETING_SOURCE_CODE_ID
, DEFAULT_FULFILLMENT_SET
, FULFILLMENT_SET_NAME
, LINE_SET_NAME
, TP_ATTRIBUTE1
, TP_ATTRIBUTE10
, TP_ATTRIBUTE11
, TP_ATTRIBUTE12
, TP_ATTRIBUTE13
, TP_ATTRIBUTE14
, TP_ATTRIBUTE15
, TP_ATTRIBUTE2
, TP_ATTRIBUTE3
, TP_ATTRIBUTE4
, TP_ATTRIBUTE5
, TP_ATTRIBUTE6
, TP_ATTRIBUTE7
, TP_ATTRIBUTE8
, TP_ATTRIBUTE9
, TP_CONTEXT
, XML_MESSAGE_ID
, upgraded_flag
, LOCK_CONTROL
-- QUOTING changes
, quote_date
, quote_number
, sales_document_name
, transaction_phase_code
, user_status_code
, draft_submitted_flag
, source_document_version_number
, sold_to_site_use_id
-- QUOTING changes END
, Minisite_Id
, Ib_owner
, Ib_installed_at_location
, Ib_current_location
, End_customer_id
, End_customer_contact_id
, End_customer_site_use_id
, Supplier_signature
, Supplier_signature_date
, customer_signature
, customer_signature_date
--key transaction dates
, order_firmed_date
)
VALUES
( p_header_rec.accounting_rule_id
, p_header_rec.accounting_rule_duration
, p_header_rec.agreement_id
, p_header_rec.attribute1
, p_header_rec.attribute10
, p_header_rec.attribute11
, p_header_rec.attribute12
, p_header_rec.attribute13
, p_header_rec.attribute14
, p_header_rec.attribute15
, p_header_rec.attribute16 --For bug 2184255
, p_header_rec.attribute17
, p_header_rec.attribute18
, p_header_rec.attribute19
, p_header_rec.attribute2
, p_header_rec.attribute20
, p_header_rec.attribute3
, p_header_rec.attribute4
, p_header_rec.attribute5
, p_header_rec.attribute6
, p_header_rec.attribute7
, p_header_rec.attribute8
, p_header_rec.attribute9
, p_header_rec.Blanket_Number
, p_header_rec.booked_flag
, p_header_rec.booked_date
, p_header_rec.cancelled_flag
, p_header_rec.context
, p_header_rec.conversion_rate
, p_header_rec.conversion_rate_date
, p_header_rec.conversion_type_code
, p_header_rec.CUSTOMER_PREFERENCE_SET_CODE
, p_header_rec.created_by
, p_header_rec.creation_date
, p_header_rec.cust_po_number
, p_header_rec.deliver_to_contact_id
, p_header_rec.deliver_to_org_id
, p_header_rec.demand_class_code
, p_header_rec.first_ack_code
, p_header_rec.first_ack_date
, p_header_rec.expiration_date
, p_header_rec.earliest_schedule_limit
, p_header_rec.fob_point_code
, p_header_rec.freight_carrier_code
, p_header_rec.freight_terms_code
, p_header_rec.global_attribute1
, p_header_rec.global_attribute10
, p_header_rec.global_attribute11
, p_header_rec.global_attribute12
, p_header_rec.global_attribute13
, p_header_rec.global_attribute14
, p_header_rec.global_attribute15
, p_header_rec.global_attribute16
, p_header_rec.global_attribute17
, p_header_rec.global_attribute18
, p_header_rec.global_attribute19
, p_header_rec.global_attribute2
, p_header_rec.global_attribute20
, p_header_rec.global_attribute3
, p_header_rec.global_attribute4
, p_header_rec.global_attribute5
, p_header_rec.global_attribute6
, p_header_rec.global_attribute7
, p_header_rec.global_attribute8
, p_header_rec.global_attribute9
, p_header_rec.global_attribute_category
, p_header_rec.header_id
, p_header_rec.invoice_to_contact_id
, p_header_rec.invoice_to_org_id
, p_header_rec.invoicing_rule_id
, p_header_rec.last_ack_code
, p_header_rec.last_ack_date
, p_header_rec.last_updated_by
, p_header_rec.last_update_date
, p_header_rec.last_update_login
, p_header_rec.latest_schedule_limit
, p_header_rec.open_flag
, p_header_rec.ordered_date
, p_header_rec.order_date_type_code
, p_header_rec.order_number
, p_header_rec.order_source_id
, p_header_rec.order_type_id
, p_header_rec.order_category_code
, l_org_id --MOAC change. Remove the comment
, p_header_rec.orig_sys_document_ref
, p_header_rec.partial_shipments_allowed
, p_header_rec.payment_term_id
, p_header_rec.price_list_id
, p_header_rec.price_request_code -- PROMOTIONS SEP/01
, p_header_rec.pricing_date
, p_header_rec.program_application_id
, p_header_rec.program_id
, p_header_rec.program_update_date
, p_header_rec.request_date
, p_header_rec.request_id
, p_header_rec.return_reason_code
, p_header_rec.salesrep_id
, p_header_rec.sales_channel_code
, p_header_rec.shipment_priority_code
, p_header_rec.shipping_method_code
, p_header_rec.ship_from_org_id
, p_header_rec.ship_tolerance_above
, p_header_rec.ship_tolerance_below
, p_header_rec.ship_to_contact_id
, p_header_rec.ship_to_org_id
, l_org_id
, p_header_rec.sold_to_contact_id
, p_header_rec.sold_to_org_id
, p_header_rec.sold_to_phone_id
, p_header_rec.source_document_id
, p_header_rec.source_document_type_id
, p_header_rec.tax_exempt_flag
, p_header_rec.tax_exempt_number
, p_header_rec.tax_exempt_reason_code
, p_header_rec.tax_point_code
, p_header_rec.transactional_curr_code
, p_header_rec.version_number
, p_header_rec.payment_type_code
, p_header_rec.payment_amount
, p_header_rec.check_number
/*, p_header_rec.credit_card_code --R12 CC Encryption
, p_header_rec.credit_card_holder_name
, p_header_rec.credit_card_number
, p_header_rec.credit_card_expiration_date
, p_header_rec.credit_card_approval_date
, p_header_rec.credit_card_approval_code*/ --R12 CC Encryption
, p_header_rec.change_sequence
-- , p_header_rec.drop_ship_flag
-- , p_header_rec.customer_payment_term_id
, p_header_rec.shipping_instructions
, p_header_rec.packing_instructions
, p_header_rec.flow_status_code
, p_header_rec.marketing_source_code_id
, p_header_rec.default_fulfillment_set
, p_header_rec.fulfillment_set_name
, p_header_rec.line_set_name
, p_header_rec.tp_attribute1
, p_header_rec.tp_attribute10
, p_header_rec.tp_attribute11
, p_header_rec.tp_attribute12
, p_header_rec.tp_attribute13
, p_header_rec.tp_attribute14
, p_header_rec.tp_attribute15
, p_header_rec.tp_attribute2
, p_header_rec.tp_attribute3
, p_header_rec.tp_attribute4
, p_header_rec.tp_attribute5
, p_header_rec.tp_attribute6
, p_header_rec.tp_attribute7
, p_header_rec.tp_attribute8
, p_header_rec.tp_attribute9
, p_header_rec.tp_context
, p_header_rec.xml_message_id
, l_upgraded_flag
, l_lock_control
-- QUOTING changes
, p_header_rec.quote_date
, p_header_rec.quote_number
, p_header_rec.sales_document_name
, p_header_rec.transaction_phase_code
, p_header_rec.user_status_code
, p_header_rec.draft_submitted_flag
, p_header_rec.source_document_version_number
, p_header_rec.sold_to_site_use_id
-- QUOTING changes END
, p_header_rec.minisite_id
, p_header_rec.Ib_owner
, p_header_rec.Ib_installed_at_location
, p_header_rec.Ib_current_location
, p_header_rec.end_customer_id
, p_header_rec.end_customer_contact_id
, p_header_rec.end_customer_site_use_id
, p_header_rec.supplier_signature
, p_header_rec.supplier_signature_date
, p_header_rec.customer_signature
, p_header_rec.customer_signature_date
--key transaction dates
, p_header_rec.order_firmed_date
);
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_old_header_rec => NULL,
p_Header_rec =>p_header_rec,
p_header_id => p_header_rec.header_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_HEADER_UTIL.insert_row is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_HEADER_UTIL.INSERT_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_HEADER_UTIL.Insert_row');
OE_DEBUG_PUB.ADD('Exiting OE_HEADER_UTIL.INSERT_ROW', 1);
oe_debug_pub.add('Exiting OE_HEADER_UTIL.INSERT_ROW', 1);
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Delete_Row
( p_header_id IN NUMBER
)
IS
l_return_status VARCHAR2(30);
oe_debug_pub.add('Entering OE_HEADER_UTIL.DELETE_ROW', 1);
/* Set the operation on the record so that globals are updated as well */
l_new_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Header_rec =>l_new_header_rec,
p_header_id => p_header_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_HEADER_UTIL.delete_row is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_HEADER_UTIL.DELETE_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_HEADER_UTIL.Delete_row');
OE_DEBUG_PUB.ADD('Exiting OE_HEADER_UTIL.DELETE_ROW', 1);
oe_debug_pub.add('log request to Reverse_Limits for HEADER level DELETE',1);
oe_debug_pub.add('Select price_request_code from HEADER',1);
SELECT price_request_code
INTO l_price_request_code
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id;
oe_debug_pub.add('Request to Reverse_Limits in OE_HEADER_UTIL.Delete_Row is done',1);
OE_Atchmt_Util.Delete_Attachments
( p_entity_code => OE_GLOBALS.G_ENTITY_HEADER
, p_entity_id => p_header_id
, x_return_status => l_return_status
);
OE_Header_Payment_Util.delete_row(p_header_id => p_header_id); --10113279
OE_Header_Adj_Util.delete_row(p_header_id => p_header_id);
OE_Header_Scredit_Util.delete_row(p_header_id => p_header_id);
OE_Line_Util.delete_row(p_header_id => p_header_id);
OE_Order_WF_Util.delete_row(p_type=>'HEADER', p_id => p_header_id);
OE_Order_WF_Util.delete_row(p_type=>'NEGOTIATE', p_id => p_header_id);
OE_Holds_PUB.Delete_Holds(p_header_id => p_header_id);
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
p_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
p_entity_id => p_header_id,
x_return_status => l_return_status
);
DELETE FROM OE_ORDER_HEADER_HISTORY
WHERE HEADER_ID = p_header_id;
/* takintoy delete Contracts*/
IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
OE_CONTRACTS_UTIL.delete_articles
(
p_api_version =>1,
p_doc_type => OE_CONTRACTS_UTIL.G_SO_DOC_TYPE,
p_doc_id =>p_header_id,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data);
DELETE FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_header_id;
oe_debug_pub.add('Exiting OE_HEADER_UTIL.DELETE_ROW', 1);
, 'Delete_Row'
);
END Delete_Row;
SELECT 'Y',trxn_extension_id
into l_exists,l_trxn_extension_id
FROM OE_PAYMENTS
WHERE header_id = p_header_id
and line_id is null and
nvl(payment_collection_event,'PREPAY') = 'INVOICE';
select credit_card_number,
credit_card_holder_name,
credit_card_expiration_date,
credit_card_code,
credit_card_approval_code,
credit_card_approval_date
into p_credit_card_number,
p_credit_card_holder_name,
p_credit_card_expiration_date,
p_credit_card_code,
p_credit_card_approval_code,
p_credit_card_approval_date
from oe_payments where
header_id = p_header_id and line_id is null
and nvl(payment_collection_event,'PREPAY') = 'INVOICE';
SELECT ACCOUNTING_RULE_ID
, ACCOUNTING_RULE_DURATION
, AGREEMENT_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16 --For bug 2184255
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE2
, ATTRIBUTE20
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, BLANKET_NUMBER
, BOOKED_FLAG
, BOOKED_DATE
, CANCELLED_FLAG
, CONTEXT
, CONVERSION_RATE
, CONVERSION_RATE_DATE
, CONVERSION_TYPE_CODE
, CUSTOMER_PREFERENCE_SET_CODE
, CREATED_BY
, CREATION_DATE
, CUST_PO_NUMBER
, DELIVER_TO_CONTACT_ID
, DELIVER_TO_ORG_ID
, DEMAND_CLASS_CODE
, FIRST_ACK_CODE
, FIRST_ACK_DATE
, EXPIRATION_DATE
, EARLIEST_SCHEDULE_LIMIT
, FOB_POINT_CODE
, FREIGHT_CARRIER_CODE
, FREIGHT_TERMS_CODE
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE_CATEGORY
, HEADER_ID
, INVOICE_TO_CONTACT_ID
, INVOICE_TO_ORG_ID
, INVOICING_RULE_ID
, LAST_ACK_CODE
, LAST_ACK_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LATEST_SCHEDULE_LIMIT
, OPEN_FLAG
, ORDERED_DATE
, ORDER_DATE_TYPE_CODE
, ORDER_NUMBER
, ORDER_SOURCE_ID
, ORDER_TYPE_ID
, ORDER_CATEGORY_CODE
, ORG_ID
, ORIG_SYS_DOCUMENT_REF
, PARTIAL_SHIPMENTS_ALLOWED
, PAYMENT_TERM_ID
, PRICE_LIST_ID
, PRICE_REQUEST_CODE -- PROMOTIONS SEP/01
, PRICING_DATE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_DATE
, REQUEST_ID
, RETURN_REASON_CODE
, SALESREP_ID
, SALES_CHANNEL_CODe
, SHIPMENT_PRIORITY_CODE
, SHIPPING_METHOD_CODE
, SHIP_FROM_ORG_ID
, SHIP_TOLERANCE_ABOVE
, SHIP_TOLERANCE_BELOW
, SHIP_TO_CONTACT_ID
, SHIP_TO_ORG_ID
, SOLD_FROM_ORG_ID
, SOLD_TO_CONTACT_ID
, SOLD_TO_ORG_ID
, SOLD_TO_PHONE_ID
, SOURCE_DOCUMENT_ID
, SOURCE_DOCUMENT_TYPE_ID
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, TAX_POINT_CODE
, TRANSACTIONAL_CURR_CODE
, VERSION_NUMBER
, PAYMENT_TYPE_CODE
, PAYMENT_AMOUNT
, CHECK_NUMBER
, CREDIT_CARD_CODE
, CREDIT_CARD_HOLDER_NAME
, CREDIT_CARD_NUMBER
, CREDIT_CARD_EXPIRATION_DATE
, CREDIT_CARD_APPROVAL_DATE
, CREDIT_CARD_APPROVAL_CODE
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, FLOW_STATUS_CODE
, MARKETING_SOURCE_CODE_ID
, DEFAULT_FULFILLMENT_SET
, FULFILLMENT_SET_NAME
, LINE_SET_NAME
, TP_ATTRIBUTE1
, TP_ATTRIBUTE10
, TP_ATTRIBUTE11
, TP_ATTRIBUTE12
, TP_ATTRIBUTE13
, TP_ATTRIBUTE14
, TP_ATTRIBUTE15
, TP_ATTRIBUTE2
, TP_ATTRIBUTE3
, TP_ATTRIBUTE4
, TP_ATTRIBUTE5
, TP_ATTRIBUTE6
, TP_ATTRIBUTE7
, TP_ATTRIBUTE8
, TP_ATTRIBUTE9
, TP_CONTEXT
, XML_MESSAGE_ID
, upgraded_flag
, LOCK_CONTROL
, CHANGE_SEQUENCE
, quote_date
, quote_number
, sales_document_name
, transaction_phase_code
, user_status_code
, draft_submitted_flag
, source_document_version_number
, sold_to_site_use_id
, MINISITE_ID
, IB_OWNER
, IB_INSTALLED_AT_LOCATION
, IB_CURRENT_LOCATION
, END_CUSTOMER_ID
, END_CUSTOMER_CONTACT_ID
, END_CUSTOMER_SITE_USE_ID
, SUPPLIER_SIGNATURE
, SUPPLIER_SIGNATURE_DATE
, CUSTOMER_SIGNATURE
, CUSTOMER_SIGNATURE_DATE
--key Transaction Dates
, order_firmed_date
INTO x_header_rec.accounting_rule_id
, x_header_rec.accounting_rule_duration
, x_header_rec.agreement_id
, x_header_rec.attribute1
, x_header_rec.attribute10
, x_header_rec.attribute11
, x_header_rec.attribute12
, x_header_rec.attribute13
, x_header_rec.attribute14
, x_header_rec.attribute15
, x_header_rec.attribute16 --For bug 2184255
, x_header_rec.attribute17
, x_header_rec.attribute18
, x_header_rec.attribute19
, x_header_rec.attribute2
, x_header_rec.attribute20
, x_header_rec.attribute3
, x_header_rec.attribute4
, x_header_rec.attribute5
, x_header_rec.attribute6
, x_header_rec.attribute7
, x_header_rec.attribute8
, x_header_rec.attribute9
, x_header_rec.Blanket_Number
, x_header_rec.booked_flag
, x_header_rec.booked_date
, x_header_rec.cancelled_flag
, x_header_rec.context
, x_header_rec.conversion_rate
, x_header_rec.conversion_rate_date
, x_header_rec.conversion_type_code
, x_header_rec.CUSTOMER_PREFERENCE_SET_CODE
, x_header_rec.created_by
, x_header_rec.creation_date
, x_header_rec.cust_po_number
, x_header_rec.deliver_to_contact_id
, x_header_rec.deliver_to_org_id
, x_header_rec.demand_class_code
, x_header_rec.first_ack_code
, x_header_rec.first_ack_date
, x_header_rec.expiration_date
, x_header_rec.earliest_schedule_limit
, x_header_rec.fob_point_code
, x_header_rec.freight_carrier_code
, x_header_rec.freight_terms_code
, x_header_rec.global_attribute1
, x_header_rec.global_attribute10
, x_header_rec.global_attribute11
, x_header_rec.global_attribute12
, x_header_rec.global_attribute13
, x_header_rec.global_attribute14
, x_header_rec.global_attribute15
, x_header_rec.global_attribute16
, x_header_rec.global_attribute17
, x_header_rec.global_attribute18
, x_header_rec.global_attribute19
, x_header_rec.global_attribute2
, x_header_rec.global_attribute20
, x_header_rec.global_attribute3
, x_header_rec.global_attribute4
, x_header_rec.global_attribute5
, x_header_rec.global_attribute6
, x_header_rec.global_attribute7
, x_header_rec.global_attribute8
, x_header_rec.global_attribute9
, x_header_rec.global_attribute_category
, x_header_rec.header_id
, x_header_rec.invoice_to_contact_id
, x_header_rec.invoice_to_org_id
, x_header_rec.invoicing_rule_id
, x_header_rec.last_ack_code
, x_header_rec.last_ack_date
, x_header_rec.last_updated_by
, x_header_rec.last_update_date
, x_header_rec.last_update_login
, x_header_rec.latest_schedule_limit
, x_header_rec.open_flag
, x_header_rec.ordered_date
, x_header_rec.order_date_type_code
, x_header_rec.order_number
, x_header_rec.order_source_id
, x_header_rec.order_type_id
, x_header_rec.order_category_code
, x_header_rec.org_id
, x_header_rec.orig_sys_document_ref
, x_header_rec.partial_shipments_allowed
, x_header_rec.payment_term_id
, x_header_rec.price_list_id
, x_header_rec.price_request_code -- PROMOTIONS SEP/01
, x_header_rec.pricing_date
, x_header_rec.program_application_id
, x_header_rec.program_id
, x_header_rec.program_update_date
, x_header_rec.request_date
, x_header_rec.request_id
, x_header_rec.return_reason_code
, x_header_rec.salesrep_id
, x_header_rec.sales_channel_code
, x_header_rec.shipment_priority_code
, x_header_rec.shipping_method_code
, x_header_rec.ship_from_org_id
, x_header_rec.ship_tolerance_above
, x_header_rec.ship_tolerance_below
, x_header_rec.ship_to_contact_id
, x_header_rec.ship_to_org_id
, x_header_rec.sold_from_org_id
, x_header_rec.sold_to_contact_id
, x_header_rec.sold_to_org_id
, x_header_rec.sold_to_phone_id
, x_header_rec.source_document_id
, x_header_rec.source_document_type_id
, x_header_rec.tax_exempt_flag
, x_header_rec.tax_exempt_number
, x_header_rec.tax_exempt_reason_code
, x_header_rec.tax_point_code
, x_header_rec.transactional_curr_code
, x_header_rec.version_number
, x_header_rec.payment_type_code
, x_header_rec.payment_amount
, x_header_rec.check_number
, x_header_rec.credit_card_code
, x_header_rec.credit_card_holder_name
, x_header_rec.credit_card_number
, x_header_rec.credit_card_expiration_date
, x_header_rec.credit_card_approval_date
, x_header_rec.credit_card_approval_code
, x_header_rec.shipping_instructions
, x_header_rec.packing_instructions
, x_header_rec.flow_status_code
, x_header_rec.marketing_source_code_id
, x_header_rec.default_fulfillment_set
, x_header_rec.fulfillment_set_name
, x_header_rec.line_set_name
, x_header_rec.tp_attribute1
, x_header_rec.tp_attribute10
, x_header_rec.tp_attribute11
, x_header_rec.tp_attribute12
, x_header_rec.tp_attribute13
, x_header_rec.tp_attribute14
, x_header_rec.tp_attribute15
, x_header_rec.tp_attribute2
, x_header_rec.tp_attribute3
, x_header_rec.tp_attribute4
, x_header_rec.tp_attribute5
, x_header_rec.tp_attribute6
, x_header_rec.tp_attribute7
, x_header_rec.tp_attribute8
, x_header_rec.tp_attribute9
, x_header_rec.tp_context
, x_header_rec.xml_message_id
, x_header_rec.upgraded_flag
, x_header_rec.lock_control
, x_header_rec.change_sequence
, x_header_rec.quote_date
, x_header_rec.quote_number
, x_header_rec.sales_document_name
, x_header_rec.transaction_phase_code
, x_header_rec.user_status_code
, x_header_rec.draft_submitted_flag
, x_header_rec.source_document_version_number
, x_header_rec.sold_to_site_use_id
, x_header_rec.minisite_id
, x_header_rec.ib_owner
, x_header_rec.ib_installed_at_location
, x_header_rec.ib_current_location
, x_header_rec.end_customer_id
, x_header_rec.end_customer_contact_id
, x_header_rec.end_customer_site_use_id
, x_header_rec.supplier_signature
, x_header_rec.supplier_signature_date
, x_header_rec.customer_signature
, x_header_rec.customer_signature_date
--key Transaction Dates
, x_header_rec.order_firmed_date
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = p_header_id;
SELECT trxn_extension_id
INTO l_trxn_extension_id
FROM oe_payments
WHERE header_id = p_header_id
AND nvl(payment_collection_event,'PREPAY') = 'INVOICE'
AND payment_type_code = 'CREDIT_CARD'
AND line_id is null;
SELECT header_id, lock_control
INTO l_header_id, l_db_lock_control
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = l_header_id
FOR UPDATE NOWAIT;
oe_debug_pub.add('selected for update, now compare', 3);
fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.sold_to_org_id = FND_API.G_MISS_NUM
THEN
SELECT SOLD_TO_ORG_ID
INTO l_sold_to_org_id
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_x_header_rec.header_id;
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND ( p_x_header_rec.ship_to_org_id = FND_API.G_MISS_NUM
OR p_x_header_rec.invoice_to_org_id = FND_API.G_MISS_NUM
OR p_x_header_rec.deliver_to_org_id = FND_API.G_MISS_NUM )
THEN
SELECT SHIP_TO_ORG_ID, INVOICE_TO_ORG_ID, DELIVER_TO_ORG_ID
-- bug 8340976 INTO l_sold_to_org_id, l_invoice_to_org_id, l_deliver_to_org_id
INTO l_ship_to_org_id, l_invoice_to_org_id, l_deliver_to_org_id
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = p_x_header_rec.header_id;
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT order_number,order_type_id
INTO l_order_number,l_order_type_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT name
INTO x_order_type
FROM OE_TRANSACTION_TYPES_TL
WHERE TRANSACTION_TYPE_ID = l_order_type_id
AND language = (select language_code from
fnd_languages
where installed_flag = 'B');
Select header_id
into l_header_id
from oe_order_headers
where order_number = p_order_number AND
order_type_id = (select tl.transaction_type_id
from oe_transaction_types_tl tl,
oe_transaction_types_all ta
where ta.transaction_type_id =
tl.transaction_type_id and
tl.name = p_order_type and
ta.transaction_type_code = 'ORDER'
and LANGUAGE = (
select language_code
from fnd_languages
where installed_flag = 'B'));
Select order_category_code --name --commented for BUG#7671483
into l_order_type
from oe_order_types_v
where transaction_type_id = p_order_type_id;
l_select_stmt VARCHAR2(240);
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_old_header_rec.order_number is not null
AND p_old_header_rec.order_number <> FND_API.G_MISS_NUM
THEN
if p_x_header_rec.booked_flag = 'Y' and
nvl(p_x_header_rec.open_flag,'Y') = 'Y' then
FND_MESSAGE.SET_NAME('ONT','ONT_INVALID_ORD_NUM_BOOKING');
if p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE and
x_doc_sequence_type = 'A'
then
FND_MESSAGE.SET_NAME('ONT','ONT_ORD_NUM_MISMATCH');
Select Count(Header_id) into
lcount
From OE_ORDER_HEADERS_ALL
WHERE order_type_id = p_x_header_rec.order_type_id
and order_number = p_x_header_rec.order_number
-- We need to remove the check condition around the version number.
--Bug #3357896.
--and version_number = p_x_header_rec.version_number
and header_id <> p_x_header_rec.header_id;
Select Count(Header_id) into
lcount
From OE_ORDER_HEADERS_ALL
WHERE order_type_id = p_x_header_rec.order_type_id
and quote_number = p_x_header_rec.quote_number
-- We need to remove the check condition around the version number.
--Bug #3357896.
--and version_number = p_x_header_rec.version_number
and header_id <> p_x_header_rec.header_id;
SELECT NAME
INTO l_order_type
FROM OE_TRANSACTION_TYPES_TL
WHERE TRANSACTION_TYPE_ID = p_order_type_id
AND language = (select language_code
from fnd_languages
where installed_flag = 'B');
SELECT oare.ATTRIBUTE_CODE attribute_code,Count(1) attachment_count
FROM oe_attachment_rule_elements oare, oe_attachment_rules oar
WHERE oare.rule_id=oar.rule_id
AND oar.DATABASE_OBJECT_NAME='OE_AK_ORDER_HEADERS_V'
GROUP BY oare.ATTRIBUTE_CODE;
SELECT count(sales_credit_id), max(sales_credit_id)
FROM oe_sales_credits sc,
oe_sales_credit_types sct
WHERE header_id = t_header_id
AND sct.sales_credit_type_id = sc.sales_credit_type_id
AND sct.quota_flag = 'Y'
AND line_id is null;
select line_id from oe_order_lines_all
where header_id = p_header_id;
IF (p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
OE_DEBUG_PUB.add('OEXUHDRB: Audit check requires reason', 5);
IF NOT OE_Internal_Requisition_Pvt.G_Update_ISO_From_Req THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Header level cancellation. Log the IR ISO update delayed request',5);
, p_request_type => OE_GLOBALS.G_UPDATE_REQUISITION
, x_return_status => l_return_status
);
SELECT count(*)
INTO l_count
FROM wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = p_x_header_rec.ship_from_org_id
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh.ship_method_code = p_x_header_rec.shipping_method_code
AND wsh_org.enabled_flag = 'Y';
SELECT count(CARRIER_SHIP_METHOD_ID)
INTO l_count
FROM wsh_carrier_ship_methods
WHERE ship_method_code = p_x_header_rec.shipping_method_code
AND organization_id = p_x_header_rec.ship_from_org_id
AND enabled_flag = 'Y'; -- added for bug 3886064
select count(*) into v_count from oe_price_adjustments
where header_id = p_x_header_rec.header_id
and line_id is null
and substitution_attribute = 'QUALIFIER_ATTRIBUTE11'
and list_line_type_code = 'TSN'
and modified_to = p_x_header_rec.shipping_method_code;
DELETE FROM OE_PRICE_ADJUSTMENTS
WHERE HEADER_ID = p_x_header_rec.header_id
AND LIST_LINE_TYPE_CODE = 'TSN'
AND SUBSTITUTION_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE11'
AND MODIFIED_TO = p_x_header_rec.shipping_method_code
RETURNING MODIFIED_FROM INTO l_modified_from;
select meaning into l_meaning from oe_ship_methods_v where lookup_type = 'SHIP_METHOD' and lookup_code = p_x_header_rec.shipping_method_code;
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SHIPPING_METHOD');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
select count(*) into v_count from oe_price_Adjustments
where header_id = p_x_header_rec.header_id
and line_id = i.line_id
and substitution_attribute = 'QUALIFIER_ATTRIBUTE11'
and list_line_type_code = 'TSN';
l_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE) AND --5893276
6896311*/
IF (p_x_header_rec.operation = OE_GLOBALS.G_OPR_CREATE ) AND --6896311
l_apply_automatic_atchmt = 'Y' THEN
oe_debug_pub.add('log request to apply atcmt');
IF (p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE ) AND
l_apply_automatic_atchmt = 'Y'
THEN
oe_debug_pub.add('log request to update atcmt');
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
l_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
ONT_HEADER_Def_Util.Clear_HEADER_Cache;
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND (p_x_header_rec.payment_type_code = 'CREDIT_CARD'
OR p_old_header_rec.payment_type_code = 'CREDIT_CARD') THEN
IF (NOT OE_GLOBALS.Is_Same_Credit_Card(p_old_header_rec.credit_card_number,
p_x_header_rec.credit_card_number,
p_old_header_rec.cc_instrument_id,
p_x_header_rec.cc_instrument_id))
OR
(p_x_header_rec.payment_type_code IS NULL AND NOT OE_GLOBALS.Is_Same_Credit_Card(p_old_header_rec.credit_card_number,p_x_header_rec.credit_card_number,p_old_header_rec.cc_instrument_id, NULL))
THEN
l_changed_attribute := l_changed_attribute||',' ||'CREDIT_CARD_NUMBER';
p_hist_type_code => 'UPDATE');
p_hist_type_code => 'UPDATE',
p_reason_code => p_x_header_rec.change_reason,
p_comments => p_x_header_rec.change_comments,
p_wf_activity_code => null,
p_wf_result_code => null,
x_return_status => l_return_status
);
oe_debug_pub.add('Inserting Header History Caused Error ',1);
AND (p_x_header_rec.operation = OE_GLOBALS.G_OPR_DELETE OR
(p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND NOT OE_GLOBALS.EQUAL(p_x_header_rec.transactional_curr_code
,p_old_header_rec.transactional_curr_code)))
THEN
Blkt_Req_For_Curr_Upd_And_Del(p_x_header_rec,p_old_header_rec);
,p_delete => FND_API.G_TRUE
,x_return_status=> l_return_status
);
OE_SCHEDULE_UTIL.Insert_into_mtl_sales_orders(p_x_header_rec);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF oe_sales_can_util.g_order_cancel AND
p_x_header_rec.cancelled_flag = 'Y' AND -- (p_old_header_rec.cancelled_flag <> 'Y') --7142748
p_x_header_rec.cancelled_flag <> NVL(p_old_header_rec.cancelled_flag,'N') THEN -- Bug 9241924
-- Bug 1895144: set recursion mode so that close does
-- not execute any delayed requests
BEGIN
l_recursion_mode := OE_GLOBALS.G_RECURSION_MODE;
-- Update the customer on the order lines if changed on the
-- order header
----------------------------------------------------------------
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND NOT OE_GLOBALS.EQUAL( p_x_header_rec.sold_to_org_id,
p_old_header_rec.sold_to_org_id )
THEN
OE_Line_Util.Query_Rows
( p_header_id => p_x_header_rec.header_id
, x_line_tbl => l_old_line_tbl );
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
l_line_tbl(I).operation := OE_GLOBALS.G_OPR_UPDATE;
AND p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND NOT OE_GLOBALS.EQUAL(p_x_header_rec.transactional_curr_code
,p_old_header_rec.transactional_curr_code)
-- QUOTING changes
AND NVL(p_x_header_rec.transaction_phase_code,'F') = 'F'
THEN
Blanket_Req_For_Curr_Update(p_x_header_rec,p_old_header_rec);
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_x_header_rec.transaction_phase_code = 'N'
AND NOT OE_GLOBALS.EQUAL(p_x_header_rec.expiration_date
,p_old_header_rec.expiration_date)
THEN
-- Call WF API to re-set expiration date timer
OE_Negotiate_WF.Offer_Date_Changed
(p_header_id => p_x_header_rec.header_id
,x_return_status => l_return_status
);
--deleted and a new one created (as the context has changed).
oe_debug_pub.add('Old invoice to'||p_x_header_rec.invoice_to_org_id);
--to delete the trxn extension id created for this creditcard
--When the old payment type is not null and the new one is null then
--need to delete the payment record from oe_payments.
IF( ( OE_GLOBALS.Equal(p_x_header_rec.payment_type_code, 'CREDIT_CARD') AND
p_x_header_rec.credit_card_number is not null
)
OR (
OE_GLOBALS.Equal(p_old_header_rec.payment_type_code,'CREDIT_CARD')
)
OR ( p_old_header_rec.payment_type_code IS NOT NULL AND
p_x_header_rec.payment_type_code IS NULL
))
AND --For copy orders, the CREATE operation need not call process order here
( --as the payment record would be inserted from oe_header_payment_util package.
nvl(p_x_header_rec.source_document_type_id,-99) <> 2 OR
( --To create or update credit cards on copied orders
nvl(p_x_header_rec.source_document_type_id,-99)=2 AND
p_x_header_rec.operation=OE_GLOBALS.G_OPR_UPDATE
)
)
AND p_x_header_rec.operation <> OE_GLOBALS.G_OPR_DELETE --10113279
THEN
BEGIN
--For update operation, payment number needs to be set in
--the payment record type as otherwise the process order
--call was failing in Query_row procedure during update operation
SELECT 'Y',payment_number,payment_type_code
INTO l_payment_exists,l_payment_number,l_old_payment_type_code
FROM oe_payments
WHERE header_id = p_x_header_rec.header_id
and line_id is null
AND nvl(PAYMENT_COLLECTION_EVENT,'PREPAY') = 'INVOICE';
--Others tab and hence it is required to delete this record from oe_payments.
IF l_old_payment_type_code IS NOT NULL
AND p_x_header_rec.payment_type_code IS NULL THEN
l_x_Header_Payment_tbl(1).operation := OE_GLOBALS.G_OPR_DELETE;
l_x_Header_Payment_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_x_Header_Payment_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
--Only credit card payment types have the pre-payment records inserted
--from this procedure. The other payment types are taken care by the
--procedure Update_Hdr_Payment in Oe_Prepayment_Pvt.
IF OE_GLOBALS.Equal(p_x_header_rec.payment_type_code, 'CREDIT_CARD') THEN
--Need to check if the payment term present in the header
--has the prepayment check box checked so as to insert the
--prepayment record as well if the other conditions are
--satisfied.
BEGIN
SELECT PAYMENT_TERM_ID,TRANSACTIONAL_CURR_CODE
INTO l_payment_term_id,l_currency_code
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = p_x_header_rec.header_id;
--Checking the count for prepayments and not inserting
--the prepayment record if there is an existing prepayment
--for this header.
BEGIN
SELECT count(*) INTO l_prepay_count
FROM OE_PAYMENTS
WHERE HEADER_ID = p_x_header_rec.header_id
AND LINE_ID IS NULL AND
NVL(PAYMENT_COLLECTION_EVENT,'PREPAY') = 'PREPAY';
--process order for inserting the prepayments.
BEGIN
SELECT COUNT(PAYMENT_TYPE_CODE) INTO l_line_payment_count
FROM OE_PAYMENTS
WHERE HEADER_ID = p_x_header_rec.header_id
AND LINE_ID IS NOT NULL
AND PAYMENT_TYPE_CODE <> 'COMMITMENT';
--If prepayment count is zero, then need to insert an
--additional prepayment record in Oe_Payments as the payment
--term used has the prepayment check box checked
IF nvl(l_prepay_count,0) = 0 and nvl(l_line_payment_count,0) = 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('OEXUHDRB: Inside prepayment record insertion....'||l_prepay_count);
select order_type_id into
l_order_type_id
from oe_order_headers_all
where header_id = l_header_id;
SELECT /*MOAC_SQL_CHANGE*/ cust.cust_account_id,
party.party_name,
cust.account_number,
site.location,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
nvl(loc.state,loc.province), --3603600
loc.postal_code,
loc.country
INTO x_customer_id,
x_customer_name,
x_customer_number,
x_location,
x_address1,
x_address2,
x_address3,
x_address4,
x_city,
x_state,
x_zip,
x_country
FROM HZ_CUST_SITE_USES_ALL site,
HZ_CUST_ACCT_SITES cas,
hz_cust_accounts cust,
hz_parties party,
hz_party_sites ps,
hz_locations loc
WHERE site.cust_acct_site_id=cas.cust_acct_site_id
AND site.site_use_code=p_site_use_code
AND site.site_use_id=p_org_id
AND cust.cust_account_id = cas.cust_account_id
AND cas.party_site_id = ps.party_site_id
AND ps.location_id = loc.location_id
AND party.party_id = cust.party_id;
SELECT Nvl(sum( Nvl(ordered_quantity,0) ),0) into l_total_quantity FROM OE_ORDER_LINES where header_id = p_header_id; --bug#9434723
OE_Header_Adj_Util.Delete_Header_Charges( p_header_id => p_header_id );
SELECT line_id
,blanket_number
,blanket_line_number
,ordered_quantity
,order_quantity_uom
,inventory_item_id
,unit_selling_price
,fulfilled_flag
,line_set_id
FROM OE_ORDER_LINES
WHERE HEADER_ID = p_header_rec.header_id
AND BLANKET_NUMBER IS NOT NULL;
IF p_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
-- Request to increment amounts based on new currency code
OE_Delayed_Requests_Pvt.Log_Request
(p_entity_code => OE_GLOBALS.G_ENTITY_ALL
,p_entity_id => c.line_id
,p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER
,p_requesting_entity_id => p_header_rec.header_id
,p_request_type => OE_GLOBALS.G_PROCESS_RELEASE
-- Old values
,p_param1 => null
,p_param2 => null
,p_param3 => 0
,p_param4 => null
,p_param5 => 0
,p_param6 => null
-- New values
,p_param11 => c.blanket_number
,p_param12 => c.blanket_line_number
,p_param13 => c.ordered_quantity
,p_param14 => c.order_quantity_uom
,p_param15 => c.unit_selling_price
,p_param16 => c.inventory_item_id
-- Other parameters - new currency code here
,p_param8 => c.fulfilled_flag
,p_param9 => c.line_set_id
,p_request_unique_key1 => p_header_rec.transactional_curr_code
,x_return_status => l_return_status
);
SELECT order_source_id, orig_sys_document_ref, change_sequence,
source_document_type_id, source_document_id
INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = p_x_header_rec.header_Id;
IF p_x_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
p_x_header_rec.sold_to_org_id = FND_API.G_MISS_NUM THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('Selecting sold_to_org_id in case of update');
SELECT NVL(SOLD_TO_ORG_ID,p_x_header_rec.sold_to_org_id)
INTO p_x_header_rec.sold_to_org_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = p_x_header_rec.header_id;
SELECT site_use.SITE_USE_ID
INTO p_site_use_id
FROM HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES site
WHERE site_use.SITE_USE_CODE = p_site_use
AND site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
AND site.CUST_ACCOUNT_ID = p_cust_acct_id
AND SITE_USE.PRIMARY_FLAG = 'Y'
AND SITE_USE.STATUS = 'A'
AND SITE.STATUS = 'A'
and SITE.ship_to_flag = 'P'
AND SITE.ORG_ID = p_org_id;
SELECT site_use.SITE_USE_ID
INTO p_site_use_id
FROM HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES site
WHERE site_use.SITE_USE_CODE = p_site_use
AND site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
AND site.CUST_ACCOUNT_ID = p_cust_acct_id
AND SITE_USE.PRIMARY_FLAG = 'Y'
AND SITE_USE.STATUS = 'A'
AND SITE.STATUS = 'A'
and SITE.bill_to_flag = 'P'
AND SITE.ORG_ID = p_org_id;