[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insertion into BCOL
|
| 01-22-2004 Kiran Konada
| bugfix 3391383
| config_orgs attribute from model is inserted into bcol_gt
| bug was we tried to get for config_item_id
|
| 02-23-2004 Kiran Konada
| bugfix 3259017
|
| 05-17-2004 Kiran Konada
| bugfix 3555026
| --null value in config_orgs should be treated as
| based on sourcing
| --When ATP passes null in ship_from_org_id, we should
| NOT default to any other organization
| AS that org could be a ware house on SO pad
| during intial scheduling and hence bcol could have
| the data AND would create a problem in re-use,
| as configitem is reused if ware house is same
| before and after re-scheduling
-------------------------------------------------------------------------------
*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_GOP_INTERFACE_PK';
p_match_rec_of_tab.CONFIG_ITEM_ID.delete;
p_match_rec_of_tab.LINE_ID.delete;
p_match_rec_of_tab.LINK_TO_LINE_ID.delete;
p_match_rec_of_tab.ATO_LINE_ID.delete;
p_match_rec_of_tab.TOP_MODEL_LINE_ID.delete;
p_match_rec_of_tab.INVENTORY_ITEM_ID.delete;
p_match_rec_of_tab.COMPONENT_CODE.delete;
p_match_rec_of_tab.COMPONENT_SEQUENCE_ID.delete;
p_match_rec_of_tab.VALIDATION_ORG.delete;
p_match_rec_of_tab.QTY_PER_PARENT_MODEL.delete;
p_match_rec_of_tab.ORDERED_QUANTITY.delete;
p_match_rec_of_tab.ORDER_QUANTITY_UOM.delete;
p_match_rec_of_tab.PARENT_ATO_LINE_ID.delete;
p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID.delete;
p_match_rec_of_tab.PERFORM_MATCH.delete;
p_match_rec_of_tab.PLAN_LEVEL.delete;
p_match_rec_of_tab.BOM_ITEM_TYPE.delete;
p_match_rec_of_tab.WIP_SUPPLY_TYPE.delete;
p_match_rec_of_tab.OSS_ERROR_CODE.delete;
p_match_rec_of_tab.SHIP_FROM_ORG_ID.delete;
p_match_rec_of_tab.Attribute_1.delete;
p_match_rec_of_tab.Attribute_2.delete;
p_match_rec_of_tab.Attribute_3.delete;
p_match_rec_of_tab.Attribute_4.delete;
p_match_rec_of_tab.Attribute_5.delete;
p_match_rec_of_tab.Attribute_6.delete;
p_match_rec_of_tab.Attribute_7.delete;
p_match_rec_of_tab.Attribute_8.delete;
p_match_rec_of_tab.Attribute_9.delete;
SELECT config_item_id,
line_id,
link_to_line_id,
parent_ato_line_id,
gop_parent_ato_line_id,
ato_line_id,
top_model_line_id,
inventory_item_id,
ordered_quantity,
qty_per_parent_model,
ship_from_org_id,
validation_org,
plan_level,
wip_supply_type,
bom_item_type,
reuse_config,
perform_match,
config_creation,
option_specific,
oss_error_code
FROM bom_cto_order_lines_gt;
DELETE FROM bom_cto_order_lines_gt;
select count(*)
into l_count
from bom_cto_order_lines_gt;
oe_debug_pub.add('count before insert into bCOL =>'||l_count,1);
INSERT INTO bom_cto_order_lines_gt
(
ATO_LINE_ID,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
BOM_ITEM_TYPE,
WIP_SUPPLY_TYPE,
INVENTORY_ITEM_ID,
LINE_ID,
LINK_TO_LINE_ID,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
TOP_MODEL_LINE_ID,
SHIP_FROM_ORG_ID,
config_item_id,
VALIDATION_ORG --3503764
)
VALUES
(
p_match_rec_of_tab.ato_line_id(i),
p_match_rec_of_tab.component_code(i),
p_match_rec_of_tab.component_sequence_id(i),
--for oss pefromance improvement
--added -1
--bom_item_type
decode(p_match_rec_of_tab.top_model_line_id(i),null,-1, --ato item
-- 1 for topst ato model and default is -1
decode(p_match_rec_of_tab.ato_line_id(i),p_match_rec_of_tab.line_id(i),1,-1)
),
-1, --wip_supply_type
p_match_rec_of_tab.inventory_item_id(i),
p_match_rec_of_tab.line_id(i),
p_match_rec_of_tab.link_to_line_id(i),
p_match_rec_of_tab.ordered_quantity(i),
p_match_rec_of_tab.order_quantity_uom(i),
p_match_rec_of_tab.top_model_line_id(i),
nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
--need for better prformance of OSS code
--if independent ato line order
--populate inv_item_id as config item id
--conatct info : kiran/renga
decode(p_match_rec_of_tab.top_model_line_id(i),
null,
p_match_rec_of_tab.inventory_item_id(i)
),
p_match_rec_of_tab.validation_org(i)--3503764
);
oe_debug_pub.add(sql%rowcount||' rows inserted into bcol_gt',5);
SELECT 'Y'
INTO l_model_exists
FROM bom_cto_order_lines_gt
WHERE line_id = ato_line_id
AND top_model_line_id is not null
AND rownum = 1;
UPDATE bom_cto_order_lines_gt child
SET qty_per_parent_model =
--used round to be consistent with can_configuration code
( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
FROM bom_cto_order_lines_gt parent
WHERE child.parent_ato_line_id= parent.line_id
)
--to filter out ato item order lines
WHERE top_model_line_id is not null;
UPDATE bom_cto_order_lines_gt child
SET qty_per_parent_model =
( SELECT ROUND(DECODE(parent.ordered_quantity, 0 , 0, (child.ordered_quantity/parent.ordered_quantity)),7)
FROM bom_cto_order_lines_gt parent
WHERE child.parent_ato_line_id= parent.line_id
)
WHERE top_model_line_id IS NOT NULL;
UPDATE /*+ INDEX (GT BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt GT
SET GT.config_creation = ( SELECT nvl(MTL.config_orgs,1)--3555026
FROM mtl_system_items MTL
WHERE MTL.inventory_item_id = GT.inventory_item_id -- bugfix 3391383
AND MTL.organization_id = GT.validation_org--3555026
AND GT.bom_item_type = '1'
AND GT.config_item_id is not null
)
WHERE GT.bom_item_type = '1'
AND GT.config_item_id is not null;
oe_debug_pub.add('Updated '||sql%rowcount||' model rows with config_orgs or config_creation attribute',5);
SELECT oss_error_code,
config_item_id,
parent_ato_line_id,
gop_parent_ato_line_id,
bom_item_type,
wip_supply_type
BULK COLLECT INTO
p_match_rec_of_tab.oss_error_code,
--during Ut make sure next statement
--over writes existing values , ifnot
--additional rows may get created during
--for MATCH 0n cases. remove comment after UT
p_match_rec_of_tab.config_item_id,
p_match_rec_of_tab.parent_ato_line_id,
p_match_rec_of_tab.gop_parent_ato_line_id,
p_match_rec_of_tab.bom_item_type,
p_match_rec_of_tab.wip_supply_type
FROM bom_cto_order_lines_gt
ORDER BY line_id; --Bugfix 6055375*/
select
CONFIG_ITEM_ID,
LINE_ID,
LINK_TO_LINE_ID,
ATO_LINE_ID,
TOP_MODEL_LINE_ID,
INVENTORY_ITEM_ID,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
VALIDATION_ORG,
QTY_PER_PARENT_MODEL,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
PARENT_ATO_LINE_ID,
GOP_PARENT_ATO_LINE_ID,
PERFORM_MATCH,
PLAN_LEVEL,
BOM_ITEM_TYPE,
WIP_SUPPLY_TYPE,
OSS_ERROR_CODE,
SHIP_FROM_ORG_ID
BULK COLLECT INTO
p_match_rec_of_tab.CONFIG_ITEM_ID,
p_match_rec_of_tab.LINE_ID,
p_match_rec_of_tab.LINK_TO_LINE_ID,
p_match_rec_of_tab.ATO_LINE_ID,
p_match_rec_of_tab.TOP_MODEL_LINE_ID,
p_match_rec_of_tab.INVENTORY_ITEM_ID,
p_match_rec_of_tab.COMPONENT_CODE,
p_match_rec_of_tab.COMPONENT_SEQUENCE_ID,
p_match_rec_of_tab.VALIDATION_ORG,
p_match_rec_of_tab.QTY_PER_PARENT_MODEL,
p_match_rec_of_tab.ORDERED_QUANTITY,
p_match_rec_of_tab.ORDER_QUANTITY_UOM,
p_match_rec_of_tab.PARENT_ATO_LINE_ID,
p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID,
p_match_rec_of_tab.PERFORM_MATCH,
p_match_rec_of_tab.PLAN_LEVEL,
p_match_rec_of_tab.BOM_ITEM_TYPE,
p_match_rec_of_tab.WIP_SUPPLY_TYPE,
p_match_rec_of_tab.OSS_ERROR_CODE,
p_match_rec_of_tab.SHIP_FROM_ORG_ID
FROM bom_cto_order_lines_gt
ORDER BY line_id;
DELETE FROM bom_cto_order_lines_gt;