[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
pa.price_adjustment_id,
pa.creation_date,
pa.created_by,
pa.last_update_date,
pa.last_updated_by,
pa.last_update_login,
pa.program_application_id,
pa.program_id,
pa.program_update_date,
pa.request_id,
pa.header_id,
pa.discount_id,
pa.discount_line_id,
pa.automatic_flag,
pa.percent,
pa.line_id,
pa.context,
pa.attribute1,
pa.attribute2,
pa.attribute3,
pa.attribute4,
pa.attribute5,
pa.attribute6,
pa.attribute7,
pa.attribute8,
pa.attribute9,
pa.attribute10,
pa.attribute11,
pa.attribute12,
pa.attribute13,
pa.attribute14,
pa.attribute15
from
so_price_adjustments pa
where ( pa.line_id = G_OLD_LINE_ID and
pa.header_id = G_HEADER_Id and
L_level_flag = 'L') or
( pa.header_id = G_header_id and
L_level_flag = 'H' and
pa.line_id is null );
select
price_adjustment_id
into
v_price_adjustment_id
from
oe_price_adjustments
where price_adjustment_id = mpa.price_adjustment_id;
select
oe_price_adjustments_s.nextval
into
v_price_adjustment_id
from dual;
insert into oe_price_adjustments
(
price_adjustment_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
header_id,
discount_id,
discount_line_id,
automatic_flag,
percent,
line_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
values
(
v_price_adjustment_id,
mpa.creation_date,
mpa.created_by,
mpa.last_update_date,
mpa.last_updated_by,
mpa.last_update_login,
mpa.program_application_id,
mpa.program_id,
mpa.program_update_date,
mpa.request_id,
mpa.header_id,
mpa.discount_id,
mpa.discount_line_id,
mpa.automatic_flag,
mpa.percent,
decode(L_level_flag,'L',G_LINE_ID,null),
mpa.context,
mpa.attribute1,
mpa.attribute2,
mpa.attribute3,
mpa.attribute4,
mpa.attribute5,
mpa.attribute6,
mpa.attribute7,
mpa.attribute8,
mpa.attribute9,
mpa.attribute10,
mpa.attribute11,
mpa.attribute12,
mpa.attribute13,
mpa.attribute14,
mpa.attribute15
);
select
ssc.sales_credit_id,
ssc.creation_date,
ssc.created_by,
ssc.last_update_date,
ssc.last_updated_by,
ssc.last_update_login,
ssc.header_id,
ssc.sales_credit_type_id,
ssc.salesrep_id,
ssc.percent,
ssc.line_id,
ssc.context,
ssc.attribute1,
ssc.attribute2,
ssc.attribute3,
ssc.attribute4,
ssc.attribute5,
ssc.attribute6,
ssc.attribute7,
ssc.attribute8,
ssc.attribute9,
ssc.attribute10,
ssc.attribute11,
ssc.attribute12,
ssc.attribute13,
ssc.attribute14,
ssc.attribute15,
null dw_update_advice_flag,
ssc.wh_update_date
from
so_sales_credits ssc
where ( (ssc.line_id = G_OLD_LINE_ID and
ssc.header_id = G_header_id and
L_level_flag = 'L')
or (ssc.header_id = G_header_id and
L_level_flag = 'H' and
ssc.line_id is null ) );
select
sales_credit_id
into
v_sales_credit_id
from
oe_sales_credits
where sales_credit_id = l_msc.sales_credit_id;
select
oe_sales_credits_s.nextval
into
v_sales_credit_id
from dual;
insert into oe_sales_credits
(
sales_credit_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
header_id,
sales_credit_type_id,
salesrep_id,
percent,
line_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
dw_update_advice_flag,
wh_update_date
)
values
(
v_sales_credit_id,
l_msc.creation_date,
l_msc.created_by,
l_msc.last_update_date,
l_msc.last_updated_by,
l_msc.last_update_login,
l_msc.header_id,
l_msc.sales_credit_type_id,
l_msc.salesrep_id,
l_msc.percent,
decode(L_level_flag,'L',G_LINE_ID,null),
l_msc.context,
l_msc.attribute1,
l_msc.attribute2,
l_msc.attribute3,
l_msc.attribute4,
l_msc.attribute5,
l_msc.attribute6,
l_msc.attribute7,
l_msc.attribute8,
l_msc.attribute9,
l_msc.attribute10,
l_msc.attribute11,
l_msc.attribute12,
l_msc.attribute13,
l_msc.attribute14,
l_msc.attribute15,
l_msc.dw_update_advice_flag,
l_msc.wh_update_date
);
select
soc.header_id,
soc.line_id,
soc.created_by,
soc.creation_date,
soc.last_updated_by,
soc.last_update_date,
soc.last_update_login,
soc.program_application_id,
soc.program_id,
soc.program_update_date,
soc.request_id,
soc.cancel_code,
soc.cancelled_by,
soc.cancel_date,
soc.cancelled_quantity,
soc.cancel_comment,
soc.context,
soc.attribute1,
soc.attribute2,
soc.attribute3,
soc.attribute4,
soc.attribute5,
soc.attribute6,
soc.attribute7,
soc.attribute8,
soc.attribute9,
soc.attribute10,
soc.attribute11,
soc.attribute12,
soc.attribute13,
soc.attribute14,
soc.attribute15
from
so_order_cancellations soc
where soc.line_id = G_old_line_id
and soc.header_id = G_header_id;
OE_Upg_SO.Upgrade_Insert_Lines_History;
select
sla.line_id,
sla.org_id,
sla.header_id,
sla.line_number,
sla.date_requested_current,
sla.promise_date,
nvl(sld.schedule_date,sla.schedule_date) schedule_date,
nvl(sld.quantity,(nvl(sla.ordered_quantity,0)-nvl(sla.cancelled_quantity,0))) ordered_quantity,
decode(nvl(sla.ordered_quantity,0),nvl(sla.cancelled_quantity,0),'Y','N') line_cancel_flag,
sla.cancelled_quantity,
decode(
nvl(decode(sla.item_type_code,'SERVICE','N',
decode(sla.ato_line_id,null,sld.shippable_flag,
decode(sla.item_type_code,'CONFIG',sld.shippable_flag,'N'))),'-'), 'Y',
decode(sla.source_type_code,'EXTERNAL',
decode(sld.receipt_status_code,'INTERFACED',sld.quantity,null),sld.shipped_quantity),
decode(nvl(sla.ordered_quantity,0),nvl(sla.shipped_quantity,0),sla.shipped_quantity,
null)) shipped_quantity,
nvl(sld.invoiced_quantity,sla.invoiced_quantity) invoiced_quantity,
sla.tax_exempt_number,
sla.tax_exempt_reason_code,
nvl(sld.warehouse_id,sla.warehouse_id) warehouse_id,
sla.ship_to_site_use_id,
sla.ship_to_contact_id,
decode(G_AUTO_FLAG,'Y',sla.customer_item_id,
nvl(sld.customer_item_id,sla.customer_item_id)) customer_item_id,
sla.demand_stream_id,
sla.customer_dock_code,
sla.customer_job,
sla.customer_production_line,
sla.customer_model_serial_number,
sla.project_id,
sla.task_id,
nvl(sld.inventory_item_id,sla.inventory_item_id) inventory_item_id,
sla.inventory_item_id service_inventory_item_id,
sla.tax_code,
sla.demand_class_code,
sla.price_list_id,
sla.agreement_id,
sla.shipment_priority_code,
sla.ship_method_code,
sla.invoicing_rule_id,
sla.accounting_rule_id,
sla.original_system_line_reference,
sla.selling_price,
sla.list_price,
sla.context,
sla.attribute1,
sla.attribute2,
sla.attribute3,
sla.attribute4,
sla.attribute5,
sla.attribute6,
sla.attribute7,
sla.attribute8,
sla.attribute9,
sla.attribute10,
sla.attribute11,
sla.attribute12,
sla.attribute13,
sla.attribute14,
sla.attribute15,
slattr.industry_context,
slattr.industry_attribute1,
slattr.industry_attribute2,
slattr.industry_attribute3,
slattr.industry_attribute4,
slattr.industry_attribute5,
slattr.industry_attribute6,
slattr.industry_attribute7,
slattr.industry_attribute8,
slattr.industry_attribute9,
slattr.industry_attribute10,
slattr.industry_attribute11,
slattr.industry_attribute12,
slattr.industry_attribute13,
slattr.industry_attribute14,
slattr.industry_attribute15,
slattr.global_attribute_category,
slattr.global_attribute1,
slattr.global_attribute2,
slattr.global_attribute3,
slattr.global_attribute4,
slattr.global_attribute5,
slattr.global_attribute6,
slattr.global_attribute7,
slattr.global_attribute8,
slattr.global_attribute9,
slattr.global_attribute10,
slattr.global_attribute11,
slattr.global_attribute12,
slattr.global_attribute13,
slattr.global_attribute14,
slattr.global_attribute15,
slattr.global_attribute16,
slattr.global_attribute17,
slattr.global_attribute18,
slattr.global_attribute19,
slattr.global_attribute20,
sla.pricing_context,
sla.pricing_attribute1,
sla.pricing_attribute2,
sla.pricing_attribute3,
sla.pricing_attribute4,
sla.pricing_attribute5,
sla.pricing_attribute6,
sla.pricing_attribute7,
sla.pricing_attribute8,
sla.pricing_attribute9,
sla.pricing_attribute10,
nvl(sld.creation_date,sla.creation_date) creation_date,
nvl(sld.created_by,sla.created_by) created_by,
nvl(sld.last_update_date,sla.last_update_date) last_update_date,
nvl(sld.last_updated_by,sla.last_updated_by) last_updated_by,
nvl(sld.last_update_login,sla.last_update_login) last_update_login,
sla.program_application_id,
sla.program_id,
sla.program_update_date,
sla.request_id,
decode(sla.item_type_code,'MODEL',
decode(sla.parent_line_id,NULL,sla.line_id,sla.parent_line_id),
sla.parent_line_id) parent_Line_id,
sla.link_to_line_id,
nvl(sld.component_sequence_id,sla.component_sequence_id) component_sequence_id,
nvl(sld.component_code,sla.component_code) component_code,
decode(sla.item_type_code,
'STANDARD', decode(sla.option_flag,
'Y','OPTION',
sla.item_type_code),
'MODEL', decode(sla.parent_line_id,
NULL,'MODEL',
'CLASS'),
sla.item_type_code) item_type_code,
sla.source_type_code,
sla.transaction_reason_code,
nvl(sld.latest_acceptable_date, sla.latest_acceptable_date) latest_acceptable_date,
sld.dep_plan_required_flag,
decode(sla.item_type_code,'SERVICE',NULL,
decode(nvl(sld.schedule_status_code,'0'),'0',NULL,'SCHEDULED')) schedule_status_code,
sld.configuration_item_flag,
sld.delivery,
' ' load_seq_number,
sla.ship_set_number,
sla.option_flag,
sla.unit_code,
sld.line_detail_id,
sla.credit_invoice_line_id,
sld.included_item_flag,
decode(sla.item_type_code,
'MODEL', decode(sla.ato_line_id,
NULL, decode(ato_flag,
'Y', sla.line_id,
sla.ato_line_id),
sla.ato_line_id),
'STANDARD', decode(sla.ato_line_id,
NULL, decode(ato_flag,
'Y', sla.line_id,
sla.ato_line_id),
sla.ato_line_id),
sla.ato_line_id) ato_line_id,
decode(sla.line_type_code,'RETURN','RETURN','ORDER') line_category_code,
sla.planning_priority,
decode(sla.return_reference_type_code,'ORDER','ORDER',
'PO','PO','INVOICE','INVOICE',sla.return_reference_type_code)
return_reference_type_code,
sla.line_type_code,
sla.return_reference_id,
nvl(sla.open_flag,'N') open_flag,
sla.ship_model_complete_flag,
sla.standard_component_freeze_date,
decode(sla.s1,1,'Y','N') booked_flag,
decode(nvl(sld.picking_line_id,0),0,'N','Y') shipping_interfaced_flag,
decode(sla.s4,6,'Y',NULL) fulfilled_flag,
decode(sla.s5,9,'YES',8,'NOT_ELIGIBLE',24,'NOT_ELIGIBLE',NULL) invoice_interface_status_code,
sla.s5,
sla.intermediate_ship_to_id,
sla.transaction_type_code,
sla.transaction_comments,
sla.selling_percent,
sla.customer_product_id,
sla.cp_service_id,
nvl(sld.quantity,sla.serviced_quantity) serviced_quantity,
sla.service_duration,
sla.service_start_date,
sla.service_end_date,
sla.service_coterminate_flag,
sla.service_period_conversion_rate,
sla.service_mass_txn_temp_id,
sla.service_parent_line_id,
sla.list_percent,
sla.percent_base_price,
sld.picking_line_id,
sla.planning_prod_seq_number,
sld.actual_departure_date,
decode(sla.item_type_code,'SERVICE',NULL,
decode(nvl(sld.schedule_status_code,'-'),'-','','Y')) visible_demand_flag,
decode(sla.item_type_code,'SERVICE','N',
decode(sla.ato_line_id,null,sld.shippable_flag,
decode(sla.item_type_code,'CONFIG',sld.shippable_flag,'N'))) shippable_flag
/* If you make changes to shippable flag in the above line, make it also on */
/* shipped_quantity decode */
from
so_lines_all sla,
so_line_attributes slattr,
oe_upgrade_wsh_iface sld
where
sla.line_id = slattr.line_id (+) and
sla.header_id = G_header_id and
decode(sla.item_type_code,'SERVICE',
nvl(sla.service_parent_line_id,0),sla.line_id) = sld.line_id(+) and
sla.line_type_code <> 'PARENT' and
not exists
(select NE.line_id from so_lines_all NE
where NE.line_Id = sla.service_parent_line_id
and NE.line_type_code = 'PARENT') /* Standalone service not upgraded */
order by
sla.ship_set_number,
sla.line_id,
sld.component_code,
sld.line_detail_id;
g_line_rec.last_update_date :=mol.last_update_date;
g_line_rec.last_updated_by :=mol.last_updated_by;
g_line_rec.last_update_login :=mol.last_update_login;
g_line_rec.program_update_date :=mol.program_update_date;
oe_upg_so.upgrade_insert_errors
(
L_header_id => g_header_id,
L_comments => 'FYI Only: Line ID: '||to_char(g_old_line_id)
||' had corrupted data in Date_requested_current field. Latest_acceptable_date is updated to NULL for all its new lines'
);
select max(system_id) into v_system_id from so_line_service_details
where line_id = mol.line_id;
select
oe_sets_s.nextval
into
g_set_id
from dual;
insert into oe_sets
(
set_id,
set_name,
set_type,
header_id,
ship_from_org_id,
ship_to_org_id,
shipment_priority_code,
schedule_ship_date,
schedule_arrival_date,
freight_carrier_code,
shipping_method_code,
set_status,
created_by,
creation_date,
update_date,
updated_by,
update_login
)
values
(
g_set_id, /* SET_ID */
mol.ship_set_number, /* SET_NAME */
'SHIP_SET', /* SET_TYPE */
G_header_id, /* HEADER_ID */
mol.warehouse_id, /* SHIP_FROM_ORG_ID */
NULL, /* SHIP_TO_ORG_ID */
mol.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
mol.schedule_date, /* SCHEDULE_SHIP_DATE */
null, /* SCHEDULE_ARRIVAL_DATE */
mol.ship_method_code, /* FREIGHT_CARRIER_CODE */
mol.ship_method_code, /* SHIP_METHOD_CODE */
null, /* SET_STATUS */
1, /* CREATED_BY */
SYSDATE, /* CREATION_DATE */
SYSDATE, /* LAST_UPDATE_DATE */
1, /* LAST_UPDATED_BY */
0 /* LAST_UPDATE_LOGIN */
);
select
oe_order_lines_s.nextval
into
g_line_id
from dual;
select received_quantity into v_received_quantity
from mtl_so_rma_interface
where mol.line_id = rma_line_id;
select
customer_trx_id
into
v_customer_trx_id
from
RA_CUSTOMER_TRX_LINES
where customer_trx_line_id = g_line_rec.return_reference_id
and ( interface_line_attribute6 is null or
interface_line_attribute6 between
'000000000000000' and '999999999999999');
select header_id into v_reference_header_id
from so_lines_all
where line_id = v_reference_line_id;
Select item_type_code, ato_flag,option_flag
into r_original_item_type_code,
r_ato_flag,r_option_flag
from so_lines_all
where line_id = mol.link_to_line_id;
Select
inventory_item_id,
unit_code,
warehouse_id
into
r_inventory_item_id_2,
r_uom_code_2,
r_warehouse_id_2
from so_line_details
where configuration_item_flag = 'Y'
and line_id = mol.link_to_line_id;
select oe_sets_s.nextval
into r_line_set_id from dual;
insert into oe_sets
(
SET_ID,
SET_NAME,
SET_TYPE,
HEADER_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
SCHEDULE_SHIP_DATE,
SCHEDULE_ARRIVAL_DATE,
FREIGHT_CARRIER_CODE,
SHIPPING_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
SET_STATUS,
CREATED_BY,
CREATION_DATE,
UPDATED_BY,
UPDATE_DATE,
UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORDERED_QUANTITY_UOM,
LINE_TYPE_ID,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW
)
values
(
r_line_set_id, /* SET_ID, */
to_char(r_line_set_id), /* SET_NAME, */
'LINE_SET', /* SET_TYPE, */
g_line_rec.header_id, /* HEADER_ID,*/
null, /* SHIP_FROM_ORG_ID, */
null, /* SHIP_TO_ORG_ID, */
null, /* SCHEDULE_SHIP_DATE, */
null, /* SCHEDULE_ARRIVAL_DATE, */
null, /* FREIGHT_CARRIER_CODE, */
null, /* SHIPPING_METHOD_CODE, */
null, /* SHIPMENT_PRIORITY_CODE, */
null, /* SET_STATUS, */
0, /* CREATED_BY, */
sysdate, /* CREATION_DATE, */
0, /* UPDATED_BY, */
sysdate, /* UPDATE_DATE, */
0, /* UPDATE_LOGIN, */
null, /* INVENTORY_ITEM_ID, */
null, /* ORDERED_QUANTITY_UOM,*/
null, /* LINE_TYPE_ID, */
null, /* SHIP_TOLERANCE_ABOVE,*/
null /* SHIP_TOLERANCE_BELOW */
);
select
H.schedule_type_ext,
L.header_id,
L.line_id
into
g_line_rec.rla_schedule_type_code,
g_line_rec.source_document_id,
g_line_rec.source_document_line_id
from
RLM_SCHEDULE_LINES_ALL L,
RLM_SCHEDULE_HEADERS_ALL H
where to_char(mol.demand_stream_id) = L.ITEM_DETAIL_REF_VALUE_3
and L.Header_id = H.Header_id
and L.ITEM_DETAIL_REF_CODE_3 = 'ID';
select
nvl(decode(G_ORDER_CATEGORY_CODE,
'ORDER',DEFAULT_OUTBOUND_LINE_TYPE_ID,
'RETURN',DEFAULT_INBOUND_LINE_TYPE_ID,0),0)
into
g_line_rec.line_type_id
from
oe_transaction_types_all tta
where tta.transaction_type_id = G_ORDER_TYPE_ID;
select
oe_order_lines_s.nextval
into
v_return_created_line_id
from dual;
/* Record insertion into Order Lines table */
insert into oe_order_lines_all
(
line_id,
org_id,
header_id,
line_type_id,
line_number,
ordered_item,
request_date,
promise_date,
schedule_ship_date,
order_quantity_uom,
pricing_quantity,
pricing_quantity_uom,
cancelled_quantity,
shipped_quantity,
ordered_quantity,
fulfilled_quantity,
shipping_quantity,
shipping_quantity_uom,
delivery_lead_time,
tax_exempt_flag,
tax_exempt_number,
tax_exempt_reason_code,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id,
deliver_to_org_id,
ship_to_contact_id,
deliver_to_contact_id,
invoice_to_contact_id,
sold_to_org_id,
cust_po_number,
ship_tolerance_above,
ship_tolerance_below,
demand_bucket_type_code,
veh_cus_item_cum_key_id,
rla_schedule_type_code,
customer_dock_code,
customer_job,
customer_production_line,
cust_model_serial_number,
project_id,
task_id,
inventory_item_id,
tax_date,
tax_code,
tax_rate,
demand_class_code,
price_list_id,
pricing_date,
shipment_number,
agreement_id,
shipment_priority_code,
shipping_method_code,
freight_carrier_code,
freight_terms_code,
fob_point_code,
tax_point_code,
payment_term_id,
invoicing_rule_id,
accounting_rule_id,
source_document_type_id,
orig_sys_document_ref,
source_document_id,
orig_sys_line_ref,
source_document_line_id,
reference_line_id,
reference_type,
reference_header_id,
item_revision,
unit_selling_price,
unit_list_price,
tax_value,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
industry_context,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_attribute16,
industry_attribute17,
industry_attribute18,
industry_attribute19,
industry_attribute20,
industry_attribute21,
industry_attribute22,
industry_attribute23,
industry_attribute24,
industry_attribute25,
industry_attribute26,
industry_attribute27,
industry_attribute28,
industry_attribute29,
industry_attribute30,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
top_model_line_id,
link_to_line_id,
component_sequence_id,
component_code,
config_display_sequence,
sort_order,
item_type_code,
option_number,
option_flag,
dep_plan_required_flag,
visible_demand_flag,
line_category_code,
actual_shipment_date,
customer_trx_line_id,
return_context,
return_attribute1,
return_attribute2,
return_attribute3,
return_attribute4,
return_attribute5,
return_attribute6,
return_attribute7,
return_attribute8,
return_attribute9,
return_attribute10,
return_attribute11,
return_attribute12,
return_attribute13,
return_attribute14,
return_attribute15,
intmed_ship_to_org_id,
intmed_ship_to_contact_id,
actual_arrival_date,
ato_line_id,
auto_selected_quantity,
component_number,
earliest_acceptable_date,
explosion_date,
latest_acceptable_date,
model_group_number,
schedule_arrival_date,
ship_model_complete_flag,
schedule_status_code,
return_reason_code,
salesrep_id,
split_from_line_id,
cust_production_seq_num,
authorized_to_ship_flag,
invoice_interface_status_code,
ship_set_id,
arrival_set_id,
over_ship_reason_code,
over_ship_resolved_flag,
shipping_interfaced_flag,
ordered_item_id,
item_identifier_type,
configuration_id,
credit_invoice_line_id,
source_type_code,
planning_priority,
open_flag,
booked_flag,
fulfilled_flag,
service_txn_reason_code,
service_txn_comments,
service_duration,
service_start_date,
service_end_date,
service_coterminate_flag,
unit_selling_percent,
unit_list_percent,
unit_percent_base_price,
service_number,
service_period,
order_source_id,
tp_context,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
flow_status_code,
re_source_flag,
service_reference_type_Code,
service_reference_line_id,
service_reference_system_id,
calculate_price_flag,
marketing_source_code_id,
shippable_flag,
fulfillment_method_code,
revenue_amount,
fulfillment_date,
cancelled_flag,
sold_from_org_id,
commitment_id,
end_item_unit_number,
mfg_component_sequence_id,
config_header_id,
config_rev_nbr,
packing_instructions,
shipping_instructions,
invoiced_quantity,
reference_customer_trx_line_id,
split_by,
line_set_id,
orig_sys_shipment_ref,
change_sequence,
drop_ship_flag,
customer_line_number,
customer_shipment_number,
customer_item_net_price,
customer_payment_term_id,
first_ack_code,
last_ack_code,
first_ack_date,
last_ack_date,
model_remnant_flag,
upgraded_flag
)
values
(
g_line_id, /* LINE_ID */
g_line_rec.org_id, /* ORG_ID */
g_line_rec.header_id, /* HEADER_ID */
g_line_rec.line_type_id, /* LINE_TYPE_ID, */
g_line_rec.line_number, /* LINE_NUMBER */
-- g_last_line_number, /* LINE_NUMBER */
null, /* ordered_item, */
g_line_rec.date_requested_current, /* REQUEST_DATE */
g_line_rec.promise_date, /* PROMISE_DATE */
g_line_rec.schedule_date, /* SCHEDULE_SHIP_DATE */
nvl(r_uom_code,g_line_rec.unit_code), /* ORDER_QUANTITY_UOM */
g_line_rec.ordered_quantity, /* PRICING_QUANTITY */
nvl(r_uom_code,g_line_rec.unit_code), /* PRICING_QUANTITY_UOM */
decode(g_hdr_canc_flag,'Y', g_line_rec.cancelled_quantity,
decode(g_line_id_Change_flag,'Y',
g_line_rec.cancelled_quantity,0)), /* CANCELLED_QUANTITY */
g_line_rec.shipped_quantity, /* SHIPPED_QUANTITY */
decode(g_hdr_canc_flag,'Y',0,
nvl(g_line_rec.ordered_quantity,0)), /* ORDERED_QUANTITY */
g_line_rec.fulfilled_quantity, /* FULFILLED_QUANTITY */
g_line_rec.shipped_quantity, /* SHIPPING_QUANTITY */
nvl(r_uom_code,
g_line_rec.shipping_quantity_uom), /* SHIPPING_QUANTITY_UOM */
null, /* DELIVERY_LEAD_TIME */
G_TAX_EXEMPT_FLAG, /* TAX_EXEMPT_FLAG */
g_line_rec.tax_exempt_number, /* TAX_EXEMPT_NUMBER */
g_line_rec.tax_exempt_reason_code, /* TAX_EXEMPT_REASON_CODE */
g_line_rec.warehouse_id, /* SHIP_FROM_ORG_ID */
g_line_rec.ship_to_site_use_id, /* SHIP_TO_ORG_ID */
G_INVOICE_TO_SITE_USE_ID, /* INVOICE_TO_ORG_ID */
null, /* DELIVER_TO_ORG_ID */
g_line_rec.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
null, /* DELIVER_TO_CONTACT_ID */
null, /* INVOICE_TO_CONTACT_ID */
G_CUSTOMER_ID, /* SOLD_TO_ORG_ID */
decode(G_AUTO_FLAG,'Y',
g_line_rec.industry_attribute3,
G_PURCHASE_ORDER_NUM), /* CUST_PO_NUMBER */
null, /* SHIP_TOLERANCE_ABOVE */
null, /* SHIP_TOLERANCE_BELOW */
decode(G_AUTO_FLAG,'Y','DAY',NULL), /* DEMAND_BUCKET_TYPE_CODE */
decode(G_AUTO_FLAG,'Y',-1,NULL), /* VEH_CUS_ITEM_CUM_KEY_ID */
g_line_rec.rla_schedule_type_code, /* RLA_SCHEDULE_TYPE_CODE */
g_line_rec.customer_dock_code, /* CUSTOMER_DOCK_CODE */
g_line_rec.customer_job, /* CUSTOMER_JOB */
g_line_rec.customer_production_line, /* CUSTOMER_PRODUCTION_LINE */
g_line_rec.customer_model_serial_number, /* CUST_MODEL_SERIAL_NUMBER */
g_line_rec.project_id, /* PROJECT_ID */
g_line_rec.task_id, /* TASK_ID */
nvl(r_inventory_item_id,g_line_rec.inventory_item_id), /* INVENTORY_ITEM_ID */
null, /* TAX_DATE */
g_line_rec.tax_code, /* TAX_CODE */
null, /* TAX_RATE */
g_line_rec.demand_class_code, /* DEMAND_CLASS_CODE */
g_line_rec.price_list_id, /* PRICE_LIST_ID */
null, /* PRICING_DATE */
r_shipment_number, /* SHIPMENT_NUMBER */
g_line_rec.agreement_id, /* AGREEMENT_ID */
g_line_rec.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
null, /* SHIPPPING_METHOD_CODE */
g_line_rec.ship_method_code, /* FREIGHT_CARRIER_CODE */
G_freight_terms_code, /* FREIGHT_TERMS_CODE */
null, /* FOB_POINT_CODE */
'INVOICE', /* TAX_POINT_CODE */
G_terms_id, /* PAYMENT_TERM_ID */
nvl(g_line_rec.invoicing_rule_id,0), /* INVOICING_RULE_ID */
nvl(g_line_rec.accounting_rule_id,0), /* ACCOUNTING_RULE_ID */
g_line_rec.source_document_type_id, /* SOURCE_DOCUMENT_TYPE_ID */
G_ORIG_SYS_DOCUMENT_REF, /* ORIG_SYS_DOCUMENT_REF */
g_line_rec.source_document_id, /* SOURCE_DOCUMENT_ID */
g_line_rec.original_system_line_reference, /* ORIG_SYS_LINE_REFERENCE */
g_line_rec.source_document_line_id, /* SOURCE_DOCUMENT_LINE_ID */
v_reference_line_id, /* REFERENCE_LINE_ID */
g_line_rec.return_reference_type_code, /* REFERENCE_TYPE */
v_reference_header_id, /* REFERENCE_HEADER_ID */
null, /* ITEM_REVISION */
g_line_rec.selling_price, /* SELLING_PRICE */
g_line_rec.list_price, /* LIST_PRICE */
null, /* TAX_VALUE */
g_line_rec.context, /* CONTEXT */
g_line_rec.attribute1, /* ATTRIBUTE1 */
g_line_rec.attribute2, /* ATTRIBUTE2 */
g_line_rec.attribute3, /* ATTRIBUTE3 */
g_line_rec.attribute4, /* ATTRIBUTE4 */
g_line_rec.attribute5, /* ATTRIBUTE5 */
g_line_rec.attribute6, /* ATTRIBUTE6 */
g_line_rec.attribute7, /* ATTRIBUTE7 */
g_line_rec.attribute8, /* ATTRIBUTE8 */
g_line_rec.attribute9, /* ATTRIBUTE9 */
g_line_rec.attribute10, /* ATTRIBUTE10 */
g_line_rec.attribute11, /* ATTRIBUTE11 */
g_line_rec.attribute12, /* ATTRIBUTE12 */
g_line_rec.attribute13, /* ATTRIBUTE13 */
g_line_rec.attribute14, /* ATTRIBUTE14 */
g_line_rec.attribute15, /* ATTRIBUTE15 */
g_line_rec.global_attribute_category, /* GLOBAL_ATTRIBUTE_CATEGORY */
g_line_rec.global_attribute1, /* GLOBAL_ATTRIBUTE1 */
g_line_rec.global_attribute2, /* GLOBAL_ATTRIBUTE2 */
g_line_rec.global_attribute3, /* GLOBAL_ATTRIBUTE3 */
g_line_rec.global_attribute4, /* GLOBAL_ATTRIBUTE4 */
g_line_rec.global_attribute5, /* GLOBAL_ATTRIBUTE5 */
g_line_rec.global_attribute6, /* GLOBAL_ATTRIBUTE6 */
g_line_rec.global_attribute7, /* GLOBAL_ATTRIBUTE7 */
g_line_rec.global_attribute8, /* GLOBAL_ATTRIBUTE8 */
g_line_rec.global_attribute9, /* GLOBAL_ATTRIBUTE9 */
g_line_rec.global_attribute10, /* GLOBAL_ATTRIBUTE10 */
g_line_rec.global_attribute11, /* GLOBAL_ATTRIBUTE11 */
g_line_rec.global_attribute12, /* GLOBAL_ATTRIBUTE12 */
g_line_rec.global_attribute13, /* GLOBAL_ATTRIBUTE13 */
g_line_rec.global_attribute14, /* GLOBAL_ATTRIBUTE14 */
g_line_rec.global_attribute15, /* GLOBAL_ATTRIBUTE15 */
g_line_rec.global_attribute16, /* GLOBAL_ATTRIBUTE16 */
g_line_rec.global_attribute17, /* GLOBAL_ATTRIBUTE17 */
g_line_rec.global_attribute18, /* GLOBAL_ATTRIBUTE18 */
g_line_rec.global_attribute19, /* GLOBAL-ATTRIBUTE19 */
g_line_rec.global_attribute20, /* GLOBAL_ATTRIBUTE20 */
g_line_rec.pricing_context, /* PRICING_CONTEXT */
g_line_rec.pricing_attribute1, /* PRICING_ATTRIBUTE1 */
g_line_rec.pricing_attribute2, /* PRICING_ATTRIBUTE2 */
g_line_rec.pricing_attribute3, /* PRICING_ATTRIBUTE3 */
g_line_rec.pricing_attribute4, /* PRICING_ATTRIBUTE4 */
g_line_rec.pricing_attribute5, /* PRICING_ATTRIBUTE5 */
g_line_rec.pricing_attribute6, /* PRICING_ATTRIBUTE6 */
g_line_rec.pricing_attribute7, /* PRICING_ATTRIBUTE7 */
g_line_rec.pricing_attribute8, /* PRICING_ATTRIBUTE8 */
g_line_rec.pricing_attribute9, /* PRICING_ATTRIBUTE9 */
g_line_rec.pricing_attribute10, /* PRICING_ATTRIBUTE10*/
g_line_rec.industry_context, /* INDUSTRY_CONTEXT */
g_line_rec.industry_attribute1, /* INDUSTRY_ATTRIBUTE1 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute2), /* INDUSTRY_ATTRIBUTE2 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute3), /* INDUSTRY_ATTRIBUTE3 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute4), /* INDUSTRY_ATTRIBUTE4 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute5), /* INDUSTRY_ATTRIBUTE5 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute6), /* INDUSTRY_ATTRIBUTE6 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute7), /* INDUSTRY_ATTRIBUTE7 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute8), /* INDUSTRY_ATTRIBUTE8 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute9), /* INDUSTRY_ATTRIBUTE9 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute10), /* INDUSTRY_ATTRIBUTE10 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute11), /* INDUSTRY_ATTRIBUTE11 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute12), /* INDUSTRY_ATTRIBUTE12 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute13), /* INDUSTRY_ATTRIBUTE13 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute14), /* INDUSTRY_ATTRIBUTE14 */
decode(g_auto_Flag,'Y',
NULL,g_line_rec.industry_attribute15), /* INDUSTRY_ATTRIBUTE15 */
NULL, /* INDUSTRY_ATTRIBUTE16 */
NULL, /* INDUSTRY_ATTRIBUTE17 */
NULL, /* INDUSTRY_ATTRIBUTE18 */
NULL, /* INDUSTRY_ATTRIBUTE19 */
NULL, /* INDUSTRY_ATTRIBUTE20 */
NULL, /* INDUSTRY_ATTRIBUTE21 */
NULL, /* INDUSTRY_ATTRIBUTE22 */
NULL, /* INDUSTRY_ATTRIBUTE23 */
NULL, /* INDUSTRY_ATTRIBUTE24 */
NULL, /* INDUSTRY_ATTRIBUTE25 */
NULL, /* INDUSTRY_ATTRIBUTE26 */
NULL, /* INDUSTRY_ATTRIBUTE27 */
NULL, /* INDUSTRY_ATTRIBUTE28 */
NULL, /* INDUSTRY_ATTRIBUTE29 */
NULL, /* INDUSTRY_ATTRIBUTE30 */
g_line_rec.creation_date, /* CREATION_DATE */
g_line_rec.created_by, /* CREATED_BY */
g_line_rec.last_update_date, /* LAST_UPDATE_DATE */
g_line_rec.last_updated_by, /* LAST_UPDATED_BY */
g_line_rec.last_update_login, /* LAST_UPDATE_LOGIN */
nvl(g_line_rec.program_application_id,0), /* PROGRAM_APPLICATION_ID */
g_line_rec.program_id, /* PROGRAM_ID */
g_line_rec.program_update_date, /* PROGRAM_UPDATE_DATE */
g_line_rec.request_id, /* REQUEST_ID */
g_line_rec.top_model_line_id, /* TOP_MODEL_LINE_ID */
g_line_rec.link_to_line_id, /* LINK_TO_LINE_ID */
g_line_rec.component_sequence_id, /* COMPONENT_SEQUENCE_ID */
g_line_rec.component_code, /* COMPONENT_CODE */
null, /* CONFIG_DISPLAY_SEQUENCE */
null, /* SORT_ORDER, */
g_line_rec.item_type_code, /* ITEM_TYPE_CODE */
null, /* OPTION_NUMBER */
g_line_rec.option_flag, /* OPTION_FLAG, */
g_line_rec.dep_plan_required_flag, /* DEP_PLAN_REQUIRED_FLAG */
g_line_rec.visible_demand_flag, /* VISIBLE_DEMAND_FLAG */
g_line_rec.line_category_code, /* LINE_CATEGORY_CODE */
g_line_rec.actual_departure_date, /* ACTUAL_SHIPMENT_DATE */
decode(g_line_rec.line_type_code,'RETURN',
decode(g_line_rec.return_reference_type_code,
'INVOICE',g_line_rec.return_reference_id,NULL), NULL), /* CUSTOMER_TRX_LINE_ID */
g_line_rec.return_reference_type_Code, /* RETURN_CONTEXT */
decode(g_line_rec.line_type_code,'RETURN',
decode(g_line_rec.return_reference_type_code,
'INVOICE',v_customer_trx_id,
v_reference_header_id),NULL), /* RETURN_ATTRIBUTE1 */
decode(g_line_rec.line_type_code,'RETURN',
decode(g_line_rec.return_reference_type_code,
'INVOICE',g_line_rec.return_reference_id,
v_reference_line_id),NULL), /* RETURN_ATTRIBUTE2 */
NULL, /* RETURN_ATTRIBUTE3 */
NULL, /* RETURN_ATTRIBUTE4 */
null, /* RETURN_ATTRIBUTE5 */
null, /* RETURN_ATTRIBUTE6 */
null, /* RETURN_ATTRIBUTE7 */
null, /* RETURN_ATTRIBUTE8 */
null, /* RETURN_ATTRIBUTE9 */
null, /* RETURN_ATTRIBUTE10 */
null, /* RETURN_ATTRIBUTE11 */
null, /* RETURN_ATTRIBUTE12 */
null, /* RETURN_ATTRIBUTE13 */
null, /* RETURN_ATTRIBUTE14 */
null, /* RETURN_ATTRIBUTE15 */
g_line_rec.intermediate_ship_to_id, /* intmed_ship_to_org_id, */
g_line_rec.ship_to_contact_id, /* intmed_ship_to_contact_id, */
null, /* actual_arrival_date, */
g_line_rec.ato_line_id, /* ATO_LINE_ID */
null, /* auto_selected_quantity, */
null, /* component_number, */
null, /* earliest_acceptable_date, */
g_line_rec.standard_component_freeze_date, /* explosion_date, */
g_line_rec.latest_acceptable_date, /* latest_acceptable_date, */
null, /* model_group_number, */
null, /* schedule_arrival_date, */
g_line_rec.ship_model_complete_flag, /* ship_model_complete_flag, */
g_line_rec.schedule_status_code, /* schedule_status_code, */
g_line_rec.transaction_reason_code, /* return_reason_code */
g_salesrep_id, /* salesrep_id */
null, /* split_from_line_id */
g_line_rec.planning_prod_seq_number, /* cust_production_seq_num */
decode(G_AUTO_FLAG,'Y','Y',NULL), /* authorized_to_ship_flag */
g_line_rec.invoice_interface_status_code, /* invoice_interface_status_code */
g_set_id, /* Ship_Set_Id */
null, /* Arrival_Set_Id */
null, /* over_ship_reason_code */
null, /* over_ship_resolved_flag */
g_line_rec.shipping_interfaced_flag, /* shipping_interfaced_flag */
decode(nvl(g_line_rec.customer_item_id,-1),
-1,g_line_rec.inventory_item_id,
g_line_rec.customer_item_id), /* ordered_item_id */
decode(nvl(g_line_rec.customer_item_id,-1),
-1,'INT','CUST'), /* item_identifier_type*/
null, /* configuration_id */
g_line_rec.credit_invoice_line_id, /* credit_invoice_line_id */
g_line_rec.source_type_code, /* source_type_code */
g_line_rec.planning_priority, /* planning_priority */
g_line_rec.open_flag, /* open_flag */
g_line_rec.booked_flag, /* booked_flag */
g_line_rec.fulfilled_flag, /* fulfilled_Flag */
g_line_rec.service_txn_reason_code, /* service_txn_reason_code */
g_line_rec.service_txn_comments, /* service_txn_comments */
g_line_rec.service_duration, /* service_duration */
g_line_rec.service_start_date, /* service_start_date */
g_line_rec.service_end_date, /* service_end_date */
g_line_rec.service_coterminate_flag, /* service_coterminate_flag */
g_line_rec.selling_percent, /* unit_selling_percent */
g_line_rec.list_percent, /* unit_list_percent */
g_line_rec.percent_base_price, /* unit_percent_base_price */
g_line_rec.service_number, /* service_number */
g_line_rec.service_period, /* service_period */
nvl(G_ORDER_SOURCE_ID,0), /* ORDER_SOURCE_ID, */
g_line_rec.tp_context, /* tp_context */
g_line_rec.tp_attribute1, /* tp_attribute1 */
g_line_rec.tp_attribute2, /* tp_attribute2 */
g_line_rec.tp_attribute3, /* tp_attribute3 */
g_line_rec.tp_attribute4, /* tp_attribute4 */
g_line_rec.tp_attribute5, /* tp_attribute5 */
g_line_rec.tp_attribute6, /* tp_attribute6 */
g_line_rec.tp_attribute7, /* tp_attribute7 */
g_line_rec.tp_attribute8, /* tp_attribute8 */
g_line_rec.tp_attribute9, /* tp_attribute9 */
g_line_rec.tp_attribute10, /* tp_attribute10 */
g_line_rec.tp_attribute11, /* tp_attribute11 */
g_line_rec.tp_attribute12, /* tp_attribute12 */
g_line_rec.tp_attribute13, /* tp_attribute13 */
g_line_rec.tp_attribute14, /* tp_attribute14 */
g_line_rec.tp_attribute15, /* tp_attribute15 */
g_line_rec.flow_status_code, /* flow_status_code */
g_line_rec.re_source_flag, /* re_source_flag */
g_line_rec.service_reference_type_Code, /* service_reference_type_code */
g_line_rec.service_reference_line_id, /* service_reference_line_id */
g_line_rec.service_reference_system_id, /* service_reference_system_id */
g_line_rec.calculate_price_flag, /* calculate_price_flag */
g_line_rec.marketing_source_code_id, /* marketing_source_code_id */
g_line_rec.shippable_flag, /* shippable_flag */
g_line_rec.fulfillment_method_code, /* fulfillment_method_code */
g_line_rec.revenue_amount, /* revenue_amount */
g_line_rec.fulfillment_date, /* fulfillment_date */
g_line_rec.cancelled_flag, /* cancelled_flag */
g_line_rec.org_id, /* sold_from_org_id */
null, /* commitment_id */
null, /* end_item_unit_number */
null, /* mfg_component_sequence_id */
null, /* config_header_id */
null, /* config_rev_nbr */
null, /* packing_instructions */
null, /* shipping_instructions */
g_line_rec.invoiced_quantity, /* invoiced_quantity */
null, /* reference_customer_trx_line_id */
null, /* split_by */
nvl(r_line_set_id,null), /* line_set_id */
null, /* orig_sys_shipment_ref */
null, /* change_sequence */
null, /* drop_ship_flag */
null, /* customer_line_number */
null, /* customer_shipment_number */
null, /* customer_item_net_price */
null, /* customer_payment_term_id */
null, /* first_ack_code */
null, /* last_ack_code */
null, /* first_ack_date */
null, /* last_ack_date */
null, /* model_remnant_flag */
nvl(r_upgraded_flag,'Y') /* upgraded_flag */
);
OE_UPG_SO.Upgrade_Insert_Upgrade_Log;
update rcv_shipment_lines
set oe_order_line_id = v_return_created_line_id
where oe_order_line_id = g_old_line_id;
update rcv_transactions
set oe_order_line_id = v_return_created_line_id
where oe_order_line_id = g_old_line_id;
update rcv_supply
set oe_order_line_id = v_return_created_line_id
where oe_order_line_id = g_old_line_id;
oe_upg_so.upgrade_insert_errors
(
L_header_id => g_header_id,
L_comments => 'Updating RCV shipment failed for line :'
||to_char(g_old_line_id)||' with oracle error ORA-'
||to_char(sqlcode));
OE_Upg_SO.Upgrade_Insert_Lines_History;
select
sha.header_id,
sha.org_id,
sha.order_type_id,
sha.order_number,
sha.credit_card_expiration_date,
sha.original_system_source_code,
sha.original_system_reference,
sha.date_ordered,
sha.date_requested_current,
sha.shipment_priority_code,
sha.demand_class_code,
sha.price_list_id,
sha.tax_exempt_flag,
sha.tax_exempt_num tax_exempt_num,
sha.tax_exempt_reason_code,
sha.conversion_rate,
sha.conversion_type_code,
sha.conversion_date,
sha.ship_partial_flag,
sha.currency_code,
sha.agreement_id,
sha.purchase_order_num,
sha.invoicing_rule_id,
sha.accounting_rule_id,
sha.terms_id,
sha.ship_method_code,
sha.fob_code,
sha.freight_terms_code,
sha.ship_to_contact_id,
sha.invoice_to_contact_id,
sha.creation_date,
sha.created_by,
sha.last_updated_by,
sha.last_update_date,
sha.last_update_login,
sha.program_application_id,
sha.program_id,
sha.program_update_date,
sha.request_id,
sha.customer_id,
sha.salesrep_id,
sha.cancelled_flag,
sha.context,
sha.attribute1,
sha.attribute2,
sha.attribute3,
sha.attribute4,
sha.attribute5,
sha.attribute6,
sha.attribute7,
sha.attribute8,
sha.attribute9,
sha.attribute10,
sha.attribute11,
sha.attribute12,
sha.attribute13,
sha.attribute14,
sha.attribute15,
sha.payment_type_code,
sha.payment_amount,
sha.check_number,
sha.credit_card_code,
sha.credit_card_holder_name,
sha.credit_card_number,
sha.credit_card_approval_code,
shattr.global_attribute_category,
shattr.global_attribute1,
shattr.global_attribute2,
shattr.global_attribute3,
shattr.global_attribute4,
shattr.global_attribute5,
shattr.global_attribute6,
shattr.global_attribute7,
shattr.global_attribute8,
shattr.global_attribute9,
shattr.global_attribute10,
shattr.global_attribute11,
shattr.global_attribute12,
shattr.global_attribute13,
shattr.global_attribute14,
shattr.global_attribute15,
shattr.global_attribute16,
shattr.global_attribute17,
shattr.global_attribute18,
shattr.global_attribute19,
shattr.global_attribute20,
sha.invoice_to_site_use_id,
sha.order_category,
sha.ordered_by_contact_id,
sha.ship_to_site_use_id,
sha.warehouse_id,
nvl(sha.open_flag,'N') open_flag,
decode(sha.s1,1,'Y','N') booked_flag,
sha.s1_date
from
so_headers_all sha,
so_header_attributes shattr
where
sha.upgrade_flag = 'N' and
sha.header_id = shattr.header_id(+) and
/*
(( L_Line_type = 'R' and sha.order_category = 'RMA') or
( L_Line_type = 'O' and sha.order_category <> 'RMA')) and
*/
sha.header_id between v_start_header and v_end_header
order by
sha.header_id;
select
start_header_id,
end_header_id
into
v_start_header,
v_end_header
from oe_upgrade_distribution
where slab = L_slab;
oe_upg_so.upgrade_insert_errors
(
L_header_id => 0,
L_comments => 'FYI Only: Parallel process of Sales Order Upgrade not used for the slab:'||to_char(L_slab)
);
select
decode(moh.order_category,'P','ORDER',
'R','ORDER','RMA','RETURN','*')
into
G_ORDER_CATEGORY_CODE
from dual;
select 10 into G_ORDER_SOURCE_ID
from so_order_sources
where order_source_id = moh.original_system_source_code
and name = 'Internal';
select
sales_order_id
into
G_MTL_SALES_ORDER_ID
from
mtl_sales_orders mso,
so_order_types_115_all sota
where segment1 = to_char(moh.order_number)
and segment2 = sota.name
and segment3 = v_source_code_profile_value
and moh.order_type_id = sota.order_type_id
and rownum = 1;
insert into oe_order_headers_all
(
header_id,
org_id,
order_type_id,
order_number,
version_number,
expiration_date,
order_source_id,
source_document_type_id,
orig_sys_document_ref,
source_document_id,
ordered_date,
request_date,
pricing_date,
shipment_priority_code,
demand_class_code,
price_list_id,
tax_exempt_flag,
tax_exempt_number,
tax_exempt_reason_code,
conversion_rate,
conversion_type_code,
conversion_rate_date,
partial_shipments_allowed,
ship_tolerance_above,
ship_tolerance_below,
transactional_curr_code,
agreement_id,
tax_point_code,
cust_po_number,
invoicing_rule_id,
accounting_rule_id,
payment_term_id,
shipping_method_code,
freight_carrier_code,
fob_point_code,
freight_terms_code,
sold_to_org_id,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id,
deliver_to_org_id,
sold_to_contact_id,
ship_to_contact_id,
invoice_to_contact_id,
deliver_to_contact_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
salesrep_id,
return_reason_code,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
order_date_type_code,
earliest_schedule_limit,
latest_schedule_limit,
payment_type_code,
payment_amount,
check_number,
credit_card_code,
credit_card_holder_name,
credit_card_number,
credit_card_expiration_date,
credit_card_approval_code,
credit_card_approval_date,
sales_channel_code,
order_category_code,
cancelled_flag,
open_flag,
booked_flag,
marketing_source_code_id,
tp_context,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
flow_status_code,
upgraded_flag,
booked_date,
sold_from_org_id
)
values
(
moh.header_id, /* HEADER_ID */
moh.org_id, /* ORG_ID */
moh.order_type_id, /* ORDER_TYPE_ID */
moh.order_number, /* ORDER_NUMBER */
1, /* VERSION_NUMBER, */
NULL, /* EXPIRATION_DATE, */
G_ORDER_SOURCE_ID, /* ORDER_SOURCE_ID, */
decode(g_copied_flag,'Y',2,NULL), /* SOURCE_DOCUMENT_TYPE_ID, */
moh.original_system_reference, /* ORIG_SYS_DOCUMENT_REF, */
decode(g_copied_flag,'Y',
g_source_document_id,NULL), /* SOURCE_DOCUMENT_ID, */
moh.date_ordered, /* ORDERED_DATE */
moh.date_requested_current, /* REQUEST_DATE */
null, /* PRICING_DATE, */
moh.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
moh.demand_class_code, /* DEMAND_CLASS_CODE */
moh.price_list_id, /* PRICE_LIST_ID */
moh.tax_exempt_flag, /* TAX_EXEMPT_FLAG */
moh.tax_exempt_num, /* TAX_EXEMPT_NUMBER */
moh.tax_exempt_reason_code, /* TAX_EXEMPT_REASON_CODE */
moh.conversion_rate, /* CONVERSION_RATE */
moh.conversion_type_code, /* CONVERSION_TYPE_CODE */
moh.conversion_date, /* CONVERSION_RATE_DATE */
moh.ship_partial_flag, /* PARTIAL_SHIPMENTS_ALLOWED */
null, /* SHIP_TOLERANCE_ABOVE, */
null, /* SHIP_TOLERANCE_BELOW, */
moh.currency_code, /* TRANSACTIONAL_CURR_CODE */
moh.agreement_id, /* AGREEMENT_ID */
'INVOICE', /* TAX_POINT_CODE */
decode(G_AUTO_FLAG,'Y',
g_line_rec.industry_attribute3,
G_PURCHASE_ORDER_NUM), /* CUST_PO_NUMBER */
nvl(moh.invoicing_rule_id,0), /* INVOICING_RULE_ID */
nvl(moh.accounting_rule_id,0), /* ACCOUNTING_RULE_ID */
nvl(moh.terms_id,0), /* PAYMENT_TERM_ID */
moh.ship_method_code, /* SHIPING_METHOD_CODE */
null, /* FREIGHT_CARRIER_CODE */
moh.fob_code, /* FOB_POINT_CODE */
moh.freight_terms_code, /* FREIGHT_TERMS_CODE */
G_customer_id, /* SOLD_TO_ORG_ID */
moh.warehouse_id, /* SHIP_FROM_ORG_ID */
moh.ship_to_site_use_id, /* SHIP_TO_ORG_ID */
moh.invoice_to_site_use_id, /* INVOICE_TO_ORG_ID */
null, /* DELIVER_TO_ORG_ID */
moh.ordered_by_contact_id, /* SOLD_TO_CONTACT_ID */
moh.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
moh.invoice_to_contact_id, /* INVOICE_TO_CONTACT_ID */
null, /* DELIVER_TO_CONTACT_ID */
moh.creation_date, /* CREATION_DATE */
moh.created_by, /* CREATED_BY */
moh.last_updated_by, /* LAST_UPDATED_BY */
moh.last_update_date, /* LAST_UPDATE_DATE */
moh.last_update_login, /* LAST_UPDATE_LOGIN */
nvl(moh.program_application_id,0), /* PROGRAM_APPLICATION_ID */
moh.program_id, /* PROGRAM_ID */
moh.program_update_date, /* PROGRAM_UPDATE_DATE */
moh.request_id, /* REQUEST_ID */
moh.salesrep_id, /* SALESREP_ID */
null, /* RETURN_REASON_CODE */
moh.context, /* CONTEXT */
moh.attribute1, /* ATTRIBUTE1 */
moh.attribute2, /* ATTRIBUTE2 */
moh.attribute3, /* ATTRIBUTE3 */
moh.attribute4, /* ATTRIBUTE4 */
moh.attribute5, /* ATTRIBUTE5 */
moh.attribute6, /* ATTRIBUTE6 */
moh.attribute7, /* ATTRIBUTE7 */
moh.attribute8, /* ATTRIBUTE8 */
moh.attribute9, /* ATTRIBUTE9 */
moh.attribute10, /* ATTRIBUTE10 */
moh.attribute11, /* ATTRIBUTE11 */
moh.attribute12, /* ATTRIBUTE12 */
moh.attribute13, /* ATTRIBUTE13 */
moh.attribute14, /* ATTRIBUTE14 */
moh.attribute15, /* ATTRIBUTE15 */
moh.global_attribute_category, /* GLOBAL_ATTRIBUTE_CATEGORY */
moh.global_attribute1, /* GLOBAL_ATTRIBUTE1 */
moh.global_attribute2, /* GLOBAL_ATTRIBUTE2 */
moh.global_attribute3, /* GLOBAL_ATTRIBUTE3 */
moh.global_attribute4, /* GLOBAL_ATTRIBUTE4 */
moh.global_attribute5, /* GLOBAL_ATTRIBUTE5 */
moh.global_attribute6, /* GLOBAL_ATTRIBUTE6 */
moh.global_attribute7, /* GLOBAL_ATTRIBUTE7 */
moh.global_attribute8, /* GLOBAL_ATTRIBUTE8 */
moh.global_attribute9, /* GLOBAL_ATTRIBUTE9 */
moh.global_attribute10, /* GLOBAL_ATTRIBUTE10 */
moh.global_attribute11, /* GLOBAL_ATTRIBUTE11 */
moh.global_attribute12, /* GLOBAL_ATTRIBUTE12 */
moh.global_attribute13, /* GLOBAL_ATTRIBUTE13 */
moh.global_attribute14, /* GLOBAL_ATTRIBUTE14 */
moh.global_attribute15, /* GLOBAL_ATTRIBUTE15 */
moh.global_attribute16, /* GLOBAL_ATTRIBUTE16 */
moh.global_attribute17, /* GLOBAL_ATTRIBUTE17 */
moh.global_attribute18, /* GLOBAL_ATTRIBUTE18 */
moh.global_attribute19, /* GLOBAL_ATTRIBUTE19 */
moh.global_attribute20, /* GLOBAL_ATTRIBUTE20 */
'SHIP', /* ORDER_DATE_TYPE_CODE */
OE_Upg_SO.G_Earliest_Schedule_Limit, /* EARLIEST_SCHEDULE_LIMIT */
OE_Upg_SO.G_Latest_Schedule_Limit, /* LATEST_SCHEDULE_LIMIT */
moh.payment_type_code, /* PAYMENT_TYPE_CODE */
moh.payment_amount, /* PAYMENT_AMOUNT */
moh.check_number, /* CHECK_NUMBER */
moh.credit_card_code, /* CREDIT_CARD_CODE */
moh.credit_card_holder_name, /* CREDIT_CARD_HOLDER_NAME */
moh.credit_card_number, /* CREDIT_CARD_NUMBER */
moh.credit_card_expiration_date, /* CREDIT_CARD_EXPIRATION_DATE */
moh.credit_card_approval_code, /* CREDIT_CARD_APPROVAL_CODE */
decode(moh.credit_card_approval_code,NULL,NULL,
moh.s1_date), /* CREDIT_CARD_APPROVAL_DATE */
null, /* SALES CHANNEL CODE */
G_ORDER_CATEGORY_CODE, /* ORDER_CATEGORY_CODE */
moh.cancelled_flag, /* CANCELLED_FLAG */
moh.open_flag, /* OPEN_FLAG */
moh.booked_flag, /* BOOKED_FLAG */
null, /* MARKETING_SOURCE_CODE_ID */
null, /* TP_CONTEXT */
null, /* TP_ATTRIBUTE1 */
null, /* TP_ATTRIBUTE2 */
null, /* TP_ATTRIBUTE3 */
null, /* TP_ATTRIBUTE4 */
null, /* TP_ATTRIBUTE5 */
null, /* TP_ATTRIBUTE6 */
null, /* TP_ATTRIBUTE7 */
null, /* TP_ATTRIBUTE8 */
null, /* TP_ATTRIBUTE9 */
null, /* TP_ATTRIBUTE10 */
null, /* TP_ATTRIBUTE11 */
null, /* TP_ATTRIBUTE12 */
null, /* TP_ATTRIBUTE13 */
null, /* TP_ATTRIBUTE14 */
null, /* TP_ATTRIBUTE15 */
decode(G_CANCELLED_FLAG,'Y','CANCELLED',
decode(moh.open_flag,'N','CLOSED',
decode(moh.booked_flag,'N','ENTERED',
'Y','BOOKED',NULL))), /* FLOW_STATUS_CODE */
'Y', /* UPGRADED_FLAG */
decode(nvl(moh.booked_flag,'-'),'Y',
moh.s1_date,NULL), /* BOOKED_DATE */
moh.org_id /* SOLD_FROM_ORG_ID */
);
select
soc.header_id,
soc.line_id,
soc.created_by,
soc.creation_date,
soc.last_updated_by,
soc.last_update_date,
soc.last_update_login,
soc.program_application_id,
soc.program_id,
soc.program_update_date,
soc.request_id,
soc.cancel_code,
soc.cancelled_by,
soc.cancel_date,
soc.cancelled_quantity,
soc.cancel_comment,
soc.context,
soc.attribute1,
soc.attribute2,
soc.attribute3,
soc.attribute4,
soc.attribute5,
soc.attribute6,
soc.attribute7,
soc.attribute8,
soc.attribute9,
soc.attribute10,
soc.attribute11,
soc.attribute12,
soc.attribute13,
soc.attribute14,
soc.attribute15
into
G_Hdr_Canc_Rec
from
so_order_cancellations soc
where soc.header_id = g_header_id
and soc.line_Id is null
and rownum =1 ;
OE_UPG_SO.Upgrade_Insert_Upgrade_Log;
Update SO_HEADERS_ALL
set upgrade_flag = 'Y'
where header_id = G_HEADER_ID;
oe_upg_so.upgrade_insert_errors
(
L_header_id => g_header_id,
L_comments => 'Exception tapped: Alert level = '
||to_char(G_ERROR_LOCATION)||' Code -'
||to_char(v_error_code)
||' - Line id '||to_char(g_line_id)
||' Line detail id'
||to_char(g_line_rec.line_detail_id)
);
oe_upg_so.upgrade_insert_errors
(
L_header_id => G_HEADER_ID,
L_comments => 'Exception tapped: Exception level ='
||to_char(G_ERROR_LOCATION)||'code -'
||to_char(v_error_code)
||' - Line id '||to_char(G_LINE_ID)
||' Line detail id'
||to_char(g_line_rec.line_detail_id)
);
Procedure Upgrade_Insert_Lines_History is
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
insert into oe_order_lines_history
(
line_id,
org_id,
header_id,
line_type_id,
line_number,
ordered_item,
request_date,
promise_date,
schedule_ship_date,
order_quantity_uom,
pricing_quantity,
pricing_quantity_uom,
cancelled_quantity,
shipped_quantity,
ordered_quantity,
fulfilled_quantity,
shipping_quantity,
shipping_quantity_uom,
delivery_lead_time,
tax_exempt_flag,
tax_exempt_number,
tax_exempt_reason_code,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id,
deliver_to_org_id,
ship_to_contact_id,
deliver_to_contact_id,
invoice_to_contact_id,
sold_to_org_id,
cust_po_number,
ship_tolerance_above,
ship_tolerance_below,
demand_bucket_type_code,
veh_cus_item_cum_key_id,
rla_schedule_type_code,
customer_dock_code,
customer_job,
customer_production_line,
cust_model_serial_number,
project_id,
task_id,
inventory_item_id,
tax_date,
tax_code,
tax_rate,
demand_class_code,
price_list_id,
pricing_date,
shipment_number,
agreement_id,
shipment_priority_code,
shipping_method_code,
freight_carrier_code,
freight_terms_code,
fob_point_code,
tax_point_code,
payment_term_id,
invoicing_rule_id,
accounting_rule_id,
source_document_type_id,
orig_sys_document_ref,
source_document_id,
orig_sys_line_ref,
source_document_line_id,
reference_line_id,
reference_type,
reference_header_id,
item_revision,
unit_selling_price,
unit_list_price,
tax_value,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
industry_context,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_attribute16,
industry_attribute17,
industry_attribute18,
industry_attribute19,
industry_attribute20,
industry_attribute21,
industry_attribute22,
industry_attribute23,
industry_attribute24,
industry_attribute25,
industry_attribute26,
industry_attribute27,
industry_attribute28,
industry_attribute29,
industry_attribute30,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
configuration_id,
link_to_line_id,
component_sequence_id,
component_code,
config_display_sequence,
sort_order,
item_type_code,
option_number,
option_flag,
dep_plan_required_flag,
visible_demand_flag,
line_category_code,
actual_shipment_date,
customer_trx_line_id,
return_context,
return_attribute1,
return_attribute2,
return_attribute3,
return_attribute4,
return_attribute5,
return_attribute6,
return_attribute7,
return_attribute8,
return_attribute9,
return_attribute10,
return_attribute11,
return_attribute12,
return_attribute13,
return_attribute14,
return_attribute15,
intmed_ship_to_org_id,
intmed_ship_to_contact_id,
actual_arrival_date,
ato_line_id,
auto_selected_quantity,
component_number,
earliest_acceptable_date,
explosion_date,
latest_acceptable_date,
model_group_number,
schedule_arrival_date,
ship_model_complete_flag,
schedule_status_code,
return_reason_code,
salesrep_id,
split_from_line_id,
cust_production_seq_num,
authorized_to_ship_flag,
invoice_interface_status_code,
ship_set_id,
arrival_set_id,
hist_comments,
hist_type_code,
reason_code,
hist_created_by,
hist_creation_date,
source_type_code,
booked_flag,
fulfilled_flag,
sold_from_org_id,
top_model_line_id,
cancelled_flag,
open_flag,
over_ship_reason_code,
over_ship_resolved_flag,
item_identifier_type,
commitment_id,
shipping_interfaced_flag,
credit_invoice_line_id,
end_item_unit_number,
mfg_component_sequence_id,
config_header_id,
config_rev_nbr,
shipping_instructions,
packing_instructions,
invoiced_quantity,
reference_customer_trx_line_id,
split_by,
line_set_id,
tp_context,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
fulfillment_method_code,
service_reference_type_code,
service_reference_line_id,
service_reference_system_id,
ordered_item_id,
service_number,
service_duration,
service_start_date,
re_source_flag,
flow_status_code,
service_end_date,
service_coterminate_flag,
shippable_flag,
order_source_id,
orig_sys_shipment_ref,
change_sequence,
drop_ship_flag,
customer_line_number,
customer_shipment_number,
customer_item_net_price,
customer_payment_term_id,
first_ack_date,
first_ack_code,
last_ack_code,
last_ack_date,
planning_priority,
service_txn_comments,
service_period,
unit_selling_percent,
unit_list_percent,
unit_percent_base_price,
model_remnant_flag,
service_txn_reason_code,
calculate_price_flag,
revenue_amount
)
values
(
g_line_id, /* LINE_ID */
g_line_rec.org_id, /* ORG_ID */
g_line_rec.header_id, /* HEADER_ID */
g_line_rec.line_type_id, /* LINE_TYPE_ID, */
g_last_line_number, /* LINE_NUMBER */
null, /* ordered_item, */
g_line_rec.date_requested_current, /* REQUEST_DATE */
g_line_rec.promise_date, /* PROMISE_DATE */
g_line_rec.schedule_date, /* SCHEDULE_SHIP_DATE */
nvl(r_uom_code,g_line_rec.unit_code), /* ORDER_QUANTITY_UOM */
g_line_rec.ordered_quantity, /* PRICING_QUANTITY */
nvl(r_uom_code,g_line_rec.unit_code), /* PRICING_QUANTITY_UOM */
decode(G_ORD_CANC_FLAG,'Y',G_canc_rec.can_cancelled_quantity,
decode(g_hdr_canc_flag,'Y', g_line_rec.cancelled_quantity,
decode(g_line_id_Change_flag,'Y', g_line_rec.cancelled_quantity,0))),
/* CANCELLED_QUANTITY */
g_line_rec.shipped_quantity, /* SHIPPED_QUANTITY */
decode(g_hdr_canc_flag,'Y',0,
nvl(g_line_rec.ordered_quantity,0)), /* ORDERED_QUANTITY */
g_line_rec.fulfilled_quantity, /* FULFILLED_QUANTITY */
g_line_rec.shipped_quantity, /* SHIPPING_QUANTITY */
nvl(r_uom_code,
g_line_rec.shipping_quantity_uom), /* SHIPPING_QUANTITY_UOM */
null, /* DELIVERY_LEAD_TIME */
G_tax_exempt_flag, /* TAX_EXEMPT_FLAG */
g_line_rec.tax_exempt_number, /* TAX_EXEMPT_NUMBER */
g_line_rec.tax_exempt_reason_code, /* TAX_EXEMPT_REASON_CODE */
g_line_rec.warehouse_id, /* SHIP_FROM_ORG_ID */
g_line_rec.ship_to_site_use_id, /* SHIP_TO_ORG_ID */
g_invoice_to_site_use_id, /* INVOICE_TO_ORG_ID */
null, /* DELIVER_TO_ORG_ID */
g_line_rec.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
null, /* DELIVER_TO_CONTACT_ID */
null, /* INVOICE_TO_CONTACT_ID */
G_customer_id, /* SOLD_TO_ORG_ID */
G_Purchase_Order_Num, /* CUST_PO_NUMBER */
null, /* SHIP_TOLERANCE_ABOVE */
null, /* SHIP_TOLERANCE_BELOW */
decode(G_AUTO_FLAG,'Y','DAY',NULL), /* DEMAND_BUCKET_TYPE_CODE */
decode(G_AUTO_FLAG,'Y',-1,NULL), /* VEH_CUS_ITEM_CUM_KEY_ID */
g_line_rec.rla_schedule_type_code, /* RLA_SCHEDULE_TYPE_CODE */
g_line_rec.customer_dock_code, /* CUSTOMER_DOCK_CODE */
g_line_rec.customer_job, /* CUSTOMER_JOB */
g_line_rec.customer_production_line, /* CUSTOMER_PRODUCTION_LINE */
g_line_rec.customer_model_serial_number, /* CUST_MODEL_SERIAL_NUMBER */
g_line_rec.project_id, /* PROJECT_ID */
g_line_rec.task_id, /* TASK_ID */
nvl(r_inventory_item_id,g_line_rec.inventory_item_id), /* INVENTORY_ITEM_ID */
null, /* TAX_DATE */
g_line_rec.tax_code, /* TAX_CODE */
null, /* TAX_RATE */
g_line_rec.demand_class_code, /* DEMAND_CLASS_CODE */
g_line_rec.price_list_id, /* PRICE_LIST_ID */
null, /* PRICING_DATE */
r_shipment_number, /* SHIPMENT_NUMBER */
g_line_rec.agreement_id, /* AGREEMENT_ID */
g_line_rec.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
null, /* SHIPPPING_METHOD_CODE */
g_line_rec.ship_method_code, /* FREIGHT_CARRIER_CODE */
G_freight_terms_code, /* FREIGHT_TERMS_CODE */
null, /* FOB_POINT_CODE */
'INVOICE', /* TAX_POINT_CODE */
G_terms_id, /* PAYMENT_TERM_ID */
nvl(g_line_rec.invoicing_rule_id,0), /* INVOICING_RULE_ID */
nvl(g_line_rec.accounting_rule_id,0), /* ACCOUNTING_RULE_ID */
g_line_rec.source_document_type_id, /* SOURCE_DOCUMENT_TYPE_ID */
G_ORIG_SYS_DOCUMENT_REF, /* ORIG_SYS_DOCUMENT_REF */
g_line_rec.source_document_id, /* SOURCE_DOCUMENT_ID */
g_line_rec.original_system_line_reference, /* ORIG_SYS_LINE_REFERENCE */
g_line_rec.source_document_line_id, /* SOURCE_DOCUMENT_LINE_ID */
v_reference_line_id, /* REFERENCE_LINE_ID */
g_line_rec.return_reference_type_code, /* REFERENCE_TYPE */
v_reference_header_id, /* REFERENCE_HEADER_ID */
null, /* ITEM_REVISION */
g_line_rec.selling_price, /* SELLING_PRICE */
g_line_rec.list_price, /* LIST_PRICE */
null, /* TAX_VALUE */
g_line_rec.context, /* CONTEXT */
g_line_rec.attribute1, /* ATTRIBUTE1 */
g_line_rec.attribute2, /* ATTRIBUTE2 */
g_line_rec.attribute3, /* ATTRIBUTE3 */
g_line_rec.attribute4, /* ATTRIBUTE4 */
g_line_rec.attribute5, /* ATTRIBUTE5 */
g_line_rec.attribute6, /* ATTRIBUTE6 */
g_line_rec.attribute7, /* ATTRIBUTE7 */
g_line_rec.attribute8, /* ATTRIBUTE8 */
g_line_rec.attribute9, /* ATTRIBUTE9 */
g_line_rec.attribute10, /* ATTRIBUTE10 */
g_line_rec.attribute11, /* ATTRIBUTE11 */
g_line_rec.attribute12, /* ATTRIBUTE12 */
g_line_rec.attribute13, /* ATTRIBUTE13 */
g_line_rec.attribute14, /* ATTRIBUTE14 */
g_line_rec.attribute15, /* ATTRIBUTE15 */
g_line_rec.global_attribute_category, /* GLOBAL_ATTRIBUTE_CATEGORY */
g_line_rec.global_attribute1, /* GLOBAL_ATTRIBUTE1 */
g_line_rec.global_attribute2, /* GLOBAL_ATTRIBUTE2 */
g_line_rec.global_attribute3, /* GLOBAL_ATTRIBUTE3 */
g_line_rec.global_attribute4, /* GLOBAL_ATTRIBUTE4 */
g_line_rec.global_attribute5, /* GLOBAL_ATTRIBUTE5 */
g_line_rec.global_attribute6, /* GLOBAL_ATTRIBUTE6 */
g_line_rec.global_attribute7, /* GLOBAL_ATTRIBUTE7 */
g_line_rec.global_attribute8, /* GLOBAL_ATTRIBUTE8 */
g_line_rec.global_attribute9, /* GLOBAL_ATTRIBUTE9 */
g_line_rec.global_attribute10, /* GLOBAL_ATTRIBUTE10 */
g_line_rec.global_attribute11, /* GLOBAL_ATTRIBUTE11 */
g_line_rec.global_attribute12, /* GLOBAL_ATTRIBUTE12 */
g_line_rec.global_attribute13, /* GLOBAL_ATTRIBUTE13 */
g_line_rec.global_attribute14, /* GLOBAL_ATTRIBUTE14 */
g_line_rec.global_attribute15, /* GLOBAL_ATTRIBUTE15 */
g_line_rec.global_attribute16, /* GLOBAL_ATTRIBUTE16 */
g_line_rec.global_attribute17, /* GLOBAL_ATTRIBUTE17 */
g_line_rec.global_attribute18, /* GLOBAL_ATTRIBUTE18 */
g_line_rec.global_attribute19, /* GLOBAL-ATTRIBUTE19 */
g_line_rec.global_attribute20, /* GLOBAL_ATTRIBUTE20 */
g_line_rec.pricing_context, /* PRICING_CONTEXT */
g_line_rec.pricing_attribute1, /* PRICING_ATTRIBUTE1 */
g_line_rec.pricing_attribute2, /* PRICING_ATTRIBUTE2 */
g_line_rec.pricing_attribute3, /* PRICING_ATTRIBUTE3 */
g_line_rec.pricing_attribute4, /* PRICING_ATTRIBUTE4 */
g_line_rec.pricing_attribute5, /* PRICING_ATTRIBUTE5 */
g_line_rec.pricing_attribute6, /* PRICING_ATTRIBUTE6 */
g_line_rec.pricing_attribute7, /* PRICING_ATTRIBUTE7 */
g_line_rec.pricing_attribute8, /* PRICING_ATTRIBUTE8 */
g_line_rec.pricing_attribute9, /* PRICING_ATTRIBUTE9 */
g_line_rec.pricing_attribute10, /* PRICING_ATTRIBUTE10*/
g_line_rec.industry_context, /* INDUSTRY_CONTEXT */
g_line_rec.industry_attribute1, /* INDUSTRY_ATTRIBUTE1 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute2), /* INDUSTRY_ATTRIBUTE2 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute3), /* INDUSTRY_ATTRIBUTE3 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute4), /* INDUSTRY_ATTRIBUTE4 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute5), /* INDUSTRY_ATTRIBUTE5 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute6), /* INDUSTRY_ATTRIBUTE6 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute7), /* INDUSTRY_ATTRIBUTE7 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute8), /* INDUSTRY_ATTRIBUTE8 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute9), /* INDUSTRY_ATTRIBUTE9 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute10), /* INDUSTRY_ATTRIBUTE10 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute11), /* INDUSTRY_ATTRIBUTE11 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute12), /* INDUSTRY_ATTRIBUTE12 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute13), /* INDUSTRY_ATTRIBUTE13 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute14), /* INDUSTRY_ATTRIBUTE14 */
decode(G_AUTO_FLAG,'Y',NULL,
g_line_rec.industry_attribute15), /* INDUSTRY_ATTRIBUTE15 */
NULL, /* INDUSTRY_ATTRIBUTE16 */
NULL, /* INDUSTRY_ATTRIBUTE17 */
NULL, /* INDUSTRY_ATTRIBUTE18 */
NULL, /* INDUSTRY_ATTRIBUTE19 */
NULL, /* INDUSTRY_ATTRIBUTE20 */
NULL, /* INDUSTRY_ATTRIBUTE21 */
NULL, /* INDUSTRY_ATTRIBUTE22 */
NULL, /* INDUSTRY_ATTRIBUTE23 */
NULL, /* INDUSTRY_ATTRIBUTE24 */
NULL, /* INDUSTRY_ATTRIBUTE25 */
NULL, /* INDUSTRY_ATTRIBUTE26 */
NULL, /* INDUSTRY_ATTRIBUTE27 */
NULL, /* INDUSTRY_ATTRIBUTE28 */
NULL, /* INDUSTRY_ATTRIBUTE29 */
NULL, /* INDUSTRY_ATTRIBUTE30 */
g_line_rec.creation_date, /* CREATION_DATE */
g_line_rec.created_by, /* CREATED_BY */
g_line_rec.last_update_date, /* LAST_UPDATE_DATE */
g_line_rec.last_updated_by, /* LAST_UPDATED_BY */
g_line_rec.last_update_login, /* LAST_UPDATE_LOGIN */
nvl(g_line_rec.program_application_id,0), /* PROGRAM_APPLICATION_ID */
g_line_rec.program_id, /* PROGRAM_ID */
g_line_rec.program_update_date, /* PROGRAM_UPDATE_DATE */
g_line_rec.request_id, /* REQUEST_ID */
g_line_rec.parent_line_id, /* CONFIGURATION_ID */
g_line_rec.link_to_line_id, /* LINK_TO_LINE_ID */
g_line_rec.component_sequence_id, /* COMPONENT_SEQUENCE_ID */
g_line_rec.component_code, /* COMPONENT_CODE */
null, /* CONFIG_DISPLAY_SEQUENCE */
null, /* SORT_ORDER, */
g_line_rec.item_type_code, /* ITEM_TYPE_CODE */
null, /* OPTION_NUMBER */
g_line_rec.option_flag, /* OPTION_FLAG, */
g_line_rec.dep_plan_required_flag, /* DEP_PLAN_REQUIRED_FLAG */
g_line_rec.visible_demand_flag, /* VISIBLE_DEMAND_FLAG */
g_line_rec.line_category_code, /* LINE_CATEGORY_CODE */
g_line_rec.actual_departure_date, /* ACTUAL_SHIPMENT_DATE */
decode(g_line_rec.line_type_code,'RETURN',
decode(g_line_rec.return_reference_type_code,
'INVOICE',g_line_rec.return_reference_id,NULL),
NULL), /* CUSTOMER_TRX_LINE_ID */
g_line_rec.return_reference_type_Code, /* RETURN_CONTEXT */
decode(g_line_rec.line_type_code,'RETURN',
decode(g_line_rec.return_reference_type_code,
'INVOICE',v_customer_trx_id,
v_reference_header_id),NULL), /* RETURN_ATTRIBUTE1 */
decode(g_line_rec.line_type_code,'RETURN',
decode(g_line_rec.return_reference_type_code,
'INVOICE',g_line_rec.return_reference_id,
v_reference_line_id),NULL), /* RETURN_ATTRIBUTE2 */
null, /* RETURN_ATTRIBUTE3 */
null, /* RETURN_ATTRIBUTE4 */
null, /* RETURN_ATTRIBUTE5 */
null, /* RETURN_ATTRIBUTE6 */
null, /* RETURN_ATTRIBUTE7 */
null, /* RETURN_ATTRIBUTE8 */
null, /* RETURN_ATTRIBUTE9 */
null, /* RETURN_ATTRIBUTE10 */
null, /* RETURN_ATTRIBUTE11 */
null, /* RETURN_ATTRIBUTE12 */
null, /* RETURN_ATTRIBUTE13 */
null, /* RETURN_ATTRIBUTE14 */
null, /* RETURN_ATTRIBUTE15 */
g_line_rec.intermediate_ship_to_id, /* intmed_ship_to_org_id, */
g_line_rec.ship_to_contact_id, /* intmed_ship_to_contact_id, */
null, /* actual_arrival_date, */
g_line_rec.ato_line_id, /* ATO_LINE_ID */
null, /* auto_selected_quantity, */
null, /* component_number, */
null, /* earliest_acceptable_date, */
g_line_rec.standard_component_freeze_date, /* explosion_date, */
g_line_rec.latest_acceptable_date, /* latest_acceptable_date, */
null, /* model_group_number, */
null, /* schedule_arrival_date, */
g_line_rec.ship_model_complete_flag, /* ship_model_complete_flag, */
g_line_rec.schedule_status_code, /* schedule_status_code, */
g_line_rec.transaction_reason_code, /* return_reason_code */
g_salesrep_id, /* salesrep_id */
null, /* split_from_line_id */
g_line_rec.planning_prod_seq_number, /* cust_production_seq_num */
decode(G_AUTO_FLAG,'Y','Y',NULL), /* authorized_to_ship_flag */
g_line_rec.invoice_interface_status_code, /* invoice_interface_status_code */
g_set_id, /* Ship_Set_Id */
null, /* Arrival_Set_Id */
g_canc_rec.can_cancel_comment, /* Hist_Comments */
'CANCELLATION', /* Hist_Type_Code */
g_canc_rec.can_cancel_code, /* Reason_Code */
g_canc_rec.can_cancelled_by, /* Hist_Created_By */
g_canc_rec.can_cancel_date, /* Hist_Creation_Date */
g_line_rec.source_type_code, /* Source_Type_Code */
g_line_rec.Booked_Flag, /* booked_Flag */
g_line_rec.fulfilled_flag, /* fulfilled_flag */
g_line_rec.org_id, /* sold_from_org_id, */
g_line_rec.top_model_line_id, /* top_model_line_id, */
g_line_rec.cancelled_flag, /* cancelled_flag, */
g_line_rec.open_flag, /* open_flag, */
null, /* over_ship_reason_code, */
null, /* over_ship_resolved_flag, */
decode(nvl(g_line_rec.customer_item_id,-1),
-1,'INT','CUST'), /* item_identifier_type, */
null, /* commitment_id, */
g_line_rec.shipping_interfaced_flag, /* shipping_interfaced_flag, */
g_line_rec.credit_invoice_line_id, /* credit_invoice_line_id, */
null, /* end_item_unit_number, */
null, /* mfg_component_sequence_id, */
null, /* config_header_id, */
null, /* config_rev_nbr, */
null, /* shipping_instructions, */
null, /* packing_instructions, */
g_line_rec.invoiced_quantity, /* invoiced_quantity, */
null, /* reference_customer_trx_line_id, */
null, /* split_by, */
null, /* line_set_id, */
g_line_rec.tp_context, /* tp_context */
g_line_rec.tp_attribute1, /* tp_attribute1 */
g_line_rec.tp_attribute2, /* tp_attribute2 */
g_line_rec.tp_attribute3, /* tp_attribute3 */
g_line_rec.tp_attribute4, /* tp_attribute4 */
g_line_rec.tp_attribute5, /* tp_attribute5 */
g_line_rec.tp_attribute6, /* tp_attribute6 */
g_line_rec.tp_attribute7, /* tp_attribute7 */
g_line_rec.tp_attribute8, /* tp_attribute8 */
g_line_rec.tp_attribute9, /* tp_attribute9 */
g_line_rec.tp_attribute10, /* tp_attribute10 */
g_line_rec.tp_attribute11, /* tp_attribute11 */
g_line_rec.tp_attribute12, /* tp_attribute12 */
g_line_rec.tp_attribute13, /* tp_attribute13 */
g_line_rec.tp_attribute14, /* tp_attribute14 */
g_line_rec.tp_attribute15, /* tp_attribute15 */
g_line_rec.fulfillment_method_code, /* fulfillment_method_code, */
g_line_rec.service_reference_type_code, /* service_reference_type_code, */
g_line_rec.service_reference_line_id, /* service_reference_line_id, */
g_line_rec.service_reference_system_id, /* service_reference_system_id, */
decode(nvl(g_line_rec.customer_item_id,-1),
-1,g_line_rec.inventory_item_id,
g_line_rec.customer_item_id), /* ordered_item_id */
g_line_rec.service_number, /* service_number, */
g_line_rec.service_duration, /* service_duration, */
g_line_rec.service_start_date, /* service_start_date, */
g_line_rec.re_source_flag, /* re_source_flag, */
g_line_rec.flow_status_code, /* flow_status_code, */
g_line_rec.service_end_date, /* service_end_date, */
g_line_rec.service_coterminate_flag, /* service_coterminate_flag, */
g_line_rec.shippable_flag, /* shippable_flag, */
nvl(G_ORDER_SOURCE_ID,0), /* order_source_id, */
null, /* orig_sys_shipment_ref, */
null, /* change_sequence, */
null, /* drop_ship_flag, */
null, /* customer_line_number, */
null, /* customer_shipment_number, */
null, /* customer_item_net_price, */
null, /* customer_payment_term_id, */
null, /* first_ack_date, */
null, /* first_ack_code, */
null, /* last_ack_code, */
null, /* last_ack_date, */
g_line_rec.planning_priority, /* planning_priority, */
g_line_rec.service_txn_comments, /* service_txn_comments, */
g_line_rec.service_period, /* service_period, */
g_line_rec.selling_percent, /* unit_selling_percent, */
g_line_rec.list_percent, /* unit_list_percent, */
g_line_rec.percent_base_price, /* unit_percent_base_price, */
null, /* model_remnant_flag, */
g_line_rec.service_txn_reason_code, /* service_txn_reason_code, */
g_line_rec.calculate_price_flag, /* calculate_price_flag, */
g_line_rec.revenue_amount /* revenue_amount, */
);
End Upgrade_Insert_Lines_History;
Procedure Upgrade_Insert_Upgrade_log
is
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
insert into oe_upgrade_log
(
header_id,
old_line_id,
old_line_detail_id,
new_line_id,
picking_line_id,
new_line_number,
mtl_sales_order_id,
return_qty_available,
comments,
creation_date,
delivery
)
values
(
g_log_rec.header_id, /* HEADER_ID */
g_log_rec.old_line_id, /* OLD_LINE_ID */
g_log_rec.old_line_detail_id, /* OLD_LINE_DETAIL_ID */
g_log_rec.new_line_id, /* NEW_LINE_ID */
g_log_rec.picking_line_id, /* PICKING_LINE_ID */
g_log_rec.new_line_number, /* NEW_LINE_NUMBER */
g_log_rec.mtl_sales_order_id, /* MTL_SALES_ORDER_ID */
g_log_rec.return_qty_available, /* RETURN_QTY_AVAILABLE */
g_log_rec.comments, /* COMMENTS */
sysdate, /* CREATION_DATE */
g_log_rec.delivery /* DELIVERY */
);
End Upgrade_Insert_Upgrade_log;
select
sha.header_id
from
so_headers_all sha
where sha.upgrade_flag = 'N'
order by sha.header_id;
delete oe_upgrade_distribution;
select
count(*),
nvl(min(sha.header_id),0),
nvl(max(sha.header_id),0)
into
v_total_headers,
v_min_header,
v_max_header
from
so_headers_all sha
where sha.upgrade_flag = 'N';
OE_UPG_SO.Upgrade_Insert_Distbn_Record
(
L_slab => 1,
L_start_header_id => v_min_header,
L_end_Header_id => v_max_header,
L_type_var => v_type
);
OE_UPG_SO.Upgrade_Insert_Distbn_Record
(
L_slab => v_slab_count,
L_start_header_id => v_min_header,
L_end_Header_id => v_max_header,
L_type_var => v_type
);
OE_UPG_SO.Upgrade_Insert_Distbn_Record
(
L_slab => v_slab_count,
L_start_header_id => v_min_header,
L_end_Header_id => v_max_header ,
L_type_var => v_type
);
Procedure Upgrade_Insert_Distbn_Record
(
L_slab IN Varchar2,
L_start_Header_id IN Number,
L_end_Header_Id IN Number,
L_type_var IN Varchar2
)
is
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
insert into oe_upgrade_distribution
(
slab,
start_header_id,
end_header_id,
alloted_flag,
line_type,
creation_date
)
values
(
L_slab,
L_start_Header_id,
L_end_Header_id,
'N',
L_type_var,
sysdate
);
End Upgrade_Insert_Distbn_Record;
Procedure Upgrade_Insert_Errors
(
L_header_id IN Varchar2,
L_comments IN varchar2
)
is
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
insert into oe_upgrade_errors
(
header_id,
comments,
creation_date
)
values
(
l_header_id,
l_comments,
sysdate
);
End Upgrade_Insert_Errors;
select
header_id,
line_id,
line_number,
inventory_item_id,
unit_code
from
so_lines_all
where line_type_code = 'PARENT'
and item_type_code in ('KIT','MODEL','STANDARD')
and parent_line_id is null -- To filter out option lines
and parent_line_id is null -- To filter out option lines
and line_id in
(select shipment_schedule_line_id from so_lines_all
where line_id in
(select old_line_id from oe_upgrade_log_v
where new_line_id in
(select line_id from oe_order_lines_all
where line_set_id is null)))
order by header_id, line_id;
select
line_id,
line_number
from oe_order_lines_all oola
where line_id in
(select new_line_id from oe_upgrade_log oul, so_lines_all sla
where oul.old_line_id = sla.line_id
and sla.shipment_schedule_line_id = v_line_id)
and item_type_code in ('KIT','MODEL','STANDARD')
order by line_id;
select
header_id,
line_id,
line_number,
inventory_item_id,
unit_code
from
so_lines_all sla
where sla.line_type_code = 'REGULAR'
AND item_type_code in ('KIT','MODEL','STANDARD')
and parent_line_id is null -- To filter out options (included on Leena's instn.)
and parent_line_id is null -- To filter out options (included on Leena's instn.)
and sla.line_id in
(select lg.old_line_id
from oe_upgrade_log_v lg, oe_order_lines_all ln
where lg.new_line_id = ln.line_id
and ln.item_type_code not in ('INCLUDED','CONFIG')
group by lg.old_line_id
having count(*) > 1);
select
new_line_id line_id,
new_line_number line_number
from oe_upgrade_log
where old_line_id = v_line_id
and old_line_id is not null;
select oe_sets_s.nextval into v_set_id from dual;
insert into oe_sets
(
SET_ID,
SET_NAME,
SET_TYPE,
HEADER_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
SCHEDULE_SHIP_DATE,
SCHEDULE_ARRIVAL_DATE,
FREIGHT_CARRIER_CODE,
SHIPPING_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
SET_STATUS,
CREATED_BY,
CREATION_DATE,
UPDATED_BY,
UPDATE_DATE,
UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORDERED_QUANTITY_UOM,
LINE_TYPE_ID,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW
)
values
(
v_set_id, /* SET_ID, */
to_char(v_set_id), /* SET_NAME, */
'LINE_SET', /* SET_TYPE, */
c2.header_id, /* HEADER_ID,*/
null, /* SHIP_FROM_ORG_ID, */
null, /* SHIP_TO_ORG_ID, */
null, /* SCHEDULE_SHIP_DATE, */
null, /* SCHEDULE_ARRIVAL_DATE, */
null, /* FREIGHT_CARRIER_CODE, */
null, /* SHIPPING_METHOD_CODE, */
null, /* SHIPMENT_PRIORITY_CODE, */
null, /* SET_STATUS, */
0, /* CREATED_BY, */
sysdate, /* CREATION_DATE, */
0, /* UPDATED_BY, */
sysdate, /* UPDATE_DATE, */
0, /* UPDATE_LOGIN, */
c2.inventory_item_id, /* INVENTORY_ITEM_ID, */
c2.unit_code, /* ORDERED_QUANTITY_UOM, */
null, /* LINE_TYPE_ID, */
null, /* SHIP_TOLERANCE_ABOVE, */
null /* SHIP_TOLERANCE_BELOW */
);
update oe_order_lines_all
set line_set_id = v_set_id,
line_number = p_line_number,
shipment_number = v_shipment_number
where line_id = c4.line_id;
select oe_sets_s.nextval into v_set_id from dual;
insert into oe_sets
(
SET_ID,
SET_NAME,
SET_TYPE,
HEADER_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
SCHEDULE_SHIP_DATE,
SCHEDULE_ARRIVAL_DATE,
FREIGHT_CARRIER_CODE,
SHIPPING_METHOD_CODE,
SHIPMENT_PRIORITY_CODE,
SET_STATUS,
CREATED_BY,
CREATION_DATE,
UPDATED_BY,
UPDATE_DATE,
UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORDERED_QUANTITY_UOM,
LINE_TYPE_ID,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW
)
values
(
v_set_id, /* SET_ID, */
to_char(v_set_id), /* SET_NAME, */
'LINE_SET', /* SET_TYPE, */
c6.header_id, /* HEADER_ID,*/
null, /* SHIP_FROM_ORG_ID, */
null, /* SHIP_TO_ORG_ID, */
null, /* SCHEDULE_SHIP_DATE, */
null, /* SCHEDULE_ARRIVAL_DATE, */
null, /* FREIGHT_CARRIER_CODE, */
null, /* SHIPPING_METHOD_CODE, */
null, /* SHIPMENT_PRIORITY_CODE, */
null, /* SET_STATUS, */
0, /* CREATED_BY, */
sysdate, /* CREATION_DATE, */
0, /* UPDATED_BY, */
sysdate, /* UPDATE_DATE, */
0, /* UPDATE_LOGIN, */
c6.inventory_item_id, /* INVENTORY_ITEM_ID, */
c6.unit_code, /* ORDERED_QUANTITY_UOM, */
null, /* LINE_TYPE_ID, */
null, /* SHIP_TOLERANCE_ABOVE, */
null /* SHIP_TOLERANCE_BELOW */
);
select item_type_code into
v_item_type_code from
oe_order_lines_all
where
line_id = c8.line_id;
update oe_order_lines_all ooal
set line_set_id = v_set_id,
line_number = p_line_number,
shipment_number = v_shipment_number
where ooal.line_id = c8.line_id;
oe_upg_so.upgrade_insert_errors
(
L_header_id => 0,
L_comments => 'Line set updation failed on ora error: '||to_char(sqlcode)
);
select header_id, line_id from oe_order_lines_all
where item_type_code = 'SERVICE';
select oul2.new_line_id into v_new_line_id
from
oe_upgrade_log_v oul1,
oe_upgrade_log_v oul2,
so_lines_all sla1,
so_lines_all sla2
where oul1.new_line_id = c2.line_id
and oul1.old_line_id = sla1.line_id
and nvl(sla1.service_parent_line_id,0) = nvl(sla2.line_id,0)
and sla2.line_id = oul2.old_line_id
and nvl(oul1.old_line_detail_id,0) = nvl(oul2.old_line_detail_id,0)
and nvl(oul1.picking_line_id,0) = nvl(oul2.picking_line_id,0);
update oe_order_lines_all
set service_reference_line_id = v_new_line_id
where line_Id = c2.line_id;
oe_upg_so.Upgrade_Insert_Errors(
c2.header_id,
'FYI Only: Service Ref.Line id not updated in OM for Line '
||to_char(c2.line_id)||
'as it is not found in Order Entry table');
PROCEDURE Insert_Row
( p_line_rec IN OE_Order_PUB.Line_Rec_Type,
p_orig_line_id IN Number,
p_upgraded_flag IN Varchar2,
p_apply_price_adj IN Varchar2 default 'Y'
);
PROCEDURE insert_multiple_models IS
cursor multiple_cfg_detail(p_ato_line_id IN NUMBER) IS
select line_id,ordered_quantity,shipped_quantity
from oe_order_lines_all
where ato_line_id=p_ato_line_id
and item_type_code = 'CONFIG';
select ato_line_id
from oe_order_lines_all
group by ato_line_id,item_type_code
having item_type_code = 'CONFIG'
and count(*) > 1;
select line_id,upgraded_flag
from oe_order_lines_all
where ato_line_id=p_ato_line_id
and item_type_code <> 'CONFIG'
order by component_code;
select line_id,upgraded_flag
from oe_order_lines_all
where service_reference_line_id = p_service_reference_line_id;
SELECT oe_order_lines_s.nextval
INTO l_new_line_rec.line_id
FROM dual;
oe_debug_pub.add( 'INSERTING A MODEL LINE :' || L_NEW_LINE_REC.LINE_ID ) ;
INSERT_ROW(l_new_line_rec, l_line_id,l_upgraded_flag);
-- Update the config item to point to the new model.
UPDATE oe_order_lines_all
SET ato_line_id = l_model_rec.line_id,
top_model_line_id = l_model_rec.line_id
WHERE line_id=l_cfg_line_id;
UPDATE oe_order_lines_all
SET link_to_line_id = l_model_rec.line_id
WHERE line_id=l_cfg_line_id;
SELECT oe_order_lines_s.nextval
INTO l_service_line_rec.line_id
FROM dual;
INSERT_ROW(l_service_line_rec,l_service_line_id,l_s_upgraded_flag);
SELECT oe_order_lines_s.nextval
INTO l_new_line_rec.line_id
FROM dual;
INSERT_ROW(l_new_line_rec,l_line_id,l_upgraded_flag);
SELECT oe_order_lines_s.nextval
INTO l_service_line_rec.line_id
FROM dual;
INSERT_ROW(l_service_line_rec,l_service_line_id,l_s_upgraded_flag);
END insert_multiple_models;
SELECT ACCOUNTING_RULE_ID
, ACTUAL_ARRIVAL_DATE
, ACTUAL_SHIPMENT_DATE
, AGREEMENT_ID
, ARRIVAL_SET_ID
, ATO_LINE_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTO_SELECTED_QUANTITY
, AUTHORIZED_TO_SHIP_FLAG
, BOOKED_FLAG
, CANCELLED_FLAG
, CANCELLED_QUANTITY
, COMPONENT_CODE
, COMPONENT_NUMBER
, COMPONENT_SEQUENCE_ID
, CONFIG_HEADER_ID
, CONFIG_REV_NBR
, CONFIG_DISPLAY_SEQUENCE
, CONFIGURATION_ID
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CREDIT_INVOICE_LINE_ID
, CUSTOMER_DOCK_CODE
, CUSTOMER_JOB
, CUSTOMER_PRODUCTION_LINE
, CUST_PRODUCTION_SEQ_NUM
, CUSTOMER_TRX_LINE_ID
, CUST_MODEL_SERIAL_NUMBER
, CUST_PO_NUMBER
, DELIVERY_LEAD_TIME
, DELIVER_TO_CONTACT_ID
, DELIVER_TO_ORG_ID
, DEMAND_BUCKET_TYPE_CODE
, DEMAND_CLASS_CODE
, DEP_PLAN_REQUIRED_FLAG
, EARLIEST_ACCEPTABLE_DATE
, END_ITEM_UNIT_NUMBER
, EXPLOSION_DATE
, FIRST_ACK_CODE
, FIRST_ACK_DATE
, FOB_POINT_CODE
, FREIGHT_CARRIER_CODE
, FREIGHT_TERMS_CODE
, FULFILLED_QUANTITY
, FULFILLED_FLAG
, FULFILLMENT_METHOD_CODE
, FULFILLMENT_DATE
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE_CATEGORY
, HEADER_ID
, INDUSTRY_ATTRIBUTE1
, INDUSTRY_ATTRIBUTE10
, INDUSTRY_ATTRIBUTE11
, INDUSTRY_ATTRIBUTE12
, INDUSTRY_ATTRIBUTE13
, INDUSTRY_ATTRIBUTE14
, INDUSTRY_ATTRIBUTE15
, INDUSTRY_ATTRIBUTE16
, INDUSTRY_ATTRIBUTE17
, INDUSTRY_ATTRIBUTE18
, INDUSTRY_ATTRIBUTE19
, INDUSTRY_ATTRIBUTE20
, INDUSTRY_ATTRIBUTE21
, INDUSTRY_ATTRIBUTE22
, INDUSTRY_ATTRIBUTE23
, INDUSTRY_ATTRIBUTE24
, INDUSTRY_ATTRIBUTE25
, INDUSTRY_ATTRIBUTE26
, INDUSTRY_ATTRIBUTE27
, INDUSTRY_ATTRIBUTE28
, INDUSTRY_ATTRIBUTE29
, INDUSTRY_ATTRIBUTE30
, INDUSTRY_ATTRIBUTE2
, INDUSTRY_ATTRIBUTE3
, INDUSTRY_ATTRIBUTE4
, INDUSTRY_ATTRIBUTE5
, INDUSTRY_ATTRIBUTE6
, INDUSTRY_ATTRIBUTE7
, INDUSTRY_ATTRIBUTE8
, INDUSTRY_ATTRIBUTE9
, INDUSTRY_CONTEXT
, INTMED_SHIP_TO_CONTACT_ID
, INTMED_SHIP_TO_ORG_ID
, INVENTORY_ITEM_ID
, INVOICE_INTERFACE_STATUS_CODE
, INVOICE_TO_CONTACT_ID
, INVOICE_TO_ORG_ID
, INVOICED_QUANTITY
, INVOICING_RULE_ID
, ORDERED_ITEM_ID
, ITEM_IDENTIFIER_TYPE
, ORDERED_ITEM
, ITEM_REVISION
, ITEM_TYPE_CODE
, LAST_ACK_CODE
, LAST_ACK_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LATEST_ACCEPTABLE_DATE
, LINE_CATEGORY_CODE
, LINE_ID
, LINE_NUMBER
, LINE_TYPE_ID
, LINK_TO_LINE_ID
, MODEL_GROUP_NUMBER
-- , MFG_COMPONENT_SEQUENCE_ID
, OPEN_FLAG
, OPTION_FLAG
, OPTION_NUMBER
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, ORG_ID
, ORIG_SYS_DOCUMENT_REF
, ORIG_SYS_LINE_REF
, OVER_SHIP_REASON_CODE
, OVER_SHIP_RESOLVED_FLAG
, PAYMENT_TERM_ID
, PLANNING_PRIORITY
, PRICE_LIST_ID
, PRICING_ATTRIBUTE1
, PRICING_ATTRIBUTE10
, PRICING_ATTRIBUTE2
, PRICING_ATTRIBUTE3
, PRICING_ATTRIBUTE4
, PRICING_ATTRIBUTE5
, PRICING_ATTRIBUTE6
, PRICING_ATTRIBUTE7
, PRICING_ATTRIBUTE8
, PRICING_ATTRIBUTE9
, PRICING_CONTEXT
, PRICING_DATE
, PRICING_QUANTITY
, PRICING_QUANTITY_UOM
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PROJECT_ID
, PROMISE_DATE
, RE_SOURCE_FLAG
, REFERENCE_CUSTOMER_TRX_LINE_ID
, REFERENCE_HEADER_ID
, REFERENCE_LINE_ID
, REFERENCE_TYPE
, REQUEST_DATE
, REQUEST_ID
, RETURN_ATTRIBUTE1
, RETURN_ATTRIBUTE10
, RETURN_ATTRIBUTE11
, RETURN_ATTRIBUTE12
, RETURN_ATTRIBUTE13
, RETURN_ATTRIBUTE14
, RETURN_ATTRIBUTE15
, RETURN_ATTRIBUTE2
, RETURN_ATTRIBUTE3
, RETURN_ATTRIBUTE4
, RETURN_ATTRIBUTE5
, RETURN_ATTRIBUTE6
, RETURN_ATTRIBUTE7
, RETURN_ATTRIBUTE8
, RETURN_ATTRIBUTE9
, RETURN_CONTEXT
, RETURN_REASON_CODE
, RLA_SCHEDULE_TYPE_CODE
, SALESREP_ID
, SCHEDULE_ARRIVAL_DATE
, SCHEDULE_SHIP_DATE
, SCHEDULE_STATUS_CODE
, SHIPMENT_NUMBER
, SHIPMENT_PRIORITY_CODE
, SHIPPED_QUANTITY
, SHIPPING_METHOD_CODE
, SHIPPING_QUANTITY
, SHIPPING_QUANTITY_UOM
, SHIP_FROM_ORG_ID
, SHIP_SET_ID
, SHIP_TOLERANCE_ABOVE
, SHIP_TOLERANCE_BELOW
, SHIPPABLE_FLAG
, SHIPPING_INTERFACED_FLAG
, SHIP_TO_CONTACT_ID
, SHIP_TO_ORG_ID
, SHIP_MODEL_COMPLETE_FLAG
, SOLD_TO_ORG_ID
, SOLD_FROM_ORG_ID
, SORT_ORDER
, SOURCE_DOCUMENT_ID
, SOURCE_DOCUMENT_LINE_ID
, SOURCE_DOCUMENT_TYPE_ID
, SOURCE_TYPE_CODE
, SPLIT_FROM_LINE_ID
, LINE_SET_ID
, SPLIT_BY
, MODEL_REMNANT_FLAG
, TASK_ID
, TAX_CODE
, TAX_DATE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, TAX_POINT_CODE
, TAX_RATE
, TAX_VALUE
, TOP_MODEL_LINE_ID
, UNIT_LIST_PRICE
, UNIT_SELLING_PRICE
, VISIBLE_DEMAND_FLAG
, VEH_CUS_ITEM_CUM_KEY_ID
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, SERVICE_TXN_REASON_CODE
, SERVICE_TXN_COMMENTS
, SERVICE_DURATION
, SERVICE_PERIOD
, SERVICE_START_DATE
, SERVICE_END_DATE
, SERVICE_COTERMINATE_FLAG
, UNIT_LIST_PERCENT
, UNIT_SELLING_PERCENT
, UNIT_PERCENT_BASE_PRICE
, SERVICE_NUMBER
, SERVICE_REFERENCE_TYPE_CODE
, SERVICE_REFERENCE_LINE_ID
, SERVICE_REFERENCE_SYSTEM_ID
, TP_CONTEXT
, TP_ATTRIBUTE1
, TP_ATTRIBUTE2
, TP_ATTRIBUTE3
, TP_ATTRIBUTE4
, TP_ATTRIBUTE5
, TP_ATTRIBUTE6
, TP_ATTRIBUTE7
, TP_ATTRIBUTE8
, TP_ATTRIBUTE9
, TP_ATTRIBUTE10
, TP_ATTRIBUTE11
, TP_ATTRIBUTE12
, TP_ATTRIBUTE13
, TP_ATTRIBUTE14
, TP_ATTRIBUTE15
, FLOW_STATUS_CODE
, MARKETING_SOURCE_CODE_ID
, CALCULATE_PRICE_FLAG
, COMMITMENT_ID
, ORDER_SOURCE_ID -- aksingh
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = p_line_id ;
l_line_rec.auto_selected_quantity := l_implicit_rec.AUTO_SELECTED_QUANTITY;
l_line_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_line_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_line_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_line_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
PROCEDURE Insert_Row
( p_line_rec IN OE_Order_PUB.Line_Rec_Type,
p_orig_line_id IN Number,
p_upgraded_flag IN Varchar2,
p_apply_price_adj IN Varchar2 default 'Y'
)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
select max(old_line_id) into G_OLD_LINE_ID from oe_upgrade_log_v
where new_line_id = p_orig_line_id;
INSERT INTO OE_ORDER_LINES_ALL
( ORG_ID
, ACCOUNTING_RULE_ID
, ACTUAL_ARRIVAL_DATE
, ACTUAL_SHIPMENT_DATE
, AGREEMENT_ID
, ARRIVAL_SET_ID
, ATO_LINE_ID
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, AUTO_SELECTED_QUANTITY
, AUTHORIZED_TO_SHIP_FLAG
, BOOKED_FLAG
, CANCELLED_FLAG
, CANCELLED_QUANTITY
, COMPONENT_CODE
, COMPONENT_NUMBER
, COMPONENT_SEQUENCE_ID
, CONFIG_HEADER_ID
, CONFIG_REV_NBR
, CONFIG_DISPLAY_SEQUENCE
, CONFIGURATION_ID
, CONTEXT
, CREATED_BY
, CREATION_DATE
, CREDIT_INVOICE_LINE_ID
, CUSTOMER_LINE_NUMBER
, CUSTOMER_SHIPMENT_NUMBER
, CUSTOMER_ITEM_NET_PRICE
, CUSTOMER_PAYMENT_TERM_ID
, CUSTOMER_DOCK_CODE
, CUSTOMER_JOB
, CUSTOMER_PRODUCTION_LINE
, CUST_PRODUCTION_SEQ_NUM
, CUSTOMER_TRX_LINE_ID
, CUST_MODEL_SERIAL_NUMBER
, CUST_PO_NUMBER
, DELIVERY_LEAD_TIME
, DELIVER_TO_CONTACT_ID
, DELIVER_TO_ORG_ID
, DEMAND_BUCKET_TYPE_CODE
, DEMAND_CLASS_CODE
, DEP_PLAN_REQUIRED_FLAG
--, DROP_SHIP_FLAG
, EARLIEST_ACCEPTABLE_DATE
, END_ITEM_UNIT_NUMBER
, EXPLOSION_DATE
, FIRST_ACK_CODE
, FIRST_ACK_DATE
, FOB_POINT_CODE
, FREIGHT_CARRIER_CODE
, FREIGHT_TERMS_CODE
, FULFILLED_QUANTITY
, FULFILLED_FLAG
, FULFILLMENT_METHOD_CODE
, FULFILLMENT_DATE
, GLOBAL_ATTRIBUTE1
, GLOBAL_ATTRIBUTE10
, GLOBAL_ATTRIBUTE11
, GLOBAL_ATTRIBUTE12
, GLOBAL_ATTRIBUTE13
, GLOBAL_ATTRIBUTE14
, GLOBAL_ATTRIBUTE15
, GLOBAL_ATTRIBUTE16
, GLOBAL_ATTRIBUTE17
, GLOBAL_ATTRIBUTE18
, GLOBAL_ATTRIBUTE19
, GLOBAL_ATTRIBUTE2
, GLOBAL_ATTRIBUTE20
, GLOBAL_ATTRIBUTE3
, GLOBAL_ATTRIBUTE4
, GLOBAL_ATTRIBUTE5
, GLOBAL_ATTRIBUTE6
, GLOBAL_ATTRIBUTE7
, GLOBAL_ATTRIBUTE8
, GLOBAL_ATTRIBUTE9
, GLOBAL_ATTRIBUTE_CATEGORY
, HEADER_ID
, INDUSTRY_ATTRIBUTE1
, INDUSTRY_ATTRIBUTE10
, INDUSTRY_ATTRIBUTE11
, INDUSTRY_ATTRIBUTE12
, INDUSTRY_ATTRIBUTE13
, INDUSTRY_ATTRIBUTE14
, INDUSTRY_ATTRIBUTE15
, INDUSTRY_ATTRIBUTE16
, INDUSTRY_ATTRIBUTE17
, INDUSTRY_ATTRIBUTE18
, INDUSTRY_ATTRIBUTE19
, INDUSTRY_ATTRIBUTE20
, INDUSTRY_ATTRIBUTE21
, INDUSTRY_ATTRIBUTE22
, INDUSTRY_ATTRIBUTE23
, INDUSTRY_ATTRIBUTE24
, INDUSTRY_ATTRIBUTE25
, INDUSTRY_ATTRIBUTE26
, INDUSTRY_ATTRIBUTE27
, INDUSTRY_ATTRIBUTE28
, INDUSTRY_ATTRIBUTE29
, INDUSTRY_ATTRIBUTE30
, INDUSTRY_ATTRIBUTE2
, INDUSTRY_ATTRIBUTE3
, INDUSTRY_ATTRIBUTE4
, INDUSTRY_ATTRIBUTE5
, INDUSTRY_ATTRIBUTE6
, INDUSTRY_ATTRIBUTE7
, INDUSTRY_ATTRIBUTE8
, INDUSTRY_ATTRIBUTE9
, INDUSTRY_CONTEXT
, INTMED_SHIP_TO_CONTACT_ID
, INTMED_SHIP_TO_ORG_ID
, INVENTORY_ITEM_ID
, INVOICE_INTERFACE_STATUS_CODE
, INVOICE_TO_CONTACT_ID
, INVOICE_TO_ORG_ID
, INVOICED_QUANTITY
, INVOICING_RULE_ID
, ORDERED_ITEM_ID
, ITEM_IDENTIFIER_TYPE
, ORDERED_ITEM
, ITEM_REVISION
, ITEM_TYPE_CODE
, LAST_ACK_CODE
, LAST_ACK_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LATEST_ACCEPTABLE_DATE
, LINE_CATEGORY_CODE
, LINE_ID
, LINE_NUMBER
, LINE_TYPE_ID
, LINK_TO_LINE_ID
, MODEL_GROUP_NUMBER
-- , MFG_COMPONENT_SEQUENCE_ID
, OPEN_FLAG
, OPTION_FLAG
, OPTION_NUMBER
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
--, ORG_ID
, ORDER_SOURCE_ID
, ORIG_SYS_DOCUMENT_REF
, ORIG_SYS_LINE_REF
, ORIG_SYS_SHIPMENT_REF
, CHANGE_SEQUENCE
, OVER_SHIP_REASON_CODE
, OVER_SHIP_RESOLVED_FLAG
, PAYMENT_TERM_ID
, PLANNING_PRIORITY
, PRICE_LIST_ID
, PRICING_ATTRIBUTE1
, PRICING_ATTRIBUTE10
, PRICING_ATTRIBUTE2
, PRICING_ATTRIBUTE3
, PRICING_ATTRIBUTE4
, PRICING_ATTRIBUTE5
, PRICING_ATTRIBUTE6
, PRICING_ATTRIBUTE7
, PRICING_ATTRIBUTE8
, PRICING_ATTRIBUTE9
, PRICING_CONTEXT
, PRICING_DATE
, PRICING_QUANTITY
, PRICING_QUANTITY_UOM
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PROJECT_ID
, PROMISE_DATE
, RE_SOURCE_FLAG
, REFERENCE_CUSTOMER_TRX_LINE_ID
, REFERENCE_HEADER_ID
, REFERENCE_LINE_ID
, REFERENCE_TYPE
, REQUEST_DATE
, REQUEST_ID
, RETURN_ATTRIBUTE1
, RETURN_ATTRIBUTE10
, RETURN_ATTRIBUTE11
, RETURN_ATTRIBUTE12
, RETURN_ATTRIBUTE13
, RETURN_ATTRIBUTE14
, RETURN_ATTRIBUTE15
, RETURN_ATTRIBUTE2
, RETURN_ATTRIBUTE3
, RETURN_ATTRIBUTE4
, RETURN_ATTRIBUTE5
, RETURN_ATTRIBUTE6
, RETURN_ATTRIBUTE7
, RETURN_ATTRIBUTE8
, RETURN_ATTRIBUTE9
, RETURN_CONTEXT
, RETURN_REASON_CODE
, RLA_SCHEDULE_TYPE_CODE
, SALESREP_ID
, SCHEDULE_ARRIVAL_DATE
, SCHEDULE_SHIP_DATE
, SCHEDULE_STATUS_CODE
, SHIPMENT_NUMBER
, SHIPMENT_PRIORITY_CODE
, SHIPPED_QUANTITY
, SHIPPING_METHOD_CODE
, SHIPPING_QUANTITY
, SHIPPING_QUANTITY_UOM
, SHIP_FROM_ORG_ID
, SHIP_SET_ID
, SHIP_TOLERANCE_ABOVE
, SHIP_TOLERANCE_BELOW
, SHIPPABLE_FLAG
, SHIPPING_INTERFACED_FLAG
, SHIP_TO_CONTACT_ID
, SHIP_TO_ORG_ID
, SHIP_MODEL_COMPLETE_FLAG
, SOLD_TO_ORG_ID
, SOLD_FROM_ORG_ID
, SORT_ORDER
, SOURCE_DOCUMENT_ID
, SOURCE_DOCUMENT_LINE_ID
, SOURCE_DOCUMENT_TYPE_ID
, SOURCE_TYPE_CODE
, SPLIT_FROM_LINE_ID
, LINE_SET_ID
, SPLIT_BY
, model_remnant_flag
, TASK_ID
, TAX_CODE
, TAX_DATE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, TAX_POINT_CODE
, TAX_RATE
, TAX_VALUE
, TOP_MODEL_LINE_ID
, UNIT_LIST_PRICE
, UNIT_SELLING_PRICE
, VISIBLE_DEMAND_FLAG
, VEH_CUS_ITEM_CUM_KEY_ID
, SHIPPING_INSTRUCTIONS
, PACKING_INSTRUCTIONS
, SERVICE_TXN_REASON_CODE
, SERVICE_TXN_COMMENTS
, SERVICE_DURATION
, SERVICE_PERIOD
, SERVICE_START_DATE
, SERVICE_END_DATE
, SERVICE_COTERMINATE_FLAG
, UNIT_LIST_PERCENT
, UNIT_SELLING_PERCENT
, UNIT_PERCENT_BASE_PRICE
, SERVICE_NUMBER
, SERVICE_REFERENCE_TYPE_CODE
, SERVICE_REFERENCE_LINE_ID
, SERVICE_REFERENCE_SYSTEM_ID
, TP_CONTEXT
, TP_ATTRIBUTE1
, TP_ATTRIBUTE2
, TP_ATTRIBUTE3
, TP_ATTRIBUTE4
, TP_ATTRIBUTE5
, TP_ATTRIBUTE6
, TP_ATTRIBUTE7
, TP_ATTRIBUTE8
, TP_ATTRIBUTE9
, TP_ATTRIBUTE10
, TP_ATTRIBUTE11
, TP_ATTRIBUTE12
, TP_ATTRIBUTE13
, TP_ATTRIBUTE14
, TP_ATTRIBUTE15
, FLOW_STATUS_CODE
, MARKETING_SOURCE_CODE_ID
, CALCULATE_PRICE_FLAG
, COMMITMENT_ID
, UPGRADED_FLAG
)
VALUES
( p_line_rec.org_id
, p_line_rec.accounting_rule_id
, p_line_rec.actual_arrival_date
, p_line_rec.actual_shipment_date
, p_line_rec.agreement_id
, p_line_rec.arrival_set_id
, p_line_rec.ato_line_id
, p_line_rec.attribute1
, p_line_rec.attribute10
, p_line_rec.attribute11
, p_line_rec.attribute12
, p_line_rec.attribute13
, p_line_rec.attribute14
, p_line_rec.attribute15
, p_line_rec.attribute2
, p_line_rec.attribute3
, p_line_rec.attribute4
, p_line_rec.attribute5
, p_line_rec.attribute6
, p_line_rec.attribute7
, p_line_rec.attribute8
, p_line_rec.attribute9
, p_line_rec.auto_selected_quantity
, p_line_rec.authorized_to_ship_flag
, p_line_rec.booked_flag
, p_line_rec.cancelled_flag
, p_line_rec.cancelled_quantity
, p_line_rec.component_code
, p_line_rec.component_number
, p_line_rec.component_sequence_id
, p_line_rec.config_header_id
, p_line_rec.config_rev_nbr
, p_line_rec.config_display_sequence
, p_line_rec.configuration_id
, p_line_rec.context
, p_line_rec.created_by
, p_line_rec.creation_date
, p_line_rec.credit_invoice_line_id
, p_line_rec.customer_line_number
, p_line_rec.customer_shipment_number
, p_line_rec.customer_item_net_price
, p_line_rec.customer_payment_term_id
, p_line_rec.customer_dock_code
, p_line_rec.customer_job
, p_line_rec.customer_production_line
, p_line_rec.cust_production_seq_num
, p_line_rec.customer_trx_line_id
, p_line_rec.cust_model_serial_number
, p_line_rec.cust_po_number
, p_line_rec.delivery_lead_time
, p_line_rec.deliver_to_contact_id
, p_line_rec.deliver_to_org_id
, p_line_rec.demand_bucket_type_code
, p_line_rec.demand_class_code
, p_line_rec.dep_plan_required_flag
--, p_line_rec.drop_ship_flag
, p_line_rec.earliest_acceptable_date
, p_line_rec.end_item_unit_number
, p_line_rec.explosion_date
, p_line_rec.first_ack_code
, p_line_rec.first_ack_date
, p_line_rec.fob_point_code
, p_line_rec.freight_carrier_code
, p_line_rec.freight_terms_code
, p_line_rec.fulfilled_quantity
, p_line_rec.fulfilled_flag
, p_line_rec.fulfillment_method_code
, p_line_rec.fulfillment_date
, p_line_rec.global_attribute1
, p_line_rec.global_attribute10
, p_line_rec.global_attribute11
, p_line_rec.global_attribute12
, p_line_rec.global_attribute13
, p_line_rec.global_attribute14
, p_line_rec.global_attribute15
, p_line_rec.global_attribute16
, p_line_rec.global_attribute17
, p_line_rec.global_attribute18
, p_line_rec.global_attribute19
, p_line_rec.global_attribute2
, p_line_rec.global_attribute20
, p_line_rec.global_attribute3
, p_line_rec.global_attribute4
, p_line_rec.global_attribute5
, p_line_rec.global_attribute6
, p_line_rec.global_attribute7
, p_line_rec.global_attribute8
, p_line_rec.global_attribute9
, p_line_rec.global_attribute_category
, p_line_rec.header_id
, p_line_rec.industry_attribute1
, p_line_rec.industry_attribute10
, p_line_rec.industry_attribute11
, p_line_rec.industry_attribute12
, p_line_rec.industry_attribute13
, p_line_rec.industry_attribute14
, p_line_rec.industry_attribute15
, p_line_rec.industry_attribute16
, p_line_rec.industry_attribute17
, p_line_rec.industry_attribute18
, p_line_rec.industry_attribute19
, p_line_rec.industry_attribute20
, p_line_rec.industry_attribute21
, p_line_rec.industry_attribute22
, p_line_rec.industry_attribute23
, p_line_rec.industry_attribute24
, p_line_rec.industry_attribute25
, p_line_rec.industry_attribute26
, p_line_rec.industry_attribute27
, p_line_rec.industry_attribute28
, p_line_rec.industry_attribute29
, p_line_rec.industry_attribute30
, p_line_rec.industry_attribute2
, p_line_rec.industry_attribute3
, p_line_rec.industry_attribute4
, p_line_rec.industry_attribute5
, p_line_rec.industry_attribute6
, p_line_rec.industry_attribute7
, p_line_rec.industry_attribute8
, p_line_rec.industry_attribute9
, p_line_rec.industry_context
, p_line_rec.intermed_ship_to_contact_id
, p_line_rec.intermed_ship_to_org_id
, p_line_rec.inventory_item_id
, p_line_rec.invoice_interface_status_code
, p_line_rec.invoice_to_contact_id
, p_line_rec.invoice_to_org_id
, p_line_rec.invoiced_quantity
, p_line_rec.invoicing_rule_id
, p_line_rec.ordered_item_id
, p_line_rec.item_identifier_type
, p_line_rec.ordered_item
, p_line_rec.item_revision
, p_line_rec.item_type_code
, p_line_rec.last_ack_code
, p_line_rec.last_ack_date
, p_line_rec.last_updated_by
, p_line_rec.last_update_date
, p_line_rec.last_update_login
, p_line_rec.latest_acceptable_date
, p_line_rec.line_category_code
, p_line_rec.line_id
, p_line_rec.line_number
, p_line_rec.line_type_id
, p_line_rec.link_to_line_id
, p_line_rec.model_group_number
--, p_line_rec.mfg_component_sequence_id
, p_line_rec.open_flag
, p_line_rec.option_flag
, p_line_rec.option_number
, p_line_rec.ordered_quantity
, p_line_rec.order_quantity_uom
--, l_org_id
, p_line_rec.order_source_id
, p_line_rec.orig_sys_document_ref
, p_line_rec.orig_sys_line_ref
, p_line_rec.orig_sys_shipment_ref
, p_line_rec.change_sequence
, p_line_rec.over_ship_reason_code
, p_line_rec.over_ship_resolved_flag
, p_line_rec.payment_term_id
, p_line_rec.planning_priority
, p_line_rec.price_list_id
, p_line_rec.pricing_attribute1
, p_line_rec.pricing_attribute10
, p_line_rec.pricing_attribute2
, p_line_rec.pricing_attribute3
, p_line_rec.pricing_attribute4
, p_line_rec.pricing_attribute5
, p_line_rec.pricing_attribute6
, p_line_rec.pricing_attribute7
, p_line_rec.pricing_attribute8
, p_line_rec.pricing_attribute9
, p_line_rec.pricing_context
, p_line_rec.pricing_date
, p_line_rec.pricing_quantity
, p_line_rec.pricing_quantity_uom
, p_line_rec.program_application_id
, p_line_rec.program_id
, p_line_rec.program_update_date
, p_line_rec.project_id
, p_line_rec.promise_date
, p_line_rec.re_source_flag
, p_line_rec.reference_customer_trx_line_id
, p_line_rec.reference_header_id
, p_line_rec.reference_line_id
, p_line_rec.reference_type
, p_line_rec.request_date
, p_line_rec.request_id
, p_line_rec.return_attribute1
, p_line_rec.return_attribute10
, p_line_rec.return_attribute11
, p_line_rec.return_attribute12
, p_line_rec.return_attribute13
, p_line_rec.return_attribute14
, p_line_rec.return_attribute15
, p_line_rec.return_attribute2
, p_line_rec.return_attribute3
, p_line_rec.return_attribute4
, p_line_rec.return_attribute5
, p_line_rec.return_attribute6
, p_line_rec.return_attribute7
, p_line_rec.return_attribute8
, p_line_rec.return_attribute9
, p_line_rec.return_context
, p_line_rec.return_reason_code
, p_line_rec.rla_schedule_type_code
, p_line_rec.salesrep_id
, p_line_rec.schedule_arrival_date
, p_line_rec.schedule_ship_date
, p_line_rec.schedule_status_code
, p_line_rec.shipment_number
, p_line_rec.shipment_priority_code
, p_line_rec.shipped_quantity
, p_line_rec.shipping_method_code
, p_line_rec.shipping_quantity
, p_line_rec.shipping_quantity_uom
, p_line_rec.ship_from_org_id
, p_line_rec.ship_set_id
, p_line_rec.ship_tolerance_above
, p_line_rec.ship_tolerance_below
, p_line_rec.shippable_flag
, p_line_rec.shipping_interfaced_flag
, p_line_rec.ship_to_contact_id
, p_line_rec.ship_to_org_id
, p_line_rec.ship_model_complete_flag
, p_line_rec.sold_to_org_id
, p_line_rec.sold_from_org_id
, p_line_rec.sort_order
, p_line_rec.source_document_id
, p_line_rec.source_document_line_id
, p_line_rec.source_document_type_id
, p_line_rec.source_type_code
, p_line_rec.split_from_line_id
, p_line_rec.line_set_id
, p_line_rec.split_by
, p_line_rec.model_remnant_flag
, p_line_rec.task_id
, p_line_rec.tax_code
, p_line_rec.tax_date
, p_line_rec.tax_exempt_flag
, p_line_rec.tax_exempt_number
, p_line_rec.tax_exempt_reason_code
, p_line_rec.tax_point_code
, p_line_rec.tax_rate
, p_line_rec.tax_value
, p_line_rec.top_model_line_id
, p_line_rec.unit_list_price
, p_line_rec.unit_selling_price
, p_line_rec.visible_demand_flag
, p_line_rec.veh_cus_item_cum_key_id
, p_line_rec.shipping_instructions
, p_line_rec.packing_instructions
, p_line_rec.service_txn_reason_code
, p_line_rec.service_txn_comments
, p_line_rec.service_duration
, p_line_rec.service_period
, p_line_rec.service_start_date
, p_line_rec.service_end_date
, p_line_rec.service_coterminate_flag
, p_line_rec.unit_list_percent
, p_line_rec.unit_selling_percent
, p_line_rec.unit_percent_base_price
, p_line_rec.service_number
, p_line_rec.service_reference_type_code
, p_line_rec.service_reference_line_id
, p_line_rec.service_reference_system_id
, p_line_rec.tp_context
, p_line_rec.tp_attribute1
, p_line_rec.tp_attribute2
, p_line_rec.tp_attribute3
, p_line_rec.tp_attribute4
, p_line_rec.tp_attribute5
, p_line_rec.tp_attribute6
, p_line_rec.tp_attribute7
, p_line_rec.tp_attribute8
, p_line_rec.tp_attribute9
, p_line_rec.tp_attribute10
, p_line_rec.tp_attribute11
, p_line_rec.tp_attribute12
, p_line_rec.tp_attribute13
, p_line_rec.tp_attribute14
, p_line_rec.tp_attribute15
, p_line_rec.flow_status_code
, p_line_rec.marketing_source_code_id
, p_line_rec.calculate_price_flag
, p_line_rec.commitment_id
, p_upgraded_flag
);
insert into oe_order_lines_history
(
line_id,
org_id,
header_id,
line_type_id,
line_number,
ordered_item,
request_date,
promise_date,
schedule_ship_date,
order_quantity_uom,
pricing_quantity,
pricing_quantity_uom,
cancelled_quantity,
shipped_quantity,
ordered_quantity,
fulfilled_quantity,
shipping_quantity,
shipping_quantity_uom,
delivery_lead_time,
tax_exempt_flag,
tax_exempt_number,
tax_exempt_reason_code,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id,
deliver_to_org_id,
ship_to_contact_id,
deliver_to_contact_id,
invoice_to_contact_id,
sold_to_org_id,
cust_po_number,
ship_tolerance_above,
ship_tolerance_below,
demand_bucket_type_code,
veh_cus_item_cum_key_id,
rla_schedule_type_code,
customer_dock_code,
customer_job,
customer_production_line,
cust_model_serial_number,
project_id,
task_id,
inventory_item_id,
tax_date,
tax_code,
tax_rate,
demand_class_code,
price_list_id,
pricing_date,
shipment_number,
agreement_id,
shipment_priority_code,
shipping_method_code,
freight_carrier_code,
freight_terms_code,
fob_point_code,
tax_point_code,
payment_term_id,
invoicing_rule_id,
accounting_rule_id,
source_document_type_id,
orig_sys_document_ref,
source_document_id,
orig_sys_line_ref,
source_document_line_id,
reference_line_id,
reference_type,
reference_header_id,
item_revision,
unit_selling_price,
unit_list_price,
tax_value,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
industry_context,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_attribute16,
industry_attribute17,
industry_attribute18,
industry_attribute19,
industry_attribute20,
industry_attribute21,
industry_attribute22,
industry_attribute23,
industry_attribute24,
industry_attribute25,
industry_attribute26,
industry_attribute27,
industry_attribute28,
industry_attribute29,
industry_attribute30,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
configuration_id,
link_to_line_id,
component_sequence_id,
component_code,
config_display_sequence,
sort_order,
item_type_code,
option_number,
option_flag,
dep_plan_required_flag,
visible_demand_flag,
line_category_code,
actual_shipment_date,
customer_trx_line_id,
return_context,
return_attribute1,
return_attribute2,
return_attribute3,
return_attribute4,
return_attribute5,
return_attribute6,
return_attribute7,
return_attribute8,
return_attribute9,
return_attribute10,
return_attribute11,
return_attribute12,
return_attribute13,
return_attribute14,
return_attribute15,
intmed_ship_to_org_id,
intmed_ship_to_contact_id,
actual_arrival_date,
ato_line_id,
auto_selected_quantity,
component_number,
earliest_acceptable_date,
explosion_date,
latest_acceptable_date,
model_group_number,
schedule_arrival_date,
ship_model_complete_flag,
schedule_status_code,
return_reason_code,
salesrep_id,
split_from_line_id,
cust_production_seq_num,
authorized_to_ship_flag,
invoice_interface_status_code,
ship_set_id,
arrival_set_id,
hist_comments,
hist_type_code,
reason_code,
hist_created_by,
hist_creation_date,
source_type_code,
booked_flag,
fulfilled_flag,
sold_from_org_id,
top_model_line_id,
cancelled_flag,
open_flag,
over_ship_reason_code,
over_ship_resolved_flag,
item_identifier_type,
commitment_id,
shipping_interfaced_flag,
credit_invoice_line_id,
end_item_unit_number,
mfg_component_sequence_id,
config_header_id,
config_rev_nbr,
shipping_instructions,
packing_instructions,
invoiced_quantity,
reference_customer_trx_line_id,
split_by,
line_set_id,
tp_context,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
fulfillment_method_code,
service_reference_type_code,
service_reference_line_id,
service_reference_system_id,
ordered_item_id,
service_number,
service_duration,
service_start_date,
re_source_flag,
flow_status_code,
service_end_date,
service_coterminate_flag,
shippable_flag,
order_source_id,
orig_sys_shipment_ref,
change_sequence,
drop_ship_flag,
customer_line_number,
customer_shipment_number,
customer_item_net_price,
customer_payment_term_id,
first_ack_date,
first_ack_code,
last_ack_code,
last_ack_date,
planning_priority,
service_txn_comments,
service_period,
unit_selling_percent,
unit_list_percent,
unit_percent_base_price,
model_remnant_flag,
service_txn_reason_code,
calculate_price_flag,
revenue_amount
)
select
p_line_rec.line_id,
org_id,
header_id,
line_type_id,
line_number,
ordered_item,
request_date,
promise_date,
schedule_ship_date,
order_quantity_uom,
pricing_quantity,
pricing_quantity_uom,
cancelled_quantity,
shipped_quantity,
ordered_quantity,
fulfilled_quantity,
shipping_quantity,
shipping_quantity_uom,
delivery_lead_time,
tax_exempt_flag,
tax_exempt_number,
tax_exempt_reason_code,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id,
deliver_to_org_id,
ship_to_contact_id,
deliver_to_contact_id,
invoice_to_contact_id,
sold_to_org_id,
cust_po_number,
ship_tolerance_above,
ship_tolerance_below,
demand_bucket_type_code,
veh_cus_item_cum_key_id,
rla_schedule_type_code,
customer_dock_code,
customer_job,
customer_production_line,
cust_model_serial_number,
project_id,
task_id,
inventory_item_id,
tax_date,
tax_code,
tax_rate,
demand_class_code,
price_list_id,
pricing_date,
shipment_number,
agreement_id,
shipment_priority_code,
shipping_method_code,
freight_carrier_code,
freight_terms_code,
fob_point_code,
tax_point_code,
payment_term_id,
invoicing_rule_id,
accounting_rule_id,
source_document_type_id,
orig_sys_document_ref,
source_document_id,
orig_sys_line_ref,
source_document_line_id,
reference_line_id,
reference_type,
reference_header_id,
item_revision,
unit_selling_price,
unit_list_price,
tax_value,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
industry_context,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_attribute16,
industry_attribute17,
industry_attribute18,
industry_attribute19,
industry_attribute20,
industry_attribute21,
industry_attribute22,
industry_attribute23,
industry_attribute24,
industry_attribute25,
industry_attribute26,
industry_attribute27,
industry_attribute28,
industry_attribute29,
industry_attribute30,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
configuration_id,
link_to_line_id,
component_sequence_id,
component_code,
config_display_sequence,
sort_order,
item_type_code,
option_number,
option_flag,
dep_plan_required_flag,
visible_demand_flag,
line_category_code,
actual_shipment_date,
customer_trx_line_id,
return_context,
return_attribute1,
return_attribute2,
return_attribute3,
return_attribute4,
return_attribute5,
return_attribute6,
return_attribute7,
return_attribute8,
return_attribute9,
return_attribute10,
return_attribute11,
return_attribute12,
return_attribute13,
return_attribute14,
return_attribute15,
intmed_ship_to_org_id,
intmed_ship_to_contact_id,
actual_arrival_date,
ato_line_id,
auto_selected_quantity,
component_number,
earliest_acceptable_date,
explosion_date,
latest_acceptable_date,
model_group_number,
schedule_arrival_date,
ship_model_complete_flag,
schedule_status_code,
return_reason_code,
salesrep_id,
split_from_line_id,
cust_production_seq_num,
authorized_to_ship_flag,
invoice_interface_status_code,
ship_set_id,
arrival_set_id,
hist_comments,
hist_type_code,
reason_code,
hist_created_by,
hist_creation_date,
source_type_code,
booked_flag,
fulfilled_flag,
sold_from_org_id,
top_model_line_id,
cancelled_flag,
open_flag,
over_ship_reason_code,
over_ship_resolved_flag,
item_identifier_type,
commitment_id,
shipping_interfaced_flag,
credit_invoice_line_id,
end_item_unit_number,
mfg_component_sequence_id,
config_header_id,
config_rev_nbr,
shipping_instructions,
packing_instructions,
invoiced_quantity,
reference_customer_trx_line_id,
split_by,
line_set_id,
tp_context,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
fulfillment_method_code,
service_reference_type_code,
service_reference_line_id,
service_reference_system_id,
ordered_item_id,
service_number,
service_duration,
service_start_date,
re_source_flag,
flow_status_code,
service_end_date,
service_coterminate_flag,
shippable_flag,
order_source_id,
orig_sys_shipment_ref,
change_sequence,
drop_ship_flag,
customer_line_number,
customer_shipment_number,
customer_item_net_price,
customer_payment_term_id,
first_ack_date,
first_ack_code,
last_ack_code,
last_ack_date,
planning_priority,
service_txn_comments,
service_period,
unit_selling_percent,
unit_list_percent,
unit_percent_base_price,
model_remnant_flag,
service_txn_reason_code,
calculate_price_flag,
revenue_amount
from oe_order_lines_history
where line_id = p_orig_line_id;
/* Insert log record here */
G_Log_Rec.Header_Id := p_line_rec.header_id;
OE_UPG_SO.Upgrade_Insert_Upgrade_Log;
oe_debug_pub.add( 'EXITING INSERT_ROW' , 1 ) ;
END Insert_Row;
Procedure Insert_Return_Included_Items(p_line_id NUMBER,module varchar2 default null)
IS
l_inventory_item_id NUMBER;
Select rma_interface_id,rma_id,rma_line_id,
inventory_item_id,component_sequence_id,
quantity, unit_code,received_quantity,delivered_quantity
from mtl_so_rma_interface
where rma_line_id = p_line_id
and inventory_item_id <> l_inventory_item_id;
select oe_order_lines_s.nextval into l_line_id from dual;
Select max(line_number) into l_line_number
from oe_order_lines_all
where header_id = l_line_rec.header_id;
Insert_Row(l_line_rec1,p_line_id,'Y','N');
select oe_sets_s.nextval into l_line_set_id from dual;
insert into oe_sets
( SET_ID, SET_NAME, SET_TYPE, HEADER_ID, SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,SCHEDULE_SHIP_DATE, SCHEDULE_ARRIVAL_DATE,
FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE,
SHIPMENT_PRIORITY_CODE, SET_STATUS,
CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE,
UPDATE_LOGIN, INVENTORY_ITEM_ID,ORDERED_QUANTITY_UOM,
LINE_TYPE_ID,SHIP_TOLERANCE_ABOVE, SHIP_TOLERANCE_BELOW)
values
(l_line_set_id, to_char(l_line_set_id),
'LINE_SET',l_line_rec.header_id,null,null, null,null,null,
null,null,null, 0,sysdate,0, sysdate,
0,null,null,null,null,null
);
Insert_Row(l_line_rec1,p_line_id,'Y','N');
select oe_order_lines_s.nextval into l_line_id1 from dual;
Insert_Row(l_line_rec1,p_line_id,'Y','N');
select shipment_line_id
into l_shipment_line_id
from rcv_shipment_lines
where oe_order_line_id = p_line_id
and item_id = l_rec.inventory_item_id;
update rcv_shipment_lines
set oe_order_line_id = l_line_id
where shipment_line_id = l_shipment_line_id;
update rcv_transactions
set oe_order_line_id = l_line_id
where shipment_line_id = l_shipment_line_id;
update rcv_supply
set oe_order_line_id = l_line_id
where shipment_line_id = l_shipment_line_id;
END Insert_Return_Included_Items;
Select l1.line_id,l1.header_id
from oe_order_lines_all l1, oe_order_lines_all l2
where l1.reference_line_id = l2.line_id
and l2.item_type_code in ('MODEL','CLASS','KIT')
and l2.ato_line_id is null
and l1.line_category_code = 'RETURN'
and l1.reference_type is not null
and l1.upgraded_flag in ('Y','P')
and nvl(l1.open_flag,'-') = 'Y'
and l1.line_id not in (
Select u.new_line_id
from oe_upgrade_log u
where u.module = 'RI') ;
Insert_Return_Included_Items(l_line_id,'Process_Returns');
insert into oe_upgrade_log(header_id,new_line_id,creation_date, module)
values (l_header_id,l_line_id,sysdate, 'RI');
oe_upg_so.upgrade_insert_errors
( L_header_id => l_header_id,
L_comments => 'Exception in Process_Upgraded_Returns: '
||'Error code -'
||to_char(v_error_code)
||' - Line id '||to_char(l_line_id)
);
Select l1.line_id,l1.header_id,'RA' item_type,m.received_quantity
from oe_order_lines_all l1, oe_order_lines_all l2,mtl_so_rma_interface m
where l1.reference_line_id = l2.line_id
and l1.line_category_code = 'RETURN'
and decode(m.received_quantity,0,NULL,m.received_quantity) <
l1.ordered_quantity
and l1.line_id = m.rma_line_id
and l2.item_type_code = 'OPTION'
and l2.ato_line_id = l2.line_id
and l1.upgraded_flag in ('Y','P')
and l1.line_id not in(
Select new_line_id
from oe_upgrade_log u
where u.module = 'RA');
select oe_sets_s.nextval into l_line_set_id from dual;
insert into oe_sets
( SET_ID, SET_NAME, SET_TYPE, HEADER_ID, SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,SCHEDULE_SHIP_DATE, SCHEDULE_ARRIVAL_DATE,
FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE,
SHIPMENT_PRIORITY_CODE, SET_STATUS,
CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE,
UPDATE_LOGIN, INVENTORY_ITEM_ID,ORDERED_QUANTITY_UOM,
LINE_TYPE_ID,SHIP_TOLERANCE_ABOVE, SHIP_TOLERANCE_BELOW)
values
(l_line_set_id, to_char(l_line_set_id),
'LINE_SET',l_line_rec.header_id,null,null, null,null,null,
null,null,null, 0,sysdate,0, sysdate,
0,null,null,null,null,null
);
Update oe_order_lines_all
set shipped_quantity = v_received_quantity,
fulfilled_quantity = v_received_quantity,
ordered_quantity = v_received_quantity ,
line_set_id = l_line_set_id
where line_id = l_line_id;
select oe_order_lines_s.nextval
into l_line_id1
from dual;
Insert_Row(l_line_rec2,l_line_id,'Y','N');
insert into oe_upgrade_log(header_id,new_line_id,creation_date, module)
values (l_header_id,l_line_id,sysdate, l_rec.item_type);
oe_upg_so.upgrade_insert_errors
( L_header_id => l_header_id,
L_comments => 'Exception in Fix_Returns_Splits: '
||'Error code -'
||to_char(v_error_code)
||' - Line id '||to_char(l_line_id)
);