[Home] [Help]
1: PACKAGE BODY WSMPJUPD AS
2: /* $Header: WSMJUPDB.pls 120.43.12020000.6 2013/03/22 13:58:45 sisankar 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
3315:
3316: l_stmt_num := 90;
3317:
3318: --Start : Additions for APS-WLT--
3319: IF (WSMPJUPD.g_copy_mode = 0) THEN
3320: null;
3321: ELSE
3322: l_stmt_num := 92;
3323: -- Create WSOR table records --
3369: End if;
3370:
3371: END IF;
3372:
3373: END IF; --WSMPJUPD.g_copy_mode
3374: --End : Additions for APS-WLT--
3375:
3376: l_stmt_num := 95;
3377:
3406: x_err_code := -100;
3407: x_err_buf := l_fnd_err_msg;
3408: WHEN FND_API.G_EXC_ERROR THEN
3409: x_err_code := SQLCODE;
3410: x_err_buf := ' WSMPJUPD.CHANGE_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3411: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
3412: p_count => x_msg_count ,
3413: p_data => x_err_buf
3414: );
3413: p_data => x_err_buf
3414: );
3415: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3416: x_err_code := SQLCODE;
3417: x_err_buf := ' WSMPJUPD.CHANGE_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3418: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
3419: p_count => x_msg_count ,
3420: p_data => x_err_buf
3421: );
3420: p_data => x_err_buf
3421: );
3422: WHEN OTHERS THEN
3423: x_err_code := SQLCODE;
3424: x_err_buf := ' WSMPJUPD.CHANGE_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3425:
3426: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
3427: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
3428: THEN
3695: p_operation_seq_num => l_new_op_added,
3696: p_organization_id => l_org_id))
3697: THEN
3698:
3699: WSMPJUPD.g_osp_exists := 1;
3700:
3701: if (l_po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION) then
3702: --if l_request_id is null means online processing so launch import req
3703: -- (i.e) not from the Interface
3731:
3732: EXCEPTION
3733: WHEN FND_API.G_EXC_ERROR THEN
3734: x_err_code := -1;
3735: x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||x_err_buf;
3736: FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
3737: p_count => x_msg_count ,
3738: p_data => x_err_buf
3739: );
3739: );
3740:
3741: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3742: x_err_code := -1;
3743: x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||x_err_buf;
3744: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
3745: p_count => x_msg_count ,
3746: p_data => x_err_buf
3747: );
3746: p_data => x_err_buf
3747: );
3748: WHEN OTHERS THEN
3749: x_err_code := SQLCODE;
3750: x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3751:
3752: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
3753: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
3754: THEN
4017: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
4018: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4019:
4020: l_stmt_num NUMBER;
4021: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPDB.CREATE_COPIES_OR_SET_COPY_DATA';
4022: -- Logging variables...
4023: l_phantom_exists NUMBER;
4024: BEGIN
4025:
4567:
4568: EXCEPTION
4569: WHEN FND_API.G_EXC_ERROR THEN
4570: x_err_code := SQLCODE;
4571: x_err_buf := ' WSMPJUPD.CREATE_COPIES_OR_SET_COPY_DATA('||l_stmt_num||'): '||x_err_buf;
4572:
4573: FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
4574: p_count => x_msg_count ,
4575: p_data => x_err_buf
4577:
4578: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4579:
4580: x_err_code := SQLCODE;
4581: x_err_buf := ' WSMPJUPD.CREATE_COPIES_OR_SET_COPY_DATA('||l_stmt_num||'): '||x_err_buf;
4582:
4583: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
4584: p_count => x_msg_count ,
4585: p_data => x_err_buf
4587:
4588:
4589: WHEN OTHERS THEN
4590: x_err_code := SQLCODE;
4591: x_err_buf := ' WSMPJUPD.CREATE_COPIES_OR_SET_COPY_DATA('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
4592:
4593: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
4594: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
4595: THEN
4633: l_msg_tokens WSM_Log_PVT.token_rec_tbl;
4634: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4635:
4636: l_stmt_num NUMBER;
4637: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPDB.CALL_INF_SCH_OR_SET_SCH_DATA';
4638: -- Logging variables...
4639:
4640: BEGIN
4641:
4733: EXCEPTION
4734: WHEN FND_API.G_EXC_ERROR THEN
4735:
4736: x_err_code := SQLCODE;
4737: x_err_buf := ' WSMPJUPD.CALL_INF_SCH_OR_SET_SCH_DATA('||l_stmt_num||'): '||x_err_buf;
4738: /*
4739: FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
4740: p_count => x_msg_count ,
4741: p_data => x_err_buf
4743: */
4744: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4745:
4746: x_err_code := SQLCODE;
4747: x_err_buf := ' WSMPJUPD.CALL_INF_SCH_OR_SET_SCH_DATA('||l_stmt_num||'): '||x_err_buf;
4748: /*
4749: FND_MSG_PUB.Count_And_Get ( p_encoded => 'F',
4750: p_count => x_msg_count ,
4751: p_data => x_err_buf
4752: );
4753: */
4754: WHEN OTHERS THEN
4755: x_err_code := SQLCODE;
4756: x_err_buf := ' WSMPJUPD.CALL_INF_SCH_OR_SET_SCH_DATA('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
4757:
4758: IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
4759: (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
4760: THEN
4844: /*logging variables*/
4845: l_stmt_num number := 0;
4846: l_msg_tokens WSM_log_PVT.token_rec_tbl;
4847: l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4848: l_module VARCHAR2(100) := 'wsm.plsql.WSMPJUPD.process_lots';
4849: l_schedule_group_id NUMBER; -- Bug 6676148
4850:
4851: l_attribute_category varchar2(30);
4852: l_attribute1 varchar2(150);
4909:
4910: l_stmt_num := 30;
4911:
4912: IF (l_profile_value = 2) THEN -- Dont make copies
4913: WSMPJUPD.g_copy_mode := 0;
4914: ELSE
4915: IF (WSMPJUPD.g_copy_mode IS NULL) THEN -- Called through the form
4916: WSMPJUPD.g_copy_mode := 1; -- For form, make copies immediately
4917: END IF;
4911:
4912: IF (l_profile_value = 2) THEN -- Dont make copies
4913: WSMPJUPD.g_copy_mode := 0;
4914: ELSE
4915: IF (WSMPJUPD.g_copy_mode IS NULL) THEN -- Called through the form
4916: WSMPJUPD.g_copy_mode := 1; -- For form, make copies immediately
4917: END IF;
4918: END IF;
4919:
4912: IF (l_profile_value = 2) THEN -- Dont make copies
4913: WSMPJUPD.g_copy_mode := 0;
4914: ELSE
4915: IF (WSMPJUPD.g_copy_mode IS NULL) THEN -- Called through the form
4916: WSMPJUPD.g_copy_mode := 1; -- For form, make copies immediately
4917: END IF;
4918: END IF;
4919:
4920: l_stmt_num := 40;
5013: else
5014: l_stmt_num := 63;
5015:
5016: -- else indicates that the job is a new job...
5017: IF (WSMPJUPD.g_copy_mode = 0) THEN -- No copies to be made
5018: -- Added condition for APS-WLT
5019:
5020: -------------------- Related Bugs for this piece of code.... ----------------------------
5021: -- 3698595
5149: p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id := l_new_we_id;
5150: END IF;
5151:
5152: --Start : Additions for APS-WLT--
5153: ELSIF (WSMPJUPD.g_copy_mode <> 0) THEN -- Copies are to be made i.e. Option C
5154: -- Make copies immediately or at the end
5155:
5156: l_stmt_num := 75;
5157: -------------------- Related Bugs for this piece of code.... ---------------------------
5281:
5282: p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id := l_new_we_id;
5283: END IF;
5284:
5285: END IF; --WSMPJUPD.g_copy_mode
5286:
5287: l_stmt_num := 85;
5288:
5289: l_new_rj_we_id_tbl(l_job_counter) := p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
5469: l_err_code := 0;
5470: l_err_buf := null;
5471:
5472: --Start: Additions for APS-WLT--
5473: IF (WSMPJUPD.g_copy_mode = 0) THEN
5474: null;
5475: ELSE -- Copies are to be made i.e. Option C
5476: -- Make copies immediately or at the end
5477: if( g_log_level_statement >= l_log_level ) then
6185: -- Start : Additions for APS-WLT --
6186: l_stmt_num := 295;
6187:
6188:
6189: IF (WSMPJUPD.g_copy_mode = 0) THEN
6190: null; -- no copies
6191: ELSE
6192: l_stmt_num := 296;
6193:
6195: l_err_buf := null;
6196:
6197: CREATE_COPIES_OR_SET_COPY_DATA (p_txn_id => null,
6198: p_txn_type_id => p_wltx_header.transaction_type_id,
6199: p_copy_mode => WSMPJUPD.g_copy_mode,
6200: p_rep_sj_index => l_rep_sj_index,
6201: p_sj_as_rj_index => l_sj_also_rj_index,
6202: p_wltx_starting_jobs_tbl => p_wltx_starting_jobs_tbl,
6203: p_wltx_resulting_jobs_tbl => p_wltx_resulting_jobs_tbl,
6239: l_err_code := 0;
6240: l_err_buf := null;
6241:
6242: CALL_INF_SCH_OR_SET_SCH_DATA( p_txn_id => null,
6243: p_copy_mode => WSMPJUPD.g_copy_mode,
6244: p_org_id => p_txn_org_id,
6245: p_par_we_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
6246: x_err_code => l_err_code,
6247: x_err_buf => l_err_buf
6578: l_err_buf := null;
6579:
6580: CREATE_COPIES_OR_SET_COPY_DATA (p_txn_id => null,
6581: p_txn_type_id => p_wltx_header.transaction_type_id,
6582: p_copy_mode => WSMPJUPD.g_copy_mode,
6583: p_rep_sj_index => p_wltx_starting_jobs_tbl.first,
6584: p_sj_as_rj_index => p_wltx_resulting_jobs_tbl.first,
6585: p_wltx_starting_jobs_tbl => p_wltx_starting_jobs_tbl,
6586: p_wltx_resulting_jobs_tbl => p_wltx_resulting_jobs_tbl,
6822: )
6823: )
6824: THEN
6825:
6826: WSMPJUPD.g_osp_exists := 1;
6827:
6828: if (l_po_creation_time <> WIP_CONSTANTS.MANUAL_CREATION) then
6829:
6830: l_stmt_num := 253;
6859: END IF;
6860: --osp end
6861:
6862: -- call to infinite schedule....
6863: IF (WSMPJUPD.g_copy_mode = 0) THEN
6864: null; -- no copies
6865: ELSE
6866: l_stmt_num := 540;
6867: l_err_code := 0;
6867: l_err_code := 0;
6868: l_err_buf := null;
6869:
6870: CALL_INF_SCH_OR_SET_SCH_DATA( p_txn_id => null,
6871: p_copy_mode => WSMPJUPD.g_copy_mode,
6872: p_org_id => p_txn_org_id,
6873: p_par_we_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
6874: x_err_code => l_err_code,
6875: x_err_buf => l_err_buf
7385: l_err_buf := null;
7386:
7387: CREATE_COPIES_OR_SET_COPY_DATA (p_txn_id => null,
7388: p_txn_type_id => p_wltx_header.transaction_type_id,
7389: p_copy_mode => WSMPJUPD.g_copy_mode,
7390: p_rep_sj_index => null,
7391: p_sj_as_rj_index => l_rj_index,
7392: p_wltx_starting_jobs_tbl => p_wltx_starting_jobs_tbl,
7393: p_wltx_resulting_jobs_tbl => p_wltx_resulting_jobs_tbl,
7629: );
7630:
7631: end PROCESS_LOTS;
7632:
7633: END WSMPJUPD;