[Home] [Help]
1328: cursor c_curr_job_info_op is
1329: select wo.operation_seq_num,
1330: wo.quantity_in_queue,
1331: wo.quantity_waiting_to_move,
1332: standard_operation_id,
1333: operation_sequence_id,
1334: department_id,
1335: description,
1336: nvl(quantity_in_queue,0)+nvl(quantity_waiting_to_move,0) qty_available
1344: cursor c_curr_job_info is
1345: select wo.operation_seq_num,
1346: wo.quantity_in_queue,
1347: wo.quantity_waiting_to_move,
1348: standard_operation_id,
1349: operation_sequence_id,
1350: department_id,
1351: description,
1352: nvl(quantity_in_queue,0)+nvl(quantity_waiting_to_move,0) qty_available
1387:
1388: l_operation_seq_num NUMBER;
1389: l_quantity_in_queue NUMBER;
1390: l_quantity_waiting_to_move NUMBER;
1391: l_standard_operation_id NUMBER;
1392: l_operation_sequence_id NUMBER;
1393: l_department_id NUMBER;
1394: l_op_description WIP_OPERATIONS.DESCRIPTION%TYPE;
1395: l_qty_available NUMBER;
1647: l_valid := true;
1648: l_operation_seq_num := l_curr_job_info.operation_seq_num ;
1649: l_quantity_in_queue := l_curr_job_info.quantity_in_queue ;
1650: l_quantity_waiting_to_move := l_curr_job_info.quantity_waiting_to_move ;
1651: l_standard_operation_id := l_curr_job_info.standard_operation_id ;
1652: l_operation_sequence_id := l_curr_job_info.operation_sequence_id ;
1653: l_department_id := l_curr_job_info.department_id ;
1654: l_op_description := l_curr_job_info.description ;
1655: l_qty_available := l_curr_job_info.qty_available ;
1659: l_valid := true;
1660: l_operation_seq_num := l_curr_job_info.operation_seq_num ;
1661: l_quantity_in_queue := l_curr_job_info.quantity_in_queue ;
1662: l_quantity_waiting_to_move := l_curr_job_info.quantity_waiting_to_move ;
1663: l_standard_operation_id := l_curr_job_info.standard_operation_id ;
1664: l_operation_sequence_id := l_curr_job_info.operation_sequence_id ;
1665: l_department_id := l_curr_job_info.department_id ;
1666: l_op_description := l_curr_job_info.description ;
1667: l_qty_available := l_curr_job_info.qty_available ;
1692: RAISE e_invalid_field;
1693: END IF;
1694:
1695: -- Fill the starting job record with the operation information
1696: p_starting_job_rec.standard_operation_id := l_standard_operation_id ;
1697: p_starting_job_rec.OPERATION_SEQ_ID := l_operation_sequence_id ;
1698: p_starting_job_rec.department_id := l_department_id ;
1699: p_starting_job_rec.OPERATION_DESCRIPTION := l_op_description ;
1700: p_starting_job_rec.quantity_available := l_qty_available ;
1699: p_starting_job_rec.OPERATION_DESCRIPTION := l_op_description ;
1700: p_starting_job_rec.quantity_available := l_qty_available ;
1701: p_starting_job_rec.operation_seq_num := l_operation_seq_num ;
1702:
1703: if p_starting_job_rec.standard_operation_id is not null then
1704:
1705: l_stmt_num := 130;
1706: -- get the standard op code in this case ....
1707: select operation_code
1702:
1703: if p_starting_job_rec.standard_operation_id is not null then
1704:
1705: l_stmt_num := 130;
1706: -- get the standard op code in this case ....
1707: select operation_code
1708: into p_starting_job_rec.operation_code
1709: from bom_standard_operations
1710: where STANDARD_OPERATION_ID = p_starting_job_rec.standard_operation_id;
1705: l_stmt_num := 130;
1706: -- get the standard op code in this case ....
1707: select operation_code
1708: into p_starting_job_rec.operation_code
1709: from bom_standard_operations
1710: where STANDARD_OPERATION_ID = p_starting_job_rec.standard_operation_id;
1711:
1712: end if;
1713:
1706: -- get the standard op code in this case ....
1707: select operation_code
1708: into p_starting_job_rec.operation_code
1709: from bom_standard_operations
1710: where STANDARD_OPERATION_ID = p_starting_job_rec.standard_operation_id;
1711:
1712: end if;
1713:
1714: if p_txn_type <> WSMPCNST.MERGE THEN
2011: END IF;
2012: -- ST : Serial Support --
2013:
2014: -- All the starting jobs should be of same job type, and should
2015: -- have the quantity at the same standard operation and same intraoperation step.
2016: -- Store these values from the first job and compare for each of the jobs
2017: if l_job_type is NULL then
2018: l_job_type := p_starting_jobs_tbl(l_counter).job_type;
2019: end if;
2017: if l_job_type is NULL then
2018: l_job_type := p_starting_jobs_tbl(l_counter).job_type;
2019: end if;
2020:
2021: if l_std_op_id is NULL and p_starting_jobs_tbl(l_counter).standard_operation_id is NOT NULL then
2022: l_std_op_id := p_starting_jobs_tbl(l_counter).standard_operation_id;
2023: end if;
2024:
2025: if l_dept_id is NULL then
2018: l_job_type := p_starting_jobs_tbl(l_counter).job_type;
2019: end if;
2020:
2021: if l_std_op_id is NULL and p_starting_jobs_tbl(l_counter).standard_operation_id is NOT NULL then
2022: l_std_op_id := p_starting_jobs_tbl(l_counter).standard_operation_id;
2023: end if;
2024:
2025: if l_dept_id is NULL then
2026: l_dept_id := p_starting_jobs_tbl(l_counter).department_id;
2096: RAISE FND_API.G_EXC_ERROR;
2097:
2098: end if;
2099:
2100: -- Merge possible only at standard operation
2101: IF p_starting_jobs_tbl(l_counter).standard_operation_id is NULL then
2102: l_stmt_num := 85;
2103: IF g_log_level_error >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) then
2104: l_msg_tokens.delete;
2097:
2098: end if;
2099:
2100: -- Merge possible only at standard operation
2101: IF p_starting_jobs_tbl(l_counter).standard_operation_id is NULL then
2102: l_stmt_num := 85;
2103: IF g_log_level_error >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) then
2104: l_msg_tokens.delete;
2105: WSM_log_PVT.logMessage(p_module_name => l_module ,
2116: END IF;
2117:
2118: l_stmt_num := 90;
2119: -- Also check for the std_op_id, dept id checks.....
2120: IF (l_std_op_id <> nvl(p_starting_jobs_tbl(l_counter).standard_operation_id,-1))
2121: OR
2122: (l_dept_id <> nvl(p_starting_jobs_tbl(l_counter).department_id,-1))
2123: OR
2124: (l_intraop_step <> nvl(p_starting_jobs_tbl(l_counter).intraoperation_step,-1))
2501: -- call for the starting op....
2502: derive_val_starting_op ( p_txn_org_id => p_resulting_job_rec.organization_id,
2503: p_curr_op_seq_id => p_starting_job_rec.operation_seq_id,
2504: p_curr_op_code => p_starting_job_rec.operation_code,
2505: p_curr_std_op_id => p_starting_job_rec.standard_operation_id,
2506: p_curr_intra_op_step => p_starting_job_rec.intraoperation_step,
2507: p_new_comm_rtg_seq_id => p_resulting_job_rec.common_routing_sequence_id,
2508: p_new_rtg_rev_date => p_resulting_job_rec.routing_revision_date ,
2509: p_new_op_seq_num => p_resulting_job_rec.starting_operation_seq_num,
2757: -- call for the starting op....
2758: derive_val_starting_op ( p_txn_org_id => p_resulting_job_rec.organization_id,
2759: p_curr_op_seq_id => p_starting_job_rec.operation_seq_id,
2760: p_curr_op_code => p_starting_job_rec.operation_code,
2761: p_curr_std_op_id => p_starting_job_rec.standard_operation_id,
2762: p_curr_intra_op_step => p_starting_job_rec.intraoperation_step,
2763: p_new_comm_rtg_seq_id => p_resulting_job_rec.common_routing_sequence_id,
2764: p_new_rtg_rev_date => p_resulting_job_rec.routing_revision_date ,
2765: p_new_op_seq_num => p_resulting_job_rec.starting_operation_seq_num,
2873: -- Starting operation details....
2874: p_resulting_job_rec.starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
2875: p_resulting_job_rec.starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
2876: p_resulting_job_rec.starting_operation_code := p_starting_job_rec.operation_code;
2877: p_resulting_job_rec.starting_std_op_id := p_starting_job_rec.standard_operation_id;
2878: p_resulting_job_rec.starting_operation_seq_id := p_starting_job_rec.operation_seq_id;
2879: p_resulting_job_rec.department_id := p_starting_job_rec.department_id;
2880: p_resulting_job_rec.department_code := p_starting_job_rec.department_code;
2881: p_resulting_job_rec.operation_description := p_starting_job_rec.operation_description;
3142: -- Starting operation details....--
3143: p_resulting_job_rec.starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
3144: p_resulting_job_rec.starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
3145: p_resulting_job_rec.starting_operation_code := p_starting_job_rec.operation_code;
3146: p_resulting_job_rec.starting_std_op_id := p_starting_job_rec.standard_operation_id;
3147: p_resulting_job_rec.starting_operation_seq_id := p_starting_job_rec.operation_seq_id;
3148: p_resulting_job_rec.department_id := p_starting_job_rec.department_id;
3149: p_resulting_job_rec.department_code := p_starting_job_rec.department_code;
3150: p_resulting_job_rec.operation_description := p_starting_job_rec.operation_description;
3311: -- Starting operation details....--
3312: p_resulting_job_rec.starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
3313: p_resulting_job_rec.starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
3314: p_resulting_job_rec.starting_operation_code := p_starting_job_rec.operation_code;
3315: p_resulting_job_rec.starting_std_op_id := p_starting_job_rec.standard_operation_id;
3316: p_resulting_job_rec.starting_operation_seq_id := p_starting_job_rec.operation_seq_id;
3317: p_resulting_job_rec.department_id := p_starting_job_rec.department_id;
3318: p_resulting_job_rec.department_code := p_starting_job_rec.department_code;
3319: p_resulting_job_rec.operation_description := p_starting_job_rec.operation_description;
3400: -- Starting operation details....--
3401: p_resulting_job_rec.starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
3402: p_resulting_job_rec.starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
3403: p_resulting_job_rec.starting_operation_code := p_starting_job_rec.operation_code;
3404: p_resulting_job_rec.starting_std_op_id := p_starting_job_rec.standard_operation_id;
3405: p_resulting_job_rec.starting_operation_seq_id := p_starting_job_rec.operation_seq_id;
3406: p_resulting_job_rec.department_id := p_starting_job_rec.department_id;
3407: p_resulting_job_rec.department_code := p_starting_job_rec.department_code;
3408: p_resulting_job_rec.operation_description := p_starting_job_rec.operation_description;
3605: -- call for the starting op....
3606: derive_val_starting_op ( p_txn_org_id => p_resulting_job_rec.organization_id,
3607: p_curr_op_seq_id => p_starting_job_rec.operation_seq_id,
3608: p_curr_op_code => p_starting_job_rec.operation_code,
3609: p_curr_std_op_id => p_starting_job_rec.standard_operation_id,
3610: p_curr_intra_op_step => p_starting_job_rec.intraoperation_step,
3611: p_new_comm_rtg_seq_id => p_resulting_job_rec.common_routing_sequence_id,
3612: p_new_rtg_rev_date => p_resulting_job_rec.routing_revision_date ,
3613: p_new_op_seq_num => p_resulting_job_rec.starting_operation_seq_num,
3758: -- Starting operation details....--
3759: p_resulting_job_rec.starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
3760: p_resulting_job_rec.starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
3761: p_resulting_job_rec.starting_operation_code := p_starting_job_rec.operation_code;
3762: p_resulting_job_rec.starting_std_op_id := p_starting_job_rec.standard_operation_id;
3763: p_resulting_job_rec.starting_operation_seq_id := p_starting_job_rec.operation_seq_id;
3764: p_resulting_job_rec.department_id := p_starting_job_rec.department_id;
3765: p_resulting_job_rec.department_code := p_starting_job_rec.department_code;
3766: p_resulting_job_rec.operation_description := p_starting_job_rec.operation_description;
4497: -- Starting operation details....
4498: p_resulting_jobs_tbl(l_counter).starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
4499: p_resulting_jobs_tbl(l_counter).starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
4500: p_resulting_jobs_tbl(l_counter).starting_operation_code := p_starting_job_rec.operation_code;
4501: p_resulting_jobs_tbl(l_counter).starting_std_op_id := p_starting_job_rec.standard_operation_id;
4502: p_resulting_jobs_tbl(l_counter).starting_operation_seq_id := p_starting_job_rec.operation_seq_id;
4503:
4504: p_resulting_jobs_tbl(l_counter).department_id := p_starting_job_rec.department_id;
4505: p_resulting_jobs_tbl(l_counter).department_code := p_starting_job_rec.department_code;
4706: l_stmt_num := 140;
4707: derive_val_starting_op ( p_txn_org_id => p_resulting_jobs_tbl(l_counter).organization_id,
4708: p_curr_op_seq_id => p_starting_job_rec.operation_seq_id,
4709: p_curr_op_code => p_starting_job_rec.operation_code,
4710: p_curr_std_op_id => p_starting_job_rec.standard_operation_id,
4711: p_curr_intra_op_step => p_starting_job_rec.intraoperation_step,
4712: p_new_comm_rtg_seq_id => p_resulting_jobs_tbl(l_counter).common_routing_sequence_id,
4713: p_new_rtg_rev_date => p_resulting_jobs_tbl(l_counter).routing_revision_date ,
4714: p_new_op_seq_num => p_resulting_jobs_tbl(l_counter).starting_operation_seq_num,
4868: -- Starting operation details....
4869: p_resulting_jobs_tbl(l_counter).starting_operation_seq_num := p_starting_job_rec.operation_seq_num;
4870: p_resulting_jobs_tbl(l_counter).starting_intraoperation_step := p_starting_job_rec.intraoperation_step;
4871: p_resulting_jobs_tbl(l_counter).starting_operation_code := p_starting_job_rec.operation_code;
4872: p_resulting_jobs_tbl(l_counter).starting_std_op_id := p_starting_job_rec.standard_operation_id;
4873: p_resulting_jobs_tbl(l_counter).department_id := p_starting_job_rec.department_id;
4874: p_resulting_jobs_tbl(l_counter).department_code := p_starting_job_rec.department_code;
4875: p_resulting_jobs_tbl(l_counter).operation_description := p_starting_job_rec.operation_description;
4876:
5029: raise FND_API.G_EXC_ERROR;
5030: end if;
5031:
5032: l_stmt_num := 20;
5033: -- Default the job type to be Standard
5034: if p_resulting_job_rec.job_type is null then
5035: p_resulting_job_rec.job_type := WIP_CONSTANTS.STANDARD;
5036:
5037: -- Bug 5487991 added elsif condition to validate the job_type is either 1 or 3
5031:
5032: l_stmt_num := 20;
5033: -- Default the job type to be Standard
5034: if p_resulting_job_rec.job_type is null then
5035: p_resulting_job_rec.job_type := WIP_CONSTANTS.STANDARD;
5036:
5037: -- Bug 5487991 added elsif condition to validate the job_type is either 1 or 3
5038: -- these are two valid values for lot based jobs
5039:
5624:
5625: END IF;
5626:
5627: -- Bug 5487991 Added the following check to ensure that class code is of correct type
5628: -- for standard and non standard bonus jobs.
5629:
5630: l_stmt_num := 150;
5631:
5632: SELECT class_type
5807: p_fnd_log_level => l_log_level
5808: );
5809: END IF;
5810:
5811: if p_sj_job_type = WIP_CONSTANTS.NONSTANDARD then
5812: -- if both null then copy from the starting....
5813: if p_rj_rtg_reference_id is null and p_rj_rtg_reference_item is null then
5814: --p_rj_rtg_reference_id := p_sj_rtg_reference_id; (AH)
5815: --error out as user has to specify some info...(AH)
5885: l_stmt_num := 19;
5886:
5887: -- Check if a OSFM routing
5888: l_row_exists := 0;
5889: IF p_sj_job_type = wip_constants.nonstandard THEN
5890: l_item_id := p_rj_rtg_reference_id;
5891: ELSE
5892: l_item_id := p_rj_primary_item_id;
5893: END IF;
5938: p_rj_rtg_revision_date := sysdate;
5939:
5940: END IF;
5941:
5942: if p_sj_job_type = wip_constants.nonstandard THEN
5943: l_item_id := p_rj_rtg_reference_id;
5944: ELSE
5945: l_item_id := p_rj_primary_item_id;
5946: END IF;
5973: -- ST : Fix for bug 5218479 : end --
5974: ELSE -- revision date is not null
5975: l_rtg_revision := null;
5976:
5977: IF p_sj_job_type = wip_constants.nonstandard THEN
5978: l_item_id := p_rj_rtg_reference_id;
5979: ELSE
5980: l_item_id := p_rj_primary_item_id;
5981: END IF;
6186: END IF;
6187:
6188: l_stmt_num := 18;
6189:
6190: IF -- ST : Fix for bug 5101841 Commenting the below statement for Standard job.
6191: -- Standard Jobs will always have a BOM
6192: -- ( (p_sj_job_type = WIP_CONSTANTS.STANDARD) AND
6193: -- (p_rj_alternate_bom_desig IS NULL) AND
6194: -- (p_rj_common_bom_seq_id IS NULL) AND
6187:
6188: l_stmt_num := 18;
6189:
6190: IF -- ST : Fix for bug 5101841 Commenting the below statement for Standard job.
6191: -- Standard Jobs will always have a BOM
6192: -- ( (p_sj_job_type = WIP_CONSTANTS.STANDARD) AND
6193: -- (p_rj_alternate_bom_desig IS NULL) AND
6194: -- (p_rj_common_bom_seq_id IS NULL) AND
6195: -- (p_rj_bom_revision IS NULL) AND
6188: l_stmt_num := 18;
6189:
6190: IF -- ST : Fix for bug 5101841 Commenting the below statement for Standard job.
6191: -- Standard Jobs will always have a BOM
6192: -- ( (p_sj_job_type = WIP_CONSTANTS.STANDARD) AND
6193: -- (p_rj_alternate_bom_desig IS NULL) AND
6194: -- (p_rj_common_bom_seq_id IS NULL) AND
6195: -- (p_rj_bom_revision IS NULL) AND
6196: -- (p_rj_bom_revision_date IS NULL)
6195: -- (p_rj_bom_revision IS NULL) AND
6196: -- (p_rj_bom_revision_date IS NULL)
6197: -- )
6198: -- OR
6199: ( (p_sj_job_type = WIP_CONSTANTS.NONSTANDARD) and
6200: (p_rj_bom_reference_id IS NULL) and
6201: (p_rj_bom_reference_item IS NULL)
6202: )
6203: then
6212: end if;
6213:
6214: l_stmt_num := 20;
6215:
6216: if p_sj_job_type = WIP_CONSTANTS.NONSTANDARD then
6217: -- if both null then copy from the starting....
6218: if p_rj_bom_reference_id is null and p_rj_bom_reference_item is null then
6219: --p_rj_bom_reference_id := p_sj_bom_reference_id; (AH)
6220: --error out as user has to specify some info...(AH)
6287: -- yes..... assign bom_revision_date to job start date or sysdate
6288: p_rj_bom_revision_date := sysdate;
6289: end if;
6290:
6291: if p_sj_job_type = wip_constants.nonstandard then
6292: l_item_id := p_rj_bom_reference_id;
6293: else
6294: l_item_id := p_rj_primary_item_id;
6295: end if;
6311:
6312: else -- revision date is not null
6313: l_bom_revision := null;
6314:
6315: if p_sj_job_type = wip_constants.nonstandard then
6316: l_item_id := p_rj_bom_reference_id;
6317: else
6318: l_item_id := p_rj_primary_item_id;
6319: end if;
6522: );
6523: END IF;
6524:
6525: l_stmt_num := 20;
6526: IF p_job_type = WIP_CONSTANTS.STANDARD THEN
6527: l_rtg_item_id := p_primary_item_id;
6528: ELSE
6529: l_rtg_item_id := p_rj_rtg_reference_item_id;
6530: END IF;
6791: l_op_rptd_time number;
6792: l_err_code number;
6793: l_err_msg varchar2(2000);
6794:
6795: l_operation_code BOM_STANDARD_OPERATIONS.OPERATION_CODE%type;
6796: l_op_rptd_times number;
6797:
6798: -- logging variables
6799: l_stmt_num number:=0;
6834:
6835: if( g_log_level_statement >= l_log_level ) then
6836: l_msg_tokens.delete;
6837: WSM_log_PVT.logMessage(p_module_name => l_module ,
6838: p_msg_text => 'Calling WSMPUTIL.operation_is_standard_repeats',
6839: p_stmt_num => l_stmt_num ,
6840: p_msg_tokens => l_msg_tokens,
6841: p_fnd_log_level => g_log_level_statement,
6842: p_run_log_level => l_log_level
6845:
6846: -- logic goes here.....
6847: l_err_code := 0;
6848: l_err_msg := null;
6849: WSMPUTIL.operation_is_standard_repeats ( p_routing_sequence_id => p_new_comm_rtg_seq_id,
6850: p_routing_revision_date => p_new_rtg_rev_date,
6851: p_standard_operation_id => p_curr_std_op_id,
6852: p_operation_code => p_curr_op_code,
6853: p_organization_id => p_txn_org_id,
6847: l_err_code := 0;
6848: l_err_msg := null;
6849: WSMPUTIL.operation_is_standard_repeats ( p_routing_sequence_id => p_new_comm_rtg_seq_id,
6850: p_routing_revision_date => p_new_rtg_rev_date,
6851: p_standard_operation_id => p_curr_std_op_id,
6852: p_operation_code => p_curr_op_code,
6853: p_organization_id => p_txn_org_id,
6854: p_op_is_std_op => l_op_is_std,
6855: p_op_repeated_times => l_op_rptd_times,
6861: if( g_log_level_statement >= l_log_level ) then
6862:
6863: l_msg_tokens.delete;
6864: WSM_log_PVT.logMessage(p_module_name => l_module ,
6865: p_msg_text => 'WSMPUTIL.operation_is_standard_repeats returned failure',
6866: p_stmt_num => l_stmt_num ,
6867: p_msg_tokens => l_msg_tokens,
6868: p_fnd_msg_level => G_MSG_LVL_ERROR ,
6869: p_fnd_log_level => G_LOG_LEVEL_ERROR ,
6889: BEGIN
6890: SELECT bos.operation_seq_num,
6891: bso.operation_code,
6892: bos.operation_sequence_id,
6893: bos.standard_operation_id,
6894: BD.department_id
6895: into p_new_op_seq_num,
6896: l_operation_code,
6897: p_new_op_seq_id,
6896: l_operation_code,
6897: p_new_op_seq_id,
6898: p_new_std_op_id,
6899: p_new_dept_id
6900: FROM bom_standard_operations BSO,
6901: bom_operation_sequences BOS,
6902: bom_departments BD
6903: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
6904: AND BSO.standard_operation_id = BOS.standard_operation_id
6900: FROM bom_standard_operations BSO,
6901: bom_operation_sequences BOS,
6902: bom_departments BD
6903: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
6904: AND BSO.standard_operation_id = BOS.standard_operation_id
6905: AND BSO.standard_operation_id = p_curr_std_op_id
6906: AND BD.department_id = BOS.department_id
6907: AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
6908: AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
6901: bom_operation_sequences BOS,
6902: bom_departments BD
6903: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
6904: AND BSO.standard_operation_id = BOS.standard_operation_id
6905: AND BSO.standard_operation_id = p_curr_std_op_id
6906: AND BD.department_id = BOS.department_id
6907: AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
6908: AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
6909:
7033: p_run_log_level => l_log_level
7034: );
7035: End if;
7036:
7037: -- might be a non-standard...
7038: SELECT bos.operation_seq_num,
7039: bso.operation_code,
7040: bos.operation_sequence_id,
7041: bos.standard_operation_id,
7037: -- might be a non-standard...
7038: SELECT bos.operation_seq_num,
7039: bso.operation_code,
7040: bos.operation_sequence_id,
7041: bos.standard_operation_id,
7042: BD.department_id
7043: into p_new_op_seq_num,
7044: p_new_op_seq_code,
7045: p_new_op_seq_id,
7044: p_new_op_seq_code,
7045: p_new_op_seq_id,
7046: p_new_std_op_id,
7047: p_new_dept_id
7048: FROM bom_standard_operations BSO,
7049: bom_operation_sequences BOS,
7050: bom_departments BD
7051: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
7052: AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
7050: bom_departments BD
7051: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
7052: AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
7053: AND BOS.operation_sequence_id = nvl(p_new_op_seq_id,BOS.operation_sequence_id)
7054: AND BOS.standard_operation_id = BSO.standard_operation_id (+)
7055: AND nvl(p_new_std_op_id,BOS.standard_operation_id) = BSO.standard_operation_id (+)
7056: AND BD.department_id = BOS.department_id
7057: AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
7058: AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
7051: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
7052: AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
7053: AND BOS.operation_sequence_id = nvl(p_new_op_seq_id,BOS.operation_sequence_id)
7054: AND BOS.standard_operation_id = BSO.standard_operation_id (+)
7055: AND nvl(p_new_std_op_id,BOS.standard_operation_id) = BSO.standard_operation_id (+)
7056: AND BD.department_id = BOS.department_id
7057: AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
7058: AND p_new_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_new_rtg_rev_date+1);
7059:
7073:
7074: SELECT bos.operation_seq_num,
7075: bso.operation_code,
7076: bos.operation_sequence_id,
7077: bos.standard_operation_id,
7078: BD.department_id
7079: into p_new_op_seq_num,
7080: p_new_op_seq_code,
7081: p_new_op_seq_id,
7080: p_new_op_seq_code,
7081: p_new_op_seq_id,
7082: p_new_std_op_id,
7083: p_new_dept_id
7084: FROM bom_standard_operations BSO,
7085: bom_operation_sequences BOS,
7086: bom_departments BD
7087: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
7088: AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
7086: bom_departments BD
7087: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
7088: AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
7089: AND BOS.operation_sequence_id = nvl(p_new_op_seq_id,BOS.operation_sequence_id)
7090: AND BOS.standard_operation_id = BSO.standard_operation_id
7091: AND BSO.standard_operation_id = nvl(p_new_std_op_id,BSO.standard_operation_id)
7092: -- ST : Fix for bug 5116062 : Added an outer NVL clause
7093: AND nvl(BSO.operation_code,'$$&&') = nvl(nvl(p_new_op_seq_code,BSO.operation_code),'$$&&')
7094: AND BD.department_id = BOS.department_id
7087: WHERE BOS.routing_sequence_id = p_new_comm_rtg_seq_id
7088: AND BOS.operation_seq_num = nvl(p_new_op_seq_num,BOS.operation_seq_num)
7089: AND BOS.operation_sequence_id = nvl(p_new_op_seq_id,BOS.operation_sequence_id)
7090: AND BOS.standard_operation_id = BSO.standard_operation_id
7091: AND BSO.standard_operation_id = nvl(p_new_std_op_id,BSO.standard_operation_id)
7092: -- ST : Fix for bug 5116062 : Added an outer NVL clause
7093: AND nvl(BSO.operation_code,'$$&&') = nvl(nvl(p_new_op_seq_code,BSO.operation_code),'$$&&')
7094: AND BD.department_id = BOS.department_id
7095: AND BD.department_id = nvl(p_new_dept_id,BD.department_id)
7918: into p_start_op_seq_num,
7919: p_start_op_seq_id,
7920: p_start_op_seq_code
7921: from bom_operation_sequences BOS,
7922: bom_standard_operations BSO
7923: where bos.OPERATION_SEQUENCE_ID = l_start_op_seq_id
7924: and BOS.routing_sequence_id = p_rtg_seq_id
7925: and BSO.standard_operation_id (+) = BOS.standard_operation_id
7926: and p_revision_date between BOS.effectivity_date and nvl(BOS.disable_date, p_revision_date+1);
7921: from bom_operation_sequences BOS,
7922: bom_standard_operations BSO
7923: where bos.OPERATION_SEQUENCE_ID = l_start_op_seq_id
7924: and BOS.routing_sequence_id = p_rtg_seq_id
7925: and BSO.standard_operation_id (+) = BOS.standard_operation_id
7926: and p_revision_date between BOS.effectivity_date and nvl(BOS.disable_date, p_revision_date+1);
7927: else
7928: l_stmt_num := 40;
7929:
7945: BEGIN
7946: select 1
7947: into l_flag
7948: from bom_operation_sequences BOS,
7949: bom_standard_operations BSO
7950: where bos.OPERATION_SEQ_NUM = nvl(p_start_op_seq_num,bos.OPERATION_SEQ_NUM)
7951: and bos.OPERATION_SEQUENCE_ID = nvl(p_start_op_seq_id,bos.OPERATION_SEQUENCE_ID)
7952: -- ST : Fix for bug 5116062 : Added an outer NVL clause
7953: and nvl(bso.OPERATION_CODE,'$$&&') = nvl(nvl(p_start_op_seq_code,bso.OPERATION_CODE),'$$&&')
7951: and bos.OPERATION_SEQUENCE_ID = nvl(p_start_op_seq_id,bos.OPERATION_SEQUENCE_ID)
7952: -- ST : Fix for bug 5116062 : Added an outer NVL clause
7953: and nvl(bso.OPERATION_CODE,'$$&&') = nvl(nvl(p_start_op_seq_code,bso.OPERATION_CODE),'$$&&')
7954: and bos.routing_sequence_id = p_rtg_seq_id
7955: and BSO.standard_operation_id (+) = BOS.standard_operation_id
7956: and p_revision_date between BOS.effectivity_date and nvl(BOS.disable_date, p_revision_date+1);
7957: EXCEPTION
7958: WHEN NO_DATA_FOUND THEN
7959: IF g_log_level_error >= l_log_level OR FND_MSG_PUB.check_msg_level(G_MSG_LVL_ERROR) THEN