[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, 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, FND_API.G_MISS_CHAR)
, 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, FND_API.G_MISS_CHAR)
, 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, 'Y')
-- , nvl(closed_flag, FND_API.G_MISS_CHAR)
, nvl(cancelled_flag, 'N')
, 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)
/* Added Attribute 16 to 20 for bug 3471009 */
, nvl(attribute16, FND_API.G_MISS_CHAR)
, 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)
, 'N'
, nvl(customer_preference_set_code, FND_API.G_MISS_CHAR)
, 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)
-- 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)
--{added for bug 4240715
, nvl(end_customer_name, FND_API.G_MISS_CHAR)
, 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, FND_API.G_MISS_CHAR)
, 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)
-- bug 4240715}
, 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)
, 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)
-- Distributer Order related change }
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'
AND closed_flag = '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_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)
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(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(schedule_arrival_date, FND_API.G_MISS_DATE)
, nvl(actual_arrival_date, FND_API.G_MISS_DATE)
-- bug 3220711 - start
, nvl(actual_shipment_date, FND_API.G_MISS_DATE)
-- bug 3220711 - end
, 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 */ -- INVCONV
, 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)
, 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, FND_API.G_MISS_CHAR)
, 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(invoice_to_contact, FND_API.G_MISS_CHAR)
, nvl(ship_tolerance_above, FND_API.G_MISS_NUM)
, nvl(drop_ship_flag, 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')
, 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, 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)
, 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_NUM)
, 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, 'N')
, 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)
/* Added Attribute 16 to 20 for the bug 3513248 */
, nvl(attribute16, FND_API.G_MISS_CHAR)
, 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)
, 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, FND_API.G_MISS_CHAR)
, nvl(invoice_to_postal_code, FND_API.G_MISS_CHAR)
, nvl(invoice_to_country, FND_API.G_MISS_CHAR)
, nvl(user_item_description, FND_API.G_MISS_CHAR)
, nvl(change_sequence, FND_API.G_MISS_CHAR)
-- { 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)
--{added for bug 4240715
, nvl(end_customer_name, FND_API.G_MISS_CHAR)
, 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, FND_API.G_MISS_CHAR)
, 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)
--bug 4240715}
, 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)
, 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)
-- Distributer Order related change }
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 orig_sys_line_ref, orig_sys_shipment_ref
;
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)
, nvl( ORIG_SYS_LINE_REF, FND_API.G_MISS_CHAR)
-- 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(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(ORIG_SYS_LINE_REF, 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 orig_sys_reservation_ref
, revision
, lot_number_id
, lot_number
, subinventory_id
, subinventory_code
, locator_id
, quantity
, 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 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';
, 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';
IF l_header_scredit_rec.operation = 'INSERT' THEN
l_header_scredit_rec.operation := 'CREATE';
oe_debug_pub.add('value inserted'||l_line_rec.end_customer_id); -- added for bug 4240715
IF l_line_rec.operation = 'INSERT' THEN
l_line_rec.operation := 'CREATE';
, 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
, l_adj_line_ref
-- Price Adjustment related changes bug# 1220921 (End)
;
IF l_line_adj_rec.operation = 'INSERT' THEN
l_line_adj_rec.operation := 'CREATE';
IF l_line_scredit_rec.operation = 'INSERT' THEN
l_line_scredit_rec.operation := 'CREATE';
| INSERTION BEGINS HERE |
| |
+------------------------*/
--
--Header Insert
--
BEGIN
--
OE_Header_Util.get_order_number(l_header_rec,l_header_rec_old);
SELECT OE_ORDER_HEADERS_S.NEXTVAL
INTO l_header_rec.header_id
FROM DUAL;
l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_header_rec.last_update_date := sysdate;
OE_Header_Util.Insert_Row(l_header_rec);
SELECT OE_SALES_CREDITS_S.NEXTVAL
INTO l_header_scredit_tbl(I).sales_credit_id
FROM DUAL;
l_header_scredit_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
l_header_scredit_tbl(I).last_update_date := sysdate;
OE_Header_Scredit_Util.Insert_Row(l_header_scredit_tbl(I));
SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
INTO l_header_adj_tbl(I).price_adjustment_id
FROM DUAL;
l_header_adj_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
l_header_adj_tbl(I).last_update_date := sysdate;
OE_Header_Adj_Util.Insert_Row(l_header_adj_tbl(I));
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_line_tbl(I).line_id
FROM DUAL;
l_line_tbl(I).last_updated_by := FND_GLOBAL.USER_ID;
l_line_tbl(I).last_update_date := SYSDATE;
SELECT order_category_code
INTO l_line_val_tbl(I).line_type
FROM OE_LINE_TYPES_V
WHERE line_type_id = l_line_tbl(I).line_type_id ;
SELECT OE_SALES_CREDITS_S.NEXTVAL
INTO l_line_scredit_tbl(J).sales_credit_id
FROM DUAL;
l_line_scredit_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
l_line_scredit_tbl(J).last_update_date := sysdate;
SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
INTO l_line_adj_tbl(K).price_adjustment_id
FROM DUAL;
l_line_adj_tbl(K).last_updated_by := FND_GLOBAL.USER_ID;
l_line_adj_tbl(K).last_update_date := sysdate;
OE_Line_Util.Insert_Row(l_line_tbl(L));
SELECT OE_SALES_CREDITS_S.NEXTVAL
INTO l_line_scredit_tbl(J).sales_credit_id
FROM DUAL;
l_line_scredit_tbl(J).last_updated_by := FND_GLOBAL.USER_ID;
l_line_scredit_tbl(J).last_update_date := sysdate;
OE_Line_Scredit_Util.Insert_Row(l_line_scredit_tbl(J));
SELECT OE_PRICE_ADJUSTMENTS_S.NEXTVAL
INTO l_line_adj_tbl(K).price_adjustment_id
FROM DUAL;
l_line_adj_tbl(K).last_updated_by := FND_GLOBAL.USER_ID;
l_line_adj_tbl(K).last_update_date := SYSDATE;
OE_Line_Adj_Util.Insert_Row(l_line_adj_tbl(K));
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( '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'
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(I).orig_sys_discount_ref,
FND_API.G_MISS_CHAR);
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(I).orig_sys_credit_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(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(I).orig_sys_shipment_ref,FND_API.G_MISS_CHAR);
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' ) ;
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 */