[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete_flag VARCHAR2(1) := 'Y';
SELECT order_source_id
, orig_sys_document_ref
, change_sequence
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(order_source, FND_API.G_MISS_CHAR)
, nvl(org_id, FND_API.G_MISS_NUM)
, nvl(header_id, FND_API.G_MISS_NUM)
, nvl(order_number, FND_API.G_MISS_NUM)
, nvl(version_number, FND_API.G_MISS_NUM)
, nvl(ordered_date, FND_API.G_MISS_DATE)
, nvl(order_type_id, FND_API.G_MISS_NUM)
, nvl(order_type, FND_API.G_MISS_CHAR)
, nvl(price_list_id, FND_API.G_MISS_NUM)
, nvl(price_list, FND_API.G_MISS_CHAR)
, nvl(conversion_rate, FND_API.G_MISS_NUM)
, nvl(conversion_rate_date, FND_API.G_MISS_DATE)
, nvl(conversion_type_code, FND_API.G_MISS_CHAR)
, nvl(conversion_type, FND_API.G_MISS_CHAR)
, nvl(transactional_curr_code, FND_API.G_MISS_CHAR)
, nvl(return_reason_code, FND_API.G_MISS_CHAR)
, nvl(salesrep_id, FND_API.G_MISS_NUM)
, nvl(salesrep, FND_API.G_MISS_CHAR)
, nvl(sales_channel_code, FND_API.G_MISS_CHAR)
, nvl(sales_channel, FND_API.G_MISS_CHAR)
, nvl(tax_point_code, FND_API.G_MISS_CHAR)
, nvl(tax_point, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_flag, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_number, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_reason_code, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_reason, FND_API.G_MISS_CHAR)
, nvl(agreement_id, FND_API.G_MISS_NUM)
, nvl(agreement, FND_API.G_MISS_CHAR)
, nvl(invoicing_rule_id, FND_API.G_MISS_NUM)
, nvl(invoicing_rule, FND_API.G_MISS_CHAR)
, nvl(accounting_rule_id, FND_API.G_MISS_NUM)
, nvl(accounting_rule_duration, FND_API.G_MISS_NUM)
, nvl(accounting_rule, FND_API.G_MISS_CHAR)
, nvl(payment_term_id, FND_API.G_MISS_NUM)
, nvl(payment_term, FND_API.G_MISS_CHAR)
, nvl(demand_class_code, FND_API.G_MISS_CHAR)
, nvl(shipment_priority_code, FND_API.G_MISS_CHAR)
, nvl(shipment_priority, FND_API.G_MISS_CHAR)
, nvl(shipping_method_code, FND_API.G_MISS_CHAR)
, nvl(freight_carrier_code, FND_API.G_MISS_CHAR)
, nvl(freight_terms_code, FND_API.G_MISS_CHAR)
, nvl(freight_terms, FND_API.G_MISS_CHAR)
, nvl(fob_point_code, FND_API.G_MISS_CHAR)
, nvl(fob_point, FND_API.G_MISS_CHAR)
, nvl(partial_shipments_allowed, FND_API.G_MISS_CHAR)
, nvl(ship_tolerance_above, FND_API.G_MISS_NUM)
, nvl(ship_tolerance_below, FND_API.G_MISS_NUM)
, nvl(shipping_instructions, FND_API.G_MISS_CHAR)
, nvl(packing_instructions, FND_API.G_MISS_CHAR)
, nvl(order_date_type_code, FND_API.G_MISS_CHAR)
, nvl(earliest_schedule_limit, FND_API.G_MISS_NUM)
, nvl(latest_schedule_limit, FND_API.G_MISS_NUM)
, nvl(customer_po_number, FND_API.G_MISS_CHAR)
, nvl(customer_payment_term_id, FND_API.G_MISS_NUM)
, nvl(customer_payment_term, FND_API.G_MISS_CHAR)
, nvl(payment_type_code, FND_API.G_MISS_CHAR)
, nvl(payment_amount, FND_API.G_MISS_NUM)
, nvl(check_number, FND_API.G_MISS_CHAR)
, nvl(credit_card_code, FND_API.G_MISS_CHAR)
, nvl(credit_card_holder_name, FND_API.G_MISS_CHAR)
, nvl(credit_card_number, FND_API.G_MISS_CHAR)
, nvl(credit_card_expiration_date, FND_API.G_MISS_DATE)
, nvl(credit_card_approval_code, FND_API.G_MISS_CHAR)
, nvl(credit_card_approval_date, FND_API.G_MISS_DATE)
, nvl(sold_from_org_id, FND_API.G_MISS_NUM)
, nvl(sold_from_org, FND_API.G_MISS_CHAR)
, nvl(sold_to_org_id, FND_API.G_MISS_NUM)
, nvl(sold_to_org, FND_API.G_MISS_CHAR)
, nvl(customer_number, FND_API.G_MISS_CHAR)
, nvl(ship_from_org_id, FND_API.G_MISS_NUM)
, nvl(ship_from_org, FND_API.G_MISS_CHAR)
, nvl(ship_to_org_id, FND_API.G_MISS_NUM)
, nvl(ship_to_org, FND_API.G_MISS_CHAR)
, nvl(invoice_to_org_id, FND_API.G_MISS_NUM)
, nvl(invoice_to_org, FND_API.G_MISS_CHAR)
, nvl(deliver_to_org_id, FND_API.G_MISS_NUM)
, nvl(deliver_to_org, FND_API.G_MISS_CHAR)
, nvl(sold_to_contact_id, FND_API.G_MISS_NUM)
, nvl(sold_to_contact, FND_API.G_MISS_CHAR)
, nvl(ship_to_contact_id, FND_API.G_MISS_NUM)
, nvl(ship_to_contact, FND_API.G_MISS_CHAR)
, nvl(invoice_to_contact_id, FND_API.G_MISS_NUM)
, nvl(invoice_to_contact, FND_API.G_MISS_CHAR)
, nvl(deliver_to_contact_id, FND_API.G_MISS_NUM)
, nvl(deliver_to_contact, FND_API.G_MISS_CHAR)
, nvl(ship_to_address1, FND_API.G_MISS_CHAR)
, nvl(ship_to_address2, FND_API.G_MISS_CHAR)
, nvl(ship_to_address3, FND_API.G_MISS_CHAR)
, nvl(ship_to_address4, FND_API.G_MISS_CHAR)
, nvl(ship_to_city, FND_API.G_MISS_CHAR)
, nvl(ship_to_state,nvl(ship_to_province , FND_API.G_MISS_CHAR)) --bug#9395147 ,9878736
, nvl(ship_to_postal_code, FND_API.G_MISS_CHAR)
, nvl(ship_to_country, FND_API.G_MISS_CHAR)
, nvl(invoice_address1, FND_API.G_MISS_CHAR)
, nvl(invoice_address2, FND_API.G_MISS_CHAR)
, nvl(invoice_address3, FND_API.G_MISS_CHAR)
, nvl(invoice_address4, FND_API.G_MISS_CHAR)
, nvl(invoice_city, FND_API.G_MISS_CHAR)
, nvl(invoice_state,nvl(invoice_province_int , FND_API.G_MISS_CHAR)) ---bug# 9395147 ,9878736
, nvl(invoice_postal_code, FND_API.G_MISS_CHAR)
, nvl(invoice_country, FND_API.G_MISS_CHAR)
, nvl(drop_ship_flag, FND_API.G_MISS_CHAR)
, nvl(booked_flag, FND_API.G_MISS_CHAR)
-- , nvl(closed_flag, FND_API.G_MISS_CHAR)
, nvl(cancelled_flag, FND_API.G_MISS_CHAR)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(attribute16, FND_API.G_MISS_CHAR) -- for bug 2184255
, nvl(attribute17, FND_API.G_MISS_CHAR)
, nvl(attribute18, FND_API.G_MISS_CHAR)
, nvl(attribute19, FND_API.G_MISS_CHAR)
, nvl(attribute20, FND_API.G_MISS_CHAR)
, nvl(tp_context, FND_API.G_MISS_CHAR)
, nvl(tp_attribute1, FND_API.G_MISS_CHAR)
, nvl(tp_attribute2, FND_API.G_MISS_CHAR)
, nvl(tp_attribute3, FND_API.G_MISS_CHAR)
, nvl(tp_attribute4, FND_API.G_MISS_CHAR)
, nvl(tp_attribute5, FND_API.G_MISS_CHAR)
, nvl(tp_attribute6, FND_API.G_MISS_CHAR)
, nvl(tp_attribute7, FND_API.G_MISS_CHAR)
, nvl(tp_attribute8, FND_API.G_MISS_CHAR)
, nvl(tp_attribute9, FND_API.G_MISS_CHAR)
, nvl(tp_attribute10, FND_API.G_MISS_CHAR)
, nvl(tp_attribute11, FND_API.G_MISS_CHAR)
, nvl(tp_attribute12, FND_API.G_MISS_CHAR)
, nvl(tp_attribute13, FND_API.G_MISS_CHAR)
, nvl(tp_attribute14, FND_API.G_MISS_CHAR)
, nvl(tp_attribute15, FND_API.G_MISS_CHAR)
, nvl(global_attribute_category, FND_API.G_MISS_CHAR)
, nvl(global_attribute1, FND_API.G_MISS_CHAR)
, nvl(global_attribute2, FND_API.G_MISS_CHAR)
, nvl(global_attribute3, FND_API.G_MISS_CHAR)
, nvl(global_attribute4, FND_API.G_MISS_CHAR)
, nvl(global_attribute5, FND_API.G_MISS_CHAR)
, nvl(global_attribute6, FND_API.G_MISS_CHAR)
, nvl(global_attribute7, FND_API.G_MISS_CHAR)
, nvl(global_attribute8, FND_API.G_MISS_CHAR)
, nvl(global_attribute9, FND_API.G_MISS_CHAR)
, nvl(global_attribute10, FND_API.G_MISS_CHAR)
, nvl(global_attribute11, FND_API.G_MISS_CHAR)
, nvl(global_attribute12, FND_API.G_MISS_CHAR)
, nvl(global_attribute13, FND_API.G_MISS_CHAR)
, nvl(global_attribute14, FND_API.G_MISS_CHAR)
, nvl(global_attribute15, FND_API.G_MISS_CHAR)
, nvl(global_attribute16, FND_API.G_MISS_CHAR)
, nvl(global_attribute17, FND_API.G_MISS_CHAR)
, nvl(global_attribute18, FND_API.G_MISS_CHAR)
, nvl(global_attribute19, FND_API.G_MISS_CHAR)
, nvl(global_attribute20, FND_API.G_MISS_CHAR)
, request_id
, NVL(request_date, FND_API.G_MISS_DATE)
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(ready_flag, 'Y')
, nvl(status_flag, '0')
, nvl(force_apply_flag, 'N')
, nvl(change_reason, FND_API.G_MISS_CHAR)
, nvl(change_comments, FND_API.G_MISS_CHAR)
, nvl(customer_preference_set_code, FND_API.G_MISS_CHAR)
-- { Start add new columns to select for the Add Customer
, Orig_Sys_Customer_Ref
, Orig_Ship_Address_Ref
, Orig_Bill_Address_Ref
, Orig_Deliver_Address_Ref
, Sold_to_Contact_Ref
, Ship_to_Contact_Ref
, Bill_to_Contact_Ref
, Deliver_to_Contact_Ref
-- End add new columns to select for the Add Customer}
, Xml_Message_Id
, nvl(ship_to_customer, FND_API.G_MISS_CHAR)
, nvl(ship_to_customer_number, FND_API.G_MISS_CHAR)
, nvl(ship_to_customer_id, FND_API.G_MISS_NUM)
, nvl(invoice_customer, FND_API.G_MISS_CHAR)
, nvl(invoice_customer_number, FND_API.G_MISS_CHAR)
, nvl(invoice_customer_id, FND_API.G_MISS_NUM)
, nvl(deliver_to_customer, FND_API.G_MISS_CHAR)
, nvl(deliver_to_customer_number, FND_API.G_MISS_CHAR)
, nvl(deliver_to_customer_id, FND_API.G_MISS_NUM)
, xml_transaction_type_code
, nvl(blanket_number, FND_API.G_MISS_NUM)
, nvl(shipping_method, FND_API.G_MISS_CHAR)
-- Added pricing_date for the bug 3001346
, nvl(pricing_date, FND_API.G_MISS_DATE)
, response_flag
, nvl(sold_to_site_use_id, FND_API.G_MISS_NUM)
, nvl(sold_to_location_address1, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_address2, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_address3, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_address4, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_city, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_postal_code, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_country, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_state, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_county, FND_API.G_MISS_CHAR)
, nvl(sold_to_location_province, FND_API.G_MISS_CHAR)
-- automatic account creation {
, sold_to_party_id
, sold_to_org_contact_id
, ship_to_party_id
, ship_to_party_site_id
, ship_to_party_site_use_id
, deliver_to_party_id
, deliver_to_party_site_id
, deliver_to_party_site_use_id
, invoice_to_party_id
, invoice_to_party_site_id
, invoice_to_party_site_use_id
-- automatic account creation }
-- start if additional quoting columns
, nvl(transaction_phase_code, FND_API.G_MISS_CHAR)
, nvl(expiration_date, FND_API.G_MISS_DATE)
, nvl(quote_number, FND_API.G_MISS_NUM)
, nvl(quote_date, FND_API.G_MISS_DATE)
, nvl(sales_document_name, FND_API.G_MISS_CHAR)
, nvl(user_status_code, FND_API.G_MISS_CHAR)
-- end of additional quoting columns
-- { Distributer Order related change
, nvl(end_customer_id, FND_API.G_MISS_NUM)
, nvl(end_customer_contact_id, FND_API.G_MISS_NUM)
, nvl(end_customer_site_use_id, FND_API.G_MISS_NUM)
, nvl(end_customer_name, FND_API.G_MISS_CHAR) --mvijayku
, nvl(end_customer_address1, FND_API.G_MISS_CHAR)
, nvl(end_customer_address2, FND_API.G_MISS_CHAR)
, nvl(end_customer_address3, FND_API.G_MISS_CHAR)
, nvl(end_customer_address4, FND_API.G_MISS_CHAR)
-- , nvl(end_customer_location, FND_API.G_MISS_CHAR)
, nvl(end_customer_city, FND_API.G_MISS_CHAR)
, nvl(end_customer_state,nvl(end_customer_province , FND_API.G_MISS_CHAR)) ---bug# 9395147 ,9878736
, nvl(end_customer_postal_code, FND_API.G_MISS_CHAR)
, nvl(end_customer_country, FND_API.G_MISS_CHAR)
, nvl(end_customer_contact, FND_API.G_MISS_CHAR)
, nvl(end_customer_number, FND_API.G_MISS_CHAR)
, nvl(ib_owner, FND_API.G_MISS_CHAR)
, nvl(ib_current_location, FND_API.G_MISS_CHAR)
, nvl(ib_installed_at_location, FND_API.G_MISS_CHAR)
, nvl(ib_owner_code, FND_API.G_MISS_CHAR)
, nvl(ib_current_location_code, FND_API.G_MISS_CHAR)
, nvl(ib_installed_at_location_code, FND_API.G_MISS_CHAR)
, END_CUSTOMER_PARTY_ID
, END_CUSTOMER_ORG_CONTACT_ID
, END_CUSTOMER_PARTY_SITE_ID
, END_CUSTOMER_PARTY_SITE_USE_ID
, END_CUSTOMER_PARTY_NUMBER
-- Distributer Order related change }
-- for automatic account creation
, sold_to_party_number
, ship_to_party_number
, invoice_to_party_number
, deliver_to_party_number
-- for automatic account creation
, nvl(deliver_to_address1, FND_API.G_MISS_CHAR)
, nvl(deliver_to_address2, FND_API.G_MISS_CHAR)
, nvl(deliver_to_address3, FND_API.G_MISS_CHAR)
, nvl(deliver_to_address4, FND_API.G_MISS_CHAR)
, nvl(deliver_to_state , nvl(deliver_to_province,FND_API.G_MISS_CHAR)) ---bug# 9395147 ,9878736
, nvl(deliver_to_county , FND_API.G_MISS_CHAR)
, nvl(deliver_to_country , FND_API.G_MISS_CHAR)
, nvl(deliver_to_province, FND_API.G_MISS_CHAR)
, nvl(deliver_to_city , FND_API.G_MISS_CHAR)
, nvl(deliver_to_postal_code , FND_API.G_MISS_CHAR)
, nvl(instrument_security_code, FND_API.G_MISS_CHAR) --R12 CVV2
FROM oe_headers_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
AND nvl(error_flag,'N') = 'N'
AND nvl(ready_flag,'Y') = 'Y'
AND nvl(rejected_flag,'N') = 'N'
AND nvl(force_apply_flag,'Y') = 'Y'
FOR UPDATE NOWAIT
ORDER BY org_id,order_source_id, orig_sys_document_ref, change_sequence
;
SELECT nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(list_header_id, FND_API.G_MISS_NUM)
, nvl(list_line_id, FND_API.G_MISS_NUM)
, nvl(discount_name, FND_API.G_MISS_CHAR)
, nvl(percent, FND_API.G_MISS_NUM)
, nvl(automatic_flag, FND_API.G_MISS_CHAR)
, nvl(applied_flag, FND_API.G_MISS_CHAR)
, nvl(operand, FND_API.G_MISS_NUM)
, nvl(arithmetic_operator, FND_API.G_MISS_CHAR)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, request_id
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
-- Price Adjustment related changes bug# 1220921 (Start)
, nvl( AC_CONTEXT, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE1, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE2, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE3, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE4, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE5, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE6, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE7, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE8, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE9, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE10, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE11, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE12, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE13, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE14, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE15, FND_API.G_MISS_CHAR)
, nvl( LIST_NAME, FND_API.G_MISS_CHAR)
, nvl( LIST_LINE_TYPE_CODE, FND_API.G_MISS_CHAR)
, nvl( LIST_LINE_NUMBER, FND_API.G_MISS_CHAR)
, nvl( VERSION_NUMBER, FND_API.G_MISS_CHAR)
, nvl( INVOICED_FLAG, FND_API.G_MISS_CHAR)
, nvl( ESTIMATED_FLAG, FND_API.G_MISS_CHAR)
, nvl( INC_IN_SALES_PERFORMANCE, FND_API.G_MISS_CHAR)
, nvl( CHARGE_TYPE_CODE, FND_API.G_MISS_CHAR)
, nvl( CHARGE_SUBTYPE_CODE, FND_API.G_MISS_CHAR)
, nvl( CREDIT_OR_CHARGE_FLAG, FND_API.G_MISS_CHAR)
, nvl( INCLUDE_ON_RETURNS_FLAG, FND_API.G_MISS_CHAR)
, nvl( COST_ID, FND_API.G_MISS_NUM)
, nvl( TAX_CODE, FND_API.G_MISS_CHAR)
, nvl( PARENT_ADJUSTMENT_ID, FND_API.G_MISS_NUM)
, nvl(MODIFIER_MECHANISM_TYPE_CODE,FND_API.G_MISS_CHAR)
, nvl( MODIFIED_FROM, FND_API.G_MISS_CHAR)
, nvl( MODIFIED_TO, FND_API.G_MISS_CHAR)
, nvl( UPDATED_FLAG, FND_API.G_MISS_CHAR)
, nvl( UPDATE_ALLOWED, FND_API.G_MISS_CHAR)
, nvl( CHANGE_REASON_CODE, FND_API.G_MISS_CHAR)
, nvl( CHANGE_REASON_TEXT, FND_API.G_MISS_CHAR)
, nvl( PRICING_PHASE_ID, FND_API.G_MISS_NUM)
, nvl( ADJUSTED_AMOUNT, FND_API.G_MISS_NUM)
-- Price Adjustment related changes bug# 1220921 (End)
FROM oe_price_adjs_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_discount_ref
;
SELECT nvl(orig_sys_atts_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(creation_date , FND_API.G_MISS_DATE)
, nvl(created_by , FND_API.G_MISS_NUM)
, nvl(last_update_date , FND_API.G_MISS_DATE)
, nvl(last_updated_by , FND_API.G_MISS_NUM)
, nvl(last_update_login , FND_API.G_MISS_NUM)
, nvl(program_application_id , FND_API.G_MISS_NUM)
, nvl(program_id , FND_API.G_MISS_NUM)
, nvl(program_update_date, FND_API.G_MISS_DATE)
, nvl(request_id , FND_API.G_MISS_NUM)
, nvl(flex_title , FND_API.G_MISS_CHAR)
, nvl(pricing_context , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute1 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute2 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute3 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute4 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute5 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute6 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute7 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute8 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute9 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute10 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute11 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute12 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute13 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute14 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute15 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute16 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute17 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute18 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute19 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute20 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute21 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute22 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute23 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute24 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute25 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute26 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute27 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute28 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute29 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute30 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute31 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute32 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute33 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute34 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute35 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute36 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute37 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute38 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute39 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute40 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute41 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute42 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute43 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute44 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute45 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute46 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute47 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute48 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute49 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute50 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute51 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute52 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute53 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute54 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute55 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute56 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute57 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute58 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute59 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute60 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute61 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute62 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute63 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute64 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute65 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute66 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute67 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute68 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute69 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute70 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute71 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute72 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute73 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute74 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute75 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute76 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute77 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute78 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute79 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute80 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute81 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute82 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute83 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute84 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute85 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute86 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute87 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute88 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute89 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute90 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute91 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute92 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute93 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute94 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute95 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute96 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute97 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute98 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute99 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute100 , FND_API.G_MISS_CHAR)
, nvl(context , FND_API.G_MISS_CHAR)
, nvl(attribute1 , FND_API.G_MISS_CHAR)
, nvl(attribute2 , FND_API.G_MISS_CHAR)
, nvl(attribute3 , FND_API.G_MISS_CHAR)
, nvl(attribute4 , FND_API.G_MISS_CHAR)
, nvl(attribute5 , FND_API.G_MISS_CHAR)
, nvl(attribute6 , FND_API.G_MISS_CHAR)
, nvl(attribute7 , FND_API.G_MISS_CHAR)
, nvl(attribute8 , FND_API.G_MISS_CHAR)
, nvl(attribute9 , FND_API.G_MISS_CHAR)
, nvl(attribute10 , FND_API.G_MISS_CHAR)
, nvl(attribute11 , FND_API.G_MISS_CHAR)
, nvl(attribute12 , FND_API.G_MISS_CHAR)
, nvl(attribute13 , FND_API.G_MISS_CHAR)
, nvl(attribute14 , FND_API.G_MISS_CHAR)
, nvl(attribute15 , FND_API.G_MISS_CHAR)
, nvl(operation_code , OE_GLOBALS.G_OPR_CREATE)
FROM oe_price_atts_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER by orig_sys_document_ref;
SELECT nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(salesrep_id, FND_API.G_MISS_NUM)
, nvl(salesrep , FND_API.G_MISS_CHAR)
, nvl(sales_credit_type_id, FND_API.G_MISS_NUM)
, nvl(sales_credit_type, FND_API.G_MISS_CHAR)
, nvl(percent, FND_API.G_MISS_NUM)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
, nvl(change_reason, FND_API.G_MISS_CHAR)
, nvl(change_comments, FND_API.G_MISS_CHAR)
FROM oe_credits_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_credit_ref
;
SELECT nvl(orig_sys_payment_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(payment_type_code, FND_API.G_MISS_CHAR)
, nvl(commitment, FND_API.G_MISS_CHAR)
, nvl(payment_trx_id, FND_API.G_MISS_NUM)
, nvl(payment_method, FND_API.G_MISS_CHAR)
, nvl(receipt_method_id, FND_API.G_MISS_NUM)
, nvl(payment_collection_event,FND_API.G_MISS_CHAR)
, nvl(payment_set_id, FND_API.G_MISS_NUM)
, nvl(prepaid_amount, FND_API.G_MISS_NUM)
, nvl(credit_card_number, FND_API.G_MISS_CHAR)
, nvl(credit_card_holder_name, FND_API.G_MISS_CHAR)
, nvl(credit_card_expiration_date,FND_API.G_MISS_DATE)
, nvl(credit_card_code, FND_API.G_MISS_CHAR)
, nvl(credit_card_approval_code,FND_API.G_MISS_CHAR)
, nvl(credit_card_approval_date,FND_API.G_MISS_DATE)
, nvl(check_number, FND_API.G_MISS_CHAR)--6367320
, nvl(payment_amount, FND_API.G_MISS_NUM)
, nvl(payment_percentage, FND_API.G_MISS_NUM)
, nvl(creation_date, FND_API.G_MISS_DATE)
, nvl(created_by, FND_API.G_MISS_NUM)
, nvl(last_update_date, FND_API.G_MISS_DATE)
, nvl(last_updated_by, FND_API.G_MISS_NUM)
, nvl(last_update_login, FND_API.G_MISS_NUM)
, nvl(program_application_id, FND_API.G_MISS_NUM)
, nvl(program_id, FND_API.G_MISS_NUM)
, nvl(program_update_date, FND_API.G_MISS_DATE)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
, nvl(payment_number, FND_API.G_MISS_NUM)
, nvl(header_id, FND_API.G_MISS_NUM)
, nvl(line_id, FND_API.G_MISS_NUM)
, nvl(DEFER_PAYMENT_PROCESSING_FLAG, FND_API.G_MISS_CHAR) -- Added for bug 8478559
, nvl(trxn_extension_id, FND_API.G_MISS_NUM)
, nvl(instrument_security_code,FND_API.G_MISS_CHAR) --R12 CVV2
FROM oe_payments_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_payment_ref;
SELECT nvl(order_source_id, FND_API.G_MISS_NUM)
, nvl(orig_sys_document_ref, FND_API.G_MISS_CHAR)
, nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
, nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(org_id, FND_API.G_MISS_NUM)
, nvl(line_number, FND_API.G_MISS_NUM)
, nvl(shipment_number, FND_API.G_MISS_NUM)
, nvl(line_id, FND_API.G_MISS_NUM)
, nvl(line_type_id, FND_API.G_MISS_NUM)
, nvl(line_type, FND_API.G_MISS_CHAR)
, nvl(item_type_code, FND_API.G_MISS_CHAR)
, nvl(inventory_item_id, FND_API.G_MISS_NUM)
, nvl(inventory_item, FND_API.G_MISS_CHAR)
, nvl(top_model_line_ref, FND_API.G_MISS_CHAR)
, nvl(link_to_line_ref, FND_API.G_MISS_CHAR)
, nvl(explosion_date, FND_API.G_MISS_DATE)
, nvl(ato_line_id, FND_API.G_MISS_NUM)
, nvl(component_sequence_id, FND_API.G_MISS_NUM)
, nvl(component_code, FND_API.G_MISS_CHAR)
, nvl(sort_order, FND_API.G_MISS_CHAR)
, nvl(model_group_number, FND_API.G_MISS_NUM)
, nvl(option_number, FND_API.G_MISS_NUM)
, nvl(option_flag, 'N')
, nvl(ship_model_complete_flag, FND_API.G_MISS_CHAR)
, nvl(source_type_code, FND_API.G_MISS_CHAR)
, nvl(schedule_status_code, FND_API.G_MISS_CHAR)
, nvl(schedule_ship_date, FND_API.G_MISS_DATE)
, nvl(late_demand_penalty_factor, FND_API.G_MISS_NUM)
, nvl(schedule_arrival_date, FND_API.G_MISS_DATE)
, nvl(actual_arrival_date, FND_API.G_MISS_DATE)
, nvl(request_date, FND_API.G_MISS_DATE)
, nvl(promise_date, FND_API.G_MISS_DATE)
, nvl(delivery_lead_time, FND_API.G_MISS_NUM)
, nvl(ordered_quantity, FND_API.G_MISS_NUM)
, nvl(order_quantity_uom , FND_API.G_MISS_CHAR)
, nvl(shipping_quantity, FND_API.G_MISS_NUM)
, nvl(shipping_quantity_uom, FND_API.G_MISS_CHAR)
, nvl(shipped_quantity, FND_API.G_MISS_NUM)
, nvl(cancelled_quantity, FND_API.G_MISS_NUM)
, nvl(fulfilled_quantity, FND_API.G_MISS_NUM)
/* OPM variables */
, nvl(ordered_quantity2, FND_API.G_MISS_NUM)
, nvl(ordered_quantity_uom2 , FND_API.G_MISS_CHAR)
, nvl(shipping_quantity2, FND_API.G_MISS_NUM)
, nvl(shipping_quantity_uom2, FND_API.G_MISS_CHAR)
, nvl(shipped_quantity2, FND_API.G_MISS_NUM)
, nvl(cancelled_quantity2, FND_API.G_MISS_NUM)
, nvl(fulfilled_quantity2, FND_API.G_MISS_NUM)
, nvl(preferred_grade, FND_API.G_MISS_CHAR)
/* end of OPM variables */
, nvl(pricing_quantity, FND_API.G_MISS_NUM)
, nvl(pricing_quantity_uom, FND_API.G_MISS_CHAR)
, nvl(sold_from_org_id, FND_API.G_MISS_NUM)
, nvl(sold_from_org, FND_API.G_MISS_CHAR)
, nvl(sold_to_org_id , FND_API.G_MISS_NUM)
, nvl(sold_to_org, FND_API.G_MISS_CHAR)
, nvl(ship_from_org_id, FND_API.G_MISS_NUM)
, nvl(ship_from_org, FND_API.G_MISS_CHAR)
, nvl(ship_to_org_id , FND_API.G_MISS_NUM)
, nvl(ship_to_org, FND_API.G_MISS_CHAR)
, nvl(deliver_to_org_id, FND_API.G_MISS_NUM)
, nvl(deliver_to_org, FND_API.G_MISS_CHAR)
, nvl(invoice_to_org_id, FND_API.G_MISS_NUM)
, nvl(invoice_to_org, FND_API.G_MISS_CHAR)
, nvl(ship_to_address1, FND_API.G_MISS_CHAR)
, nvl(ship_to_address2, FND_API.G_MISS_CHAR)
, nvl(ship_to_address3, FND_API.G_MISS_CHAR)
, nvl(ship_to_address4, FND_API.G_MISS_CHAR)
, nvl(ship_to_city, FND_API.G_MISS_CHAR)
, nvl(ship_to_state,nvl(ship_to_province ,FND_API.G_MISS_CHAR)) ---bug# 9395147 ,9878736
, nvl(ship_to_postal_code, FND_API.G_MISS_CHAR)
, nvl(ship_to_country, FND_API.G_MISS_CHAR)
, nvl(ship_to_contact_id, FND_API.G_MISS_NUM)
, nvl(ship_to_contact, FND_API.G_MISS_CHAR)
, nvl(deliver_to_contact_id, FND_API.G_MISS_NUM)
, nvl(deliver_to_contact, FND_API.G_MISS_CHAR)
, nvl(invoice_to_contact_id, FND_API.G_MISS_NUM)
, nvl(invoice_to_contact, FND_API.G_MISS_CHAR)
, nvl(drop_ship_flag, FND_API.G_MISS_CHAR)
, nvl(ship_tolerance_above, FND_API.G_MISS_NUM)
, nvl(ship_tolerance_below, FND_API.G_MISS_NUM)
, nvl(price_list_id, FND_API.G_MISS_NUM)
, nvl(price_list, FND_API.G_MISS_CHAR)
, nvl(pricing_date, FND_API.G_MISS_DATE)
, nvl(unit_list_price, FND_API.G_MISS_NUM)
, nvl(unit_selling_price, FND_API.G_MISS_NUM)
--, nvl(calculate_price_flag, 'Y') --commented for BUG#7304558
, nvl(calculate_price_flag, FND_API.G_MISS_CHAR) --added for BUG#7304558
, nvl(ship_set_id, FND_API.G_MISS_NUM)
, nvl(ship_set_name, FND_API.G_MISS_CHAR)
, nvl(arrival_set_id, FND_API.G_MISS_NUM)
, nvl(arrival_set_name, FND_API.G_MISS_CHAR)
, nvl(fulfillment_set_id, FND_API.G_MISS_NUM)
, nvl(fulfillment_set_name, FND_API.G_MISS_CHAR)
, nvl(tax_code, FND_API.G_MISS_CHAR)
, nvl(tax_value, FND_API.G_MISS_NUM)
, nvl(tax_date, FND_API.G_MISS_DATE)
, nvl(tax_point_code, FND_API.G_MISS_CHAR)
, nvl(tax_point, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_flag, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_number, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_reason_code, FND_API.G_MISS_CHAR)
, nvl(tax_exempt_reason, FND_API.G_MISS_CHAR)
, nvl(agreement_id, FND_API.G_MISS_NUM)
, nvl(agreement, FND_API.G_MISS_CHAR)
, nvl(invoicing_rule_id, FND_API.G_MISS_NUM)
, nvl(invoicing_rule, FND_API.G_MISS_CHAR)
, nvl(accounting_rule_id, FND_API.G_MISS_NUM)
, nvl(accounting_rule_duration, FND_API.G_MISS_NUM)
, nvl(accounting_rule, FND_API.G_MISS_CHAR)
, nvl(payment_term_id, FND_API.G_MISS_NUM)
, nvl(payment_term, FND_API.G_MISS_CHAR)
, nvl(demand_class_code, FND_API.G_MISS_CHAR)
, nvl(shipment_priority_code, FND_API.G_MISS_CHAR)
, nvl(shipment_priority, FND_API.G_MISS_CHAR)
, nvl(shipping_method_code, FND_API.G_MISS_CHAR)
, nvl(shipping_instructions, FND_API.G_MISS_CHAR)
, nvl(packing_instructions, FND_API.G_MISS_CHAR)
, nvl(freight_carrier_code, FND_API.G_MISS_CHAR)
, nvl(freight_terms_code, FND_API.G_MISS_CHAR)
, nvl(freight_terms, FND_API.G_MISS_CHAR)
, nvl(fob_point_code, FND_API.G_MISS_CHAR)
, nvl(fob_point, FND_API.G_MISS_CHAR)
, nvl(return_reason_code, FND_API.G_MISS_CHAR)
, nvl(reference_type, FND_API.G_MISS_CHAR)
, nvl(reference_header_id, FND_API.G_MISS_NUM)
, nvl(reference_line_id, FND_API.G_MISS_NUM)
, nvl(credit_invoice_line_id, FND_API.G_MISS_NUM)
, nvl(customer_po_number, FND_API.G_MISS_CHAR)
--bug#4063831:
--FP:11I9-12.0CUST_PRODUCTION_SEQ_NUM IS NOT GETTING POPULATED
-- DURING ORDER IMPORT
, nvl(cust_production_seq_num, FND_API.G_MISS_CHAR)
, nvl(customer_line_number, FND_API.G_MISS_CHAR)
, nvl(customer_shipment_number, FND_API.G_MISS_CHAR)
, nvl(customer_item_id, FND_API.G_MISS_NUM)
, nvl(customer_item_id_type, FND_API.G_MISS_CHAR)
, nvl(customer_item_name, FND_API.G_MISS_CHAR)
-- , nvl(customer_item_revision, FND_API.G_MISS_CHAR)
, nvl(customer_item_net_price, FND_API.G_MISS_NUM)
, nvl(customer_payment_term_id, FND_API.G_MISS_NUM)
, nvl(customer_payment_term, FND_API.G_MISS_CHAR)
, nvl(demand_bucket_type_code, FND_API.G_MISS_CHAR)
, nvl(demand_bucket_type, FND_API.G_MISS_CHAR)
, nvl(customer_dock_code, FND_API.G_MISS_CHAR)
, nvl(customer_job, FND_API.G_MISS_CHAR)
, nvl(customer_production_line, FND_API.G_MISS_CHAR)
, nvl(cust_model_serial_number, FND_API.G_MISS_CHAR)
, nvl(project_id, FND_API.G_MISS_NUM)
, nvl(project, FND_API.G_MISS_CHAR)
, nvl(task_id, FND_API.G_MISS_NUM)
, nvl(task, FND_API.G_MISS_CHAR)
, nvl(end_item_unit_number, FND_API.G_MISS_CHAR)
, nvl(item_revision, FND_API.G_MISS_CHAR)
, nvl(service_duration, FND_API.G_MISS_NUM)
, nvl(service_period, FND_API.G_MISS_CHAR)
, nvl(service_start_date, FND_API.G_MISS_DATE)
, nvl(service_end_date, FND_API.G_MISS_DATE)
, nvl(service_coterminate_flag, FND_API.G_MISS_CHAR)
, nvl(unit_selling_percent, FND_API.G_MISS_NUM)
, nvl(unit_list_percent, FND_API.G_MISS_NUM)
, nvl(unit_percent_base_price, FND_API.G_MISS_NUM)
, nvl(service_number, FND_API.G_MISS_NUM)
-- , nvl(fulfilled_flag, FND_API.G_MISS_CHAR)--ToBeAdded
-- , nvl(closed_flag, FND_API.G_MISS_CHAR)
, nvl(cancelled_flag, FND_API.G_MISS_CHAR)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(attribute16, FND_API.G_MISS_CHAR) -- for bug 2184255
, nvl(attribute17, FND_API.G_MISS_CHAR)
, nvl(attribute18, FND_API.G_MISS_CHAR)
, nvl(attribute19, FND_API.G_MISS_CHAR)
, nvl(attribute20, FND_API.G_MISS_CHAR)
, nvl(tp_context, FND_API.G_MISS_CHAR)
, nvl(tp_attribute1, FND_API.G_MISS_CHAR)
, nvl(tp_attribute2, FND_API.G_MISS_CHAR)
, nvl(tp_attribute3, FND_API.G_MISS_CHAR)
, nvl(tp_attribute4, FND_API.G_MISS_CHAR)
, nvl(tp_attribute5, FND_API.G_MISS_CHAR)
, nvl(tp_attribute6, FND_API.G_MISS_CHAR)
, nvl(tp_attribute7, FND_API.G_MISS_CHAR)
, nvl(tp_attribute8, FND_API.G_MISS_CHAR)
, nvl(tp_attribute9, FND_API.G_MISS_CHAR)
, nvl(tp_attribute10, FND_API.G_MISS_CHAR)
, nvl(tp_attribute11, FND_API.G_MISS_CHAR)
, nvl(tp_attribute12, FND_API.G_MISS_CHAR)
, nvl(tp_attribute13, FND_API.G_MISS_CHAR)
, nvl(tp_attribute14, FND_API.G_MISS_CHAR)
, nvl(tp_attribute15, FND_API.G_MISS_CHAR)
, nvl(industry_context, FND_API.G_MISS_CHAR)
, nvl(industry_attribute1, FND_API.G_MISS_CHAR)
, nvl(industry_attribute2, FND_API.G_MISS_CHAR)
, nvl(industry_attribute3, FND_API.G_MISS_CHAR)
, nvl(industry_attribute4, FND_API.G_MISS_CHAR)
, nvl(industry_attribute5, FND_API.G_MISS_CHAR)
, nvl(industry_attribute6, FND_API.G_MISS_CHAR)
, nvl(industry_attribute7, FND_API.G_MISS_CHAR)
, nvl(industry_attribute8, FND_API.G_MISS_CHAR)
, nvl(industry_attribute9, FND_API.G_MISS_CHAR)
, nvl(industry_attribute10, FND_API.G_MISS_CHAR)
, nvl(industry_attribute11, FND_API.G_MISS_CHAR)
, nvl(industry_attribute12, FND_API.G_MISS_CHAR)
, nvl(industry_attribute13, FND_API.G_MISS_CHAR)
, nvl(industry_attribute14, FND_API.G_MISS_CHAR)
, nvl(industry_attribute15, FND_API.G_MISS_CHAR)
, nvl(industry_attribute16, FND_API.G_MISS_CHAR)
, nvl(industry_attribute17, FND_API.G_MISS_CHAR)
, nvl(industry_attribute18, FND_API.G_MISS_CHAR)
, nvl(industry_attribute19, FND_API.G_MISS_CHAR)
, nvl(industry_attribute20, FND_API.G_MISS_CHAR)
, nvl(industry_attribute21, FND_API.G_MISS_CHAR)
, nvl(industry_attribute22, FND_API.G_MISS_CHAR)
, nvl(industry_attribute23, FND_API.G_MISS_CHAR)
, nvl(industry_attribute24, FND_API.G_MISS_CHAR)
, nvl(industry_attribute25, FND_API.G_MISS_CHAR)
, nvl(industry_attribute26, FND_API.G_MISS_CHAR)
, nvl(industry_attribute27, FND_API.G_MISS_CHAR)
, nvl(industry_attribute28, FND_API.G_MISS_CHAR)
, nvl(industry_attribute29, FND_API.G_MISS_CHAR)
, nvl(industry_attribute30, FND_API.G_MISS_CHAR)
, nvl(pricing_context, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute1, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute2, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute3, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute4, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute5, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute6, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute7, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute8, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute9, FND_API.G_MISS_CHAR)
, nvl(pricing_attribute10, FND_API.G_MISS_CHAR)
, nvl(global_attribute_category, FND_API.G_MISS_CHAR)
, nvl(global_attribute1, FND_API.G_MISS_CHAR)
, nvl(global_attribute2, FND_API.G_MISS_CHAR)
, nvl(global_attribute3, FND_API.G_MISS_CHAR)
, nvl(global_attribute4, FND_API.G_MISS_CHAR)
, nvl(global_attribute5, FND_API.G_MISS_CHAR)
, nvl(global_attribute6, FND_API.G_MISS_CHAR)
, nvl(global_attribute7, FND_API.G_MISS_CHAR)
, nvl(global_attribute8, FND_API.G_MISS_CHAR)
, nvl(global_attribute9, FND_API.G_MISS_CHAR)
, nvl(global_attribute10, FND_API.G_MISS_CHAR)
, nvl(global_attribute11, FND_API.G_MISS_CHAR)
, nvl(global_attribute12, FND_API.G_MISS_CHAR)
, nvl(global_attribute13, FND_API.G_MISS_CHAR)
, nvl(global_attribute14, FND_API.G_MISS_CHAR)
, nvl(global_attribute15, FND_API.G_MISS_CHAR)
, nvl(global_attribute16, FND_API.G_MISS_CHAR)
, nvl(global_attribute17, FND_API.G_MISS_CHAR)
, nvl(global_attribute18, FND_API.G_MISS_CHAR)
, nvl(global_attribute19, FND_API.G_MISS_CHAR)
, nvl(global_attribute20, FND_API.G_MISS_CHAR)
, nvl(return_context, FND_API.G_MISS_CHAR)
, nvl(return_attribute1, FND_API.G_MISS_CHAR)
, nvl(return_attribute2, FND_API.G_MISS_CHAR)
, nvl(return_attribute3, FND_API.G_MISS_CHAR)
, nvl(return_attribute4, FND_API.G_MISS_CHAR)
, nvl(return_attribute5, FND_API.G_MISS_CHAR)
, nvl(return_attribute6, FND_API.G_MISS_CHAR)
, nvl(return_attribute7, FND_API.G_MISS_CHAR)
, nvl(return_attribute8, FND_API.G_MISS_CHAR)
, nvl(return_attribute9, FND_API.G_MISS_CHAR)
, nvl(return_attribute10, FND_API.G_MISS_CHAR)
, nvl(return_attribute11, FND_API.G_MISS_CHAR)
, nvl(return_attribute12, FND_API.G_MISS_CHAR)
, nvl(return_attribute13, FND_API.G_MISS_CHAR)
, nvl(return_attribute14, FND_API.G_MISS_CHAR)
, nvl(return_attribute15, FND_API.G_MISS_CHAR)
, request_id
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
, nvl(change_reason, FND_API.G_MISS_CHAR)
, nvl(change_comments, FND_API.G_MISS_CHAR)
, nvl(service_txn_reason_code, FND_API.G_MISS_CHAR)
, nvl(service_txn_comments, FND_API.G_MISS_CHAR)
, nvl(service_reference_type_code, FND_API.G_MISS_CHAR)
, nvl(service_reference_order, FND_API.G_MISS_CHAR)
, nvl(service_reference_line, FND_API.G_MISS_CHAR)
, nvl(service_reference_system, FND_API.G_MISS_CHAR)
, INVENTORY_ITEM_SEGMENT_1
, INVENTORY_ITEM_SEGMENT_2
, INVENTORY_ITEM_SEGMENT_3
, INVENTORY_ITEM_SEGMENT_4
, INVENTORY_ITEM_SEGMENT_5
, INVENTORY_ITEM_SEGMENT_6
, INVENTORY_ITEM_SEGMENT_7
, INVENTORY_ITEM_SEGMENT_8
, INVENTORY_ITEM_SEGMENT_9
, INVENTORY_ITEM_SEGMENT_10
, INVENTORY_ITEM_SEGMENT_11
, INVENTORY_ITEM_SEGMENT_12
, INVENTORY_ITEM_SEGMENT_13
, INVENTORY_ITEM_SEGMENT_14
, INVENTORY_ITEM_SEGMENT_15
, INVENTORY_ITEM_SEGMENT_16
, INVENTORY_ITEM_SEGMENT_17
, INVENTORY_ITEM_SEGMENT_18
, INVENTORY_ITEM_SEGMENT_19
, INVENTORY_ITEM_SEGMENT_20
, nvl(commitment, FND_API.G_MISS_CHAR)
, nvl(commitment_id, FND_API.G_MISS_NUM)
-- aksingh subinventory
, nvl(subinventory, FND_API.G_MISS_CHAR)
,nvl(salesrep, FND_API.G_MISS_CHAR)
,nvl(salesrep_id, FND_API.G_MISS_NUM)
, nvl(earliest_acceptable_date, FND_API.G_MISS_DATE)
, nvl(latest_acceptable_date, FND_API.G_MISS_DATE)
, split_from_line_ref --bsadri
, split_from_shipment_ref
, nvl(invoice_to_address1, FND_API.G_MISS_CHAR)
, nvl(invoice_to_address2, FND_API.G_MISS_CHAR)
, nvl(invoice_to_address3, FND_API.G_MISS_CHAR)
, nvl(invoice_to_address4, FND_API.G_MISS_CHAR)
, nvl(invoice_to_city, FND_API.G_MISS_CHAR)
, nvl(invoice_to_state,nvl(invoice_to_province ,FND_API.G_MISS_CHAR)) ----bug# 9395147 ,9878736
, nvl(invoice_to_postal_code, FND_API.G_MISS_CHAR)
, nvl(invoice_to_country, FND_API.G_MISS_CHAR)
-- { Start add new columns to select for the Add Customer
, Orig_Ship_Address_Ref
, Orig_Bill_Address_Ref
, Orig_Deliver_Address_Ref
, Ship_to_Contact_Ref
, Bill_to_Contact_Ref
, Deliver_to_Contact_Ref
-- End add new columns to select for the Add Customer}
, nvl(Config_Header_Id, FND_API.G_MISS_NUM)
, nvl(Config_Rev_Nbr, FND_API.G_MISS_NUM)
, nvl(Configuration_ID, FND_API.G_MISS_NUM)
, nvl(ship_to_customer_name, FND_API.G_MISS_CHAR)
, nvl(ship_to_customer_number, FND_API.G_MISS_CHAR)
, nvl(ship_to_customer_id, FND_API.G_MISS_NUM)
, nvl(invoice_to_customer_name, FND_API.G_MISS_CHAR)
, nvl(invoice_to_customer_number, FND_API.G_MISS_CHAR)
, nvl(invoice_to_customer_id, FND_API.G_MISS_NUM)
, nvl(deliver_to_customer_name, FND_API.G_MISS_CHAR)
, nvl(deliver_to_customer_number, FND_API.G_MISS_CHAR)
, nvl(deliver_to_customer_id, FND_API.G_MISS_NUM)
, nvl(user_item_description, FND_API.G_MISS_CHAR)
, override_atp_date_code
, xml_transaction_type_code
, nvl(blanket_number, FND_API.G_MISS_NUM)
, nvl(blanket_line_number, FND_API.G_MISS_NUM)
, nvl(shipping_method, FND_API.G_MISS_CHAR)
, nvl(change_sequence, FND_API.G_MISS_CHAR)
-- automatic account creation {
, ship_to_party_id
, ship_to_party_site_id
, ship_to_party_site_use_id
, deliver_to_party_id
, deliver_to_party_site_id
, deliver_to_party_site_use_id
, invoice_to_party_id
, invoice_to_party_site_id
, invoice_to_party_site_use_id
-- automatic account creation }
-- { Distributer Order related change
, nvl(end_customer_id, FND_API.G_MISS_NUM)
, nvl(end_customer_contact_id, FND_API.G_MISS_NUM)
, nvl(end_customer_site_use_id, FND_API.G_MISS_NUM)
, nvl(end_customer_name, FND_API.G_MISS_CHAR) --mvijayku
, nvl(end_customer_address1, FND_API.G_MISS_CHAR)
, nvl(end_customer_address2, FND_API.G_MISS_CHAR)
, nvl(end_customer_address3, FND_API.G_MISS_CHAR)
, nvl(end_customer_address4, FND_API.G_MISS_CHAR)
-- , nvl(end_customer_location, FND_API.G_MISS_CHAR)
, nvl(end_customer_city, FND_API.G_MISS_CHAR)
, nvl(end_customer_state,nvl(end_customer_province ,FND_API.G_MISS_CHAR)) ---bug#9395147 ,9878736
, nvl(end_customer_postal_code, FND_API.G_MISS_CHAR)
, nvl(end_customer_country, FND_API.G_MISS_CHAR)
, nvl(end_customer_contact, FND_API.G_MISS_CHAR)
, nvl(end_customer_number, FND_API.G_MISS_CHAR)
, nvl(ib_owner, FND_API.G_MISS_CHAR)
, nvl(ib_current_location, FND_API.G_MISS_CHAR)
, nvl(ib_installed_at_location, FND_API.G_MISS_CHAR)
, nvl(ib_owner_code, FND_API.G_MISS_CHAR)
, nvl(ib_current_location_code, FND_API.G_MISS_CHAR)
, nvl(ib_installed_at_location_code, FND_API.G_MISS_CHAR)
, END_CUSTOMER_PARTY_ID
, END_CUSTOMER_ORG_CONTACT_ID
, END_CUSTOMER_PARTY_SITE_ID
, END_CUSTOMER_PARTY_SITE_USE_ID
, END_CUSTOMER_PARTY_NUMBER
-- Distributer Order related change }
-- for automatic account creation
, ship_to_party_number
, invoice_to_party_number
, deliver_to_party_number
-- for automatic account creation
, nvl(deliver_to_address1, FND_API.G_MISS_CHAR)
, nvl(deliver_to_address2, FND_API.G_MISS_CHAR)
, nvl(deliver_to_address3, FND_API.G_MISS_CHAR)
, nvl(deliver_to_address4, FND_API.G_MISS_CHAR)
, nvl(deliver_to_state,nvl(deliver_to_province,FND_API.G_MISS_CHAR)) ---bug#9395147 ,9878736
, nvl(deliver_to_county, FND_API.G_MISS_CHAR)
, nvl(deliver_to_country, FND_API.G_MISS_CHAR)
, nvl(deliver_to_province, FND_API.G_MISS_CHAR)
, nvl(deliver_to_city, FND_API.G_MISS_CHAR)
, nvl(deliver_to_postal_code, FND_API.G_MISS_CHAR)
, nvl(planning_priority, FND_API.G_MISS_NUM) --Bug#6924881
--sol_ord_er #16014165
, nvl(SERVICE_BILL_PROFILE_ID ,FND_API.G_MISS_NUM)
, nvl(SERVICE_COV_TEMPLATE_ID,FND_API.G_MISS_NUM)
, nvl(SERVICE_SUBS_TEMPLATE_ID,FND_API.G_MISS_NUM)
, nvl(SERVICE_BILL_OPTION_CODE,FND_API.G_MISS_CHAR)
, nvl(SERVICE_BILLING_PROFILE,FND_API.G_MISS_CHAR)
, nvl(SERVICE_COVERAGE_TEMPLATE,FND_API.G_MISS_CHAR)
, nvl(SERVICE_SUBSCRIPTION_TEMPLATE,FND_API.G_MISS_CHAR)
, nvl(SUBSCRIPTION_ENABLE_FLAG,FND_API.G_MISS_CHAR)
--sol_ord_er #16014165 end
FROM oe_lines_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
AND nvl(rejected_flag,'N') = 'N'
FOR UPDATE NOWAIT
ORDER BY operation_code desc, orig_sys_line_ref asc, orig_sys_shipment_ref asc
;
SELECT nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(list_header_id, FND_API.G_MISS_NUM)
, nvl(list_line_id, FND_API.G_MISS_NUM)
, nvl(discount_name, FND_API.G_MISS_CHAR)
, nvl(percent, FND_API.G_MISS_NUM)
, nvl(automatic_flag, FND_API.G_MISS_CHAR)
, nvl(applied_flag, FND_API.G_MISS_CHAR)
, nvl(operand, FND_API.G_MISS_NUM)
, nvl(arithmetic_operator, FND_API.G_MISS_CHAR)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, request_id
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
-- Price Adjustment related changes bug# 1220921 (Start)
, nvl( AC_CONTEXT, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE1, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE2, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE3, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE4, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE5, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE6, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE7, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE8, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE9, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE10, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE11, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE12, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE13, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE14, FND_API.G_MISS_CHAR)
, nvl( AC_ATTRIBUTE15, FND_API.G_MISS_CHAR)
, nvl( LIST_NAME, FND_API.G_MISS_CHAR)
, nvl( LIST_LINE_TYPE_CODE, FND_API.G_MISS_CHAR)
, nvl( LIST_LINE_NUMBER, FND_API.G_MISS_CHAR)
, nvl( VERSION_NUMBER, FND_API.G_MISS_CHAR)
, nvl( INVOICED_FLAG, FND_API.G_MISS_CHAR)
, nvl( ESTIMATED_FLAG, FND_API.G_MISS_CHAR)
, nvl( INC_IN_SALES_PERFORMANCE, FND_API.G_MISS_CHAR)
, nvl( CHARGE_TYPE_CODE, FND_API.G_MISS_CHAR)
, nvl( CHARGE_SUBTYPE_CODE, FND_API.G_MISS_CHAR)
, nvl( CREDIT_OR_CHARGE_FLAG, FND_API.G_MISS_CHAR)
, nvl( INCLUDE_ON_RETURNS_FLAG, FND_API.G_MISS_CHAR)
, nvl( COST_ID, FND_API.G_MISS_NUM)
, nvl( TAX_CODE, FND_API.G_MISS_CHAR)
, nvl( PARENT_ADJUSTMENT_ID, FND_API.G_MISS_NUM)
, nvl(MODIFIER_MECHANISM_TYPE_CODE,FND_API.G_MISS_CHAR)
, nvl( MODIFIED_FROM, FND_API.G_MISS_CHAR)
, nvl( MODIFIED_TO, FND_API.G_MISS_CHAR)
, nvl( UPDATED_FLAG, FND_API.G_MISS_CHAR)
, nvl( UPDATE_ALLOWED, FND_API.G_MISS_CHAR)
, nvl( CHANGE_REASON_CODE, FND_API.G_MISS_CHAR)
, nvl( CHANGE_REASON_TEXT, FND_API.G_MISS_CHAR)
, nvl( PRICING_PHASE_ID, FND_API.G_MISS_NUM)
, nvl( ADJUSTED_AMOUNT, FND_API.G_MISS_NUM)
-- Price Adjustment related changes bug# 1220921 (End)
FROM oe_price_adjs_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_discount_ref
;
SELECT nvl(creation_date , FND_API.G_MISS_DATE)
, nvl(created_by , FND_API.G_MISS_NUM)
, nvl(last_update_date , FND_API.G_MISS_DATE)
, nvl(last_updated_by , FND_API.G_MISS_NUM)
, nvl(last_update_login , FND_API.G_MISS_NUM)
, nvl(program_application_id , FND_API.G_MISS_NUM)
, nvl(program_id , FND_API.G_MISS_NUM)
, nvl(program_update_date , FND_API.G_MISS_DATE)
, nvl(request_id , FND_API.G_MISS_NUM)
, nvl(flex_title , FND_API.G_MISS_CHAR)
, nvl(pricing_context , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute1 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute2 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute3 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute4 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute5 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute6 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute7 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute8 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute9 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute10 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute11 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute12 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute13 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute14 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute15 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute16 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute17 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute18 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute19 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute20 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute21 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute22 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute23 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute24 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute25 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute26 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute27 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute28 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute29 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute30 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute31 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute32 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute33 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute34 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute35 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute36 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute37 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute38 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute39 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute40 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute41 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute42 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute43 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute44 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute45 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute46 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute47 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute48 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute49 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute50 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute51 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute52 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute53 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute54 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute55 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute56 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute57 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute58 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute59 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute60 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute61 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute62 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute63 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute64 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute65 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute66 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute67 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute68 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute69 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute70 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute71 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute72 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute73 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute74 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute75 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute76 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute77 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute78 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute79 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute80 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute81 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute82 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute83 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute84 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute85 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute86 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute87 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute88 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute89 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute90 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute91 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute92 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute93 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute94 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute95 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute96 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute97 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute98 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute99 , FND_API.G_MISS_CHAR)
, nvl(pricing_attribute100 , FND_API.G_MISS_CHAR)
, nvl(context , FND_API.G_MISS_CHAR)
, nvl(attribute1 , FND_API.G_MISS_CHAR)
, nvl(attribute2 , FND_API.G_MISS_CHAR)
, nvl(attribute3 , FND_API.G_MISS_CHAR)
, nvl(attribute4 , FND_API.G_MISS_CHAR)
, nvl(attribute5 , FND_API.G_MISS_CHAR)
, nvl(attribute6 , FND_API.G_MISS_CHAR)
, nvl(attribute7 , FND_API.G_MISS_CHAR)
, nvl(attribute8 , FND_API.G_MISS_CHAR)
, nvl(attribute9 , FND_API.G_MISS_CHAR)
, nvl(attribute10 , FND_API.G_MISS_CHAR)
, nvl(attribute11 , FND_API.G_MISS_CHAR)
, nvl(attribute12 , FND_API.G_MISS_CHAR)
, nvl(attribute13 , FND_API.G_MISS_CHAR)
, nvl(attribute14 , FND_API.G_MISS_CHAR)
, nvl(attribute15 , FND_API.G_MISS_CHAR)
, nvl(operation_code , OE_GLOBALS.G_OPR_CREATE)
FROM oe_price_atts_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER by orig_sys_line_ref;
SELECT nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(salesrep_id, FND_API.G_MISS_NUM)
, nvl(salesrep , FND_API.G_MISS_CHAR)
, nvl(sales_credit_type_id, FND_API.G_MISS_NUM)
, nvl(sales_credit_type, FND_API.G_MISS_CHAR)
, nvl(percent, FND_API.G_MISS_NUM)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
, nvl(change_reason, FND_API.G_MISS_CHAR)
, nvl(change_comments, FND_API.G_MISS_CHAR)
FROM oe_credits_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_credit_ref
;
SELECT nvl(orig_sys_payment_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(payment_type_code, FND_API.G_MISS_CHAR)
, nvl(commitment, FND_API.G_MISS_CHAR)
, nvl(payment_trx_id, FND_API.G_MISS_NUM)
, nvl(payment_method, FND_API.G_MISS_CHAR)
, nvl(receipt_method_id, FND_API.G_MISS_NUM)
, nvl(payment_collection_event,FND_API.G_MISS_CHAR)
, nvl(payment_set_id, FND_API.G_MISS_NUM)
, nvl(prepaid_amount, FND_API.G_MISS_NUM)
, nvl(credit_card_number, FND_API.G_MISS_CHAR)
, nvl(credit_card_holder_name, FND_API.G_MISS_CHAR)
, nvl(credit_card_expiration_date,FND_API.G_MISS_DATE)
, nvl(credit_card_code, FND_API.G_MISS_CHAR)
, nvl(credit_card_approval_code,FND_API.G_MISS_CHAR)
, nvl(credit_card_approval_date,FND_API.G_MISS_DATE)
, nvl(check_number, FND_API.G_MISS_CHAR)--6367320
, nvl(payment_amount, FND_API.G_MISS_NUM)
, nvl(payment_percentage, FND_API.G_MISS_NUM)
, nvl(creation_date, FND_API.G_MISS_DATE)
, nvl(created_by, FND_API.G_MISS_NUM)
, nvl(last_update_date, FND_API.G_MISS_DATE)
, nvl(last_updated_by, FND_API.G_MISS_NUM)
, nvl(last_update_login, FND_API.G_MISS_NUM)
, nvl(program_application_id, FND_API.G_MISS_NUM)
, nvl(program_id, FND_API.G_MISS_NUM)
, nvl(program_update_date, FND_API.G_MISS_DATE)
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
, nvl(payment_number, FND_API.G_MISS_NUM)
, nvl(header_id, FND_API.G_MISS_NUM)
, nvl(line_id, FND_API.G_MISS_NUM)
, nvl(DEFER_PAYMENT_PROCESSING_FLAG, FND_API.G_MISS_CHAR) -- Added for bug 8478559
, nvl(trxn_extension_id, FND_API.G_MISS_NUM)
, nvl(instrument_security_code,FND_API.G_MISS_CHAR) --R12 CVV2
FROM oe_payments_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_payment_ref;
SELECT nvl(orig_sys_lotserial_ref, FND_API.G_MISS_CHAR)
, nvl(change_request_code, FND_API.G_MISS_CHAR)
, nvl(lot_number, FND_API.G_MISS_CHAR)
-- , nvl(sublot_number, FND_API.G_MISS_CHAR) -- OPM 3322359 INVCONV
, nvl(from_serial_number, FND_API.G_MISS_CHAR)
, nvl(to_serial_number, FND_API.G_MISS_CHAR)
, nvl(quantity, FND_API.G_MISS_NUM)
, nvl(quantity2, FND_API.G_MISS_NUM) -- OPM 3322359
, nvl(context, FND_API.G_MISS_CHAR)
, nvl(attribute1, FND_API.G_MISS_CHAR)
, nvl(attribute2, FND_API.G_MISS_CHAR)
, nvl(attribute3, FND_API.G_MISS_CHAR)
, nvl(attribute4, FND_API.G_MISS_CHAR)
, nvl(attribute5, FND_API.G_MISS_CHAR)
, nvl(attribute6, FND_API.G_MISS_CHAR)
, nvl(attribute7, FND_API.G_MISS_CHAR)
, nvl(attribute8, FND_API.G_MISS_CHAR)
, nvl(attribute9, FND_API.G_MISS_CHAR)
, nvl(attribute10, FND_API.G_MISS_CHAR)
, nvl(attribute11, FND_API.G_MISS_CHAR)
, nvl(attribute12, FND_API.G_MISS_CHAR)
, nvl(attribute13, FND_API.G_MISS_CHAR)
, nvl(attribute14, FND_API.G_MISS_CHAR)
, nvl(attribute15, FND_API.G_MISS_CHAR)
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
, nvl(status_flag, FND_API.G_MISS_CHAR)
FROM oe_lotserials_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_lotserial_ref
;
SELECT orig_sys_reservation_ref
, revision
, lot_number_id
, lot_number
, subinventory_id
, subinventory_code
, locator_id
, quantity
, quantity2 -- INVCONV
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, nvl(operation_code, OE_GLOBALS.G_OPR_CREATE)
FROM oe_reservtns_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
ORDER BY orig_sys_reservation_ref
;
SELECT nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
, nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
, hold_id
, hold_type_code
, hold_type_id
, hold_until_date
, release_reason_code
, comments
, context
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, operation_code
, error_flag
, status_flag
, interface_status
--myerrams, start, added the following fields to the cursor for Customer Acceptance
, char_param1
, char_param2
, char_param3
, char_param4
, char_param5
, date_param1
, date_param2
, date_param3
, date_param4
, date_param5
--myerrams, end
FROM oe_actions_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
= FND_API.G_MISS_CHAR
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
;
SELECT nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
, nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
, hold_id
, hold_type_code
, hold_type_id
, hold_until_date
, release_reason_code
, comments
, context
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, operation_code
, fulfillment_set_name
, error_flag
, status_flag
, interface_status
--myerrams, start, added the following fields to the cursor for Customer Acceptance
, char_param1
, char_param2
, char_param3
, char_param4
, char_param5
, date_param1
, date_param2
, date_param3
, date_param4
, date_param5
--myerrams, end
FROM oe_actions_iface_all
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND orig_sys_line_ref = l_orig_sys_line_ref
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl(org_id, FND_API.G_MISS_NUM)
= nvl(l_org_id, FND_API.G_MISS_NUM)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
FOR UPDATE NOWAIT
;
select DECODE(p_validate_only,'Y','T','N','F',p_validate_only)
into l_validate_only
from dual;
IF l_header_rec.operation = 'INSERT' THEN
l_header_rec.operation := 'CREATE';
SELECT oe_xml_message_seq_s.nextval
INTO l_header_rec.xml_message_id
FROM DUAL;
, l_header_adj_rec.UPDATED_FLAG
, l_header_adj_rec.UPDATE_ALLOWED
, l_header_adj_rec.CHANGE_REASON_CODE
, l_header_adj_rec.CHANGE_REASON_TEXT
, l_header_adj_rec.PRICING_PHASE_ID
, l_header_adj_rec.ADJUSTED_AMOUNT
-- Price Adjustment related changes bug# 1220921 (End)
;
IF l_header_adj_rec.operation = 'INSERT' THEN
l_header_adj_rec.operation := 'CREATE';
, l_header_price_att_rec.last_update_date
, l_header_price_att_rec.last_updated_by
, l_header_price_att_rec.last_update_login
, l_header_price_att_rec.program_application_id
, l_header_price_att_rec.program_id
, l_header_price_att_rec.program_update_date
, l_header_price_att_rec.request_id
, l_header_price_att_rec.flex_title
, l_header_price_att_rec.pricing_context
, l_header_price_att_rec.pricing_attribute1
, l_header_price_att_rec.pricing_attribute2
, l_header_price_att_rec.pricing_attribute3
, l_header_price_att_rec.pricing_attribute4
, l_header_price_att_rec.pricing_attribute5
, l_header_price_att_rec.pricing_attribute6
, l_header_price_att_rec.pricing_attribute7
, l_header_price_att_rec.pricing_attribute8
, l_header_price_att_rec.pricing_attribute9
, l_header_price_att_rec.pricing_attribute10
, l_header_price_att_rec.pricing_attribute11
, l_header_price_att_rec.pricing_attribute12
, l_header_price_att_rec.pricing_attribute13
, l_header_price_att_rec.pricing_attribute14
, l_header_price_att_rec.pricing_attribute15
, l_header_price_att_rec.pricing_attribute16
, l_header_price_att_rec.pricing_attribute17
, l_header_price_att_rec.pricing_attribute18
, l_header_price_att_rec.pricing_attribute19
, l_header_price_att_rec.pricing_attribute20
, l_header_price_att_rec.pricing_attribute21
, l_header_price_att_rec.pricing_attribute22
, l_header_price_att_rec.pricing_attribute23
, l_header_price_att_rec.pricing_attribute24
, l_header_price_att_rec.pricing_attribute25
, l_header_price_att_rec.pricing_attribute26
, l_header_price_att_rec.pricing_attribute27
, l_header_price_att_rec.pricing_attribute28
, l_header_price_att_rec.pricing_attribute29
, l_header_price_att_rec.pricing_attribute30
, l_header_price_att_rec.pricing_attribute31
, l_header_price_att_rec.pricing_attribute32
, l_header_price_att_rec.pricing_attribute33
, l_header_price_att_rec.pricing_attribute34
, l_header_price_att_rec.pricing_attribute35
, l_header_price_att_rec.pricing_attribute36
, l_header_price_att_rec.pricing_attribute37
, l_header_price_att_rec.pricing_attribute38
, l_header_price_att_rec.pricing_attribute39
, l_header_price_att_rec.pricing_attribute40
, l_header_price_att_rec.pricing_attribute41
, l_header_price_att_rec.pricing_attribute42
, l_header_price_att_rec.pricing_attribute43
, l_header_price_att_rec.pricing_attribute44
, l_header_price_att_rec.pricing_attribute45
, l_header_price_att_rec.pricing_attribute46
, l_header_price_att_rec.pricing_attribute47
, l_header_price_att_rec.pricing_attribute48
, l_header_price_att_rec.pricing_attribute49
, l_header_price_att_rec.pricing_attribute50
, l_header_price_att_rec.pricing_attribute51
, l_header_price_att_rec.pricing_attribute52
, l_header_price_att_rec.pricing_attribute53
, l_header_price_att_rec.pricing_attribute54
, l_header_price_att_rec.pricing_attribute55
, l_header_price_att_rec.pricing_attribute56
, l_header_price_att_rec.pricing_attribute57
, l_header_price_att_rec.pricing_attribute58
, l_header_price_att_rec.pricing_attribute59
, l_header_price_att_rec.pricing_attribute60
, l_header_price_att_rec.pricing_attribute61
, l_header_price_att_rec.pricing_attribute62
, l_header_price_att_rec.pricing_attribute63
, l_header_price_att_rec.pricing_attribute64
, l_header_price_att_rec.pricing_attribute65
, l_header_price_att_rec.pricing_attribute66
, l_header_price_att_rec.pricing_attribute67
, l_header_price_att_rec.pricing_attribute68
, l_header_price_att_rec.pricing_attribute69
, l_header_price_att_rec.pricing_attribute70
, l_header_price_att_rec.pricing_attribute71
, l_header_price_att_rec.pricing_attribute72
, l_header_price_att_rec.pricing_attribute73
, l_header_price_att_rec.pricing_attribute74
, l_header_price_att_rec.pricing_attribute75
, l_header_price_att_rec.pricing_attribute76
, l_header_price_att_rec.pricing_attribute77
, l_header_price_att_rec.pricing_attribute78
, l_header_price_att_rec.pricing_attribute79
, l_header_price_att_rec.pricing_attribute80
, l_header_price_att_rec.pricing_attribute81
, l_header_price_att_rec.pricing_attribute82
, l_header_price_att_rec.pricing_attribute83
, l_header_price_att_rec.pricing_attribute84
, l_header_price_att_rec.pricing_attribute85
, l_header_price_att_rec.pricing_attribute86
, l_header_price_att_rec.pricing_attribute87
, l_header_price_att_rec.pricing_attribute88
, l_header_price_att_rec.pricing_attribute89
, l_header_price_att_rec.pricing_attribute90
, l_header_price_att_rec.pricing_attribute91
, l_header_price_att_rec.pricing_attribute92
, l_header_price_att_rec.pricing_attribute93
, l_header_price_att_rec.pricing_attribute94
, l_header_price_att_rec.pricing_attribute95
, l_header_price_att_rec.pricing_attribute96
, l_header_price_att_rec.pricing_attribute97
, l_header_price_att_rec.pricing_attribute98
, l_header_price_att_rec.pricing_attribute99
, l_header_price_att_rec.pricing_attribute100
, l_header_price_att_rec.context
, l_header_price_att_rec.attribute1
, l_header_price_att_rec.attribute2
, l_header_price_att_rec.attribute3
, l_header_price_att_rec.attribute4
, l_header_price_att_rec.attribute5
, l_header_price_att_rec.attribute6
, l_header_price_att_rec.attribute7
, l_header_price_att_rec.attribute8
, l_header_price_att_rec.attribute9
, l_header_price_att_rec.attribute10
, l_header_price_att_rec.attribute11
, l_header_price_att_rec.attribute12
, l_header_price_att_rec.attribute13
, l_header_price_att_rec.attribute14
, l_header_price_att_rec.attribute15
, l_header_price_att_rec.operation;
IF( l_header_price_att_rec.operation = 'INSERT') THEN
l_header_price_att_rec.operation := 'CREATE';
IF l_header_scredit_rec.operation = 'INSERT' THEN
l_header_scredit_rec.operation := 'CREATE';
, l_header_payment_rec.last_update_date
, l_header_payment_rec.last_updated_by
, l_header_payment_rec.last_update_login
, l_header_payment_rec.program_application_id
, l_header_payment_rec.program_id
, l_header_payment_rec.program_update_date
, l_header_payment_rec.context
, l_header_payment_rec.attribute1
, l_header_payment_rec.attribute2
, l_header_payment_rec.attribute3
, l_header_payment_rec.attribute4
, l_header_payment_rec.attribute5
, l_header_payment_rec.attribute6
, l_header_payment_rec.attribute7
, l_header_payment_rec.attribute8
, l_header_payment_rec.attribute9
, l_header_payment_rec.attribute10
, l_header_payment_rec.attribute11
, l_header_payment_rec.attribute12
, l_header_payment_rec.attribute13
, l_header_payment_rec.attribute14
, l_header_payment_rec.attribute15
, l_header_payment_rec.operation
, l_header_payment_rec.status_flag
, l_header_payment_rec.payment_number
, l_header_payment_rec.header_id
, l_header_payment_rec.line_id
, l_header_payment_rec.DEFER_PAYMENT_PROCESSING_FLAG -- Added for bug 8478559
, l_header_payment_rec.trxn_extension_id
, l_header_payment_rec.instrument_security_code --R12 CVV2
;
IF l_header_payment_rec.operation = 'INSERT' THEN
l_header_payment_rec.operation := 'CREATE';
select header_id into l_header_id_temp
from oe_order_headers_all
where orig_sys_document_ref = l_orig_sys_document_ref;
IF l_line_rec.operation = 'INSERT' THEN
l_line_rec.operation := 'CREATE';
SELECT calculate_price_flag
INTO l_line_rec.calculate_price_flag
FROM OE_ORDER_LINES_ALL
WHERE orig_sys_document_ref = l_line_rec.orig_sys_document_ref
AND orig_sys_line_ref = l_line_rec.orig_sys_line_ref;
SELECT unit_selling_price,unit_list_price
INTO l_line_rec.unit_selling_price,l_line_rec.unit_list_price
FROM OE_ORDER_LINES_ALL
WHERE orig_sys_document_ref = l_line_rec.orig_sys_document_ref
AND orig_sys_line_ref = l_line_rec.orig_sys_line_ref;
SELECT unit_selling_price,unit_list_price
INTO l_line_rec.unit_selling_price,l_line_rec.unit_list_price
FROM OE_ORDER_LINES_ALL
WHERE orig_sys_document_ref = l_line_rec.orig_sys_document_ref
AND orig_sys_line_ref = l_line_rec.orig_sys_line_ref;
SELECT calculate_price_flag
INTO l_line_rec.calculate_price_flag
FROM OE_ORDER_LINES_ALL
WHERE orig_sys_document_ref = l_line_rec.orig_sys_document_ref
AND orig_sys_line_ref = l_line_rec.orig_sys_line_ref
AND orig_sys_shipment_ref = l_line_rec.orig_sys_shipment_ref;
SELECT unit_selling_price,unit_list_price
INTO l_line_rec.unit_selling_price,l_line_rec.unit_list_price
FROM OE_ORDER_LINES_ALL
WHERE orig_sys_document_ref = l_line_rec.orig_sys_document_ref
AND orig_sys_line_ref = l_line_rec.orig_sys_line_ref
AND orig_sys_shipment_ref = l_line_rec.orig_sys_shipment_ref;
SELECT unit_selling_price,unit_list_price
INTO l_line_rec.unit_selling_price,l_line_rec.unit_list_price
FROM OE_ORDER_LINES_ALL
WHERE orig_sys_document_ref = l_line_rec.orig_sys_document_ref
AND orig_sys_line_ref = l_line_rec.orig_sys_line_ref
AND orig_sys_shipment_ref = l_line_rec.orig_sys_shipment_ref;
IF l_line_rec.operation = 'UPDATE' AND
((l_line_rec.fulfillment_set IS NOT NULL AND
l_line_rec.fulfillment_set <> FND_API.G_MISS_CHAR) OR
(l_line_rec.fulfillment_set_id IS NOT NULL AND
l_line_rec.fulfillment_set_id <> FND_API.G_MISS_NUM)) AND
OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
oe_debug_pub.add('Ignoring : '|| l_line_rec.fulfillment_set, 1 ) ;
, l_line_adj_rec.UPDATED_FLAG
, l_line_adj_rec.UPDATE_ALLOWED
, l_line_adj_rec.CHANGE_REASON_CODE
, l_line_adj_rec.CHANGE_REASON_TEXT
, l_line_adj_rec.PRICING_PHASE_ID
, l_line_adj_rec.ADJUSTED_AMOUNT
-- Price Adjustment related changes bug# 1220921 (End)
;
IF l_line_adj_rec.operation = 'INSERT' THEN
l_line_adj_rec.operation := 'CREATE';
, l_line_price_att_rec.last_update_date
, l_line_price_att_rec.last_updated_by
, l_line_price_att_rec.last_update_login
, l_line_price_att_rec.program_application_id
, l_line_price_att_rec.program_id
, l_line_price_att_rec.program_update_date
, l_line_price_att_rec.request_id
, l_line_price_att_rec.flex_title
, l_line_price_att_rec.pricing_context
, l_line_price_att_rec.pricing_attribute1
, l_line_price_att_rec.pricing_attribute2
, l_line_price_att_rec.pricing_attribute3
, l_line_price_att_rec.pricing_attribute4
, l_line_price_att_rec.pricing_attribute5
, l_line_price_att_rec.pricing_attribute6
, l_line_price_att_rec.pricing_attribute7
, l_line_price_att_rec.pricing_attribute8
, l_line_price_att_rec.pricing_attribute9
, l_line_price_att_rec.pricing_attribute10
, l_line_price_att_rec.pricing_attribute11
, l_line_price_att_rec.pricing_attribute12
, l_line_price_att_rec.pricing_attribute13
, l_line_price_att_rec.pricing_attribute14
, l_line_price_att_rec.pricing_attribute15
, l_line_price_att_rec.pricing_attribute16
, l_line_price_att_rec.pricing_attribute17
, l_line_price_att_rec.pricing_attribute18
, l_line_price_att_rec.pricing_attribute19
, l_line_price_att_rec.pricing_attribute20
, l_line_price_att_rec.pricing_attribute21
, l_line_price_att_rec.pricing_attribute22
, l_line_price_att_rec.pricing_attribute23
, l_line_price_att_rec.pricing_attribute24
, l_line_price_att_rec.pricing_attribute25
, l_line_price_att_rec.pricing_attribute26
, l_line_price_att_rec.pricing_attribute27
, l_line_price_att_rec.pricing_attribute28
, l_line_price_att_rec.pricing_attribute29
, l_line_price_att_rec.pricing_attribute30
, l_line_price_att_rec.pricing_attribute31
, l_line_price_att_rec.pricing_attribute32
, l_line_price_att_rec.pricing_attribute33
, l_line_price_att_rec.pricing_attribute34
, l_line_price_att_rec.pricing_attribute35
, l_line_price_att_rec.pricing_attribute36
, l_line_price_att_rec.pricing_attribute37
, l_line_price_att_rec.pricing_attribute38
, l_line_price_att_rec.pricing_attribute39
, l_line_price_att_rec.pricing_attribute40
, l_line_price_att_rec.pricing_attribute41
, l_line_price_att_rec.pricing_attribute42
, l_line_price_att_rec.pricing_attribute43
, l_line_price_att_rec.pricing_attribute44
, l_line_price_att_rec.pricing_attribute45
, l_line_price_att_rec.pricing_attribute46
, l_line_price_att_rec.pricing_attribute47
, l_line_price_att_rec.pricing_attribute48
, l_line_price_att_rec.pricing_attribute49
, l_line_price_att_rec.pricing_attribute50
, l_line_price_att_rec.pricing_attribute51
, l_line_price_att_rec.pricing_attribute52
, l_line_price_att_rec.pricing_attribute53
, l_line_price_att_rec.pricing_attribute54
, l_line_price_att_rec.pricing_attribute55
, l_line_price_att_rec.pricing_attribute56
, l_line_price_att_rec.pricing_attribute57
, l_line_price_att_rec.pricing_attribute58
, l_line_price_att_rec.pricing_attribute59
, l_line_price_att_rec.pricing_attribute60
, l_line_price_att_rec.pricing_attribute61
, l_line_price_att_rec.pricing_attribute62
, l_line_price_att_rec.pricing_attribute63
, l_line_price_att_rec.pricing_attribute64
, l_line_price_att_rec.pricing_attribute65
, l_line_price_att_rec.pricing_attribute66
, l_line_price_att_rec.pricing_attribute67
, l_line_price_att_rec.pricing_attribute68
, l_line_price_att_rec.pricing_attribute69
, l_line_price_att_rec.pricing_attribute70
, l_line_price_att_rec.pricing_attribute71
, l_line_price_att_rec.pricing_attribute72
, l_line_price_att_rec.pricing_attribute73
, l_line_price_att_rec.pricing_attribute74
, l_line_price_att_rec.pricing_attribute75
, l_line_price_att_rec.pricing_attribute76
, l_line_price_att_rec.pricing_attribute77
, l_line_price_att_rec.pricing_attribute78
, l_line_price_att_rec.pricing_attribute79
, l_line_price_att_rec.pricing_attribute80
, l_line_price_att_rec.pricing_attribute81
, l_line_price_att_rec.pricing_attribute82
, l_line_price_att_rec.pricing_attribute83
, l_line_price_att_rec.pricing_attribute84
, l_line_price_att_rec.pricing_attribute85
, l_line_price_att_rec.pricing_attribute86
, l_line_price_att_rec.pricing_attribute87
, l_line_price_att_rec.pricing_attribute88
, l_line_price_att_rec.pricing_attribute89
, l_line_price_att_rec.pricing_attribute90
, l_line_price_att_rec.pricing_attribute91
, l_line_price_att_rec.pricing_attribute92
, l_line_price_att_rec.pricing_attribute93
, l_line_price_att_rec.pricing_attribute94
, l_line_price_att_rec.pricing_attribute95
, l_line_price_att_rec.pricing_attribute96
, l_line_price_att_rec.pricing_attribute97
, l_line_price_att_rec.pricing_attribute98
, l_line_price_att_rec.pricing_attribute99
, l_line_price_att_rec.pricing_attribute100
, l_line_price_att_rec.context
, l_line_price_att_rec.attribute1
, l_line_price_att_rec.attribute2
, l_line_price_att_rec.attribute3
, l_line_price_att_rec.attribute4
, l_line_price_att_rec.attribute5
, l_line_price_att_rec.attribute6
, l_line_price_att_rec.attribute7
, l_line_price_att_rec.attribute8
, l_line_price_att_rec.attribute9
, l_line_price_att_rec.attribute10
, l_line_price_att_rec.attribute11
, l_line_price_att_rec.attribute12
, l_line_price_att_rec.attribute13
, l_line_price_att_rec.attribute14
, l_line_price_att_rec.attribute15
, l_line_price_att_rec.operation;
IF l_line_price_att_rec.operation = 'INSERT' THEN
l_line_price_att_rec.operation := 'CREATE';
IF l_line_scredit_rec.operation = 'INSERT' THEN
l_line_scredit_rec.operation := 'CREATE';
, l_line_payment_rec.last_update_date
, l_line_payment_rec.last_updated_by
, l_line_payment_rec.last_update_login
, l_line_payment_rec.program_application_id
, l_line_payment_rec.program_id
, l_line_payment_rec.program_update_date
, l_line_payment_rec.context
, l_line_payment_rec.attribute1
, l_line_payment_rec.attribute2
, l_line_payment_rec.attribute3
, l_line_payment_rec.attribute4
, l_line_payment_rec.attribute5
, l_line_payment_rec.attribute6
, l_line_payment_rec.attribute7
, l_line_payment_rec.attribute8
, l_line_payment_rec.attribute9
, l_line_payment_rec.attribute10
, l_line_payment_rec.attribute11
, l_line_payment_rec.attribute12
, l_line_payment_rec.attribute13
, l_line_payment_rec.attribute14
, l_line_payment_rec.attribute15
, l_line_payment_rec.operation
, l_line_payment_rec.status_flag
, l_line_payment_rec.payment_number
, l_line_payment_rec.header_id
, l_line_payment_rec.line_id
, l_line_payment_rec.DEFER_PAYMENT_PROCESSING_FLAG -- Added for bug 8478559
, l_line_payment_rec.trxn_extension_id
, l_line_payment_rec.instrument_security_code --R12 CVV2
;
IF l_line_payment_rec.operation = 'INSERT' THEN
l_line_payment_rec.operation := 'CREATE';
IF l_lot_serial_rec.operation = 'INSERT' THEN
l_lot_serial_rec.operation := 'CREATE';
IF l_reservation_rec.operation = 'INSERT' THEN
l_reservation_rec.operation := 'CREATE';
IF l_header_rec.operation IN ('INSERT', 'CREATE') AND
l_return_status_po = FND_API.G_RET_STS_SUCCESS
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'HEADER ID GENERATED: '|| TO_CHAR ( L_HEADER_REC_NEW.HEADER_ID ) ) ;
Delete order from interface tables
-----------------------------------------------------------
*/
-- aksingh
IF l_debug_level > 0 THEN
oe_debug_pub.add('l_validate_only '||l_validate_only ) ;
l_delete_flag := 'Y';
l_delete_flag := 'N';
oe_debug_pub.add( 'DELETE FLAG '||L_DELETE_FLAG ) ;
IF l_delete_flag = 'Y' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE DELETING ORDER FROM INTERFACE TABLES' ) ;
OE_ORDER_IMPORT_UTIL_PVT.Delete_Order (
p_request_id => l_request_id,
p_order_source_id => l_order_source_id,
p_orig_sys_document_ref => l_orig_sys_document_ref,
p_sold_to_org_id => l_sold_to_org_id,
p_sold_to_org => l_sold_to_org,
p_change_sequence => l_change_sequence,
p_return_status => l_return_status_del_ord
);
oe_debug_pub.add( 'BEFORE CALLING DELETE OF CUSTOMER INFO' ) ;
OE_INLINE_CUSTOMER_PUB.Delete_Customer_Info(
p_header_customer_rec => l_header_customer_rec,
p_line_customer_tbl => l_line_customer_tbl
);
oe_debug_pub.add( 'AFTER CALLING DELETE OF CUSTOMER INFO' ) ;
oe_debug_pub.add( 'DELETE ORDER RETURN STATUS: '||L_RETURN_STATUS_DEL_ORD ) ;
END IF; /* l_delete_flag = 'Y' */
Update error_flag in interface tables
-----------------------------------------------------------
*/
IF p_return_status IN (FND_API.G_RET_STS_ERROR,
FND_API.G_RET_STS_UNEXP_ERROR)
THEN
BEGIN
BEGIN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE UPDATING ERROR FLAG FOR HEADER' ) ;
UPDATE oe_headers_interface
SET error_flag = 'Y', xml_message_id = nvl(xml_message_id, l_header_rec.xml_message_id)
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM);
UPDATE oe_price_adjs_interface
SET error_flag = 'Y'
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_discount_ref, FND_API.G_MISS_CHAR)
= nvl(l_header_adj_tbl_new(I).orig_sys_discount_ref,
FND_API.G_MISS_CHAR);
UPDATE oe_price_atts_interface
SET error_flag = 'Y'
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM);
UPDATE oe_credits_interface
SET error_flag = 'Y'
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_credit_ref, FND_API.G_MISS_CHAR)
= nvl(l_header_scredit_tbl_new(I).orig_sys_credit_ref,
FND_API.G_MISS_CHAR);
UPDATE oe_payments_interface
SET error_flag = 'Y'
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_payment_ref, FND_API.G_MISS_CHAR)
= nvl(l_header_payment_tbl_new(I).orig_sys_payment_ref,
FND_API.G_MISS_CHAR);
UPDATE oe_lines_interface
SET error_flag = 'Y'
WHERE order_source_id = l_order_source_id
AND orig_sys_document_ref = l_orig_sys_document_ref
AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
= nvl(l_sold_to_org_id, FND_API.G_MISS_NUM)
AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
= nvl(l_sold_to_org, FND_API.G_MISS_CHAR)
AND nvl( change_sequence, FND_API.G_MISS_CHAR)
= nvl(l_change_sequence, FND_API.G_MISS_CHAR)
AND nvl( request_id, FND_API.G_MISS_NUM)
= nvl(l_request_id, FND_API.G_MISS_NUM)
AND nvl(orig_sys_line_ref, FND_API.G_MISS_CHAR)
= nvl(l_line_tbl_new(I).orig_sys_line_ref, FND_API.G_MISS_CHAR)
AND nvl(orig_sys_shipment_ref, FND_API.G_MISS_CHAR)
= nvl(l_line_tbl_new(I).orig_sys_shipment_ref,FND_API.G_MISS_CHAR);
oe_msg_pub.Delete_Msg(p_msg_index => l_tbl_index);
oe_debug_pub.add( 'UPDATE ERROR_FLAG RETURN STATUS: '||L_RETURN_STATUS_UPD_ERR ) ;
COMMIT; /* commit the error_flag updated */
ROLLBACK; /* rollback the error_flag updated */
Update the processing messages table
-----------------------------------------------------------
*/
OE_MSG_PUB.Count_And_Get (p_count => p_msg_count
,p_data => p_msg_data);
oe_debug_pub.add( 'Header id updated in msg stack:' || l_header_rec.header_id ) ;
Delete messages from the database table
-----------------------------------------------------------
*/
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE DELETING OLD MESSAGES FROM THE DATABASE TABLE' ) ;
/* Commmenting the call to delete message bug 2467558
OE_ORDER_IMPORT_UTIL_PVT.Delete_Messages (
p_request_id => l_request_id,
p_order_source_id => l_order_source_id,
p_orig_sys_document_ref => l_orig_sys_document_ref,
p_change_sequence => l_change_sequence,
p_return_status => l_return_status_del_msg
);
oe_debug_pub.add( 'DELETE MESSAGES RETURN STATUS: '||L_RETURN_STATUS_DEL_MSG ) ;
ROLLBACK; /* rollback the error messages deleted */