[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 2. Update these tables with the config_item_id
| 3. Copy sourcing rule assignments from model to config item
|
|HISTORY : Created on 04-MAY-2000 by Sajani Sheth
| Modified on 18-MAY-2000 by Sushant Sawant
| Modified on 23-JUN-2000 by Sushant Sawant
| Modified on 08-AUG-2000 by Sushant Sawant
| Modified on 11-JAN-2001 by Sushant Sawant
| removed multilevel profile query
| Modified on 30-APR-2001 By Renga Kannan
| FiXed the where condition in MRP_SOURCES_V query.
| The where condition for the field source_type is added
| with nvl function.
|
|
| Modified on 14-MAY-2001 by Sushant Sawant
| changes made to trunc to reflect
| changes made to branch due to BUG
| 1728383 for performance.
|
| Modified on 05-JUN-2001 by Sushant Sawant
| changes made to derive perform_match value
| through BOM:MATCH_CONFIG profile.
| Modified on 15-JUN-2001 by Renga Kannan
| Moved the get_model_sourcing_org code from
| CTO_ATP_INTERFACE_PK to CTO_UTILITY_PK
| This decision is taken by CTO team on
| 06/15/2001 to avoid the dependency
| with CTOATPIB.pls for this procedure
| This procedure is used in change order
| package. We are expecting this procedure to
| be used in future also.
| Modified on 22-JUN-2001 by Shashi Bhaskaran : bugfix 1811007
| Added a new function convert_uom for wt/vol
| calculation.
| Modified on 18-JUL-2001 by Kundan Sarkar
| fixed bug 1876618 to improve performance
| Modified on 18-JUL-2001 by Shashi Bhaskaran : bugfix 1799874
| Added a new function get_source_document_id
| to know if it is a regular SO or internal SO.
|
| Modified on 21-AUG-21001 by Renga Kannan
|
| Get_model_sourcing_org and related procedures
| are modified to handle BUY model type also. This
| change is done as part of 'Procuring config' and
| Auto create Req for ATO item project(Patch set G)
| The changes made in the CTOATPIB.pls file is replicated
| here .Look at the individual places for Further comments.
| Get_all_item_orgs procedure is modified as part of this
| Project
| Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
| Added a new functionality for preconfigure bom.
| Modified on 02-NOV-2001 by Renga Kannan
| Modified Generate_routing_attachment_text
| Operation code was incorrect. One more join
| is added to it. This bug was found during
| Patch set G system testing.
|
|
|
| Modified on 08-NOV-2001 by Renga Kannan
| Modified the populate_src_orgs procedure
| Added one more exception for invalid sourcing
| When the item is not defined in the sourcing
| org it will error out saying invalid sourcing
|
|
| Modified on 13-NOV-2001 by Renga Kannan
|
| The error message handling for this file is
| changed completely. The FND_MESSAGE.SET_NAME
| needs to be called twice in all the error handling
| exception. And there should be one add for OE and
| one add for FND. IN the exception block we need
| to call the fnd_msg_pub.count_and_get and
| oe_msg_pub.count_and_get.
|
|
| Modified on 13-NOV-2001 By Renga Kannan
|
|
| Modified the procedure Create_sourcing_rule
| to have a filter condition to choose only
| assignment_type 3 and 6.
|
| Modified on 08-MAR-2002 By Sushant Sawant
|
| BUG#2234858
| Added new functionality for Drop Shipment
| organization_type = 3 ,4 BUY
| organization_type = 5 ,6 DROP SHIP
| Modified on 27-MAR-2002 By Kiran Konada
| removed the procedure GENERATE_ROUTING_ATTACH_TEXT
| changed the signature and modified the logic of
| GENERATE_BOM_ATTACH_TEXT to get bom from BCOL
| above changes have been made as part of patchset-H
| to be in sync with decisions made for cto-isp page
|
| Modified on 12-APR-2002 By Sushant Sawant
| Fixed BUG2310356
| Drop Ship should respect buy sourcing rules.
|
| Modified on 04-JUN-2002 BY Kiran Konada--bug fix 2327972
| added a new procedure chk_all_rsv_details
| This returns reservation details all types of
| reservation for a given line_id in a table of records
|
| Modified on 16-SEP-2002 By Sushant Sawant
| Added New Function isModelMLMO copied from G branch
| This function checks whether a model
| is ML/MO.
|
| Modified on 14-FEB-2003 By Kundan Sarkar
| Bugfix 2804321 : Propagating customer bugfix 2774570
| to main.
|
| Modified on 14-MAR-2003 By Sushant Sawant
| Decimal-Qty Support for Option Items.
|
|
| Modified on 22-Aug-2003 By Kiran Konada
| for enabling multiple soucres from DMF-J
| removed the error_code =66 in query sourcing org
| P_source_type will be 66 for multiple sources
|
|
| Modified on 26-Mar-2004 By Sushant Sawant
| Fixed Bug#3484511
| all queries referencing oe_system_parameters_all
| should be replaced with a function call to oe_sys_parameters.value
|
|
| modified on 17-May-2004 Kiran Konada
|
| inserted ship_from_org-id from BCOL into the
| validation_org col on BCOL_GT
| code has been changed in CTO_REUSE for
| 3555026 to look at validation_org, and so
| validation-org cannot be null
|
| on 07/09/2004 Kiran Konada
| --bugfix#3756670, added delte before insert in bcol_gt
|
|
| Modified on 21-APR-2005 By Sushant Sawant
| Fixed Bug#4044709
| added validate_oe_data procedure to validate bcol/bcol_gt
| data against OEL.
|
|
| 16-Jun-2005 Kiran Konada
| changes for OPM and Ireq
| chaneg comment : OPM
| check_cto_can_create_supply_api
| --two new parameters l_sourcing_org and l_message
| --new logic to set x_can_create_supply for processorg
| and make combination.
| Hard dependency:
| INV_GMI_RSV_BRANCH.Process_Branch
|
|
|
+-----------------------------------------------------------------------------*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_UTILITY_PK';
select line_id,
ato_line_id,
inventory_item_id,
plan_level
from bom_cto_order_lines
where ato_line_id = pTopAtoLineId
and bom_item_type = 1
and nvl(wip_supply_type,0) <> 6
order by plan_level;
select distinct bcso.organization_id
from bom_cto_src_orgs bcso,
bom_cto_order_lines bcol
where bcol.line_id = lLineId
and bcol.parent_ato_line_id = bcso.line_id
and bcso.create_bom = 'Y';
select line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
create_src_rules
from bom_cto_src_orgs
where top_model_line_id = pTopAtoLineId;
select ship_from_org_id
into lShipFromOrgId
from bom_cto_order_lines
where line_id = v_model_lines.line_id;
select 'Y'
from bom_cto_src_orgs bcso
where line_id = pLineId
and model_item_id = pModelItemId
and rcv_org_id = l_curr_src_org;
select ato_line_id,
program_id
into lTopAtoLineId,
lProgramId
from bom_cto_order_lines
where line_id = pLineId;
select ato_line_id,parent_ato_line_id, nvl(program_id,0) /* added by sushant for preconfigure bom identification */
into lTopAtoLineId,l_parent_ato_line_id, lProgramId
from bom_cto_order_lines
where line_id = pLineId;
Select organization_type
Into l_source_type
from bom_cto_src_orgs bcso
where bcso.line_id = l_parent_ato_line_id
and bcso.create_bom = 'Y';
select source_type_code
into v_source_type_code
from oe_order_lines_all
where line_id = pLineId ;
select planning_make_buy_code
into l_make_buy_code
from MTL_SYSTEM_ITEMS
where inventory_item_id = pModelItemId
and organization_id = pRcvOrgId;
select
nvl(msv.source_organization_id,l_curr_RcvOrgId),
msv.assignment_type,
msv.rank,
nvl(msv.source_type,1)
into
l_curr_src_org,
l_curr_assg_type,
l_curr_rank,
l_source_type
from mrp_sources_v msv
where msv.assignment_set_id = lMrpAssignmentSet
and msv.inventory_item_id = pModelItemId
and msv.organization_id = l_curr_RcvOrgId
--and nvl(msv.source_type,1) <> 3 -- Commented by Renga Kannan on 08/21/01
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate;
select count(*)
into l_sourcing_rule_count
from mrp_sources_v msv
where msv.assignment_set_id = lMrpAssignmentSet
and msv.inventory_item_id = pModelItemId
and msv.organization_id = l_curr_RcvOrgId
and nvl(msv.source_type,1) <> 3
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate)
/* Nvl fun is added by Renga Kannan on 05/05/2001 */
and nvl(disable_date, sysdate+1) > sysdate;
-- update final src for BOM creation
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('populate_plan_level: ' || 'NDF::End of chain for model '||to_char(pModelItemId), 1);
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = pModelItemId
AND organization_id = l_curr_RcvOrgId;
update bom_cto_src_orgs
set create_bom = 'Y',organization_type = l_source_type
where line_id = pLineId
and model_item_id = pModelItemId
and organization_id = l_curr_src_org;
oe_debug_pub.add('populate_plan_level: ' || 'Rows updated::'||sql%rowcount,2);
insert into bom_cto_src_orgs
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
lTopAtoLineId,
pLineId,
pModelItemId,
l_curr_RcvOrgId,
l_curr_src_org,
'N', -- create_bom
'Y', -- cost_rollup
l_source_type, -- org_type is used to store the source type
NULL, -- config_item_id
decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
l_curr_rank,
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual;
-- update final src for BOM creation
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('populate_plan_level: ' || 'End of chain for model '||to_char(pModelItemId), 1);
update bom_cto_src_orgs
set create_bom = 'Y', organization_type = l_source_type
where line_id = pLineId
and model_item_id = pModelItemId
and organization_id = l_curr_src_org
and rcv_org_id = l_curr_RcvOrgId;
oe_debug_pub.add('populate_plan_level: ' || 'inserted rcv org::'||to_char(l_curr_RcvOrgId)||' src org::'||to_char(l_curr_src_org), 2);
oe_debug_pub.add('populate_plan_level: ' || 'after insert 1',2);
-- If mrp_sources_v does not insert any rows into
-- bom_cto_src_orgs, this means that no sourcing rules are set-up
-- for this model item in this org. Assuming that in this case
-- the item in this org is sourced from itself, inserting a row
-- with the receiving org as the sourcing org
lStmtNumber := 150;
insert into bom_cto_src_orgs
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type,
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
pRcvOrgId,
pRcvOrgId,
'Y', -- create_bom
decode( l_source_type , 4 , 'N' , 6 , 'N' , 'Y' ) , -- cost_rollup
l_source_type, -- org_type is used to store the source_type
NULL, -- config_item_id
decode(l_curr_assg_type,6,'Y',3,'Y','N'), -- create_src_rules
NULL, -- rank, n/a
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs
where line_id = pLineId
and model_item_id = pModelItemId);
oe_debug_pub.add('populate_plan_level: ' || 'after insert 2',2);
-- do nothing, else insert a new row
-- Added by Renga Kannan to include one more column source_type to it.
lStmtNumber := 160;
insert into bom_cto_src_orgs
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the Source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
NULL, -- rec_org_id
pRcvOrgId,
'N', -- create_bom
'N', -- cost_rollup
l_source_type, -- org_type is used to store the source type
NULL, -- config_item_id
NULL, -- create_src_rules, n/a
NULL, -- rank, n/a
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs
where line_id = pLineId
and model_item_id = pModelItemId
and organization_id = pRcvOrgId);
oe_debug_pub.add('populate_plan_level: ' || 'after insert 3',2);
-- do nothing, else insert a new row
--
lStmtNumber := 170;
select nvl(master_organization_id,-99) -- bugfix 2646849: master_organization_id can be 0
into lValidationOrg
from oe_order_lines_all oel,
oe_system_parameters_all ospa
where oel.line_id = pLineid
and nvl(oel.org_id, -1) = nvl(ospa.org_id, -1) --bug 1531691
and oel.inventory_item_id = pModelItemId;
select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
into lValidationOrg from oe_order_lines_all oel
where oel.line_id = pLineId ;
insert into bom_cto_src_orgs
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type,
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
NULL, -- rec_org_id
lValidationOrg,
'N', -- create_bom
'N', -- cost_rollup
NULL, -- org_type, pending
NULL, -- config_item_id
NULL, -- create_src_rules, n/a
NULL, -- rank, n/a
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs
where line_id = pLineId
and model_item_id = pModelItemId
and organization_id = lVAlidationOrg);
oe_debug_pub.add('populate_plan_level: ' || 'after insert 4',2);
-- do nothing, else insert a new row
--
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('populate_plan_level: ' || 'Before getting validation org',2);
select nvl(fsp.inventory_organization_id,-99) --bugfix 2646849: 0 can be a valid orgn_id.
into lPoValidationOrg
from bom_cto_src_orgs bcso,
financials_system_params_all fsp,
inv_organization_info_v org
where bcso.line_id = pLineId
and bcso.create_bom = 'Y'
and bcso.organization_id = org.organization_id
and nvl(fsp.org_id, -1) = nvl(org.Operating_unit, -1); --bug 1531691
insert into bom_cto_src_orgs
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type,
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
NULL, -- rec_org_id
lPoValidationOrg,
'N', -- create_bom
'N', -- cost_rollup
NULL, -- org_type, pending
NULL, -- config_item_id
NULL, -- create_src_rules, n/a
NULL, -- rank, n/a
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs
where line_id = pLineId
and model_item_id = pModelItemId
and organization_id = lPoVAlidationOrg);
oe_debug_pub.add('populate_plan_level: ' || 'after insert 5',2);
insert into bom_cto_src_orgs
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type,
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
NULL, -- rec_org_id
mtl.organization_id,
'N', -- create_bom
'N', -- cost_rollup
NULL, -- org_type, pending
NULL, -- config_item_id
NULL, -- create_src_rules, n/a
NULL, -- rank, n/a
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from mtl_system_items mtl
where inventory_item_id = pModelItemId
and INVENTORY_ITEM_STATUS_CODE = 'Active'
and organization_id not in
(select organization_id
from bom_cto_src_orgs
where line_id = plineid
and top_model_line_id = lTopAtoLineId);
oe_debug_pub.add('populate_plan_level: ' || 'PO validation org is NULL, not inserting row for PO val org',2);
oe_debug_pub.add('populate_plan_level: ' || 'po_multiorg_error, not inserting row for PO val org',2);
oe_debug_pub.add('populate_plan_level: ' || 'others exception in PO validation block, not inserting row for PO val org',2);
This function updates table bom_cto_order_lines with the config_item_id for
a given model item.
It is called by "Match" and "Create_Item" programs.
+-------------------------------------------------------------------------*/
FUNCTION Update_Order_Lines(pLineId in number,
pModelId in number,
pConfigId in number)
RETURN integer
IS
lStmtNumber number;
-- If line exists, update it with the config item id
--
lStmtNumber := 20;
update bom_cto_order_lines
set config_item_id = pConfigId
where line_id = pLineId
and inventory_item_id = pModelId;
oe_debug_pub.add('populate_plan_level: ' || 'Update_Order_Lines:: ndf::model line does not exist in bcol'||to_char(lStmtNumber)||sqlerrm,1);
oe_debug_pub.add('populate_plan_level: ' || 'Update_Order_Lines:: others exception'||to_char(lStmtNumber)||sqlerrm,1);
END Update_Order_Lines;
This function updates table bom_cto_src_orgs with the config_item_id for
a given model item.
It is called by "Match" and "Create_Item" programs.
+-------------------------------------------------------------------------*/
FUNCTION Update_Src_Orgs(pLineId in number,
pModelId in number,
pConfigId in number)
RETURN integer
IS
BEGIN
--
-- Update all lines for the model item with the config item id
--
update bom_cto_src_orgs
set config_item_id = pConfigId
where line_id = pLineId
and model_item_id = pModelId;
oe_debug_pub.add('populate_plan_level: ' || 'Update_Src_Orgs:: Could not update the config item: '||sqlerrm,1);
oe_debug_pub.add('populate_plan_level: ' || 'Update_Src_Orgs:: others exception'||sqlerrm,1);
END Update_Src_Orgs;
select distinct assignment_id, assignment_type
into lAssignmentId, lAssignmentType
from mrp_sources_v msv
where msv.assignment_set_id = lMrpAssignmentSet
and msv.inventory_item_id = pModelItemId
and msv.organization_id = pRcvOrgId
and effective_date <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate
and assignment_type in (3,6);
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS
WHERE ASSIGNMENT_ID = lAssignmentId;
select 1
into lAssignmentExists
from mrp_sr_assignments
where assignment_set_id = lAssignmentRec.assignment_set_id
and assignment_type = lAssignmentRec.assignment_type
and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)
and nvl(ship_to_site_id,-1) = nvl(lAssignmentRec.ship_to_site_id,-1)
and sourcing_rule_type = lAssignmentRec.sourcing_rule_type
and nvl(inventory_item_id,-1) = pConfigId
and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
select 1
into lAssignmentExists
from mrp_sr_assignments
where assignment_set_id = lAssignmentRec.assignment_set_id
and assignment_type = lAssignmentRec.assignment_type
and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)
and nvl(ship_to_site_id,-1) = nvl(lAssignmentRec.ship_to_site_id,-1)
and sourcing_rule_type = lAssignmentRec.sourcing_rule_type
and inventory_item_id is null
and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
SELECT mrp_sr_assignments_s.nextval
INTO lConfigAssignmentId
FROM DUAL;
lAssignmentTbl(1).Last_Updated_By := lAssignmentRec.Last_Updated_By;
lAssignmentTbl(1).Last_Update_Date := lAssignmentRec.Last_Update_Date;
lAssignmentTbl(1).Last_Update_Login := lAssignmentRec.Last_Update_Login;
lAssignmentTbl(1).Program_Update_Date := lAssignmentRec.Program_Update_Date;
-- call mrp API to insert rec into assignment set
--
lStmtNum := 60;
select OEOL.line_id
, OEOL.top_model_line_id
, OEOL.ato_line_id
, OEOL.link_to_line_id
, OEOL.inventory_item_id
, OEOL.ship_from_org_id
, OEOL.component_sequence_id
, OEOL.component_code
, OEOL.item_type_code
, OEOL.schedule_ship_date
, MSYI.bom_item_type
, decode( OEOL.line_id, OEOL.ato_line_id , null , BIC.wip_supply_type )
, OEOL.header_id
, OEOL.ordered_quantity
, OEOL.order_quantity_uom
, nvl( MSYI.config_orgs , 1)
, MSYI.config_match
from oe_order_lines_all OEOL , bom_inventory_components BIC , mtl_system_items MSYI
where ato_line_id = p_bcol_line_id
and OEOL.component_sequence_id = BIC.component_sequence_id
and OEOL.inventory_item_id = MSYI.inventory_item_id
and MSYI.organization_id = c_organization_id
and OEOL.open_flag='Y' -- bugfix 1876618: look at only open orders
order by line_id ;
v_prog_update_date DATE;
select bom_explosion_temp_s.nextval
into v_mfg_comp_seq_id
from dual;
select inventory_item_id
into v_inventory_item_id
from oe_order_lines_all
where ato_line_id = p_bcol_line_id
and line_id = p_bcol_line_id ;
select master_organization_id
into v_organization_id
from oe_order_lines_all oel,
oe_system_parameters_all ospa
where oel.line_id = p_bcol_line_id
and nvl(oel.org_id, -1) = nvl(ospa.org_id, -1) --bug 1531691
and oel.inventory_item_id = v_inventory_item_id ;
select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
into v_organization_id from oe_order_lines_all oel
where oel.line_id = p_bcol_line_id;
select OEOL.line_id
, OEOL.top_model_line_id
, OEOL.ato_line_id
, OEOL.link_to_line_id
, OEOL.inventory_item_id
, OEOL.ship_from_org_id
, OEOL.component_sequence_id
, OEOL.component_code
, OEOL.item_type_code
, OEOL.schedule_ship_date
, MSYI.bom_item_type
, OEOL.header_id
, OEOL.ordered_quantity
, null
, OEOL.order_quantity_uom
, nvl( MSYI.config_orgs , 1 )
, MSYI.config_match
into v_bcol_line_id
, v_bcol_top_model_line_id
, v_bcol_ato_line_id
, v_bcol_link_to_line_id
, v_bcol_inventory_item_id
, v_bcol_ship_from_org_id
, v_bcol_component_sequence_id
, v_bcol_component_code
, v_bcol_item_type_code
, v_bcol_schedule_ship_date
, v_bcol_bom_item_type
, v_bcol_header_id
, v_bcol_ordered_quantity
, v_bcol_wip_supply_type
, v_bcol_order_quantity_uom
, v_bcol_config_creation
, v_bcol_perform_match
from oe_order_lines_all OEOL , mtl_system_items MSYI
where OEOL.ato_line_id = p_bcol_line_id
and OEOL.line_id = p_bcol_line_id
and MSYI.bom_item_type = '1'
and OEOL.inventory_item_id = MSYI.inventory_item_id
and v_organization_id = MSYI.organization_id ;
** check whether to update the oeol_all table with batchid?
*/
v_step := 'Step A5' ;
** update these records in oe_order_lines to indicate process locks
*/
v_step := 'Step A9' ;
oe_config_util.update_mfg_comp_seq_id( t_bcol(i).line_id
, v_mfg_comp_seq_id
, l_return_status );
oe_debug_pub.add('populate_bcol: ' || 'unexp error in update_mfg_comp_seq_id::'||sqlerrm , 1 );
oe_debug_pub.add('populate_bcol: ' || 'error in update_mfg_comp_seq_id::'||sqlerrm , 1 );
** insert this information into bom_cto_order_lines table
*/
insert into bom_cto_order_lines (
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,REQUEST_ID
,PROGRAM_UPDATE_DATE
,QTY_PER_PARENT_MODEL
,OPTION_SPECIFIC
,REUSE_CONFIG
,CONFIG_CREATION)
values (
t_bcol(i).LINE_ID
,t_bcol(i).HEADER_ID
,t_bcol(i).TOP_MODEL_LINE_ID
,t_bcol(i).LINK_TO_LINE_ID
,t_bcol(i).ATO_LINE_ID
,t_bcol(i).PARENT_ATO_LINE_ID
,t_bcol(i).INVENTORY_ITEM_ID
,t_bcol(i).SHIP_FROM_ORG_ID
,t_bcol(i).COMPONENT_SEQUENCE_ID
,t_bcol(i).COMPONENT_CODE
,t_bcol(i).ITEM_TYPE_CODE
,t_bcol(i).SCHEDULE_SHIP_DATE
,t_bcol(i).PLAN_LEVEL
,t_bcol(i).PERFORM_MATCH
,t_bcol(i).CONFIG_ITEM_ID
,t_bcol(i).BOM_ITEM_TYPE
,t_bcol(i).WIP_SUPPLY_TYPE
,t_bcol(i).ORDERED_QUANTITY
,t_bcol(i).ORDER_QUANTITY_UOM
,t_bcol(i).BATCH_ID
,sysdate
,gUserId /* CREATED_BY */
,sysdate /* LAST_UPDATE_DATE */
,gUserId /* LAST_UPDATED_BY */
,gLoginId /* LAST_UPDATE_LOGIN */
,FND_GLOBAL.PROG_APPL_ID /* PROGRAM_APPLICATION_ID */
,FND_GLOBAL.CONC_PROGRAM_ID /* PROGRAM_ID */
,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
,sysdate /* PROGRAM_UPDATE_DATE */
,t_bcol(i).ordered_quantity / t_bcol(t_bcol(i).parent_ato_line_id).ordered_quantity
,'N'
,'N'
,t_bcol(i).config_creation );
insert into bom_cto_order_lines_gt (
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,REQUEST_ID
,PROGRAM_UPDATE_DATE
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
,OPTION_SPECIFIC
,REUSE_CONFIG
,VALIDATION_ORG
)
values (
t_bcol(i).LINE_ID
,t_bcol(i).HEADER_ID
,t_bcol(i).TOP_MODEL_LINE_ID
,t_bcol(i).LINK_TO_LINE_ID
,t_bcol(i).ATO_LINE_ID
,t_bcol(i).PARENT_ATO_LINE_ID
,t_bcol(i).INVENTORY_ITEM_ID
,t_bcol(i).SHIP_FROM_ORG_ID
,t_bcol(i).COMPONENT_SEQUENCE_ID
,t_bcol(i).COMPONENT_CODE
,t_bcol(i).ITEM_TYPE_CODE
,t_bcol(i).SCHEDULE_SHIP_DATE
,t_bcol(i).PLAN_LEVEL
,t_bcol(i).PERFORM_MATCH
,t_bcol(i).CONFIG_ITEM_ID
,t_bcol(i).BOM_ITEM_TYPE
,t_bcol(i).WIP_SUPPLY_TYPE
,t_bcol(i).ORDERED_QUANTITY
,t_bcol(i).ORDER_QUANTITY_UOM
,t_bcol(i).BATCH_ID
,sysdate
,gUserId /* CREATED_BY */
,sysdate /* LAST_UPDATE_DATE */
,gUserId /* LAST_UPDATED_BY */
,gLoginId /* LAST_UPDATE_LOGIN */
,FND_GLOBAL.PROG_APPL_ID /* PROGRAM_APPLICATION_ID */
,FND_GLOBAL.CONC_PROGRAM_ID /* PROGRAM_ID */
,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
,sysdate /* PROGRAM_UPDATE_DATE */
,t_bcol(i).ordered_quantity / t_bcol(t_bcol(i).parent_ato_line_id).ordered_quantity
,t_bcol(i).config_creation
, 'N'
, 'N'
,t_bcol(i).SHIP_FROM_ORG_ID --bugfix 3555026
) ;
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select distinct
source_organization_id,
sourcing_rule_id,
nvl(source_type,1) ,
nvl( avg_transit_lead_time , 0 )
into
p_sourcing_org
, v_sourcing_rule_id
, v_source_type
, p_transit_lead_time
from mrp_sources_v msv
where msv.assignment_set_id = gMrpAssignmentSet
and msv.inventory_item_id = p_inventory_item_id
and msv.organization_id = p_organization_id
-- and nvl(msv.source_type,1) <> 3 commented by Renga for BUY odel
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate) -- Nvl fun is added by Renga Kannan on 05/05/2001
and nvl(disable_date, sysdate+1) > sysdate;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select count(*)
into v_sourcing_rule_count
from mrp_sources_v msv
where msv.assignment_set_id = gMrpAssignmentSet
and msv.inventory_item_id = p_inventory_item_id
and msv.organization_id = p_organization_id
and nvl(msv.source_type,1) <> 3
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate)
/* Nvl fun is added by Renga Kannan on 05/05/2001 */
and nvl(disable_date, sysdate+1) > sysdate;
select assignment_set_name into assign_set_name
from mrp_Assignment_sets
where assignment_set_id = gMrpAssignmentSet ;
v_orgs_tbl.delete ; /* reinitialize table to check circular sourcing */
select h.source_document_type_id
into l_source_document_type_id
from oe_order_headers_all h, oe_order_lines_all l
where h.header_id = l.header_id
and l.line_id = pLineId
and rownum = 1;
-- WIP first updates the existing wip-reservation to the overcompleted-qty and then
-- calls INV to do the transfer. INV transfers the new qty from wip to inv.
-- for eg., if workorder qty=10, and you overcomplete 15, then, wip reservation is first
-- updated to 15, and inv then transfers this to inv reservation.
-- In this scenerio, since the new qty is more than the sales order qty, CTO was preventing
-- an unreserve activity. With this fix, CTO will check wrt reservation qty and decide whether
-- to allow unreservation or not.
--
l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_order_line_id );
select nvl(sum( LEAST(nvl(wdd.shipped_quantity,0), nvl(wdd.picked_quantity,0)) ), 0)
into l_shipped_qty
from wsh_delivery_details_ob_grp_v wdd -- Modified by Renga on 11/02/03
where wdd.source_line_id = p_order_line_id
and wdd.source_code = 'OE'
and wdd.released_status = 'C' -- Closed [C]
and nvl(wdd.inv_interfaced_flag, 'N') <> 'Y';
select nvl(sum(mr.primary_reservation_quantity),0)
into l_reservation_qty
from mtl_reservations mr
where mr.demand_source_type_id = decode (l_source_document_type_id, 10,
inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe )
and mr.primary_reservation_quantity > 0
and mr.demand_source_line_id = p_order_line_id;
select fl.file_data into l_blob_loc
from fnd_lobs fl, fnd_attached_documents fad, fnd_documents_tl fdt
where fad.pk1_value = to_char(p_po_val_org_id)
and fad.pk2_value = to_char(p_item_id)
and fad.entity_name = 'MTL_SYSTEM_ITEMS'
and fad.pk3_value = 'CTO:BOM:ATTACHMENT'
and fad.document_id = fdt.document_id
and fdt.media_id = fl.file_id
and fdt.language = userenv('LANG');
fnd_documents_pkg.insert_row(
x_rowid => l_row_id,
x_document_id => l_doc_id,
x_creation_date => sysdate,
x_created_by => fnd_global.USER_ID,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.USER_ID,
x_last_update_login => fnd_global.USER_ID,
x_request_id => fnd_global.USER_ID,
x_program_application_id => fnd_global.PROG_APPL_ID,
x_program_id => fnd_global.CONC_REQUEST_ID,
x_program_update_date => sysdate,
x_datatype_id => 2,
x_category_id => 33,
x_security_type => 4,
x_security_id => NULL
,x_publish_flag => 'Y'
,x_image_type => null
,x_storage_type => null
,x_usage_type => 'S'
,x_start_date_active => sysdate
,x_end_date_active => null
,x_language => 'AMERICAN'
,x_description => p_desc
,x_file_name => null
,x_media_id => l_media_id
,x_attribute_category => null
,x_attribute1 => null
,x_attribute2 => null
,x_attribute3 => null
,x_attribute4 => null
,x_attribute5 => null
,x_attribute6 => null
,x_attribute7 => null
,x_attribute8 => null
,x_attribute9 => null
,x_attribute10 => null
,x_attribute11 => null
,x_attribute12 => null
,x_attribute13 => null
,x_attribute14 => null
,x_attribute15 => null );
Insert into fnd_documents_long_text
(
Media_id,
long_text)
Values
( l_media_id,p_text);
select (nvl(max(seq_num),0) + 10)
into l_seq_num
from fnd_attached_documents
where entity_name = 'MTL_SYSTEM_ITEMS'
and pk1_value = to_char(p_org_id) -- 2774570
and pk2_value = to_char(p_item_id); -- 6069512: Added to improve performance, we do not need seq to be unique across items.
select fnd_attached_documents_s.nextval
into l_attached_document_id
from dual;
FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
(x_rowid => l_row_id
, x_attached_document_id => l_attached_document_id
, x_document_id => l_doc_id
, x_seq_num => l_seq_num
, x_entity_name => 'MTL_SYSTEM_ITEMS'
, x_pk1_value => p_org_id
, x_pk2_value => p_item_id
, x_pk3_value => p_doc_type -- This field is used for procuring config
, x_pk4_value => NULL
, x_pk5_value => NULL
, x_automatically_added_flag => 'N'
, x_creation_date => sysdate
, x_created_by => fnd_global.USER_ID
, x_last_update_date => sysdate
, x_last_updated_by => fnd_global.USER_ID
, x_last_update_login => fnd_global.LOGIN_ID
-- following parameters are required for the API but we do not
-- use so send in as null
, x_column1 => null
, x_datatype_id => null
, x_category_id => null
, x_security_type => null
, X_security_id => null
, X_publish_flag => null
, X_image_type => null
, X_storage_type => null
, X_usage_type => null
, X_language => null
, X_description => null
, X_file_name => null
, X_media_id => l_media_id
, X_doc_attribute_Category => null
, X_doc_attribute1 => null
, X_doc_attribute2 => null
, X_doc_attribute3 => null
, X_doc_attribute4 => null
, X_doc_attribute5 => null
, X_doc_attribute6 => null
, X_doc_attribute7 => null
, X_doc_attribute8 => null
, X_doc_attribute9 => null
, X_doc_attribute10 => null
, X_doc_attribute11 => null
, X_doc_attribute12 => null
, X_doc_attribute13 => null
, X_doc_attribute14 => null
, X_doc_attribute15 => null
);
select level,
bcol.inventory_item_id inventory_item_id,
bcol.ordered_quantity ordered_qty,
bcol.ship_from_org_id ship_from_org_id
from bom_cto_order_lines bcol
start with line_id = p_line_id
connect by link_to_line_id = prior line_id;
select msi.description,
msi.primary_uom_code,
msi.concatenated_segments
into l_desc,
l_prim_uom,
l_item_name
from mtl_system_items_kfv msi
where msi.inventory_item_id = comp_cur.inventory_item_id
and msi.organization_id = comp_cur.ship_from_org_id;
/* Select the base model_item_id from the parent. Then compare the given inventory_item with the first
level bill of parent model. If you get a match it is ato item. If we cannot get a match it is config item
*/
SELECT base_item_id
INTO l_model_item_id
FROM MTL_SYSTEM_ITEMS
WHERE Inventory_item_id = p_parent_item_id
AND organization_id = p_organization_id;
SELECT 'Y'
INTO l_found
FROM BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BOM
WHERE BIC.bill_sequence_id = BOM.Common_bill_sequence_id
AND BOM.assembly_item_id = l_model_item_id
AND BOM.Organization_id = p_organization_id
AND BIC.component_item_id = p_inventory_item_id;
select reservation_id,reservation_quantity,supply_source_type_id
from mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where mr.demand_source_line_id = oel.line_id --ato item line id
and oel.line_id = p_line_Id
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code='ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = mr.demand_source_header_id
--and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
and mr.reservation_quantity > 0;
select component_item_id
, component_sequence_id
, bom_item_type
from bom_inventory_components
where bill_sequence_id = c_bill_sequence_id
and ( bom_item_type = '2' OR
( bom_item_type = '1' and nvl( wip_supply_type , 6 ) <> 6 )
/* check only non phantom models and option classes */
) ;
select organization_id
, assembly_item_id
into v_organization_id
, v_assembly_item_id
from bom_bill_of_materials
where bill_sequence_id = p_bill_sequence_id ;
select common_bill_sequence_id
into v_element_bill_seq_id
from bom_bill_of_materials
where assembly_item_id = v_component_item_id
and organization_id = v_organization_id ;
This function recursively explodes a configuration item BOM and inserts
it into bom_explosion_temp with a unique group_id. It is called while
displaying the configuration BOM from iSupplierPortal.
+----------------------------------------------------------------------*/
FUNCTION create_isp_bom
(
p_item_id IN number,
p_org_id IN number)
RETURN NUMBER IS
xGrpId number;
select bom_explosion_temp_s.nextval
into xGrpId
from dual;
-- insert top level config BOM
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_item_id,
--component_sequence_id,
plan_level,
component_quantity,
component_code,
item_num,
group_id)
select
bic.bill_sequence_id,
bic.bill_sequence_id,
p_org_id,
to_char(l_sort),
bic.component_item_id,
--bic.component_sequence_id,
nvl(bic.plan_level, 0),
bic.component_quantity,
to_char(bic.bill_sequence_id),
bic.item_num,
xGrpId
from
bom_inventory_components bic,
bom_bill_of_materials bbom
where bbom.assembly_item_id = p_item_id
and bbom.organization_id = p_org_id
and bbom.alternate_bom_designator is null
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and nvl(bic.optional_on_model,1) = 1;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_item_id,
--component_sequence_id,
plan_level,
component_quantity,
component_code,
item_num,
group_id)
select
bic.bill_sequence_id,
bic.bill_sequence_id,
p_org_id,
to_char(l_sort),
bic.component_item_id,
--concat(concat(bet.component_sequence_id,'-'),bic.component_sequence_id),
decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
bic.component_quantity,
CTO_UTILITY_PK.Concat_Values(bet.component_code,bic.bill_sequence_id),
bic.item_num,
xGrpId
from
bom_inventory_components bic,
bom_bill_of_materials bbom,
bom_explosion_temp bet,
mtl_system_items msi
where bbom.assembly_item_id = bet.component_item_id
and bbom.organization_id = bet.organization_id
and bbom.alternate_bom_designator is null
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and nvl(bic.optional_on_model,1) = 1
and bet.group_id = xGrpId
and bet.sort_order = to_char(l_sort - 1)
and bet.component_item_id = msi.inventory_item_id
and bet.organization_id = msi.organization_id
and msi.base_item_id is not null
and nvl(msi.auto_created_config_flag, 'N') = 'Y';
delete from bom_explosion_temp bet
where bet.group_id = xGrpId
and bet.component_item_id =
(select msi.inventory_item_id
from mtl_system_items msi
where msi.inventory_item_id = bet.component_item_id
and msi.organization_id = bet.organization_id
and msi.base_item_id is not null
and nvl(msi.auto_created_config_flag, 'N') = 'Y');
oe_debug_pub.add ('Deleted Row Count : ' || rowcount, 2);
cto_wip_workflow_api_pk.cto_debug('create_isp_bom', 'Deleted Row Count:'||rowcount);
delete from cst_item_cost_details
where inventory_item_id = p_config_item_id
and organization_id = p_organization_id
and cost_type_id = p_dest_cost_type_id ;
delete from cst_item_costs
where inventory_item_id = p_config_item_id
and organization_id = p_organization_id
and cost_type_id = p_dest_cost_type_id ;
Insert a row into the cst_item_costs_table
+------------------------------------------------------- */
lStmtNumber := 220;
insert into CST_ITEM_COSTS
(inventory_item_id,
organization_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_asset_flag,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
defaulted_flag,
cost_update_id,
pl_material,
pl_material_overhead,
pl_resource,
pl_outside_processing,
pl_overhead,
tl_material,
tl_material_overhead,
tl_resource,
tl_outside_processing,
tl_overhead,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost ,
overhead_cost,
pl_item_cost,
tl_item_cost,
item_cost,
unburdened_cost ,
burden_cost,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select distinct
p_config_item_id, -- INVENTORY_ITEM_ID
p_organization_id,
p_dest_cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
C.inventory_asset_flag,
C.lot_size,
C.based_on_rollup_flag,
C.shrinkage_rate,
C.defaulted_flag,
p_src_cost_type_id, -- cost_update_id
C.pl_material,
C.pl_material_overhead,
C.pl_resource,
C.pl_outside_processing,
C.pl_overhead,
C.tl_material,
C.tl_material_overhead,
C.tl_resource,
C.tl_outside_processing,
C.tl_overhead,
C.material_cost,
C.material_overhead_cost,
C.resource_cost,
C.outside_processing_cost ,
C.overhead_cost,
C.pl_item_cost,
C.tl_item_cost,
C.item_cost,
C.unburdened_cost ,
C.burden_cost,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.ATTRIBUTE12,
C.attribute13,
C.attribute14,
C.attribute15
from
cst_item_costs C
where C.inventory_item_id = p_config_item_id
and C.organization_id = p_organization_id
and C.cost_type_id = p_src_cost_type_id;
oe_debug_pub.add('copy_cost: ' || 'after insert:CST_ITEM_COSTS',2);
oe_debug_pub.add('copy_cost: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
Insert rows into the cst_item_cost_details table
+-----------------------------------------------------*/
lStmtNumber := 230;
insert into cst_item_cost_details
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_resource_id,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select distinct
p_config_item_id, -- inventory_item_id
p_dest_cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
p_organization_id,
c.operation_sequence_id,
c.operation_seq_num,
c.department_id,
c.level_type,
c.activity_id,
c.resource_seq_num,
c.resource_id,
c.resource_rate,
c.item_units,
c.activity_units,
c.usage_rate_or_amount,
c.basis_type,
c.basis_resource_id,
c.basis_factor,
c.net_yield_or_shrinkage_factor,
c.item_cost,
c.cost_element_id,
C.rollup_source_type,
C.activity_context,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.attribute12,
C.attribute13,
C.attribute14,
C.attribute15
from
cst_item_cost_details C
where C.inventory_item_id = p_config_item_id
and C.organization_id = p_organization_id
and C.cost_type_id = p_src_cost_type_id ;
oe_debug_pub.add('copy_cost: ' || 'after insert:cst_item_cost_details',2);
oe_debug_pub.add('copy_cost: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
cursor config_update( c_organization_id in number)
is
select line_id, split_from_line_id
from oe_order_lines_all oeol , mtl_system_items msi
where oeol.line_id = p_ato_line_id
and oeol.inventory_item_id = msi.inventory_item_id
and msi.organization_id = c_organization_id
and msi.bom_item_type = 1 ;
v_config config_update%rowtype ;
select master_organization_id
into v_organization_id
from oe_order_lines_all oel,
oe_system_parameters_all ospa
where oel.line_id = p_ato_line_id
and nvl(oel.org_id, -1) = nvl(ospa.org_id, -1) ; --bug 1531691
select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
into v_organization_id from oe_order_lines_all oel
where oel.line_id = p_ato_line_id;
oe_debug_pub.add('CTOUTILB.split_line: opening config_update cursor ' , 1);
open config_update( v_organization_id ) ;
fetch config_update into v_config ;
exit when config_update%notfound ;
update bom_cto_order_lines set config_item_id = ( select bcol1.config_item_id
from bom_cto_order_lines bcol1
where bcol1.line_id = v_config.split_from_line_id )
where line_id = v_config.line_id ;
oe_debug_pub.add('CTOUTILB.split_line: update cnt ' || SQL%ROWCOUNT , 1);
update bom_cto_order_lines bcol
set ordered_quantity = ( select ordered_quantity
from oe_order_lines_all
where ato_line_id = bcol.ato_line_id
and line_id = bcol.line_id )
where ato_line_id = p_ato_line_id ;
update bom_cto_order_lines bcol
set ship_from_org_id = ( select ship_from_org_id
from oe_order_lines_all
where ato_line_id = bcol.ato_line_id
and line_id = bcol.line_id )
where ato_line_id = p_ato_line_id ;
SELECT reservation_id
INTO l_reservation_id
FROM
mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
WHERE
mr.demand_source_line_id = oel.line_id
and oel.line_id = pconfigLineId --- Configuration item line id
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code= 'ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag ='B')
and mso.sales_order_id = mr.demand_source_header_id
--and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10, INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
and mr.reservation_quantity > 0
and rownum = 1;
insert into bom_cto_order_lines (
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION)
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
from bom_cto_order_lines_gt
where ato_line_id = p_ato_line_id ;
delete from bom_cto_order_lines_gt
where ato_line_id = p_ato_line_id ;
insert into bom_cto_order_lines_gt (
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
,VALIDATION_ORG)
select
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
,SHIP_FROM_ORG_ID --3555026
from bom_cto_order_lines
where ato_line_id = p_ato_line_id ;
CTO_CONFIG_BOM_PK.g_t_dropped_item_type.delete;
SELECT u.user_name
INTO x_planner_code
FROM mtl_system_items_vl item
,mtl_planners p
,fnd_user u
WHERE item.inventory_item_id = p_inventory_item_id
and item.organization_id = p_organization_id
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
Select bcol.line_id,
bcol.config_item_id
from bom_cto_order_lines bcol
where ato_line_id = p_ato_line_id
and config_item_id is not null;
SELECT distinct nvl(fsp.inventory_organization_id,0) po_valid_org
FROM financials_system_params_all fsp
Where fsp.org_id in (select org.operating_unit
from inv_organization_info_v org,
mtl_system_items msi
where msi.inventory_item_id = p_config_item_id
and msi.organization_id = org.organization_id);
SELECT document_id
INTO l_document_id
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(config_orgs_rec.po_valid_org) -- 2774570
AND pk2_value = to_char(config_items_rec.config_item_id) -- 2774570
AND entity_name = 'MTL_SYSTEM_ITEMS'
AND Pk3_value = 'CTO:BOM:ATTACHMENT';
select count(*) into bcol_count
from bom_cto_order_lines
where ato_line_id = p_bcol_line_id;
select count(*) into oe_count
from oe_order_lines_all
where ato_line_id = p_bcol_line_id
and item_type_code <>'CONFIG'
and ordered_quantity > 0 ; -- Added this condition to take care of cancel line cases.
select 'Y' into v_oe_bcol_diff from dual
where exists
((Select 1
from oe_order_lines_all oel
Where not exists
( Select bcol.line_id,
bcol.ordered_quantity,
bcol.inventory_item_id
from bom_cto_order_lines bcol
where bcol.ato_line_id = p_bcol_line_id
and bcol.line_id = oel.line_id
and bcol.ordered_quantity = oel.ordered_quantity
and bcol.inventory_item_id = oel.inventory_item_id
)
AND oel.top_model_line_id is not null
AND oel.ato_line_id = p_bcol_line_id
AND oel.item_type_code <>'CONFIG'
AND oel.ordered_quantity > 0 ) -- Added this condition to take care of cancel line cases.
UNION
(Select 1
from bom_cto_order_lines bcol
Where not exists
( Select oel.line_id,
oel.ordered_quantity,
oel.inventory_item_id
from oe_order_lines_all oel
where oel.ato_line_id = p_bcol_line_id
and oel.line_id = bcol.line_id
and oel.ordered_quantity = bcol.ordered_quantity
and oel.inventory_item_id = bcol.inventory_item_id
and oel.item_type_code <>'CONFIG'
and oel.ordered_quantity > 0 -- Added this condition to take care of cancel line cases.
)
AND bcol.top_model_line_id is not null
AND bcol.ato_line_id = p_bcol_line_id )) ;
select nvl(decode(bcolOptions.line_id, bcolModel.line_id, bcolOptions.inventory_item_id,
bcolOptions.config_item_id),
bcolOptions.inventory_item_id) COMPONENT_ITEM_ID
from
bom_cto_order_lines_gt bcolModel, -- Model /* sushant made changes for bug 4341156 */
bom_cto_order_lines_gt bcolOptions -- Options /* sushant made changes for bug 4341156 */
where bcolModel.line_id = p_line_id
and (bcolOptions.parent_ato_line_id = bcolModel.line_id or
bcolOptions.line_id = bcolModel.line_id)
order by 1;
oe_debug_pub.add ('Number of records selected = '|| l_comp_item_id.count);
select sum(nvl(primary_reservation_quantity,0)) primary_reservation_quantity,--bugfix2466429
sum(nvl(reservation_quantity,0)) secondary_reservation_quantity, --OPM
supply_source_type_id
from mtl_reservations
where demand_source_type_id = decode (l_source_document_type_id, 10,
inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and demand_source_line_id = p_order_line_id
and supply_source_type_id IN
( inv_reservation_global.g_source_type_inv,
inv_reservation_global.g_source_type_wip,
inv_reservation_global.g_source_type_po,
inv_reservation_global.g_source_type_req,
inv_reservation_global.g_source_type_internal_req,
inv_reservation_global.g_source_type_asn,
inv_reservation_global.g_source_type_rcv
)
group by supply_source_type_id;
Select msi.primary_uom_code
into x_primary_uom_code
from mtl_system_items msi,
oe_order_lines_all oel
where msi.inventory_item_id = oel.inventory_item_id
and msi.organization_id = oel.ship_from_org_id
and oel.line_id = p_order_line_id; --bugfix 4557050
-- Since flow does not update the schedule with new line_id when the order line is split, we need
-- to call the following function which will determine the open quantity.
-- If open_qty exists, we should keep the line status in PRODUCTION_OPEN
--OPM and IREQ (kkonada), get flow open quantity from MRP api
--This would work for both fresh order line and split order line
-- As per Kiran, Flow API allwasy returns in primary reservation qty.
lStmtNum := 30;
Select sum(CTO_UTILITY_PK.convert_uom(po.uom_code,x_primary_uom_code,nvl(po.quantity,0),po.item_id)),
sum(nvl(po.secondary_quantity,0))
into l_ext_req_qty,
l_ext_req_secondary_qty
from po_requisitions_interface_all po,
oe_order_lines_all oel
where po.interface_source_line_id = oel.line_id
and oel.line_id = p_order_line_id
and po.item_id = oel.inventory_item_id
and po.source_type_code = 'VENDOR'
and po.process_flag is null;
Select sum(CTO_UTILITY_PK.convert_uom(po.uom_code,x_primary_uom_code,nvl(po.quantity,0),po.item_id)),
sum(nvl(po.secondary_quantity,0))
into l_int_req_qty,
l_int_req_secondary_qty --changed as part of opm code review
from po_requisitions_interface_all po,
oe_order_lines_all oel
where po.interface_source_line_id = oel.line_id
and oel.line_id = p_order_line_id
and po.item_id = oel.inventory_item_id
and po.source_type_code = 'INVENTORY'
and po.process_flag is null;