[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 05/03/94 Julie Maeyama Update logic |
| |
+===========================================================================+
-------------------------- bmartorg_assign_rtg_orgid ------------------------
NAME
bmartorg_assign_rtg_orgid - assign organization_id to all routing tables
DESCRIPTION
assign org id to all routing and its child tables
REQUIRES
err_text out buffer to return error message
MODIFIES
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_OP_RESOURCES_INTERFACE
MTL_RTG_ITEM_REVS_INTERFACE
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
----------------------------------------------------------------------------*/
FUNCTION bmartorg_assign_rtg_orgid (
err_text IN OUT VARCHAR2
)
return INTEGER
IS
stmt_num NUMBER;
update bom_op_routings_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update bom_op_sequences_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update bom_op_resources_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
update mtl_rtg_item_revs_interface ori
set organization_id = (select organization_id from
mtl_parameters a
where a.organization_code = ori.organization_code)
where process_flag = 1
and organization_id is null
and organization_code is not null
and exists (select organization_code
from mtl_parameters b
where b.organization_code = ori.organization_code)
and rownum < 2000;
inserts record into MTL_INTERFACE_ERRORS.
REQUIRES
err_text out buffer to return error message
MODIFIES
MTL_RTG_ITEM_REVS_INTERFACE
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
-----------------------------------------------------------------------------*/
FUNCTION bmasrrev_assign_rtg_revision (
org_id NUMBER,
all_org NUMBER,
user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text IN OUT VARCHAR2
)
return INTEGER
IS
stmt_num NUMBER := 0;
select organization_code OC, organization_id OI,
process_revision PR,
inventory_item_id III, inventory_item_number IIN,
transaction_id TI, change_notice CN, ecn_initiation_date CID,
implementation_date ID, effectivity_date ED
from mtl_rtg_item_revs_interface
where process_flag = 1
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
update mtl_rtg_item_revs_interface
set transaction_id = mtl_system_items_interface_s.nextval
where transaction_id is null
and process_flag = 1
and rownum < 500;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update mtl_rtg_item_revs_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update mtl_rtg_item_revs_interface set
organization_id = nvl(organization_id, c1rec.OI),
inventory_item_id = nvl(inventory_item_id, c1rec.III),
process_revision = UPPER(c1rec.PR),
process_flag = 2,
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
effectivity_date = nvl(effectivity_date, sysdate),
IMPLEMENTATION_DATE = nvl(effectivity_date, sysdate)
where transaction_id = c1rec.TI;
inserts record into MTL_INTERFACE_ERRORS.
REQUIRES
err_text out buffer to return error message
MODIFIES
BOM_OP_ROUTINGS_INTERFACE
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
-----------------------------------------------------------------------------*/
FUNCTION bmprtgh_assign_rtg_header (
org_id NUMBER,
all_org NUMBER,
user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text IN OUT VARCHAR2
)
return INTEGER
IS
stmt_num NUMBER;
cursor c1 is select
organization_id OI, organization_code OC,
assembly_item_id AII, assembly_item_number AIN,
completion_locator_id CLI, location_name LN,
common_assembly_item_id CAII, common_item_number CAIN,
common_routing_sequence_id CRSI,
alternate_routing_designator ARD, transaction_id TI,
routing_sequence_id RSI, process_revision PR,
creation_date CD, created_by CB, last_update_login LUL,
last_update_date LUD, last_updated_by LUB
from bom_op_routings_interface
where process_flag = 1
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
cursor c2 is select
transaction_id TI, common_routing_sequence_id CRSI,
assembly_item_id AII, routing_sequence_id RSI,
common_assembly_item_id CAID, organization_id OI,
alternate_routing_designator ARD
from bom_op_routings_interface
where process_flag = 99
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
update bom_op_routings_interface ori
set transaction_id = mtl_system_items_interface_s.nextval,
routing_sequence_id = nvl(routing_sequence_id,
bom_operational_routings_s.nextval)
where transaction_id is null
and process_flag = 1
and rownum < 500;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = 3
where transaction_id = c1rec.TI;
** insert given process_revision
*/
if (c1rec.PR is not null) then
insert into mtl_rtg_item_revs_interface
(organization_id,
inventory_item_id,
process_revision,
process_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
effectivity_date,
IMPLEMENTATION_DATE,
transaction_id)
values (c1rec.OI,
c1rec.AII,
UPPER(c1rec.PR),
2,
nvl(c1rec.LUD,sysdate),
nvl(c1rec.LUB,user_id),
nvl(c1rec.CD,sysdate),
nvl(c1rec.CB,user_id),
nvl(c1rec.LUL, user_id),
req_id,
prog_appid,
prog_id,
sysdate,
sysdate,
sysdate,
mtl_system_items_interface_s.nextval);
goto update_rtg;
select starting_revision into c1rec.PR
from mtl_parameters
where organization_id = c1rec.OI;
select count(process_revision) into revs_int
from mtl_rtg_item_revs_interface
where organization_id = c1rec.OI
and inventory_item_id = c1rec.AII
and process_flag <> 3 and process_flag <> 7;
select count(process_revision) into revs_prod
from mtl_rtg_item_revisions
where organization_id = c1rec.OI
and inventory_item_id = c1rec.AII;
** insert a record into the revs interface table because one does not exist
*/
if (revs = 0) then
insert into mtl_rtg_item_revs_interface
(organization_id,
inventory_item_id,
process_revision,
process_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
effectivity_date,
IMPLEMENTATION_DATE,
transaction_id)
values (c1rec.OI,
c1rec.AII,
c1rec.PR,
2,
nvl(c1rec.LUD, sysdate),
nvl(c1rec.LUB, user_id),
nvl(c1rec.CD, sysdate),
nvl(c1rec.CB, user_id),
nvl(c1rec.LUL, user_id),
req_id,
prog_appid,
prog_id,
sysdate,
sysdate,
sysdate,
mtl_system_items_interface_s.nextval);
** update bom_op_routings_interface with the modified column values
*/
<>
stmt_num := 6;
update bom_op_routings_interface
set organization_id = nvl(organization_id, c1rec.OI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
completion_locator_id = nvl(completion_locator_id, c1rec.CLI),
common_assembly_item_id = nvl(common_assembly_item_id, c1rec.CAII),
routing_type = nvl(routing_type, 1),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 99
where transaction_id = c1rec.TI;
update bom_op_routings_interface set
process_flag = proc_flag,
common_routing_sequence_id = c2rec.CRSI,
common_assembly_item_id = assy_id
where transaction_id = c2rec.TI;
inserts record into MTL_INTERFACE_ERRORS.
REQUIRES
err_text out buffer to return error message
MODIFIES
BOM_OP_SEQUENCES_INTERFACE
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
-----------------------------------------------------------------------------*/
FUNCTION bmasopd_assign_operation_data (
org_id NUMBER,
all_org NUMBER,
user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text IN OUT VARCHAR2
)
return INTEGER
IS
stmt_num NUMBER := 0;
select organization_code OC, organization_id OI,
operation_sequence_id OSI,
assembly_item_id AII, assembly_item_number AIN,
alternate_routing_designator ARD, routing_sequence_id RSI,
department_id DI, department_code DC,
operation_code SOC, standard_operation_id SOI,
transaction_id TI, operation_seq_num OSN,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED, -- Changed for bug 2647027
-- to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED,
minimum_transfer_quantity MTQ, count_point_type CPT,
operation_description OD, backflush_flag BF,
option_dependent_flag ODF, attribute_category AC,
attribute1 A1, attribute1 A2, attribute1 A3,
attribute1 A4,attribute1 A5,attribute1 A6,attribute1 A7,
attribute1 A8,attribute1 A9,attribute1 A10,attribute1 A11,
attribute1 A12,attribute1 A13,attribute1 A14,attribute1 A15
from bom_op_sequences_interface
where process_flag = 1
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
update bom_op_sequences_interface
set transaction_id = mtl_system_items_interface_s.nextval,
operation_sequence_id = nvl(operation_sequence_id,
bom_operation_sequences_s.nextval)
where transaction_id is null
and process_flag = 1
and rownum < 500;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
select DEPARTMENT_ID, MINIMUM_TRANSFER_QUANTITY,
COUNT_POINT_TYPE, OPERATION_DESCRIPTION,
BACKFLUSH_FLAG, OPTION_DEPENDENT_FLAG,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
into std_dept_id, std_min_qty, std_cnt_pt, std_desc,
std_bkflsh, std_opt, std_attcat, std_att1, std_att2,
std_att3, std_att4, std_att5, std_att6, std_att7,
std_att8, std_att9, std_att10, std_att11, std_att12,
std_att13, std_att14, std_att15
from bom_standard_operations
where organization_id = c1rec.OI
and standard_operation_id = c1rec.SOI;
update bom_op_sequences_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_sequences_interface set
department_id = nvl(department_id, c1rec.DI),
organization_id = nvl(organization_id, c1rec.OI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
process_flag = 2,
standard_operation_id = nvl(standard_operation_id,c1rec.SOI),
routing_sequence_id = nvl(routing_sequence_id, c1rec.RSI),
operation_sequence_id = nvl(operation_sequence_id, c1rec.OSI),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
backflush_flag = nvl(backflush_flag, nvl(c1rec.BF,1)),
count_point_type = nvl(count_point_type,nvl(c1rec.CPT, 1)),
operation_description = nvl(operation_description,
nvl(c1rec.OD,NULL)),
option_dependent_flag =nvl(option_dependent_flag,nvl(c1rec.ODF,2)),
minimum_transfer_quantity = nvl(minimum_transfer_quantity,
nvl(c1rec.MTQ, 0.00)),
attribute_category = nvl(attribute_category,nvl(c1rec.AC,NULL)),
attribute1 = nvl(attribute1, nvl(c1rec.A1, NULL)),
attribute2 = nvl(attribute2, nvl(c1rec.A2, NULL)),
attribute3 = nvl(attribute3, nvl(c1rec.A3, NULL)),
attribute4 = nvl(attribute4, nvl(c1rec.A4, NULL)),
attribute5 = nvl(attribute5, nvl(c1rec.A5, NULL)),
attribute6 = nvl(attribute6, nvl(c1rec.A6, NULL)),
attribute7 = nvl(attribute7, nvl(c1rec.A7, NULL)),
attribute8 = nvl(attribute8, nvl(c1rec.A8, NULL)),
attribute9 = nvl(attribute9, nvl(c1rec.A9, NULL)),
attribute10 = nvl(attribute10, nvl(c1rec.A10, NULL)),
attribute11 = nvl(attribute11, nvl(c1rec.A11, NULL)),
attribute12 = nvl(attribute12, nvl(c1rec.A12, NULL)),
attribute13 = nvl(attribute13, nvl(c1rec.A13, NULL)),
attribute14 = nvl(attribute14, nvl(c1rec.A14, NULL)),
attribute15 = nvl(attribute15, nvl(c1rec.A15, NULL))
where transaction_id = c1rec.TI;
insert into bom_op_resources_interface (
OPERATION_SEQUENCE_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
BASIS_TYPE,
SCHEDULE_FLAG,
AUTOCHARGE_TYPE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ASSEMBLY_ITEM_ID,
ALTERNATE_ROUTING_DESIGNATOR,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
ROUTING_SEQUENCE_ID,
ORGANIZATION_CODE,
ASSEMBLY_ITEM_NUMBER,
RESOURCE_CODE,
ACTIVITY,
TRANSACTION_ID,
PROCESS_FLAG) select
c1rec.OSI,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
USAGE_RATE_OR_AMOUNT,
USAGE_RATE_OR_AMOUNT_INVERSE,
BASIS_TYPE,
SCHEDULE_FLAG,
AUTOCHARGE_TYPE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
NULL,
NULL,
c1rec.OI,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
1
from bom_std_op_resources
where standard_operation_id = c1rec.SOI;
inserts record into MTL_INTERFACE_ERRORS.
REQUIRES
err_text out buffer to return error message
MODIFIES
BOM_OP_RESOURCES_INTERFACE
MTL_INTERFACE_ERRORS
RETURNS
0 if successful
SQLCODE if unsuccessful
NOTES
-----------------------------------------------------------------------------*/
FUNCTION bmasrsd_assign_resource_data (
org_id NUMBER,
all_org NUMBER,
user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text IN OUT VARCHAR2
)
return INTEGER
IS
stmt_num NUMBER := 0;
select operation_sequence_id OSI,assembly_item_number AIN,
assembly_item_id AII, organization_id OI,
organization_code OC,
alternate_routing_designator ARD, operation_seq_num OSN,
to_char(effectivity_date, 'YYYY/MM/DD HH24:MI:SS') ED, -- Changed for bug 2647027
-- to_char(effectivity_date, 'YYYY/MM/DD HH24:MI') ED,
routing_sequence_id RSI,
transaction_id TI
from bom_op_resources_interface
where process_flag = 1
and operation_sequence_id is null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select transaction_id TI, organization_id OI,
operation_sequence_id OSI, resource_id RI, resource_code RC,
activity_id AI, activity A, usage_rate_or_amount URA,
usage_rate_or_amount_inverse URAI, assigned_units AU,
basis_type BT, autocharge_type AUT, standard_rate_flag SRF
from bom_op_Resources_interface
where process_flag = 1
and operation_sequence_id is not null
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500;
select operation_sequence_id OSI
from bom_op_Resources_interface
where process_flag = 99
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and rownum < 500
group by operation_sequence_id;
insert into bom_op_resources_interface (
OPERATION_SEQUENCE_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_CODE,
ORGANIZATION_ID,
PROCESS_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY) select
operation_sequence_id,
10,
resource_id1,
resource_code1,
organization_id,
1,
sysdate,
user_id,
sysdate,
user_id
from bom_op_sequences_interface
where process_flag = 2
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and (resource_id1 is not null or resource_code1 is not null);
insert into bom_op_resources_interface (
OPERATION_SEQUENCE_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_CODE,
ORGANIZATION_ID,
PROCESS_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY) select
operation_sequence_id,
20,
resource_id2,
resource_code2,
organization_id,
1,
sysdate,
user_id,
sysdate,
user_id
from bom_op_sequences_interface
where process_flag = 2
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and (resource_id2 is not null or resource_code2 is not null);
insert into bom_op_resources_interface (
OPERATION_SEQUENCE_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_CODE,
ORGANIZATION_ID,
PROCESS_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY) select
operation_sequence_id,
30,
resource_id3,
resource_code3,
organization_id,
1,
sysdate,
user_id,
sysdate,
user_id
from bom_op_sequences_interface
where process_flag = 2
and (all_org = 1
or
(all_org = 2 and organization_id = org_id)
)
and (resource_id3 is not null or resource_code3 is not null);
update bom_op_resources_interface
set transaction_id = mtl_system_items_interface_s.nextval
where transaction_id is null
and process_flag = 1
and rownum < 500;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c1rec.TI;
update bom_op_resources_interface
set operation_sequence_id = c1rec.OSI,
assembly_item_id = c1rec.AII,
routing_sequence_id = c1rec.RSI
where transaction_id = c1rec.TI;
select bor.organization_id
into dummy_org_id
from bom_operation_sequences bos,
bom_operational_routings bor
where operation_sequence_id = c2rec.OSI
and bos.routing_sequence_id = bor.routing_sequence_id;
select bori.organization_id
into dummy_org_id
from bom_op_sequences_interface bosi,
bom_op_routings_interface bori
where operation_sequence_id = c2rec.OSI
and bosi.process_flag <>3 and bosi.process_flag <>7
and bori.process_flag <>3 and bori.process_flag <>7
and bosi.routing_sequence_id = bori.routing_sequence_id
and rownum = 1;
select bor.organization_id
into dummy_org_id
from bom_op_sequences_interface bosi,
bom_operational_routings bor
where operation_sequence_id = c2rec.OSI
and bosi.process_flag <> 3 and bosi.process_flag <> 7
and bosi.routing_sequence_id = bor.routing_sequence_id
and rownum = 1;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c2rec.TI;
select resource_id
into c2rec.RI
from bom_resources
where resource_code = c2rec.RC
and organization_id = c2rec.OI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c2rec.TI;
select activity_id
into c2rec.AI
from cst_activities
where activity = c2rec.A
and nvl(organization_id,c2rec.OI) = c2rec.OI;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c2rec.TI;
select department_id
into dummy_dept
from bom_operation_sequences
where operation_sequence_id = c2rec.OSI;
select department_id
into dummy_dept
from bom_op_sequences_interface
where operation_sequence_id = c2rec.OSI
and process_flag <>3 and process_flag <> 7
and rownum = 1;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c2rec.TI;
select bdr.AVAILABLE_24_HOURS_FLAG, bd.location_id
into dummy_24hours, dummy_loc
from bom_department_resources bdr,
bom_departments bd
where bdr.resource_id = c2rec.RI
and bdr.department_id = dummy_dept
and bdr.department_id = bd.department_id;
update bom_op_resources_interface set
process_flag = 3
where transaction_id = c2rec.TI;
select nvl(c2rec.BT,default_basis_type),
nvl(c2rec.AI, default_activity_id),
nvl(c2rec.AUT,decode(dummy_loc, NULL,
decode(AUTOCHARGE_TYPE, NULL, 2, 3, 2, 4, 2,
AUTOCHARGE_TYPE), nvl(AUTOCHARGE_TYPE, 2))),
nvl(c2rec.SRF, standard_rate_flag)
into c2rec.BT, c2rec.AI, c2rec.AUT, c2rec.SRF
from bom_resources
where resource_id = c2rec.RI;
update bom_op_resources_interface set
resource_id = c2rec.RI,
organization_id = c2rec.OI,
BASIS_TYPE = nvl(c2rec.BT,1),
AUTOCHARGE_TYPE = c2rec.AUT,
ACTIVITY_ID = c2rec.AI,
STANDARD_RATE_FLAG = nvl(c2rec.SRF, 1),
ASSIGNED_UNITS = c2rec.AU,
USAGE_RATE_OR_AMOUNT = nvl(c2rec.URA,1),
USAGE_RATE_OR_AMOUNT_INVERSE = nvl(c2rec.URAI,1),
SCHEDULE_FLAG = nvl(schedule_flag, 2),
PROCESS_FLAG = 99,
last_update_date = sysdate,
last_updated_by = user_id,
creation_date = sysdate,
created_by = user_id,
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id = nvl(program_application_id, prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate)
where transaction_id = c2rec.TI;
select count(distinct operation_sequence_id)
into total_recs
from bom_op_resources_interface
where process_flag = 99;
select mtl_system_items_interface_s.nextval
into dummy_txn
from sys.dual;
update bom_op_resources_interface
set transaction_id = dummy_txn,
process_flag = 2
where operation_sequence_id = c3rec.OSI
and process_flag = 99;
select routing_sequence_id
into rtg_seq_id
from bom_operational_routings
where organization_id = org_id
and assembly_item_id = item_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE');
select routing_sequence_id
into rtg_seq_id
from bom_op_routings_interface
where organization_id = org_id
and assembly_item_id = item_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and process_flag <> 3 and process_flag <> 7
and rownum = 1;
select operation_sequence_id
into op_seq_id
from bom_operation_sequences
where routing_sequence_id = rtg_seq_id
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date; -- Changed for bug 2647027
select operation_sequence_id
into op_seq_id
from bom_op_sequences_interface
where routing_sequence_id = rtg_seq_id
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
-- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and process_flag <> 3 and process_flag <> 7
and rownum = 1;
update bom_op_resources_interface set
operation_sequence_id = op_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and operation_seq_num = op_seq
and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
-- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
and operation_sequence_id is null
and rownum < 500;
update bom_op_sequences_interface set
routing_sequence_id = rtg_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and routing_sequence_id is null
and rownum < 500;
update bom_op_resources_interface set
routing_sequence_id = rtg_id
where process_flag = 1
and organization_id = org_id
and assembly_item_id = assy_id
and nvl(alternate_routing_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and routing_sequence_id is null
and rownum < 500;
select assembly_item_id, organization_id, alternate_routing_designator
into item_id, org_id, alt_desg
from bom_operational_routings
where routing_sequence_id = rtg_seq_id;
select assembly_item_id, organization_id, alternate_routing_designator
into item_id, org_id, alt_desg
from bom_op_routings_interface
where routing_sequence_id = rtg_seq_id
and process_flag <> 3 and process_flag <> 7
and rownum = 1;
select department_id
into dept_id
from bom_departments
where organization_id = org_id
and department_code = dept_code;
select standard_operation_id
into stdop_id
from bom_Standard_operations
where organization_id = org_id
and operation_code = stdop_code;
Select bos.rowid row_id
From bom_op_sequences_interface bos
where bos.process_flag = 1
and bos.assembly_item_id is null
and bos.organization_id = org_id
and bos.assembly_item_number = item_num
and bos.routing_sequence_id is null;
Select bor.rowid row_id
From bom_op_resources_interface bor
where bor.process_flag = 1
and bor.assembly_item_id is null
and bor.organization_id = org_id
and bor.assembly_item_number = item_num
and bor.routing_sequence_id is null;
update bom_op_sequences_interface
set assembly_item_id = item_id
where rowid = X_Operation.row_id;
update bom_op_resources_interface
set assembly_item_id = item_id
where rowid = X_Resource.row_id;