[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| configurations and insert unique configurations into |
| BOM_ATO_CONFIGURATIONS. |
| |
| check_config_match - checks BOM_ATO_CONFIGURATIONS for |
| configurations that match the ordered configuration. It |
| is called from the Match Configuration Workflow activity |
| and from the Create Configuration batch process. |
| |
| can_configurations - inserts unique configurations into |
| BOM_ATO_CONFIGURATIONS. It is called from the Create |
| Configuration batch process and the Create Configuration |
| Item and BOM workflow activity. |
| |
| To Do: Handle Errors. Need to discuss with Usha and Girish what |
| error information to include in Notification. |
| |
| HISTORY : |
| May 10, 99 Angela Makalintal Initial version |
| |
| 2/23/01 SBHASKAR Bugfix 1553467 |
|
|
|
|
| 2/31/03 SSAWANT BugFix 2789771 |
| A fundamental bug for matching was fixed. |
| This happens due to the new |
| Multiple Instantiation |
| feature for all levels of ATO Models |
| introduced in current DMF, CZ 11.5.9 |
| |
| |
| |
| 7/02/03 KSARKAR Bugfix 2986192 |
|
|
| Modified on 14-MAR-2003 By Sushant Sawant
| Decimal-Qty Support for Option Items
|
|09/05/03 Kiran Konada chnages for patchset-J
|
|
|||09-10-2003 Kiran Konada
|
| bugfix 3070429,3124169
| pragtion bugfix #3143556
|
|
| After a call to create item , a new call is added to
| CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
|
| NOTE: CTO_ENI_WRAPPER is maintained in bom Source control and
| is owned by ENI team. This is done as part of bugfix 3070429
|
| Always the main code contains stubbed version and branch has the
| a call to file maintained in ENI product top
|
| Branch is always shipped with ENI
|
| The above approach was taken as CTO could not directly make a
| call to a ENI file. ENI is present from 11.5.4 onwards and
| CTO bugfixes can be shipped to all customers since base release
| (11.5.2)
|
| The error messages if any from CTO_CALL_TO_ENI are ignored
| decision:Usha Arora,Krishna Bhagvatula,Anuradha subramnian
| added delete before insert into BCOL_GT
|
|
+=============================================================================*/
/****************************************************************************
Procedure: Match_and_create_all_items
Parameters: p_model_line_id - line id of the top model in
oe_order_lines_all
x_return_status - return status
x_msg_count
x_msg_data
Description: This function looks for a configuration in
bom_ato_configurations that matches the ordered
configuration in oe_order_lines_all.
*****************************************************************************/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
select line_id, parent_ato_line_id, inventory_item_id
from bom_cto_order_lines
where bom_item_type = 1
--and top_model_line_id = pModelLineId -- top model
and ato_line_id = pModelLineId
and nvl(wip_supply_type,0) <> 6
and config_item_id is null -- do we need this in case on-line match
and ato_line_id is not null -- could be a PTO
order by plan_level desc;
select perform_match
into lPerformMatch
from bom_cto_order_lines
where line_id = lNextRec.line_id;
update bom_cto_order_lines
set perform_match = 'N'
where line_id = lNextRec.parent_ato_line_id
and perform_match = 'Y';
-- if the update fails, its not an error
end;
update bom_cto_order_lines
set perform_match = 'N'
where line_id = lNextRec.parent_ato_line_id
and perform_match = 'Y';
-- if the update fails, its not an error
end;
update bom_cto_order_lines
set config_item_id = lXConfigId
where line_id = lNextRec.line_id;
the PTO model. Since the PTO options are not inserted in
bom_cto_order_lines, the condition
"and colp.line_id = nvl(col1.link_to_line_id, col1.line_id)"
will fail.
Removed "colp" from the FROM clause and added a new condition after
commenting the old. Search on 1553467.
PTO-MODEL-1
... ATO-MODEL-1
......ATO-OPTCLASS-1
.........OPTION-1
.........OPTION-2
......OPTION-3
08-AUG-2003 Kiran Konada
chnaged the code to use BCOL_TEMP instead of BCOL for patchser J
*****************************************************************************/
function check_config_match(
p_model_line_id in number,
x_config_match_id out NOCOPY number,
x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
)
RETURN integer
IS
l_stmt_num number;
The first sql shall insert into bom_ato_configs_temp the "approximate"
matching configurations. For "approximate" match, it must have the same
count of components and the sum of component item ids must be equal.
The second sql shall work on the filtered set of probable match candidate
configs to determine if there is any extra component in the order or in
the config or whether the config has been deactivated in some orgs.
********************************************************************/
l_start_time := sysdate;
delete bom_ato_configs_temp;
select count(*), sum( nvl( decode(line_id, p_model_line_id, inventory_item_id, config_item_id),
inventory_item_id
)
)
into l_component_count, l_component_sum
from bom_cto_order_lines_gt
where parent_ato_line_id = p_model_line_id
or line_id = p_model_line_id;
select inventory_item_id
into l_base_model_id
from bom_cto_order_lines_gt
where line_id = p_model_line_id;
insert into bom_ato_configs_temp(
config_item_id,
organization_id,
base_model_id,
component_item_id,
component_code,
component_quantity)
select bac1.config_item_id,
bac1.organization_id,
bac1.base_model_id,
bac1.component_item_id,
bac1.component_code,
bac1.component_quantity
from bom_ato_configurations bac1
where bac1.config_item_id in (
select config_item_id
from BOM_ATO_CONFIGURATIONS bac3
where bac3.base_model_id = l_base_model_id
group by bac3.config_item_id
having count(*) = l_component_count
and sum(component_item_id) = l_component_sum
)
and bac1.component_item_id = bac1.base_model_id; --6086540: load just 1 record per config item
insert into bom_ato_configs_temp(
config_item_id,
organization_id,
base_model_id,
component_item_id,
component_code,
component_quantity)
select /*+ INDEX(BAC1 BOM_ATO_CONFIGURATIONS_N1)*/
bac1.config_item_id,
bac1.organization_id,
bac1.base_model_id,
bac1.component_item_id,
bac1.component_code,
bac1.component_quantity
from bom_ato_configurations bac1
where bac1.component_item_id = bac1.base_model_id
and bac1.base_model_id = l_base_model_id
and EXISTS (SELECT 1
from BOM_ATO_CONFIGURATIONS bac3
where bac3.base_model_id = l_base_model_id
and bac1.config_item_id = bac3.config_item_id
and bac1.base_model_id = bac3.base_model_id
group by bac3.config_item_id
having count(*) = l_component_count
and sum(component_item_id) = l_component_sum);
oe_debug_pub.add ('Rows inserted into gtt :'||sql%rowcount);
oe_debug_pub.add ('check_config_match :: after bom_ato_configs_temp insert time : '||to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS'));
select /*+ ordered */ bac1.config_item_id -- 6086540: added the ordered hint
into x_config_match_id
from bom_cto_order_lines_gt col1, /* model */
bom_ato_configs_temp bact1, --6086540: use the GTT for filtering based on approx match
bom_ato_configurations bac1 --6086540: matching will be done in main table
where col1.line_id = p_model_line_id
and bac1.base_model_id = col1.inventory_item_id
and bact1.base_model_id = col1.inventory_item_id
and bac1.component_item_id = col1.inventory_item_id
and bac1.config_item_id = bact1.config_item_id
and not exists (
select 'Config Item is not active in atleast one orgn'
from mtl_system_items msi,
bom_parameters bp
where msi.organization_id = bp.organization_id
and msi.inventory_item_id = bac1.config_item_id
and msi.inventory_item_status_code = nvl(bp.bom_delete_status_code,'NULL')
)
and not exists
(select 'Extra Options in Order'
from bom_cto_order_lines_gt col5
where (col5.parent_ato_line_id = col1.line_id
or col5.line_id = col1.line_id) -- to pick up top model
and col5.ordered_quantity > 0
and nvl(decode(col5.line_id, col1.line_id, col5.inventory_item_id,
col5.config_item_id),
col5.inventory_item_id) not in
(select bac2.component_item_id
from bom_ato_configurations bac2 -- 6086540
where bac2.config_item_id = bac1.config_item_id
and bac2.component_item_id =
decode(col5.config_item_id, NULL,
col5.inventory_item_id, decode(col5.line_id, col1.line_id,
col5.inventory_item_id, col5.config_item_id))
and bac2.component_code =
substrb(col5.component_code,
instrb(col5.component_code||'-',
'-'||to_char(col1.inventory_item_id)||'-')+1)
and bac2.component_quantity =
Round( nvl(col5.ordered_quantity,0)/ nvl(col1.ordered_quantity,0) , 7 ) /* Decimal-Qty Support for Option Items */
)
)
and not exists /* Added due to Multiple Instantiation */
( select 'Extra Options in Config' from bom_ato_configurations bac9 -- 6086540
where bac9.config_item_id = bac1.config_item_id /* v_config_item_id */
and ( bac9.component_item_id , bac9.component_quantity )
not in
( select decode( col1.line_id , col9.line_id, col9.inventory_item_id ,
nvl( col9.config_item_id, col9.inventory_item_id )),
Round( nvl( col9.ordered_quantity, 0)/nvl( col1.ordered_quantity, 0 ), 7 ) /* Decimal-Qty Support for Option Items */
from bom_cto_order_lines_gt col9
where col9.parent_ato_line_id = col1.line_id or col9.line_id = col1.line_id
)
)
and rownum = 1;
SELECT last_referenced_date
FROM bom_ato_configurations
WHERE config_item_id = x_config_match_id
FOR UPDATE NOWAIT;
update bom_ato_configurations
set last_referenced_date = SYSDATE
where config_item_id = x_config_match_id;
OE_DEBUG_PUB.add ('check_config_match: ' || 'Could not lock for config id '|| x_config_match_id ||' for update.');
configuration will be inserted
prg_appid - program application id
prg_id - program id
req_id - job id
user_id - id of user running process
login_id - login id
x_error_message - error message if match function fails
x_message_name - name of error msg if match function fails
Description: This function inserts the configuration (model and components)
into BOM_ATO_CONFIGURATIONS for use when matching a
configuration via the Match functionality.
It is called from the Create Item and BOM batch process.
Bugfix 1553467 : If an ATO model is part of a PTO model (see fig below),
then, the link_to_line_id of ATO model will be the line_id of
the PTO model. Since the PTO options are not inserted in
bom_cto_order_lines, the condition
"and bcolParent.line_id = NVL(bcolModel.link_to_line_id, bcolModel.line_id); "
INSERT_ERROR exception;
SELECT 'Y'
INTO l_ato_flag
FROM bom_cto_order_lines bcol,
mtl_system_items msi
WHERE bcol.line_id = p_model_line_id
AND bcol.config_item_id = msi.inventory_item_id
AND msi.replenish_to_order_flag = 'Y'
AND rownum =1;
Insert into BOM_ATO_CONFIGURATIONS the model configuration from
oe_order_lines_all.
******************************************************************/
l_stmt_num := 100;
insert into BOM_ATO_CONFIGURATIONS(
config_item_id,
organization_id,
base_model_id,
component_item_id,
component_code,
component_quantity,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
last_referenced_date,
request_id,
program_application_id,
program_id,
program_update_date)
select bcolModel.config_item_id,
bcolModel.ship_from_org_id,
bcolModel.inventory_item_id,
nvl(decode(bcolOptions.line_id, bcolModel.line_id, bcolOptions.inventory_item_id,
bcolOptions.config_item_id),
bcolOptions.inventory_item_id),
-- bugfix 1553467 begin
substrb(bcolOptions.component_code,
instrb(bcolOptions.component_code||'-',
'-'||to_char(bcolModel.inventory_item_id)||'-')+1),
-- bugfix 1553467 end
/* -- bugfix 1553467 comment begin
decode(bcolModel.link_to_line_id, NULL,
bcolOptions.component_code,
substr(bcolOptions.component_code,
lengthb(bcolParent.component_code)+2)),
-- bugfix 1553467 comment end
*/
Round( (bcolOptions.ordered_quantity / bcolModel.ordered_quantity), 7 ) ,
-- qty represents ordered - canclld
/* Decimal-Qty Support for Option Items */
SYSDATE,
user_id,
SYSDATE,
user_id,
login_id,
SYSDATE,
req_id,
prg_appid,
prg_id,
SYSDATE
from
-- bugfix 1553467: bom_cto_order_lines bcolParent, /* Parent of Model, if any */
bom_cto_order_lines bcolModel, /* Model */
bom_cto_order_lines bcolOptions /* Options */
where bcolModel.line_id = p_model_line_id
and (bcolOptions.parent_ato_line_id = bcolModel.line_id or
bcolOptions.line_id = bcolModel.line_id);
raise INSERT_ERROR;
when INSERT_ERROR then
-- IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('can_configurations: ' || 'Failed in can_configurations 2. ', 1);
'Insert Error';
v_raw_line_id.delete ;
v_raw_line_id.delete ;
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
select line_id, parent_ato_line_id, inventory_item_id
from bom_cto_order_lines_gt
where bom_item_type = '1'
and ato_line_id = p_ato_line_id
and nvl(wip_supply_type,0) <> 6
and ato_line_id is not null -- could be a PTO
and config_item_id is null --becos item could have been re-used
and perform_match in ('Y','C')
order by plan_level desc, inventory_item_id asc;
select perform_match
into lPerformMatch
from bom_cto_order_lines_gt
where line_id = lNextRec.line_id;
update bom_cto_order_lines_gt
set perform_match = 'U'
where line_id = lNextRec.parent_ato_line_id
and perform_match in ('Y','C');
-- if the update fails, its not an error
else
lStmtNum := 120;
update bom_cto_order_lines_gt
set perform_match = 'U'
where line_id = lNextRec.line_id
and perform_match in ('Y','C');
-- if the update fails, its not an error
oe_debug_pub.add('perform_match: ' || 'updated to U : ' || to_char(lNextRec.Line_Id), 1);
update bom_cto_order_lines_gt
set perform_match = 'U'
where line_id = lNextRec.parent_ato_line_id
and perform_match in ( 'Y','C');
-- if the update fails, its not an error
oe_debug_pub.add('perform_match: ' || 'updated to U : ' || to_char(lNextRec.parent_ato_Line_Id), 1);
update bom_cto_order_lines_gt
set config_item_id = lXConfigId
where line_id = lNextRec.line_id;
update bom_cto_order_lines_gt
set perform_match = 'Y'
where ato_line_id = p_ato_line_id
and inventory_item_id in
( select inventory_item_id
from bom_cto_order_lines_gt
where ato_line_id = p_ato_line_id
and bom_item_type = '1'
and wip_supply_type <> 6
and perform_match = 'U'
group by inventory_item_id
having count(*) > 1
);
oe_debug_pub.add('perform_match: ' || 'Updated possible similar models to Y : '
|| to_char(sql%rowcount), 1);
SELECT line_id,
parent_ato_line_id,
reuse_config
FROM bom_cto_order_lines_gt
--added nvl, bugfix 3530054
WHERE nvl(wip_supply_type,1) <>6 --non phantom ato models
AND bom_item_type = '1' --used inverted commas to use index N5
AND ato_line_id = p_ato_line_id;
SELECT line_id,
parent_ato_line_id,
reuse_config
FROM bom_cto_order_lines_gt
----added nvl, bugfix 3530054
WHERE nvl(wip_supply_type,1) <>6 --non phantom
AND bom_item_type = '1' ; --'1' for using idx_N5 --ato models
SELECT
line_id,
parent_ato_line_id,
ato_line_id,
wip_supply_type,
bom_item_type,
qty_per_parent_model,
reuse_config
FROM bom_cto_order_lines_gt
WHERE ato_line_id = p_ato_line_id;
SELECT
line_id,
reuse_config,
config_item_id,
qty_per_parent_model,
config_creation,
ship_from_org_id,
validation_org
FROM bom_cto_order_lines_gt
WHERE reuse_config is not null;
UPDATE bom_cto_order_lines_gt
SET reuse_config = 'Y'
WHERE ato_line_id = p_ato_line_id
AND bom_item_type = '1' --'1' uses idx_n5 --identifies non-phantom
--need a nvl as for top most ato model there is no value
AND nvl(WIP_SUPPLY_TYPE,1) <> 6; --model lines
--debug select
IF PG_DEBUG = 5 THEN
SELECT line_id,
qty_per_parent_model
BULK COLLECT INTO
l_temp_line_id,
l_qty_per_parent_model
FROM bom_cto_order_lines_gt
WHERE ato_line_id = p_ato_line_id;
UPDATE bom_cto_order_lines_gt
SET reuse_config = 'Y'
WHERE bom_item_type = '1' --used idx_n5 --identifies non-phantom
AND nvl(WIP_SUPPLY_TYPE,1) <> 6;
SELECT distinct(ato_line_id)
BULK COLLECT INTO l_ato_line_tbl
FROM bom_cto_order_lines_gt
WHERE top_model_line_id is not null;
SELECT /*+ leading(BCGT) use_nl(BCGT BCOL) */ distinct(bcol.ato_line_id)
BULK COLLECT INTO l_bcol_ato_line_tbl
FROM bom_cto_order_lines bcol,
bom_cto_order_lines_gt bcgt
WHERE bcgt.line_id = bcol.line_id
AND bcol.qty_per_parent_model is null;
UPDATE bom_cto_order_lines 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 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
AND ato_line_id = l_bcol_ato_line_tbl(j) ;
UPDATE bom_cto_order_lines_gt bcol_gt
SET config_creation =
--used round to be consistent with can_configuration code
( SELECT nvl(mtl.config_orgs,1)--3555026
FROM mtl_system_items mtl
WHERE mtl.inventory_item_id = bcol_gt.inventory_item_id
AND mtl.organization_id = bcol_gt.validation_org--3555026
)
--to filter out ato item order lines
WHERE top_model_line_id is not null
AND bom_item_type= '1'
--nvl as for top most model there wst is not populated
AND nvl(wip_supply_type,1) <> 6;
UPDATE bom_cto_order_lines_gt bcolt
SET bcolt.reuse_config = 'N'
WHERE
line_id in (
--bugfix start 3503764
--if ware house is different then reuse = N
(SELECT bcol_gt1.line_id
FROM bom_cto_order_lines_gt bcol_gt1,
bom_cto_order_lines bcol
WHERE bcol.line_id = l_ato_line_tbl(i)
AND bcol_gt1.config_creation in (1,2)
AND bcol_gt1.ato_line_id = l_ato_line_tbl(i)
AND bcol_gt1.ship_from_org_id <> bcol.ship_from_org_id
AND bcol_gt1.bom_item_type = '1'
AND nvl(bcol_gt1.wip_supply_type,1) <> 6
)
--end bugfix 3503764
UNION
( Select parent_ato_line_id
from bom_cto_order_lines_gt bcolt1
Where (bcolt1.line_id,
bcolt1.qty_per_parent_model,
bcolt1.inventory_item_id)
not in ( Select line_id,
qty_per_parent_model,
inventory_item_id
from bom_cto_order_lines
where ato_line_id = l_ato_line_tbl(i) )
--filters out pure ato item lines
AND bcolt1.top_model_line_id is not null
AND bcolt1.ato_line_id = l_ato_line_tbl(i)
)
-- bugfix 3381658 start
UNION
(Select parent_ato_line_id
from bom_cto_order_lines bcol2
Where (bcol2.line_id,
bcol2.qty_per_parent_model,
bcol2.inventory_item_id)
not in ( Select bcolgt.line_id,
bcolgt.qty_per_parent_model,
bcolgt.inventory_item_id
from bom_cto_order_lines_gt bcolgt
where ato_line_id = l_ato_line_tbl(i) )
--filters out pure ato item lines
AND bcol2.top_model_line_id is not null
AND bcol2.ato_line_id = l_ato_line_tbl(i)
)
--end bugfix 3381658
)
RETURNING parent_ato_line_id BULK COLLECT INTO l_parent_ato_line_tbl;
--previous update might have put reuse_config to N
--so following if condition
--OR previous element might have updated reuse to N
IF g_reuse_tbl(l_parent_ato_line_tbl(i)).reuse_config= 'Y' THEN
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('calling flag_reuse_config for model_line_id=>'
||l_parent_ato_line_tbl(i),5);
UPDATE bom_cto_order_lines_gt
SET reuse_config = 'N'
WHERE line_id = g_model_line_tbl(i);
--deleted, as the procedure gets called again
--for another ATO model line during ACC
g_reuse_tbl.DELETE;
g_model_line_tbl.DELETE;
UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
SET bcolt.config_item_id =
(SELECT bcol.config_item_id
FROM bom_cto_order_lines bcol
WHERE bcolt.line_id = bcol.line_id
)
WHERE bcolt.bom_item_type = '1'--used idx_n5
AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
AND bcolt.reuse_config = 'Y'
AND bcolt.ato_line_id = p_ato_line_id ;
UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
SET bcolt.config_item_id =
(SELECT bcol.config_item_id
FROM bom_cto_order_lines bcol
WHERE bcolt.line_id = bcol.line_id
)
WHERE bcolt.bom_item_type = '1' --used inverted commas, so that index is used
AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
AND bcolt.reuse_config = 'Y';
UPDATE bom_cto_order_lines_gt bcol
SET (bcol.wip_supply_type,
bcol.bom_item_type )=
(SELECT wip_supply_type,
bom_item_type
FROM bom_inventory_components bic
WHERE bcol.component_sequence_id = bic.component_sequence_id
)
where bcol.ato_line_id <>bcol.line_id;
oe_debug_pub.add('rowcount after update from bic=>'||sql%rowcount,5);
--rowcount after insert of bom_item_type and wip_supply_type is l_rowcount;
SELECT bom_item_type,
wip_supply_type
BULK COLLECT INTO
p_match_rec_of_tab.bom_item_type,
p_match_rec_of_tab.wip_supply_type
FROM bom_cto_order_lines_gt;
oe_debug_pub.add('rowcount after select for BIT,WST=>'||sql%rowcount,5);
--rowcount of bom_itemtype,wip_supply_typ after select l_rowcount;
--update the remaining columns into bcol_temp
FORALL i IN 1..l_last_index
UPDATE bom_cto_order_lines_gt
SET PARENT_ATO_LINE_ID = p_match_rec_of_tab.PARENT_ATO_LINE_ID(i),
GOP_PARENT_ATO_LINE_ID = p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID(i),
PLAN_LEVEL = p_match_rec_of_tab.PLAN_LEVEL (i)
WHERE line_id = p_match_rec_of_tab.LINE_ID(i);
PROCEDURE Insert_into_bcol_gt(
p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_last_index number;
delete from bom_cto_order_lines_gt;
oe_debug_pub.add('insert into bcol_gt',5);
INSERT INTO bom_cto_order_lines_gt
(
ATO_LINE_ID,
BOM_ITEM_TYPE,
COMPONENT_CODE,
COMPONENT_SEQUENCE_ID,
INVENTORY_ITEM_ID,
LINE_ID,
LINK_TO_LINE_ID,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
PARENT_ATO_LINE_ID,
PLAN_LEVEL,
TOP_MODEL_LINE_ID,
WIP_SUPPLY_TYPE,
SHIP_FROM_ORG_ID,
VALIDATION_ORG --3503764
)
VALUES
(
p_match_rec_of_tab.ato_line_id(i),
--added -1 to be consistent with CTOGOPIB insert
-- -1 is used in where cluase in downstream procedure
-- prepare_bcol_temp
nvl(p_match_rec_of_tab.bom_item_type(i),-1),
p_match_rec_of_tab.component_code(i),
p_match_rec_of_tab.component_sequence_id(i),
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.parent_ato_line_id(i),
p_match_rec_of_tab.plan_level(i),
p_match_rec_of_tab.top_model_line_id(i),
--added -1 to be consistent with CTOGOPIB insert
-- -1 is used in where cluase in downstream procedure
-- prepare_bcol_temp
nvl(p_match_rec_of_tab.wip_supply_type(i),-1),
nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
p_match_rec_of_tab.validation_org(i)--3503764
);
oe_debug_pub.add('Insert_into_bcol_gt: ' || 'Exception in stmt num: '
|| to_char(lStmtNum), 1);
oe_debug_pub.add('Insert_into_bcol_gt: ' || ' Unexpected Exception in stmt num: '
|| to_char(lStmtNum), 1);
oe_debug_pub.add('Insert_into_bcol_gt: ' || 'Others Exception in stmt num: '
|| to_char(lStmtNum), 1);
END Insert_into_bcol_gt;
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
PROCEDURE Update_BCOLGT_with_match_flag
(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_match_flag_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
SELECT line_id,
parent_ato_line_id,
ato_line_id,
perform_match
FROM bom_cto_order_lines_gt
WHERE bom_item_type = '1' -- put in inverted commas to use hint
AND nvl(wip_supply_type,1)<> 6;
oe_debug_pub.add('ENTERED Update_BCOLGT_with_match_flag', 5);
UPDATE /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt bcol
SET bcol.perform_match=
(SELECT config_match
FROM mtl_system_items_b mtl
WHERE mtl.inventory_item_id = bcol.inventory_item_id
AND mtl.organization_id = bcol.validation_org --reuse_revert
--3555026
)
WHERE bcol.bom_item_type = '1'-- used inverted commas to use index
AND nvl(bcol.wip_supply_type,1) <> 6;
oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'Sql%row count ='||sql%rowcount,3);
oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||
'success after Evaluate_N_Pop_Match_Flag', 1);
oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'success after xfer_match_flag_to_rec_of_tab', 1);
UPDATE bom_cto_order_lines_gt
SET perform_match = l_match_flag_rec_of_tab.match_flag(i)
WHERE line_id = l_match_flag_rec_of_tab.line_id (i);
oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'Sql%row count ='||sql%rowcount,3);
oe_debug_pub.add('Update_BCOLGT_with_match_flag ' || 'Exception in stmt num: '
|| to_char(lStmtNum), 1);
oe_debug_pub.add('Update_BCOLGT_with_match_flag ' || ' Unexpected Exception in stmt num: '
|| to_char(lStmtNum), 1);
oe_debug_pub.add('Update_BCOLGT_with_match_flag' || 'Others Exception in stmt num: '
|| to_char(lStmtNum), 1);