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,
round(pa.percent,38) 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');
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,
round(pa.percent,38) percent, --fix bug 2854690
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.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,
list_header_id, /* New columns added by jefflee 6/21/00 */
list_line_id,
list_line_type_code,
modified_from,
modified_to,
update_allowed,
updated_flag,
applied_flag,
operand,
arithmetic_operator,
adjusted_amount,
pricing_phase_id,
charge_type_code,
charge_subtype_code,
pricing_group_sequence,
list_line_no,
source_system_code,
benefit_qty,
benefit_uom_code,
print_on_invoice_flag,
expiration_date,
modifier_level_code,
price_break_type_code,
lock_control
)
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,
pa_rec.list_header_id, /* New columns added by jefflee 6/21/00 */
pa_rec.list_line_id,
pa_rec.list_line_type_code,
pa_rec.modified_from,
pa_rec.modified_to,
pa_rec.update_allowed,
--bug 2121206 Begin
decode(mpa.automatic_flag,'N','Y',pa_rec.updated_flag),
--bug 2121206 End
pa_rec.applied_flag,
pa_rec.operand,
pa_rec.arithmetic_operator,
pa_rec.adjusted_amount,
pa_rec.pricing_phase_id,
pa_rec.charge_type_code,
pa_rec.charge_subtype_code,
pa_rec.pricing_group_sequence,
pa_rec.list_line_no,
pa_rec.source_system_code,
pa_rec.benefit_qty,
pa_rec.benefit_uom_code,
pa_rec.print_on_invoice_flag,
pa_rec.expiration_date,
pa_rec.modifier_level_code,
pa_rec.price_break_type_code,
1
);
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 = mscx.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,
lock_control
)
values
(
v_sales_credit_id,
mscx.creation_date,
mscx.created_by,
mscx.last_update_date,
mscx.last_updated_by,
mscx.last_update_login,
mscx.header_id,
mscx.sales_credit_type_id,
mscx.salesrep_id,
mscx.percent,
decode(L_level_flag,'L',G_LINE_ID,null),
mscx.context,
mscx.attribute1,
mscx.attribute2,
mscx.attribute3,
mscx.attribute4,
mscx.attribute5,
mscx.attribute6,
mscx.attribute7,
mscx.attribute8,
mscx.attribute9,
mscx.attribute10,
mscx.attribute11,
mscx.attribute12,
mscx.attribute13,
mscx.attribute14,
mscx.attribute15,
mscx.dw_update_advice_flag,
mscx.wh_update_date,
1
);
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;
g_canc_rec.can_last_updated_by := mcan.last_updated_by;
g_canc_rec.can_last_update_date := mcan.last_update_date;
g_canc_rec.can_last_update_login := mcan.last_update_login;
g_canc_rec.can_program_update_date := mcan.program_update_date;
OE_UPG_SO_NEW.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,
/*
decode(sld.schedule_status_code,
NULL, NULL ,
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(sla.ordered_quantity,sla.cancelled_quantity,'Y','N') line_cancel_flag,
sla.cancelled_quantity,
/*
decode(nvl(sld.picking_line_id,0),0,sla.shipped_quantity,
sld.shipped_quantity) shipped_quantity,
*/
decode(sla.line_type_code,'RETURN',sla.shipped_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.cancelled_quantity,0)),nvl(sla.shipped_quantity,0),sla.shipped_quantity,
null))) shipped_quantity,
/*Bug2639916 start */
nvl(sld.invoiced_quantity,sla.invoiced_quantity) invoiced_quantity,
sld.invoiced_quantity sld_invoiced_quantity,
sla.invoiced_quantity sla_invoiced_quantity,
sla.parent_line_id sla_parent_line_id,
/* Bug2639916 end */
sla.tax_exempt_number,
sla.tax_exempt_reason_code,
nvl(sld.warehouse_id,sla.warehouse_id) warehouse_id,
sld.subinventory,
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.commitment_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,
sla.pricing_attribute11,
sla.pricing_attribute12,
sla.pricing_attribute13,
sla.pricing_attribute14,
sla.pricing_attribute15,
nvl(sla.creation_date,sysdate) 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),
'KIT', 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,
/* Following lines are changed to fix the item type problem(from ontupg53)
decode(sla.item_type_code,'STANDARD',
decode(sla.option_flag,'Y','OPTION',
sla.item_type_code),sla.item_type_code) item_type_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,
nvl(sla.source_type_code,'INTERNAL') 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,
/* sld.load_seq_number, */
' ' 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,
/* Fix from ontupg39(Rupal)
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),
sla.ato_line_id) ato_line_id,
*/
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,
decode(sla.ordered_quantity,
sla.cancelled_quantity,'N',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',NULL) invoice_interface_status_code,
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',
sld.shippable_flag) shippable_flag,
sla.component_sort_code sort_order,
sla.terms_id
from
so_lines_all sla,
so_line_attributes slattr,
oe_upgrade_wsh_iface sld
/* so_line_details 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
/* Bug #4681686, reshuffled the order by clause to make sure the at the Lines are pulled by
line_id first */
sla.line_id,
sla.ship_set_number,
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;
select max(system_id) into v_system_id from so_line_service_details
where line_id = mol.line_id;
Select line_id into
v_shipped_line_id from
so_lines_all where
header_id = g_line_rec.header_id and
--shipped_quantity is not null and
NVL(shipped_quantity,0) > 0 and
ship_set_number = v_ship_set_number
and rownum = 1;
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 decode(agr.override_arule_flag,
'N', agr.accounting_rule_id,
decode(comt.override_arule_flag,
'N', comt.accounting_rule_id,
nvl(si.accounting_rule_id,
nvl(comt.accounting_rule_id,
nvl(agr.accounting_rule_id,
nvl(g_accounting_rule_id,NULL)))))),
decode(agr.override_irule_flag,
'N', agr.invoicing_rule_id,
decode(comt.override_irule_flag,
'N', comt.invoicing_rule_id,
nvl(si.invoicing_rule_id,
nvl(comt.invoicing_rule_id,
nvl(agr.invoicing_rule_id,
nvl(g_invoicing_rule_id,NULL))))))
INTO g_line_rec.accounting_rule_id,
g_line_rec.invoicing_rule_id
FROM so_lines_all l,
so_agreements_b agr,
ra_customer_trx_all ct,
so_agreements_b comt,
mtl_system_items si
WHERE l.line_id= mol.line_id
AND l.agreement_id = agr.agreement_id (+)
AND l.commitment_id = ct.customer_trx_id (+)
AND ct.agreement_id = comt.agreement_id (+)
AND nvl(l.warehouse_id, nvl(v_master_org_for_single_org, oe_sys_parameters.value('MASTER_ORGANIZATION_ID', l.org_id))) = si.organization_id
AND l.inventory_item_id = si.inventory_item_id;
select customer_trx_id
into v_customer_trx_id
from RA_CUSTOMER_TRX_LINES_ALL
where customer_trx_line_id =
g_line_rec.return_reference_id;
select l.header_id into v_reference_header_id
from oe_order_lines_all l
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
into r_inventory_item_id_2,
r_uom_code_2
from mtl_so_rma_interface
where rma_line_id = mol.line_id;
select received_quantity into v_received_quantity
from mtl_so_rma_interface
where mol.line_id = rma_line_id
and mol.inventory_item_id = inventory_item_id;
select received_quantity into v_received_quantity
from mtl_so_rma_interface
where mol.line_id = rma_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 max(line_number) + 1
into g_line_rec.line_number
from so_lines_all
where header_id = G_HEADER_ID;
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 requisition_line_id
into g_line_rec.source_document_line_id
from po_requisition_lines_all
where requisition_header_id = G_SOURCE_DOCUMENT_ID
and line_num = mol.original_system_line_reference
and nvl(org_id,-99) = nvl(mol.org_id,-99);
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;
/* Update of line_number/option_number */
/* Update from ontupg81 */
if g_line_rec.line_type_code = 'RETURN' THEN
g_line_rec.ato_line_id := NULL;
SELECT line_number
INTO g_line_rec.line_number
FROM so_lines_all
WHERE line_id = (select shipment_schedule_line_id
from so_lines_all
where line_id = mol.line_id);
select ship_to_site_use_id
into g_line_rec.ship_to_site_use_id
from so_lines_all
where line_id = g_line_rec.ato_line_id;
select ship_to_site_use_id
into g_line_rec.ship_to_site_use_id
from so_lines_all
where line_id = g_line_rec.parent_line_id;
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,
subinventory,
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,
reference_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,
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,
lock_control
)
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 */
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.subinventory, /* SUBINVENOTRY */
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 */
g_line_rec.tax_date, /* 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, /* Renga 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 */
g_line_rec.ship_method_code, /* SHIPPPING_METHOD_CODE */
g_line_rec.ship_method_code, /* FREIGHT_CARRIER_CODE */
G_freight_terms_code, /* FREIGHT_TERMS_CODE */
G_FOB_POINT_CODE, /* FOB_POINT_CODE */
'INVOICE', /* TAX_POINT_CODE */
g_line_rec.terms_id, /* PAYMENT_TERM_ID */
g_line_rec.invoicing_rule_id, /* INVOICING_RULE_ID */
g_line_rec.accounting_rule_id, /* ACCOUNTING_RULE_ID */
g_line_rec.source_document_type_id, /* SOURCE_DOCUMENT_TYPE_ID */
g_line_rec.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, /* 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 */
g_line_rec.sort_order, /* SORT_ORDER, */
g_line_rec.item_type_code, /* ITEM_TYPE_CODE */
g_line_rec.option_number, /* 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), /* REFERENCE_CUSTOMER_TRX_LINE_ID */
/* Fixing bug 2001159 */
decode(g_line_rec.line_type_code,'RETURN',g_line_rec.return_reference_type_Code,NULL), /* 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 */
g_line_rec.split_from_line_id, /* 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 */
decode(g_include_ship_set,'N',null,
decode(g_line_rec.cancelled_flag,'Y',null,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 */
g_line_rec.order_source_id, /* 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 */
g_line_rec.commitment_id, /* commitment_id */
null, /* end_item_unit_number */
null, /* mfg_component_sequence_id */
null, /* config_header_id */
null, /* config_rev_nbr */
G_PACKING_INSTRUCTIONS, /* packing_instructions */
G_SHIPPING_INSTRUCTIONS, /* shipping_instructions */
g_line_rec.invoiced_quantity, /* invoiced_quantity */
null, /* 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 */
decode(g_line_rec.open_flag,'Y','I','Y'),/* upgraded_flag */
1
);
UPDATE oe_drop_ship_sources
SET line_id=g_line_id
WHERE line_id=g_orig_line_id;
OE_UPG_SO_NEW.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_NEW.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_NEW.Upgrade_Insert_Lines_History;
select /*+ ORDERED USE_NL(SHA SHATTR) */
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.sales_channel_code, -- Added by JAUTOMO
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,
decode(sha.cancelled_flag ,'Y', 'N',nvl(sha.open_flag,'N')) open_flag,
decode(sha.s1,1,'Y','N') booked_flag,
sha.s1_date,
sha.shipping_instructions,
sha.packing_instructions
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
and nvl(line_type,'O') = L_line_type ;
OE_UPG_SO_NEW.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 count(*) into l_count from oe_system_parameters_all;
select master_organization_id
into v_master_org_for_single_org
from oe_system_parameters_all;
-- insert error message for invalid order category
G_ERROR_MESSAGE := 'Invalid Order Category.';
-- insert error message for invalid source
G_ERROR_MESSAGE := 'Invalid Order Source code.';
select requisition_header_id into G_SOURCE_DOCUMENT_ID
from po_requisition_headers_all
where segment1 = moh.original_system_reference
and nvl(org_id,-99) = nvl(moh.org_id,-99);
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;
select new_list_header_id
into g_list_header_id
from qp_discount_mapping
where old_discount_id = moh.price_list_id
and old_discount_line_id is null
and new_list_line_id is null
and new_type = 'P';
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,
lock_control,
shipping_instructions,
packing_instructions
)
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, */
G_SOURCE_DOCUMENT_TYPE_ID, /* SOURCE_DOCUMENT_TYPE_ID,*/
G_ORIG_SYS_DOCUMENT_REF, /* ORIG_SYS_DOCUMENT_REF,*/
G_SOURCE_DOCUMENT_ID, /* 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, /* Renga 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 */
moh.invoicing_rule_id, /* INVOICING_RULE_ID */
moh.accounting_rule_id, /* ACCOUNTING_RULE_ID */
moh.terms_id, /* PAYMENT_TERM_ID */
moh.ship_method_code, /* SHIPING_METHOD_CODE */
moh.ship_method_code, /* 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_NEW.G_Earliest_Schedule_Limit, /* EARLIEST_SCHEDULE_LIMIT */
OE_UPG_SO_NEW.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 */
moh.sales_channel_code, /* SALES CHANNEL CODE */
G_ORDER_CATEGORY_CODE, /* ORDER_CATEGORY_CODE */
nvl(moh.cancelled_flag,'N'), /* 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 */
decode(moh.open_flag,'Y','I','Y'), /* UPGRADED_FLAG */
decode(nvl(moh.booked_flag,'-'),'Y',
moh.s1_date,NULL), /* BOOKED_DATE */
moh.org_id, /* SOLD_FROM_ORG_ID */
1,
moh.shipping_instructions,
moh.packing_instructions
);
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.can_header_id ,
g_hdr_canc_rec.can_line_id ,
g_hdr_canc_rec.can_created_by ,
g_hdr_canc_rec.can_creation_date ,
g_hdr_canc_rec.can_last_updated_by ,
g_hdr_canc_rec.can_last_update_date ,
g_hdr_canc_rec.can_last_update_login ,
g_hdr_canc_rec.can_program_application_id ,
g_hdr_canc_rec.can_program_id ,
g_hdr_canc_rec.can_program_update_date ,
g_hdr_canc_rec.can_request_id ,
g_hdr_canc_rec.can_cancel_code ,
g_hdr_canc_rec.can_cancelled_by ,
g_hdr_canc_rec.can_cancel_date ,
g_hdr_canc_rec.can_cancelled_quantity ,
v_cancel_comment ,
g_hdr_canc_rec.can_context ,
g_hdr_canc_rec.can_attribute1 ,
g_hdr_canc_rec.can_attribute2 ,
g_hdr_canc_rec.can_attribute3 ,
g_hdr_canc_rec.can_attribute4 ,
g_hdr_canc_rec.can_attribute5 ,
g_hdr_canc_rec.can_attribute6 ,
g_hdr_canc_rec.can_attribute7 ,
g_hdr_canc_rec.can_attribute8 ,
g_hdr_canc_rec.can_attribute9 ,
g_hdr_canc_rec.can_attribute10 ,
g_hdr_canc_rec.can_attribute11 ,
g_hdr_canc_rec.can_attribute12 ,
g_hdr_canc_rec.can_attribute13 ,
g_hdr_canc_rec.can_attribute14 ,
g_hdr_canc_rec.can_attribute15
-- 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_NEW.Insert_Multiple_Models;
/* ============ Updates After Creation =======*/
OE_UPG_SO_NEW.Update_After_Insert;
/* ============ Update Remnant Flag(ontupg53) =======*/
IF G_OPEN_FLAG = 'Y' THEN
OE_UPG_SO_NEW.Update_remnant_flag;
/* ============ Updates for Returns =======*/
IF G_ORDER_CATEGORY_CODE = 'RETURN' THEN
Process_Upgraded_Returns(G_HEADER_ID);
OE_UPG_SO_NEW.Upgrade_Insert_Upgrade_Log;
Update SO_HEADERS_ALL
set upgrade_flag = 'Y'
where header_id = G_HEADER_ID;
OE_UPG_SO_NEW.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_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => G_HEADER_ID,
L_comments => G_ERROR_MESSAGE
);
OE_UPG_SO_NEW.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,
reference_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,
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_line_rec.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 */
g_line_rec.tax_date, /* 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 */
g_line_rec.ship_method_code, /* SHIPPPING_METHOD_CODE */
g_line_rec.ship_method_code, /* FREIGHT_CARRIER_CODE */
G_freight_terms_code, /* FREIGHT_TERMS_CODE */
G_FOB_POINT_CODE, /* FOB_POINT_CODE */
'INVOICE', /* TAX_POINT_CODE */
g_line_rec.terms_id, /* PAYMENT_TERM_ID */
g_line_rec.invoicing_rule_id, /* INVOICING_RULE_ID */
g_line_rec.accounting_rule_id, /* ACCOUNTING_RULE_ID */
g_line_rec.source_document_type_id, /* SOURCE_DOCUMENT_TYPE_ID */
null, /* 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 */
g_line_rec.sort_order, /* 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), /* REFERENCE_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 */
g_line_rec.split_from_line_id, /* 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 */
decode(g_include_ship_set,'N',null,
decode(g_line_rec.cancelled_flag,'Y',null,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.parent_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, */
g_line_rec.commitment_id, /* 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, */
g_shipping_instructions, /* shipping_instructions, */
g_packing_instructions, /* packing_instructions, */
g_line_rec.invoiced_quantity, /* invoiced_quantity, */
null, /* 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' and (sha.order_category = 'RMA' and v_type = 'R') ) or
( sha.upgrade_flag = 'N' and (sha.order_category <> 'RMA' and v_type = 'O' ) ) or
( nvl(sha.upgrade_flag,'N') in ('N','X') and v_type = 'M') or
( sha.open_flag = 'Y' and sha.upgrade_flag = 'Y' and v_type = 'W')
order by sha.header_id;
delete oe_upgrade_distribution
where line_type = v_type;
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' and (sha.order_category = 'RMA' and v_type = 'R') ) or
( sha.upgrade_flag = 'N' and (sha.order_category <> 'RMA' and v_type = 'O') ) or
( nvl(sha.upgrade_flag,'N') in ('N','X') and v_type = 'M') or
( sha.open_flag = 'Y' and sha.upgrade_flag = 'Y' and v_type = 'W');
OE_UPG_SO_NEW.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_NEW.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_NEW.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
);
-- Dynamic SQL used because the select based column expressions
-- did not compile when used directly as a cursor definition.
-- This cursor allows us to determine the actual slab distribution
-- by doing only one index scan of the primary key index for this table
l_cursor_stmt VARCHAR2(2000) :=
'select ' ||
' line_service_detail_id,'||
' (select count(1) from so_line_service_details),' ||
' (select min(line_service_detail_id) from so_line_service_details),' ||
' (select max(line_service_detail_id) from so_line_service_details)' ||
' from so_line_service_details' ||
' order by line_service_detail_id';
DELETE FROM oe_upgrade_distribution
WHERE line_type = 'I';
oe_upg_so_new.upgrade_insert_distbn_record
(
l_slab => 1,
l_start_header_id => l_min_id,
l_end_header_id => l_max_id,
l_type_var => 'I'
);
oe_upg_so_new.upgrade_insert_distbn_record
(
l_slab => l_current_slab,
l_start_header_id => l_starting_id,
l_end_header_id => l_line_service_detail_id,
l_type_var => 'I'
);
oe_upg_so_new.upgrade_insert_distbn_record
(
l_slab => l_current_slab,
l_start_header_id => l_starting_id,
l_end_header_id => l_max_id,
l_type_var => 'I'
);
select
shsa.hold_source_id
from
so_hold_sources_all shsa
order by shsa.hold_source_id;
delete oe_upgrade_distribution
where line_type = v_type;
select
count(*),
nvl(min(shsa.hold_source_id),0),
nvl(max(shsa.hold_source_id),0)
into
v_total_sources,
v_min_source,
v_max_source
from
so_hold_sources_all shsa;
OE_UPG_SO_NEW.Upgrade_Insert_Distbn_Record
(
L_slab => 1,
L_start_header_id => v_min_source,
L_end_Header_id => v_max_source,
L_type_var => v_type
);
OE_UPG_SO_NEW.Upgrade_Insert_Distbn_Record
(
L_slab => v_slab_count,
L_start_header_id => v_min_source,
L_end_Header_id => v_max_source,
L_type_var => v_type
);
OE_UPG_SO_NEW.Upgrade_Insert_Distbn_Record
(
L_slab => v_slab_count,
L_start_header_id => v_min_source,
L_end_Header_id => v_max_source ,
L_type_var => v_type
);
SELECT freight_charge_id
FROM so_freight_charges
ORDER BY freight_charge_id;
DELETE oe_upgrade_distribution
WHERE line_type = v_type;
SELECT
count(*),
nvl(min(freight_charge_id),0),
nvl(max(freight_charge_id),0)
INTO
v_total_headers,
v_min_header,
v_max_header
FROM
so_freight_charges;
OE_UPG_SO_NEW.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_NEW.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_NEW.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,
substr(l_comments,1,240),
sysdate
);
End Upgrade_Insert_Errors;
Procedure update_remnant_flag
IS
cursor model_lines IS
select /*+ INDEX(SO_LINES_N1) */ lines.line_id
from so_lines_all lines
where lines.header_id = G_HEADER_ID
and lines.item_type_code in ('MODEL','KIT')
and parent_line_id is null
and lines.line_type_code in ('REGULAR','DETAIL');
select line_id,inventory_item_id,item_type_code
from so_lines_all
where parent_line_id=p_parent_line_id;
select line_detail_id
from so_line_details
where line_id=p_line_id
AND included_item_flag = 'Y';
SELECT count(*)
INTO no_of_details
FROM SO_LINE_DETAILS
WHERE LINE_ID = l_parent_line_id
AND INCLUDED_ITEM_FLAG = 'N'
AND nvl(CONFIGURATION_ITEM_FLAG,'N') = 'N';
SELECT line_detail_id
INTO l_detail_line_id
FROM SO_LINE_DETAILS
WHERE LINE_ID = l_parent_line_id
AND INCLUDED_ITEM_FLAG = 'N'
AND nvl(CONFIGURATION_ITEM_FLAG,'N') = 'N';
SELECT /*+ INDEX(oe_upgrade_wsh_iface OE_UPGRADE_WSH_IFACE_N2) */
count(*)
INTO no_of_picking_details
from oe_upgrade_wsh_iface
where line_detail_id = l_detail_line_id;
SELECT 'Y'
INTO l_valid
FROM so_line_details
WHERE line_id=l_parent_line_id
AND included_item_flag='Y'
group by inventory_item_id
having count(*) > 1;
SELECT count(*)
INTO no_of_picking_details
from oe_upgrade_wsh_iface
where line_detail_id = l_ii_line_id;
SELECT count(*)
INTO no_of_details
FROM SO_LINE_DETAILS
WHERE LINE_ID = l_option_line_id
AND INVENTORY_ITEM_ID = l_inventory_item_id
AND INCLUDED_ITEM_FLAG = 'N'
AND nvl(CONFIGURATION_ITEM_FLAG,'N') = 'N';
SELECT line_detail_id
INTO l_detail_line_id
FROM SO_LINE_DETAILS
WHERE LINE_ID = l_option_line_id
AND INVENTORY_ITEM_ID = l_inventory_item_id
AND INCLUDED_ITEM_FLAG = 'N'
AND nvl(CONFIGURATION_ITEM_FLAG,'N') = 'N';
SELECT count(*)
INTO no_of_picking_details
from oe_upgrade_wsh_iface
where line_detail_id = l_detail_line_id;
SELECT 'Y'
INTO l_valid
FROM so_line_details
WHERE line_id=l_option_line_id
AND included_item_flag='Y'
group by inventory_item_id
having count(*) > 1;
SELECT count(*)
INTO no_of_picking_details
from oe_upgrade_wsh_iface
where line_detail_id = l_ii_line_id;
UPDATE /*+ INDEX(OE_ORDER_LINES_N10) */ OE_ORDER_LINES_ALL
SET model_remnant_flag='Y'
WHERE top_model_line_id=l_parent_line_id
AND model_remnant_flag is null ;
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => g_header_id,
L_comments => 'Update_Remnant_Flag failed on ora error: '||to_char(sqlcode)
);
end update_remnant_flag;
Procedure Update_After_Insert
IS
Cursor c_get_record is
select /*+ INDEX(OE_ORDER_LINES_N1) */
line_id,
item_type_code,
top_model_line_id,
ato_line_id,
shippable_flag,
shipped_quantity,
line_number,
shipment_number,
model_remnant_flag,
link_to_line_id,
line_category_code,
fulfilled_quantity,
fulfilled_flag,
fulfillment_date,
actual_shipment_date,
ordered_quantity,
service_reference_line_id,
option_number,
component_number,
unit_selling_price,
unit_list_price,
ship_set_id,
ship_from_org_id,
ship_to_org_id,
schedule_ship_date,
schedule_status_code
from
oe_order_lines_all
where
header_id = G_HEADER_ID;
select line_id , service_parent_line_id
from so_lines_all
where service_parent_line_id = p_serviceable_line_id;
select old_line_id, new_line_id from
oe_upgrade_log_v where
old_line_id = p_service_parent_line_id
and old_line_id <> new_line_id
order by new_line_id;
select old_line_id, new_line_id from
oe_upgrade_log_v where
old_line_id = p_line_id
and old_line_id <> new_line_id
order by new_line_id;
l_update_table update_tbl_type;
l_update_index NUMBER:=0;
l_update_table(l_get_index).line_number := l_temp_record.line_number;
l_update_table(l_get_index).option_number := l_temp_record.option_number;
l_update_table(l_get_index).shipment_number := l_temp_record.shipment_number;
l_update_table(l_get_index).component_number := l_temp_record.component_number;
l_update_table(l_get_index).ordered_quantity := l_temp_record.ordered_quantity;
l_update_table(l_get_index).shipped_quantity := l_temp_record.shipped_quantity;
l_update_table(l_get_index).fulfilled_quantity := l_temp_record.fulfilled_quantity;
l_update_table(l_get_index).fulfilled_flag := l_temp_record.fulfilled_flag;
l_update_table(l_get_index).fulfillment_date := l_temp_record.fulfillment_date;
l_update_table(l_get_index).actual_shipment_date := l_temp_record.actual_shipment_date;
l_update_table(l_get_index).model_remnant_flag := l_temp_record.model_remnant_flag;
l_update_table(l_get_index).service_reference_line_id := l_temp_record.service_reference_line_id;
l_update_table(l_get_index).item_type_code := l_temp_record.item_type_code;
l_update_table(l_get_index).top_model_line_id := l_temp_record.top_model_line_id;
l_update_table(l_get_index).ato_line_id := l_temp_record.ato_line_id;
SELECT /*+ INDEX(OE_ORDER_LINES_N1) */
count(*)
INTO l_count
FROM oe_order_lines_all
WHERE ato_line_id = l_temp_record.ato_line_id
AND top_model_line_id = l_temp_record.top_model_line_id
AND header_id = G_HEADER_ID;
l_update_table(l_get_index).ato_line_id := l_temp_record.line_id;
l_update_table(l_get_index).shippable_flag := l_temp_record.shippable_flag;
l_update_table(l_get_index).temp_update_flag := 'N';
l_update_table(l_get_index).unit_selling_price := 0;
l_update_table(l_get_index).unit_list_price := 0;
l_update_table(l_get_index).temp_update_flag := 'Y';
l_update_table(l_get_index).unit_selling_price := 0;
l_update_table(l_get_index).unit_list_price := 0;
l_update_table(l_get_index).temp_update_flag := 'Y';
l_update_table(l_get_index).unit_selling_price := l_temp_record.unit_selling_price;
l_update_table(l_get_index).unit_list_price := l_temp_record.unit_list_price;
l_update_table(l_get_index).ship_set_id := l_temp_record.ship_set_id;
l_update_table(l_new_line_id2).service_reference_line_id := l_new_line_id1;
l_update_table(l_new_line_id2).temp_update_flag := 'Y';
OE_UPG_SO_NEW.Upgrade_Insert_Errors(
G_HEADER_ID,
'FYI Only: Service Ref.Line id not updated in OM for Line '
||to_char(l_new_line_id2));
SELECT /*+ INDEX(OE_ORDER_LINES_N1) */ ACTUAL_SHIPMENT_DATE
INTO l_update_table(l_temp_index).actual_shipment_date
FROM OE_ORDER_LINES_ALL
WHERE TOP_MODEL_LINE_ID = l_get_table(l_temp_index).top_model_line_id
AND ATO_LINE_ID = l_get_table(l_temp_index).ato_line_id
AND ITEM_TYPE_CODE='CONFIG'
AND header_id=G_HEADER_ID;
l_update_table(l_temp_index).line_number := l_get_table(l_top_model_tbl(I)).line_number;
l_update_table(l_temp_index).shipment_number := l_get_table(l_top_model_tbl(I)).shipment_number;
l_update_table(l_temp_index).component_number := l_main_component;
l_update_table(l_temp_index).option_number := NULL;
l_update_table(l_temp_index).temp_update_flag := 'Y';
l_update_table(l_temp_index).line_number := l_get_table(l_top_model_tbl(I)).line_number;
l_update_table(l_temp_index).shipment_number := l_get_table(l_top_model_tbl(I)).shipment_number;
l_update_table(l_temp_index).component_number := NULL;
l_update_table(l_temp_index).option_number := NULL;
l_update_table(l_temp_index).temp_update_flag := 'Y';
l_update_table(l_temp_index).line_number := l_get_table(l_top_model_tbl(I)).line_number;
l_update_table(l_temp_index).shipment_number := l_get_table(l_top_model_tbl(I)).shipment_number;
l_update_table(l_temp_index).component_number := l_get_table(l_temp_index).component_number;
l_update_table(l_temp_index).option_number := l_get_table(l_temp_index).option_number;
l_update_table(l_temp_index).temp_update_flag := 'Y';
l_update_table(l_ii_index).line_number := l_get_table(l_top_model_tbl(I)).line_number;
l_update_table(l_ii_index).shipment_number := l_get_table(l_top_model_tbl(I)).shipment_number;
l_update_table(l_ii_index).component_number := l_ii_component;
l_update_table(l_ii_index).option_number := l_get_table(l_top_model_tbl(I)).option_number;
l_update_table(l_ii_index).temp_update_flag := 'Y';
l_update_index := l_update_table.FIRST;
WHILE l_update_index IS NOT NULL
LOOP
IF nvl(l_update_table(l_update_index).top_model_line_id,0) <> l_top_model_tbl(I) THEN
GOTO NEXT_LINE_2;
l_update_table(l_update_index).model_remnant_flag := 'Y';
l_update_table(l_update_index).temp_update_flag := 'Y';
l_update_index := l_update_table.NEXT(l_update_index);
l_update_index := l_update_table.FIRST;
WHILE l_update_index IS NOT NULL
LOOP
IF nvl(l_update_table(l_update_index).top_model_line_id,0) <> l_top_model_tbl(I) THEN
GOTO NEXT_LINE_3;
IF l_update_table(l_update_index).ato_line_id IS NULL AND
l_update_table(l_update_index).shippable_flag = 'N' THEN
l_update_table(l_update_index).shipped_quantity := l_update_table(l_update_index).ordered_quantity;
l_update_table(l_update_index).fulfilled_quantity := l_update_table(l_update_index).ordered_quantity;
l_update_table(l_update_index).fulfilled_flag := 'Y';
l_update_table(l_update_index).fulfillment_date := l_update_table(l_update_index).actual_shipment_date;
l_update_table(l_update_index).temp_update_flag := 'Y';
l_update_index := l_update_table.NEXT(l_update_index);
l_update_index := l_update_table.FIRST;
WHILE l_update_index IS NOT NULL
LOOP
IF nvl(l_update_table(l_update_index).top_model_line_id,0) <> l_top_model_tbl(I) THEN
GOTO NEXT_LINE_4;
l_update_table(l_update_index).ship_set_id := l_ship_set_id;
l_update_table(l_update_index).temp_update_flag := 'Y';
l_update_index := l_update_table.NEXT(l_update_index);
l_update_index := l_update_table.FIRST;
WHILE l_update_index IS NOT NULL
LOOP
IF nvl(l_update_table(l_update_index).top_model_line_id,0) <> l_top_model_tbl(I) THEN
GOTO NEXT_LINE_5;
l_update_table(l_update_index).ship_set_id := Null;
l_update_table(l_update_index).temp_update_flag := 'Y';
l_update_index := l_update_table.NEXT(l_update_index);
l_update_index := l_update_table.FIRST;
WHILE l_update_index IS NOT NULL
LOOP
BEGIN
IF l_update_table(l_update_index).item_type_code = 'SERVICE' AND
l_update_table(l_update_index).service_reference_line_id IS NOT NULL THEN
l_service_reference_line_id := l_update_table(l_update_index).service_reference_line_id;
l_update_table(l_update_index).line_number := l_update_table(l_service_reference_line_id).line_number;
l_update_table(l_update_index).shipment_number := l_update_table(l_service_reference_line_id).shipment_number;
l_update_table(l_update_index).option_number := l_update_table(l_service_reference_line_id).option_number;
l_update_table(l_update_index).temp_update_flag := 'Y';
OE_UPG_SO_NEW.Upgrade_Insert_Errors(
G_HEADER_ID,
'FYI Only: Service Ref information not updated since parent line does not exist'
||to_char(l_update_index));
l_update_index := l_update_table.NEXT(l_update_index);
/* Update the lines */
l_update_index := l_update_table.FIRST;
WHILE l_update_index IS NOT NULL
LOOP
IF l_update_table(l_update_index).temp_update_flag = 'Y' THEN
UPDATE /*+ INDEX(OE_ORDER_LINES_U1) */ OE_ORDER_LINES_ALL
SET SHIPPED_QUANTITY = l_update_table(l_update_index).shipped_quantity,
FULFILLED_FLAG = l_update_table(l_update_index).fulfilled_flag,
FULFILLED_QUANTITY = l_update_table(l_update_index).fulfilled_quantity,
ACTUAL_SHIPMENT_DATE = l_update_table(l_update_index).actual_shipment_date,
FULFILLMENT_DATE = l_update_table(l_update_index).fulfillment_date,
LINE_NUMBER = l_update_table(l_update_index).line_number,
OPTION_NUMBER = l_update_table(l_update_index).option_number,
SHIPMENT_NUMBER = l_update_table(l_update_index).shipment_number,
COMPONENT_NUMBER = l_update_table(l_update_index).component_number,
MODEL_REMNANT_FLAG = l_update_table(l_update_index).model_remnant_flag,
SERVICE_REFERENCE_LINE_ID = l_update_table(l_update_index).service_reference_line_id,
SHIPPABLE_FLAG = l_update_table(l_update_index).shippable_flag,
UNIT_SELLING_PRICE = l_update_table(l_update_index).unit_selling_price,
UNIT_LIST_PRICE = l_update_table(l_update_index).unit_list_price,
ATO_LINE_ID = l_update_table(l_update_index).ato_line_id,
SHIP_SET_ID = l_update_table(l_update_index).ship_set_id
WHERE LINE_ID = l_update_index;
l_update_index := l_update_table.NEXT(l_update_index);
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => g_header_id,
L_comments => 'Update_After_Insert failed on ora error: '||to_char(sqlcode)
);
END Update_After_Insert;
select /*+ ORDERED USE_NL(sla1,sla2,upg,oeol)
INDEX (sla1 SO_LINES_N1)
INDEX (sla2 SO_LINES_N18)
INDEX (upg OE_UPGRADE_LOG_N1)
INDEX (oeol OE_ORDER_LINES_U1)
*/
sla1.header_id,
sla1.line_id,
sla1.line_number,
sla1.inventory_item_id,
sla1.unit_code
from
so_lines_all sla1, so_lines_all sla2, oe_upgrade_log upg,
oe_order_lines_all oeol
where sla1.header_id = G_HEADER_ID
and sla1.line_type_code = 'PARENT'
and sla1.item_type_code in ('KIT','MODEL','STANDARD')
and sla1.parent_line_id is null -- To filter out option lines
and sla1.parent_line_id is null -- To filter out option lines
and sla1.line_id = sla2.shipment_schedule_line_id
and sla2.line_id = upg.old_line_id
and upg.new_line_id = oeol.line_id
order by sla1.line_id;
SELECT /*+ ORDERED USE_NL(OOLA SLA UPG) INDEX(oola OE_ORDER_LINES_N1)
INDEX(upg OE_UPGRADE_LOG_N6) index(sla SO_LINES_U1) */
OOLA.LINE_ID,OOLA.LINE_NUMBER
FROM OE_ORDER_LINES_ALL OOLA,
OE_UPGRADE_LOG UPG ,
SO_LINES_ALL SLA
WHERE OOLA.HEADER_ID = G_HEADER_ID AND
OOLA.LINE_ID = UPG.NEW_LINE_ID AND
UPG.OLD_LINE_ID = SLA.LINE_ID AND
SLA.SHIPMENT_SCHEDULE_LINE_ID = v_line_id
ORDER BY OOLA.LINE_ID;
select
header_id,
line_id,
line_number,
inventory_item_id,
unit_code
from
so_lines_all sla
where sla.header_id = G_HEADER_ID
AND 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
and exists
(select /*+ INDEX(ln OE_ORDER_LINES_N1) */lg.old_line_id
from oe_upgrade_log_v lg, oe_order_lines_all ln
where ln.header_id = sla.header_id
and lg.old_line_id = sla.line_id
and 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 /*+ INDEX(oe_upgrade_log OE_UPGRADE_LOG_N1) */ --bug5909908
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_NEW.upgrade_insert_errors
( L_header_id => g_header_id,
L_comments => 'Line set updation failed on ora error: '||to_char(sqlcode)
);
PROCEDURE Insert_Row
( p_line_rec IN OE_Order_PUB.Line_Rec_Type,
p_orig_line_id IN Number,
p_upgraded_flag IN Varchar2 default 'Y',
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 /*+ INDEX(OE_ORDER_LINES_ALL OE_ORDER_LINES_N1) */ line_id,ordered_quantity,shipped_quantity
from oe_order_lines_all
where header_id = G_HEADER_ID
and ato_line_id=p_ato_line_id
and item_type_code = 'CONFIG';
select /*+ INDEX(OE_ORDER_LINES_ALL OE_ORDER_LINES_N1) */
ato_line_id
from oe_order_lines_all
where header_id = G_HEADER_ID
group by ato_line_id,item_type_code
having item_type_code = 'CONFIG'
and count(*) > 1;
select /*+ INDEX(OE_ORDER_LINES_ALL OE_ORDER_LINES_N1) */
line_id
from oe_order_lines_all
where header_id = G_HEADER_ID
and ato_line_id=p_ato_line_id
and item_type_code <> 'CONFIG'
order by component_code;
select /*+ INDEX(OE_ORDER_LINES_ALL OE_ORDER_LINES_N1) */
line_id
from oe_order_lines_all
where header_id = G_HEADER_ID
and service_reference_line_id = p_service_reference_line_id;
SELECT ordered_quantity
INTO l_orig_model_quantity
FROM oe_order_lines_all
WHERE line_id=p_ato_line_id;
UPDATE OE_ORDER_LINES_ALL
SET ordered_quantity = ordered_quantity/l_orig_model_quantity *
l_cfg_ordered_quantity,
shipped_quantity = ordered_quantity/l_orig_model_quantity *
l_cfg_shipped_quantity,
fulfilled_quantity = ordered_quantity/l_orig_model_quantity *
l_cfg_shipped_quantity,
fulfilled_flag = decode((ordered_quantity/l_orig_model_quantity *
l_cfg_shipped_quantity),null,'N','Y'),
fulfillment_date = decode((ordered_quantity/l_orig_model_quantity *
l_cfg_shipped_quantity),null,NULL,actual_shipment_date)
WHERE header_id=G_HEADER_ID
AND ato_line_id=p_ato_line_id
AND item_type_code <> 'CONFIG';
UPDATE OE_ORDER_LINES_ALL OOL
SET OOL.ORDERED_QUANTITY = (SELECT ORDERED_QUANTITY
FROM OE_ORDER_LINES_ALL OOL1
WHERE OOL1.LINE_ID = OOL.SERVICE_REFERENCE_LINE_ID)
WHERE OOL.HEADER_ID = G_HEADER_ID
AND OOL.ITEM_TYPE_CODE = 'SERVICE';
SELECT ordered_quantity
INTO l_orig_model_quantity
FROM oe_order_lines_all
WHERE line_id=p_ato_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);
-- 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.top_model_line_id,
link_to_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);
SELECT oe_order_lines_s.nextval
INTO l_new_line_rec.line_id
FROM dual;
INSERT_ROW(l_new_line_rec,l_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);
/* Update LINK_TO_LINE_ID for all the new classes and options
created */
UPDATE OE_ORDER_LINES_ALL OEOPT
SET LINK_TO_LINE_ID = (
SELECT OELNK.LINE_ID
FROM OE_ORDER_LINES_ALL OELNK
WHERE( OELNK.LINE_ID = OEOPT.TOP_MODEL_LINE_ID
OR OELNK.TOP_MODEL_LINE_ID = OEOPT.TOP_MODEL_LINE_ID )
AND OELNK.COMPONENT_CODE =
SUBSTR( OEOPT.COMPONENT_CODE,
1, LENGTH( RTRIM( OEOPT.COMPONENT_CODE,
'0123456789' ) ) - 1 )
AND OELNK.ATO_LINE_ID = l_model_rec.line_id)
WHERE HEADER_ID = l_model_rec.header_id
AND ATO_LINE_ID = l_model_rec.line_id
AND ITEM_TYPE_CODE <> 'SERVICE';
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => g_header_id,
L_comments => 'Exception insert_multiple_models: '
||'Error code -'
||to_char(v_error_code)
);
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
, UPGRADED_FLAG
, 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 default 'Y',
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
, LOCK_CONTROL
)
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
, p_line_rec.upgraded_flag
, 1
);
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,
reference_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,
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,
reference_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,
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_NEW.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/*+ INDEX(l1 OE_ORDER_LINES_N1) INDEX(l2 OE_ORDER_LINES_U1) */ 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 nvl(l1.open_flag,'-') = 'Y'
and l1.header_id = p_header_id;
Insert_Return_Included_Items(l_line_id,'Process_Returns');
OE_UPG_SO_NEW.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)
);
Update so_headers_all
set upgrade_flag = 'X'
where header_id = p_header_id;
Select /*+ INDEX(l1 OE_ORDER_LINES_N1) INDEX(l2 OE_ORDER_LINES_U1) */ l1.header_id
from oe_order_lines_all l1, oe_order_lines_all l2
where l1.header_id = p_header_id
and l1.reference_line_id = l2.line_id
and nvl(l1.open_flag,'-') = 'Y'
and l2.item_type_code in ('MODEL','CLASS','KIT')
and l1.line_category_code = 'RETURN';
Select /*+ INDEX(l1 OE_ORDER_LINES_N1) INDEX(l2 OE_ORDER_LINES_U1) */
l1.line_id,l2.item_type_code,l2.line_id ref_line_id,l2.header_id ref_header_id
from oe_order_lines_all l1, oe_order_lines_all l2
where l1.reference_line_id = l2.line_id
and l1.inventory_item_id = l2.inventory_item_id
and nvl(l1.open_flag,'-') = 'Y'
and l2.item_type_code in ('MODEL','CLASS')
and l2.ato_line_id = l2.line_id
and l1.header_id = l_header_id;
Select /*+ INDEX(OE_ORDER_LINES_N1) */ line_id
from oe_order_lines_all
where ato_line_id = l_ref_line_id
and header_id = l_ref_header_id
and item_type_code = 'CONFIG';
Select /*+ INDEX(OE_ORDER_LINES_N1) */ line_id
from oe_order_lines_all
where item_type_code in ('CLASS','CONFIG','OPTION')
and header_id = l_ref_header_id
and ato_line_id = l_ref_line_id
and ato_line_id <> line_id;
Select /*+ INDEX(OE_ORDER_LINES_N1) */ line_id
from oe_order_lines_all
where reference_line_id = l_fulfillment_ref_line_id
and header_id = l_header_id;
Select /*+ INDEX(l1 OE_ORDER_LINES_N1) INDEX(l2 OE_ORDER_LINES_U1) */
l1.line_id,l2.item_type_code,l2.line_id ref_line_id,l2.header_id ref_header_id
from oe_order_lines_all l1, oe_order_lines_all l2
where l1.reference_line_id = l2.line_id
and l1.inventory_item_id = l2.inventory_item_id
and l2.item_type_code in ('MODEL','CLASS','KIT')
and l2.ato_line_id is null
and nvl(l1.open_flag,'-') = 'Y'
and l1.header_id = l_header_id;
Select /*+ INDEX(OE_ORDER_LINES_N1) */
line_id,inventory_item_id
from oe_order_lines_all
where item_type_code in ('INCLUDED')
and header_id = l_ref_header_id
and link_to_line_id = l_ref_line_id;
Select /*+ INDEX(OE_ORDER_LINES_N1) */
line_id
from oe_order_lines_all
where reference_line_id = l_fulfillment_ref_line_id
and header_id = l_header_id;
Update /*+ INDEX(OE_ORDER_LINES_ALL OE_ORDER_LINES_N1) */
oe_order_lines_all
set reference_line_id = l_config_line_id,
ordered_item_id = inventory_item_id,
unit_list_price = null,
unit_selling_price = null
where reference_line_id = l_ref_line_id
and line_id <> l_line_id
and header_id = l_header_id;
delete from oe_price_adjustments
where line_id in (select line_id
from oe_order_lines_all
where reference_line_id = l_config_line_id);
select oe_sets_s.nextval into l_fulfillment_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_fulfillment_set_id, to_char(l_fulfillment_set_id),
'FULFILLMENT_SET',l_header_id,null,null, null,null,null,
null,null,null, 0,sysdate,0, sysdate,
0,null,null,null,null,null
);
Insert into oe_line_sets( Line_id, Set_id, SYSTEM_REQUIRED_FLAG)
Values (l_line_id, l_fulfillment_set_id, 'Y');
Insert into oe_line_sets( Line_id, Set_id, SYSTEM_REQUIRED_FLAG)
Values (l_rec21.line_id, l_fulfillment_set_id, 'Y');
Update /*+ INDEX(OE_ORDER_LINES_ALL OE_ORDER_LINES_N1) */
oe_order_lines_all
set reference_line_id = l_rec1_1.line_id
where reference_line_id = l_ref_line_id
and inventory_item_id = l_rec1_1.inventory_item_id
and line_id <> l_line_id
and header_id = l_header_id;
select oe_sets_s.nextval into l_fulfillment_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_fulfillment_set_id, to_char(l_fulfillment_set_id),
'FULFILLMENT_SET',l_header_id,null,null, null,null,null,
null,null,null, 0,sysdate,0, sysdate,
0,null,null,null,null,null
);
Insert into oe_line_sets( Line_id, Set_id, SYSTEM_REQUIRED_FLAG)
Values (l_line_id, l_fulfillment_set_id, 'Y');
Insert into oe_line_sets( Line_id, Set_id, SYSTEM_REQUIRED_FLAG)
Values (l_rec21.line_id, l_fulfillment_set_id, 'Y');
OE_UPG_SO_NEW.upgrade_insert_errors
( L_header_id => l_header_id,
L_comments => 'Exception return_fulfillment_sets: '
||'Error code -'
||to_char(v_error_code)
||' - Line id '||to_char(l_line_id)
);
select line_id from oe_order_lines_all
where item_type_code <> 'SERVICE';
select line_id , service_parent_line_id
from so_lines_all
where service_parent_line_id = p_serviceable_line_id;
select old_line_id, new_line_id from
oe_upgrade_log_v where
old_line_id = p_service_parent_line_id
and old_line_id <> new_line_id
order by new_line_id;
select old_line_id, new_line_id from
oe_upgrade_log_v where
old_line_id = p_line_id
and old_line_id <> new_line_id
order by new_line_id;
update oe_order_lines_all
set service_reference_line_id = l_new_line_id1
where line_id = l_new_line_id2;
Select pricing_attribute11,pricing_attribute12,
pricing_attribute13,pricing_attribute14,pricing_attribute15
INTO g_line_rec.pricing_attribute11,
g_line_rec.pricing_attribute12,
g_line_rec.pricing_attribute13,
g_line_rec.pricing_attribute14,
g_line_rec.pricing_attribute15
From oe_order_price_attribs
Where line_id = p_line_id
and header_id = p_header_id;