[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT constraint_type , message
FROM cz_config_messages
WHERE config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr;
l_option CZ_BATCH_VALIDATE.INPUT_SELECTION;
l_db_options_tbl OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE;
oe_debug_pub.add('OUT OF NEWLY INSERTED OPTIONS LOOP' , 2 );
l_html_pieces.DELETE;
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
l_selection_line_id NUMBER;
OE_Config_Pvt.Delete_Config
( p_config_hdr_id => l_config_header_id
,p_config_rev_nbr => l_config_rev_nbr
,x_return_status => l_return_status_del);
SELECT l.line_id,
l.top_model_line_ref,
l.config_header_id,
l.config_rev_nbr,
nvl(l.configuration_id, z.CONFIG_ITEM_ID) configuration_id,
l.orig_sys_document_ref,
l.orig_sys_line_ref,
l.orig_sys_shipment_ref,
l.order_source_id,
l.ordered_quantity,
l.inventory_item_id,
h.header_id,
nvl(h.booked_flag,decode(a.order_source_id,NULL,'N','Y')) booked_flag,
decode(l.ordered_quantity, nvl(z.quantity, l.ordered_quantity),'Y','N')
cz_qty_match_flag,
nvl(l.component_code, z.component_code) component_code
FROM OE_HEADERS_IFACE_ALL H,
OE_LINES_IFACE_ALL L ,
CZ_CONFIG_DETAILS_V Z,
OE_ACTIONS_INTERFACE a
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND nvl(h.error_flag,'N') = 'N'
AND nvl(l.error_flag,'N') = 'N'
AND nvl(l.rejected_flag,'N') = 'N'
AND l.item_type_code = 'MODEL'
AND l.top_model_line_ref = l.orig_sys_line_ref
AND nvl(l.config_header_id,-1) = z.config_hdr_id (+)
AND nvl(l.config_rev_nbr, -1) = z.config_rev_nbr(+)
-- AND NVL(l.configuration_id,-1) = z.config_item_id(+)
AND NVL(l.component_code, '-1') = z.component_code(+)
AND a.order_source_id(+) = h.order_source_id
AND a.orig_sys_document_ref(+) = h.orig_sys_document_ref
AND a.operation_code(+) = 'BOOK_ORDER';
SELECT l.line_id,
h.header_id,
l.top_model_line_ref,
l.config_header_id,
l.config_rev_nbr,
l.configuration_id,
l.orig_sys_document_ref,
l.orig_sys_line_ref,
l.orig_sys_shipment_ref,
l.order_source_id,
l.ordered_quantity
FROM OE_HEADERS_IFACE_ALL H,
OE_LINES_IFACE_ALL L
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND nvl(h.error_flag,'N') = 'N'
AND nvl(l.error_flag,'N') = 'N'
AND nvl(l.rejected_flag,'N') = 'N'
AND l.item_type_code = 'MODEL'
AND l.top_model_line_ref = l.orig_sys_line_ref;
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
-- insert missing child lines
INSERT INTO oe_config_details_tmp
(
LINE_ID ,
TOP_MODEL_LINE_ID,
ATO_LINE_ID,
LINK_TO_LINE_ID,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF ,
ORIG_SYS_SHIPMENT_REF ,
TOP_MODEL_LINE_REF ,
INVENTORY_ITEM_ID,
--ORDERED_ITEM,
UOM_CODE,
ORDERED_QUANTITY,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
SORT_ORDER ,
CONFIG_HEADER_ID,
CONFIG_REV_NBR ,
CONFIGURATION_ID,
TOP_BILL_SEQUENCE_ID,
ITEM_TYPE_CODE,
LINE_TYPE,
CZ_QTY_MATCH_FLAG,
HIGH_QUANTITY,
LOW_QUANTITY,
MUTUALLY_EXCLUSIVE_OPTIONS,
BOM_ITEM_TYPE,
LOCK_CONTROL,
REPLENISH_TO_ORDER_FLAG )
SELECT
oe_order_lines_s.nextval Line_id,
l_line_rec.line_id(I) top_model_line_id,
decode(z.config_item_id, z.ato_config_item_id, oe_order_lines_s.currval,NULL)
ato_line_id,
decode(z.config_item_id, z.ato_config_item_id,l_line_rec.line_id(I),NULL)
link_to_line_id,
l_line_rec.order_source_id(I) order_source_id,
l_line_rec.orig_sys_document_ref(I) orig_sys_document_ref,
'OE_ORDER_LINES_ALL'||oe_order_lines_s.currval orig_sys_line_ref,
null orig_sys_shipment_ref,
l_line_rec.orig_sys_line_ref(I) top_model_line_ref,
z.inventory_item_id,
-- NULL ordered_item,
z.uom_code,
z.quantity ordered_quantity,
z.Component_code,
z.Component_sequence_id,
z.Bom_Sort_order,
z.config_hdr_id,
z.Config_rev_nbr,
z.config_item_id Configuration_id,
null top_bill_sequence_id,
null item_type_code,
z.line_type,
'Y' cz_qty_match_flag,
null HIGH_QUANTITY,
null LOW_QUANTITY,
null MUTUALLY_EXCLUSIVE_OPTIONS,
null BOM_ITEM_TYPE,
null LOCK_CONTROL,
null REPLENISH_TO_ORDER_FLAG
FROM cz_config_details_v z
WHERE z.config_hdr_id = l_line_rec.config_header_id(I)
AND z.config_rev_nbr = l_line_rec.config_rev_nbr(I)
AND NOT EXISTS (
Select l.line_id
from oe_lines_iface_all l
WHERE NVL(l.configuration_id, z.config_item_id) = z.config_item_id
AND NVL(l.component_code, z.component_code) = z.component_code
AND l.top_model_line_ref = l_line_rec.ORIG_SYS_LINE_REF(I)
AND l.orig_sys_document_ref = l_line_rec.ORIG_SYS_DOCUMENT_REF(I)
AND l.order_source_id = l_line_rec.ORDER_SOURCE_ID(I));
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' MISSING CHILD TO TMP'||I , 3 ) ;
-- insert lines from interface table
INSERT INTO oe_config_details_tmp
(
LINE_ID ,
TOP_MODEL_LINE_ID,
ATO_LINE_ID,
LINK_TO_LINE_ID,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF ,
ORIG_SYS_SHIPMENT_REF ,
TOP_MODEL_LINE_REF ,
INVENTORY_ITEM_ID,
-- ORDERED_ITEM,
UOM_CODE,
ORDERED_QUANTITY,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
SORT_ORDER ,
CONFIG_HEADER_ID,
CONFIG_REV_NBR ,
CONFIGURATION_ID,
TOP_BILL_SEQUENCE_ID,
ITEM_TYPE_CODE,
LINE_TYPE,
CZ_QTY_MATCH_FLAG,
HIGH_QUANTITY,
LOW_QUANTITY,
MUTUALLY_EXCLUSIVE_OPTIONS,
BOM_ITEM_TYPE,
LOCK_CONTROL,
REPLENISH_TO_ORDER_FLAG )
SELECT
l.Line_id,
l_line_rec.line_id(I) top_model_line_id,
decode(z.config_item_id, z.ato_config_item_id,l.line_id,NULL)
ato_line_id,
decode(z.config_item_id, z.ato_config_item_id,l_line_rec.line_id(I),NULL)
link_to_line_id,
l_line_rec.order_source_id(I) order_source_id,
l_line_rec.orig_sys_document_ref(I) orig_sys_document_ref,
l_line_rec.orig_sys_line_ref(I) orig_sys_line_ref,
null orig_sys_shipment_ref,
l.top_model_line_ref,
z.inventory_item_id,
-- l_line_rec.ordered_item(I) ordered_item,
z.uom_code,
z.quantity ordered_quantity,
z.Component_code,
z.Component_sequence_id,
z.Bom_Sort_order,
z.config_hdr_id,
z.Config_rev_nbr,
z.config_item_id Configuration_id,
null top_bill_sequence_id,
l.item_type_code,
z.line_type,
decode(l.ordered_quantity, z.quantity,'Y','N')
cz_qty_match_flag,
null HIGH_QUANTITY,
null LOW_QUANTITY,
null MUTUALLY_EXCLUSIVE_OPTIONS,
null BOM_ITEM_TYPE,
l_line_rec.lock_control(I) LOCK_CONTROL,
null REPLENISH_TO_ORDER_FLAG
FROM cz_config_details_v z, oe_lines_iface_all l
WHERE z.config_hdr_id = l_line_rec.config_header_id(I)
AND z.config_rev_nbr = l_line_rec.config_rev_nbr(I)
AND NVL(l.configuration_id,z.config_item_id) = z.config_item_id
AND NVL(l.component_code, z.component_code) = z.component_code
AND l.orig_sys_document_ref = l_line_rec.ORIG_SYS_DOCUMENT_REF(I)
AND l.order_source_id = l_line_rec.ORDER_SOURCE_ID(I)
AND l.top_model_line_ref = l_line_rec.ORIG_SYS_LINE_REF(I) ;
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' LINES TO TMP FROM IFACE '||I , 3 ) ;
Select l.line_id,
l.Top_model_line_REF,
null Top_model_line_id,
l.Component_code,
l.Component_sequence_id,
l.sort_order,
l.config_header_id,
l.config_rev_nbr,
l.configuration_id,
l.item_type_code,
l.order_source_id,
l.orig_sys_document_ref,
l.orig_sys_line_ref,
l.orig_sys_shipment_ref,
l.ordered_quantity,
-- l.top_bill_sequence_id, -- Donot need this column
l.inventory_item_id,
l.customer_item_id_type,
l.customer_item_id,
l.customer_item_name,
l.sold_to_org_id,
l.ship_from_org_id,
h.header_id,
nvl(h.booked_flag,decode(a.order_source_id,NULL,'N','Y')) booked_flag,
'N' cz_qty_match_flag
FROM OE_HEADERS_IFACE_ALL H,
OE_LINES_IFACE_ALL L,
OE_ACTIONS_INTERFACE a
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND nvl(h.error_flag,'N') = 'N'
AND nvl(l.error_flag,'N') = 'N'
AND nvl(l.rejected_flag,'N') = 'N'
AND l.top_model_line_ref IS NOT NULL
AND l.config_header_id IS NULL
AND l.config_rev_nbr IS NULL
AND l.configuration_id IS NULL
AND a.order_source_id(+) = h.order_source_id
AND a.orig_sys_document_ref(+) = h.orig_sys_document_ref
AND a.operation_code(+) = 'BOOK_ORDER'
ORDER BY h.header_id,
l.top_model_line_ref,
decode(item_type_code,'MODEL',item_type_code,'XXX'),
l.component_code;
Select l.line_id,
h.header_id,
l.Top_model_line_REF,
null Top_model_line_id,
l.Component_code,
l.Component_sequence_id,
l.sort_order,
l.config_header_id,
l.config_rev_nbr,
l.configuration_id,
l.item_type_code,
l.order_source_id,
l.orig_sys_document_ref,
l.orig_sys_line_ref,
l.orig_sys_shipment_ref,
l.ordered_quantity,
-- l.top_bill_sequence_id,
l.inventory_item_id,
l.customer_item_id_type,
l.customer_item_id,
l.customer_item_name,
l.sold_to_org_id,
l.ship_from_org_id
FROM OE_HEADERS_IFACE_ALL H,
OE_LINES_IFACE_ALL L
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND nvl(h.error_flag,'N') = 'N'
AND nvl(l.error_flag,'N') = 'N'
AND nvl(l.rejected_flag,'N') = 'N'
AND top_model_line_ref IS NOT NULL
ORDER BY h.header_id,
l.top_model_line_ref,
decode(item_type_code,'MODEL',item_type_code,'XXX'),
l.component_code;
SELECT bill_sequence_id,
component_code,
component_sequence_id,
sort_order,
primary_uom_code,
high_quantity,
low_quantity,
mutually_exclusive_options,
bom_item_type,
replenish_to_order_flag
INTO l_line_rec.Top_Bill_Sequence_Id(I),
l_line_rec.component_code(I),
l_line_rec.component_sequence_id(I),
l_line_rec.sort_order(I),
l_line_rec.order_quantity_uom(I),
l_config_rec.high_quantity(I),
l_config_rec.low_quantity(I),
l_config_rec.mutually_exclusive_options(I),
l_config_rec.bom_item_type(I),
l_config_rec.replenish_to_order_flag(I)
FROM bom_explosions
WHERE COMPONENT_ITEM_ID = l_line_rec.inventory_item_id(I)
AND ORGANIZATION_ID = OE_BULK_ORDER_PVT.G_ITEM_ORG
AND PLAN_LEVEL = 0
AND nvl(effectivity_date, sysdate) <= sysdate
AND nvl(disable_date, sysdate+1) > sysdate
AND explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS;
SELECT component_code,
component_sequence_id,
sort_order,
primary_uom_code,
high_quantity,
low_quantity,
mutually_exclusive_options,
bom_item_type,
replenish_to_order_flag
INTO l_line_rec.component_code(I),
l_line_rec.component_sequence_id(I),
l_line_rec.sort_order(I),
l_line_rec.order_quantity_uom(I),
l_config_rec.high_quantity(I),
l_config_rec.low_quantity(I),
l_config_rec.mutually_exclusive_options(I),
l_config_rec.bom_item_type(I),
l_config_rec.replenish_to_order_flag(I)
FROM bom_explosions
WHERE component_item_id = l_line_rec.inventory_item_id(I)
AND explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS
AND top_bill_sequence_id = l_line_rec.top_bill_sequence_id(I)
AND plan_level > 0
AND nvl(effectivity_date, sysdate) <= sysdate
AND nvl(disable_date, sysdate+1) > sysdate
AND organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG
AND component_code = NVL(l_line_rec.component_code(I), component_code);
oe_debug_pub.add('SELECT COMP_CODE FAILED , NO DATA FOUND ' , 1);
oe_debug_pub.add('SELECT COMP_CODE FAILED , TOO_MANY ROWS ' , 1);
oe_debug_pub.add('SELECT COMP_CODE FAILED , OTHERS ' , 1);
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
INSERT INTO oe_config_details_tmp
(
LINE_ID ,
TOP_MODEL_LINE_ID,
ATO_LINE_ID,
LINK_TO_LINE_ID,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF ,
ORIG_SYS_SHIPMENT_REF ,
TOP_MODEL_LINE_REF ,
INVENTORY_ITEM_ID,
ORDERED_ITEM,
UOM_CODE,
ORDERED_QUANTITY,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
SORT_ORDER ,
CONFIG_HEADER_ID,
CONFIG_REV_NBR ,
CONFIGURATION_ID,
TOP_BILL_SEQUENCE_ID,
ITEM_TYPE_CODE,
--LINE_TYPE,
--CZ_QTY_MATCH_FLAG,
HIGH_QUANTITY,
LOW_QUANTITY,
MUTUALLY_EXCLUSIVE_OPTIONS,
BOM_ITEM_TYPE,
LOCK_CONTROL,
REPLENISH_TO_ORDER_FLAG
)
VALUES
(
l_line_rec.line_id(I),
l_line_rec.top_model_line_id(I),
l_line_rec.ato_line_id(I),
l_line_rec.link_to_line_id(I),
l_line_rec.order_source_id(I),
l_line_rec.orig_sys_document_ref(I),
l_line_rec.orig_sys_line_ref(I),
l_line_rec.orig_sys_shipment_ref(I),
l_line_rec.Top_model_line_REF(I),
l_line_rec.inventory_item_id(I),
l_line_rec.ordered_item(I),
l_line_rec.order_quantity_uom(I),
l_line_rec.ordered_quantity(I),
l_line_rec.Component_code(I),
l_line_rec.Component_sequence_id(I),
l_line_rec.sort_order(I),
l_line_rec.config_header_id(I),
l_line_rec.config_rev_nbr(I),
l_line_rec.configuration_id(I),
l_line_rec.top_bill_sequence_id(I),
l_line_rec.item_type_code(I),
l_config_rec.high_quantity(I),
l_config_rec.low_quantity(I),
l_config_rec.mutually_exclusive_options(I),
l_config_rec.bom_item_type(I),
l_line_rec.lock_control(I),
l_config_rec.replenish_to_order_flag(I)
);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' TO TMP FROM LINE_REC' , 3 ) ;
INSERT INTO oe_config_details_tmp
(
LINE_ID ,
TOP_MODEL_LINE_ID,
ATO_LINE_ID,
LINK_TO_LINE_ID,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF ,
--ORIG_SYS_SHIPMENT_REF ,
TOP_MODEL_LINE_REF ,
INVENTORY_ITEM_ID,
ORDERED_ITEM,
UOM_CODE,
ORDERED_QUANTITY,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
SORT_ORDER ,
--CONFIG_HEADER_ID,
--CONFIG_REV_NBR ,
--CONFIGURATION_ID,
TOP_BILL_SEQUENCE_ID,
ITEM_TYPE_CODE,
--LINE_TYPE,
--CZ_QTY_MATCH_FLAG,
HIGH_QUANTITY,
LOW_QUANTITY,
MUTUALLY_EXCLUSIVE_OPTIONS,
BOM_ITEM_TYPE,
LOCK_CONTROL,
REPLENISH_TO_ORDER_FLAG
)
SELECT
oe_order_lines_s.nextval Line_id,
L.top_model_line_id top_model_line_id,
L.ato_line_id ato_line_id,
NULL link_to_line_id,
l.order_source_id,
l.orig_sys_document_ref orig_sys_document_ref,
'OE_ORDER_LINES_ALL'||oe_order_lines_s.currval orig_sys_line_ref,
l.orig_sys_line_ref top_model_line_ref,
b.component_item_id,
NULL ordered_item,
b.primary_uom_code,
b.EXTENDED_QUANTITY * l.ordered_quantity,
b.Component_code,
b.Component_sequence_id,
b.Sort_order,
l.top_bill_sequence_id,
'CLASS' item_type_code,
b.high_quantity,
b.low_quantity,
b.mutually_exclusive_options,
b.bom_item_type,
null LOCK_CONTROL,
b.replenish_to_order_flag
FROM BOM_EXPLOSIONS b,
oe_config_details_tmp L
WHERE b.top_bill_sequence_id = L.top_bill_sequence_id
AND L.item_type_code = 'MODEL'
AND L.line_id = L.top_model_line_id
AND nvl(L.lock_control, 0) <> -99
AND b.explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS
AND b.plan_level > 0
AND nvl(b.effectivity_date, sysdate) <= sysdate
AND nvl(b.disable_date, sysdate + 1) > sysdate
AND b.component_sequence_id <> b.top_bill_sequence_id -- Exclude Model Lines
AND b.component_code NOT IN (
SELECT l2.component_code
FROM oe_config_details_tmp l2
WHERE l2.top_model_line_id = L.top_model_line_id )
AND EXISTS (
SELECT l3.line_id
FROM oe_config_details_tmp l3
WHERE
instr(l3.component_code, b.component_code, 1) = 1
AND l3.top_model_line_id = L.top_model_line_id
AND l3.item_type_code <> 'MODEL'
AND l3.bom_item_type = 4);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' MISSING CLASSES TO TMP' , 3 ) ;
INSERT INTO oe_config_details_tmp
(
LINE_ID ,
TOP_MODEL_LINE_ID,
ATO_LINE_ID,
LINK_TO_LINE_ID,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF ,
ORIG_SYS_SHIPMENT_REF ,
TOP_MODEL_LINE_REF ,
INVENTORY_ITEM_ID,
UOM_CODE,
ORDERED_QUANTITY,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
SORT_ORDER ,
CONFIG_HEADER_ID,
CONFIG_REV_NBR ,
CONFIGURATION_ID,
--TOP_BILL_SEQUENCE_ID,
ITEM_TYPE_CODE,
LINE_TYPE,
--CZ_QTY_MATCH_FLAG,
--HIGH_QUANTITY,
--LOW_QUANTITY,
--MUTUALLY_EXCLUSIVE_OPTIONS,
--BOM_ITEM_TYPE,
LOCK_CONTROL
--REPLENISH_TO_ORDER_FLAG
)
SELECT
l_line_rec.line_id(I),
l_line_rec.top_model_line_id(I),
NVL(l_line_rec.ato_line_id(I),
decode(z.config_item_id, z.ato_config_item_id,
l_line_rec.line_id(I),NULL)) ato_line_id,
NULL link_to_line_id,
l_line_rec.order_source_id(I),
l_line_rec.orig_sys_document_ref(I),
l_line_rec.orig_sys_line_ref(I),
l_line_rec.orig_sys_shipment_ref(I),
l_line_rec.Top_model_line_REF(I),
z.inventory_item_id,
z.uom_code,
z.quantity,
l_line_rec.Component_code(I),
l_line_rec.Component_sequence_id(I),
z.bom_sort_order, -- l_line_rec.sort_order(I),
z.config_hdr_id,
z.config_rev_nbr,
z.config_item_id,
l_line_rec.item_type_code(I),
z.line_type,
--high_quantity(I)
--low_quantity(I)
--mutually_exclusive_options(I)
--bom_item_type(I)
l_line_rec.lock_control(I)
FROM CZ_CONFIG_DETAILS_V z
WHERE z.config_hdr_id = l_line_rec.config_header_id(I)
AND z.config_rev_nbr = l_line_rec.config_rev_nbr(I)
AND z.component_code = l_line_rec.component_code(I);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' TO TMP FROM LINE_REC' , 3 ) ;
INSERT INTO oe_config_details_tmp
(
LINE_ID ,
TOP_MODEL_LINE_ID,
ATO_LINE_ID,
LINK_TO_LINE_ID,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF ,
-- ORIG_SYS_SHIPMENT_REF ,
TOP_MODEL_LINE_REF ,
INVENTORY_ITEM_ID,
UOM_CODE,
ORDERED_QUANTITY,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
SORT_ORDER ,
CONFIG_HEADER_ID,
CONFIG_REV_NBR ,
CONFIGURATION_ID,
TOP_BILL_SEQUENCE_ID,
ITEM_TYPE_CODE,
--LINE_TYPE,
--CZ_QTY_MATCH_FLAG,
--HIGH_QUANTITY,
--LOW_QUANTITY,
--MUTUALLY_EXCLUSIVE_OPTIONS,
BOM_ITEM_TYPE,
LOCK_CONTROL
--REPLENISH_TO_ORDER_FLAG
)
SELECT
oe_order_lines_s.nextval Line_id,
L.top_model_line_id top_model_line_id,
decode(z.config_item_id, z.ato_config_item_id,
oe_order_lines_s.currval, NULL) ato_line_id ,
NULL link_to_line_id,
L.order_source_id order_source_id,
L.orig_sys_document_ref orig_sys_document_ref,
'OE_ORDER_LINES_ALL'||oe_order_lines_s.currval orig_sys_line_ref,
L.orig_sys_line_ref top_model_line_ref,
z.inventory_item_id,
z.uom_code,
z.quantity,
z.Component_code,
z.Component_sequence_id,
z.bom_sort_order,
z.config_hdr_id,
z.config_rev_nbr,
z.config_item_id,
L.top_bill_sequence_id,
'CLASS', -- In new configuration, only CLASS can be missing
z.bom_item_type,
null LOCK_CONTROL
FROM CZ_CONFIG_DETAILS_V z,
oe_config_details_tmp L
WHERE L.item_type_code = 'MODEL'
AND L.line_id = L.top_model_line_id
AND nvl(L.lock_control, 0) <> -99
AND L.config_header_id = z.config_hdr_id
AND L.config_rev_nbr = z.config_rev_nbr
AND z.config_item_id NOT IN
(Select configuration_id
FROM oe_config_details_tmp L2
WHERE L2.top_model_line_id = L.line_id);
oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' TO TMP FROM CZ' , 3 ) ;
oe_debug_pub.add( 'BEFORE update TMP for Config ') ;
update oe_config_details_tmp L
set ato_line_id =
( select ato_line_id
from oe_config_details_tmp L1
where L1.ato_line_id IS NOT NULL
AND L1.top_model_line_id = L.top_model_line_id
AND INSTR(L1.component_code,'-',1,2) = 0
AND L1.component_code = decode( L1.item_type_code, 'MODEL',
substr(L.component_code,1, instr(L.component_code,'-',1,1)-1),
substr(L.component_code,1, instr(L.component_code,'-',1,2)-1))
AND ROWNUM = 1 )
Where line_id <> top_model_line_id
And ato_line_id is NULL;
oe_debug_pub.add( 'AFTER update TMP for Config ') ;
oe_debug_pub.add( 'BEFORE update TMP for Bom ') ;
UPDATE oe_config_details_tmp L
SET ato_line_id=
( SELECT L1.line_id
FROM oe_config_details_tmp L1
WHERE L.top_model_line_id = L1.top_model_line_id
AND L1.top_model_line_id <> L1.line_id
AND L1.bom_item_type = 1
AND L1.replenish_to_order_flag = 'Y'
AND L1.component_code = SUBSTR( L.component_code, 1, LENGTH( L1.component_code ))
AND L1.component_code =
( SELECT MIN( L2.component_code )
FROM oe_config_details_tmp L2
WHERE L2.top_model_line_id = L.top_model_line_id
AND L2.component_code = SUBSTR( L.component_code, 1,
LENGTH( L2.component_code ))
AND L2.bom_item_type = 1
AND L2.replenish_to_order_flag = 'Y')
AND ((SUBSTR(L.component_code, LENGTH(L1.component_code) + 1, 1) = '-' OR
SUBSTR(L.component_code, LENGTH(L1.component_code) + 1, 1) is NULL)))
WHERE L.top_model_line_id <> line_id
AND NOT (item_type_code = 'OPTION' AND
ato_line_id = line_id AND
ato_line_id is not null)
AND L.ato_line_id IS NULL;
oe_debug_pub.add( 'AFTER update TMP for Bom ') ;
oe_debug_pub.add( 'BEFORE update link_to_line_id ') ;
update oe_config_details_tmp L
SET link_to_line_id = (select line_id
from oe_config_details_tmp L2
where L2.component_code = substr(L.component_code,
1,instr(L.component_code,'-',-1,1)-1)
and L2.top_model_line_id = L.top_model_line_id)
where line_id <> top_model_line_id
and link_to_line_id is NULL;
oe_debug_pub.add( 'AFTER update link_to_line_id ') ;
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
PROCEDURE Delete_Configurations
( p_error_rec IN OE_BULK_ORDER_PVT.INVALID_HDR_REC_TYPE
,x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_configs( p_orig_sys_document_ref VARCHAR2, p_order_source_id NUMBER)
IS
select config_header_id,
config_rev_nbr,
orig_sys_document_ref,
order_source_id
from OE_CONFIG_DETAILS_TMP
where orig_sys_document_ref = p_orig_sys_document_ref
and order_source_id = p_order_source_id
and item_type_code = 'MODEL';
oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Delete_Configurations ') ;
FOR l_delete_rec IN c_configs(P_ERROR_REC.orig_sys_document_ref(I),
P_ERROR_REC.order_source_id(I)) LOOP
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'DOC_REF: ' || l_delete_rec.orig_sys_document_ref ) ;
oe_debug_pub.add( 'SOURCE: ' || l_delete_rec.order_source_id ) ;
oe_debug_pub.add( 'CHI: ' || l_delete_rec.config_header_id ) ;
oe_debug_pub.add( 'CRN: ' || l_delete_rec.config_rev_nbr ) ;
OE_Config_Pvt.Delete_Config
( p_config_hdr_id => l_delete_rec.config_header_id
,p_config_rev_nbr => l_delete_rec.config_rev_nbr
,x_return_status => x_return_status);
oe_debug_pub.add( 'CONFIG DELETED WITH SUCCESS' ) ;
IF l_delete_rec.config_header_id is not null AND
l_delete_rec.config_rev_nbr is not null THEN
CZ_CF_API.Delete_Configuration
( config_hdr_id => l_delete_rec.config_header_id
,config_rev_nbr => l_delete_rec.config_rev_nbr
,usage_exists => l_usage_exists
,error_message => l_error_message
,return_value => l_return_value );
oe_debug_pub.add('Error from CZ delete: ' ||l_error_message ) ;
oe_debug_pub.add( 'CONFIG DELETED WITH SUCCESS' ) ;
oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Delete_Configurations ') ;
oe_debug_pub.add('Others Error, OE_BULK_CONFIG_UTIL.Delete_Configurations');
'Delete_Configurations'
);
END Delete_Configurations;