[Home] [Help]
1: PACKAGE BODY WSMPJUPD AS
2: /* $Header: WSMJUPDB.pls 120.40.12000000.3 2007/07/26 09:21:53 adasa ship $ */
3:
4: g_user_id number;
5: g_user_login_id number;
8: g_program_id number;
9: g_translated_meaning varchar2(240);
10:
11: /* Package name */
12: g_pkg_name VARCHAR2(20) := 'WSMPJUPD';
13:
14: /*logging variables*/
15:
16: g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
100: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
101: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
102:
103: l_stmt_num NUMBER;
104: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.process_mes_info';
105: l_param_tbl WSM_Log_PVT.param_tbl_type;
106:
107: BEGIN
108:
775: -- Logging variables.....
776: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
777: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
778: l_stmt_num NUMBER;
779: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.copy_wsm_op_reason_codes';
780:
781: BEGIN
782:
783: savepoint start_copy_wsm_op_reason_codes;
957: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
958: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
959:
960: l_stmt_num NUMBER;
961: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.copy_wsm_substitute_components';
962:
963:
964: BEGIN
965: savepoint start_copy_wsm_subst_comp;
1150: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
1151: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1152:
1153: l_stmt_num NUMBER;
1154: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.GET_JOB_CURR_OP_INFO';
1155:
1156:
1157: BEGIN
1158:
1203: p_intra_op := 3;
1204: p_op_qty := l_qty_TM;
1205: ELSIF l_qty_RUN > 0 THEN
1206: x_err_code := -1;
1207: x_err_buf := 'WSMPJUPD.GET_JOB_CURR_OP_INFO('||to_char(l_stmt_num)||'): Incorrect job status for this transaction';
1208:
1209: IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
1210:
1211: l_msg_tokens.delete;
1223: ELSIF (l_qty_Q = 0) AND (l_qty_RUN = 0) AND (l_qty_TM = 0) THEN
1224: p_intra_op := 5;
1225: ELSE
1226: x_err_code := -1;
1227: x_err_buf := 'WSMPJUPD.GET_JOB_CURR_OP_INFO('||to_char(l_stmt_num)||'): Incorrect job status for this transaction';
1228:
1229: IF G_LOG_LEVEL_ERROR >= l_log_level OR FND_MSG_PUB.check_msg_level(g_msg_lvl_error) THEN
1230:
1231: l_msg_tokens.delete;
1248:
1249: EXCEPTION
1250: WHEN FND_API.G_EXC_ERROR THEN
1251: x_err_code := -1;
1252: x_err_buf := 'WSMPJUPD.GET_JOB_CURR_OP_INFO('||to_char(l_stmt_num)||'): Incorrect job status for this transaction';
1253:
1254: WHEN OTHERS THEN
1255: x_err_code := SQLCODE;
1256: x_err_buf := 'WSMPJUPD.GET_JOB_CURR_OP_INFO('||to_char(l_stmt_num)||'): '||substrb(sqlerrm,1,1000);
1252: x_err_buf := 'WSMPJUPD.GET_JOB_CURR_OP_INFO('||to_char(l_stmt_num)||'): Incorrect job status for this transaction';
1253:
1254: WHEN OTHERS THEN
1255: x_err_code := SQLCODE;
1256: x_err_buf := 'WSMPJUPD.GET_JOB_CURR_OP_INFO('||to_char(l_stmt_num)||'): '||substrb(sqlerrm,1,1000);
1257:
1258: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1259: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1260: THEN
1534:
1535: EXCEPTION
1536: WHEN OTHERS THEN
1537: x_err_code := SQLCODE;
1538: x_err_buf := ' WSMPJUPD.COPY_REP_JOB_WO_FA('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
1539:
1540: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1541: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1542: THEN
1566: -- Logging variables.....
1567: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
1568: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1569: l_stmt_num NUMBER;
1570: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.COPY_REP_JOB_WOR';
1571:
1572: BEGIN
1573:
1574: forall l_job_counter in indices of p_new_rj_we_id_tbl
1691:
1692: EXCEPTION
1693: WHEN OTHERS THEN
1694: x_err_code := SQLCODE;
1695: x_err_buf := ' WSMPJUPD.COPY_REP_JOB_WOR('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
1696:
1697: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1698: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1699: THEN
1866:
1867: EXCEPTION
1868: WHEN OTHERS THEN
1869: x_err_code := SQLCODE;
1870: x_err_buf := ' WSMPJUPD.COPY_REP_JOB_WSOR('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
1871:
1872: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1873: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1874: THEN
1954: x_err_buf := NULL;
1955: EXCEPTION
1956: WHEN OTHERS THEN
1957: x_err_code := SQLCODE;
1958: x_err_buf := ' WSMPJUPD.COPY_REP_JOB_WORI('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
1959:
1960: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1961: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1962: THEN
2172:
2173: EXCEPTION
2174: WHEN OTHERS THEN
2175: x_err_code := SQLCODE;
2176: x_err_buf := ' WSMPJUPD.COPY_REP_JOB_WRO('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
2177:
2178: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
2179: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
2180: THEN
2258:
2259: EXCEPTION
2260: WHEN OTHERS THEN
2261: x_err_code := SQLCODE;
2262: x_err_buf := ' WSMPJUPD.COPY_REP_JOB_WOY('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
2263:
2264: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
2265: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
2266: THEN
2424:
2425: EXCEPTION
2426: WHEN OTHERS THEN
2427: x_err_code := SQLCODE;
2428: x_err_buf := ' WSMPJUPD.CHANGE_QUANTITY('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
2429:
2430: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
2431: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
2432: THEN
2520: EXCEPTION
2521:
2522: WHEN OTHERS THEN
2523: x_err_code := SQLCODE;
2524: x_err_msg := 'WSMPJUPD.handle_kanban_sub_loc_change('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
2525:
2526: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
2527: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
2528: THEN
2752:
2753: EXCEPTION
2754: WHEN OTHERS THEN
2755: x_err_code := SQLCODE;
2756: x_err_buf := 'WSMPJUPD.UPDATE_QTY_ISSUED('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
2757:
2758: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
2759: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
2760: THEN
3312:
3313: l_stmt_num := 90;
3314:
3315: --Start : Additions for APS-WLT--
3316: IF (WSMPJUPD.g_copy_mode = 0) THEN
3317: null;
3318: ELSE
3319: l_stmt_num := 92;
3320: -- Create WSOR table records --
3366: End if;
3367:
3368: END IF;
3369:
3370: END IF; --WSMPJUPD.g_copy_mode
3371: --End : Additions for APS-WLT--
3372:
3373: l_stmt_num := 95;
3374:
3400:
3401: EXCEPTION
3402: WHEN FND_API.G_EXC_ERROR THEN
3403: x_err_code := SQLCODE;
3404: x_err_buf := ' WSMPJUPD.CHANGE_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3405: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
3406: p_count => x_msg_count ,
3407: p_data => x_err_buf
3408: );
3407: p_data => x_err_buf
3408: );
3409: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3410: x_err_code := SQLCODE;
3411: x_err_buf := ' WSMPJUPD.CHANGE_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3412: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
3413: p_count => x_msg_count ,
3414: p_data => x_err_buf
3415: );
3414: p_data => x_err_buf
3415: );
3416: WHEN OTHERS THEN
3417: x_err_code := SQLCODE;
3418: x_err_buf := ' WSMPJUPD.CHANGE_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3419:
3420: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
3421: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
3422: THEN
3689: p_operation_seq_num => l_new_op_added,
3690: p_organization_id => l_org_id))
3691: THEN
3692:
3693: WSMPJUPD.g_osp_exists := 1;
3694:
3695: if (l_po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION) then
3696: --if l_request_id is null means online processing so launch import req
3697: -- (i.e) not from the Interface
3725:
3726: EXCEPTION
3727: WHEN FND_API.G_EXC_ERROR THEN
3728: x_err_code := -1;
3729: x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||x_err_buf;
3730: FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
3731: p_count => x_msg_count ,
3732: p_data => x_err_buf
3733: );
3733: );
3734:
3735: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3736: x_err_code := -1;
3737: x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||x_err_buf;
3738: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
3739: p_count => x_msg_count ,
3740: p_data => x_err_buf
3741: );
3740: p_data => x_err_buf
3741: );
3742: WHEN OTHERS THEN
3743: x_err_code := SQLCODE;
3744: x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3745:
3746: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
3747: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
3748: THEN
4011: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
4012: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4013:
4014: l_stmt_num NUMBER;
4015: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPDB.CREATE_COPIES_OR_SET_COPY_DATA';
4016: -- Logging variables...
4017: l_phantom_exists NUMBER;
4018: BEGIN
4019:
4559:
4560: EXCEPTION
4561: WHEN FND_API.G_EXC_ERROR THEN
4562: x_err_code := SQLCODE;
4563: x_err_buf := ' WSMPJUPD.CREATE_COPIES_OR_SET_COPY_DATA('||l_stmt_num||'): '||x_err_buf;
4564:
4565: FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
4566: p_count => x_msg_count ,
4567: p_data => x_err_buf
4569:
4570: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4571:
4572: x_err_code := SQLCODE;
4573: x_err_buf := ' WSMPJUPD.CREATE_COPIES_OR_SET_COPY_DATA('||l_stmt_num||'): '||x_err_buf;
4574:
4575: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
4576: p_count => x_msg_count ,
4577: p_data => x_err_buf
4579:
4580:
4581: WHEN OTHERS THEN
4582: x_err_code := SQLCODE;
4583: x_err_buf := ' WSMPJUPD.CREATE_COPIES_OR_SET_COPY_DATA('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
4584:
4585: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
4586: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
4587: THEN
4625: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
4626: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4627:
4628: l_stmt_num NUMBER;
4629: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPDB.CALL_INF_SCH_OR_SET_SCH_DATA';
4630: -- Logging variables...
4631:
4632: BEGIN
4633:
4725: EXCEPTION
4726: WHEN FND_API.G_EXC_ERROR THEN
4727:
4728: x_err_code := SQLCODE;
4729: x_err_buf := ' WSMPJUPD.CALL_INF_SCH_OR_SET_SCH_DATA('||l_stmt_num||'): '||x_err_buf;
4730: /*
4731: FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
4732: p_count => x_msg_count ,
4733: p_data => x_err_buf
4735: */
4736: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4737:
4738: x_err_code := SQLCODE;
4739: x_err_buf := ' WSMPJUPD.CALL_INF_SCH_OR_SET_SCH_DATA('||l_stmt_num||'): '||x_err_buf;
4740: /*
4741: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
4742: p_count => x_msg_count ,
4743: p_data => x_err_buf
4744: );
4745: */
4746: WHEN OTHERS THEN
4747: x_err_code := SQLCODE;
4748: x_err_buf := ' WSMPJUPD.CALL_INF_SCH_OR_SET_SCH_DATA('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
4749:
4750: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
4751: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
4752: THEN
4836: /*logging variables*/
4837: l_stmt_num number := 0;
4838: l_msg_tokens WSM_log_PVT.token_rec_tbl;
4839: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4840: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.process_lots';
4841:
4842:
4843: BEGIN
4844:
4884:
4885: l_stmt_num := 30;
4886:
4887: IF (l_profile_value = 2) THEN -- Dont make copies
4888: WSMPJUPD.g_copy_mode := 0;
4889: ELSE
4890: IF (WSMPJUPD.g_copy_mode IS NULL) THEN -- Called through the form
4891: WSMPJUPD.g_copy_mode := 1; -- For form, make copies immediately
4892: END IF;
4886:
4887: IF (l_profile_value = 2) THEN -- Dont make copies
4888: WSMPJUPD.g_copy_mode := 0;
4889: ELSE
4890: IF (WSMPJUPD.g_copy_mode IS NULL) THEN -- Called through the form
4891: WSMPJUPD.g_copy_mode := 1; -- For form, make copies immediately
4892: END IF;
4893: END IF;
4894:
4887: IF (l_profile_value = 2) THEN -- Dont make copies
4888: WSMPJUPD.g_copy_mode := 0;
4889: ELSE
4890: IF (WSMPJUPD.g_copy_mode IS NULL) THEN -- Called through the form
4891: WSMPJUPD.g_copy_mode := 1; -- For form, make copies immediately
4892: END IF;
4893: END IF;
4894:
4895: l_stmt_num := 40;
4949: else
4950: l_stmt_num := 63;
4951:
4952: -- else indicates that the job is a new job...
4953: IF (WSMPJUPD.g_copy_mode = 0) THEN -- No copies to be made
4954: -- Added condition for APS-WLT
4955:
4956: -------------------- Related Bugs for this piece of code.... ----------------------------
4957: -- 3698595
5085: p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id := l_new_we_id;
5086: END IF;
5087:
5088: --Start : Additions for APS-WLT--
5089: ELSIF (WSMPJUPD.g_copy_mode <> 0) THEN -- Copies are to be made i.e. Option C
5090: -- Make copies immediately or at the end
5091:
5092: l_stmt_num := 75;
5093: -------------------- Related Bugs for this piece of code.... ---------------------------
5217:
5218: p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id := l_new_we_id;
5219: END IF;
5220:
5221: END IF; --WSMPJUPD.g_copy_mode
5222:
5223: l_stmt_num := 85;
5224:
5225: l_new_rj_we_id_tbl(l_job_counter) := p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
5405: l_err_code := 0;
5406: l_err_buf := null;
5407:
5408: --Start: Additions for APS-WLT--
5409: IF (WSMPJUPD.g_copy_mode = 0) THEN
5410: null;
5411: ELSE -- Copies are to be made i.e. Option C
5412: -- Make copies immediately or at the end
5413: if( g_log_level_statement >= l_log_level ) then
6121: -- Start : Additions for APS-WLT --
6122: l_stmt_num := 295;
6123:
6124:
6125: IF (WSMPJUPD.g_copy_mode = 0) THEN
6126: null; -- no copies
6127: ELSE
6128: l_stmt_num := 296;
6129:
6131: l_err_buf := null;
6132:
6133: CREATE_COPIES_OR_SET_COPY_DATA (p_txn_id => null,
6134: p_txn_type_id => p_wltx_header.transaction_type_id,
6135: p_copy_mode => WSMPJUPD.g_copy_mode,
6136: p_rep_sj_index => l_rep_sj_index,
6137: p_sj_as_rj_index => l_sj_also_rj_index,
6138: p_wltx_starting_jobs_tbl => p_wltx_starting_jobs_tbl,
6139: p_wltx_resulting_jobs_tbl => p_wltx_resulting_jobs_tbl,
6175: l_err_code := 0;
6176: l_err_buf := null;
6177:
6178: CALL_INF_SCH_OR_SET_SCH_DATA( p_txn_id => null,
6179: p_copy_mode => WSMPJUPD.g_copy_mode,
6180: p_org_id => p_txn_org_id,
6181: p_par_we_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
6182: x_err_code => l_err_code,
6183: x_err_buf => l_err_buf
6514: l_err_buf := null;
6515:
6516: CREATE_COPIES_OR_SET_COPY_DATA (p_txn_id => null,
6517: p_txn_type_id => p_wltx_header.transaction_type_id,
6518: p_copy_mode => WSMPJUPD.g_copy_mode,
6519: p_rep_sj_index => p_wltx_starting_jobs_tbl.first,
6520: p_sj_as_rj_index => p_wltx_resulting_jobs_tbl.first,
6521: p_wltx_starting_jobs_tbl => p_wltx_starting_jobs_tbl,
6522: p_wltx_resulting_jobs_tbl => p_wltx_resulting_jobs_tbl,
6758: )
6759: )
6760: THEN
6761:
6762: WSMPJUPD.g_osp_exists := 1;
6763:
6764: if (l_po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION) then
6765:
6766: l_stmt_num := 253;
6795: END IF;
6796: --osp end
6797:
6798: -- call to infinite schedule....
6799: IF (WSMPJUPD.g_copy_mode = 0) THEN
6800: null; -- no copies
6801: ELSE
6802: l_stmt_num := 540;
6803: l_err_code := 0;
6803: l_err_code := 0;
6804: l_err_buf := null;
6805:
6806: CALL_INF_SCH_OR_SET_SCH_DATA( p_txn_id => null,
6807: p_copy_mode => WSMPJUPD.g_copy_mode,
6808: p_org_id => p_txn_org_id,
6809: p_par_we_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
6810: x_err_code => l_err_code,
6811: x_err_buf => l_err_buf
7321: l_err_buf := null;
7322:
7323: CREATE_COPIES_OR_SET_COPY_DATA (p_txn_id => null,
7324: p_txn_type_id => p_wltx_header.transaction_type_id,
7325: p_copy_mode => WSMPJUPD.g_copy_mode,
7326: p_rep_sj_index => null,
7327: p_sj_as_rj_index => l_rj_index,
7328: p_wltx_starting_jobs_tbl => p_wltx_starting_jobs_tbl,
7329: p_wltx_resulting_jobs_tbl => p_wltx_resulting_jobs_tbl,
7565: );
7566:
7567: end PROCESS_LOTS;
7568:
7569: END WSMPJUPD;