[Home] [Help]
PACKAGE BODY: APPS.WSH_OPM_CONV_MIG_PKG
Source
1 PACKAGE BODY WSH_OPM_CONV_MIG_PKG AS
2 /* $Header: WSHOPMDB.pls 120.0.12010000.2 2008/08/04 12:32:03 suppal ship $ */
3
4 /*====================================================================
5 -- PROCEDURE:
6 -- WSH_LOT_NUMBERS
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to OPM-OM Wdd lot number updates
10 --
11 -- PARAMETERS:
12 -- p_migration_run_id This is used for message logging.
13 -- p_commit Commit flag.
14 -- x_failure_count count of the failed lines.An out parameter.
15 --
16 -- SYNOPSIS:
17 --
18 -- MIGRATE_OPM_OM_OPEN_LINES ( p_migration_run_id IN NUMBER
19 -- , p_commit IN VARCHAR2
20 -- , x_failure_count OUT NUMBER)
21 -- HISTORY
22 --====================================================================*/
23
24 PROCEDURE WSH_LOT_NUMBERS( p_migration_run_id IN NUMBER
25 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
26 , x_failure_count OUT NOCOPY NUMBER
27 )
28 IS
29
30 /* Migration specific variables */
31 l_failure_count NUMBER := 0;
32 l_success_count NUMBER := 0;
33 l_table_name VARCHAR2(30) DEFAULT NULL;
34 l_opm_table_name VARCHAR2(30) DEFAULT NULL;
35
36 -- Local Variables.
37 l_msg_count NUMBER :=0;
38 l_msg_data VARCHAR2(2000);
39 l_return_status VARCHAR2(1);
40
41 --l_wdd_rec wsh_delivery_details%rowtype;
42 l_wdd_id NUMBER;
43 l_so_line_id NUMBER;
44 l_api_return_status VARCHAR2(1);
45 l_api_error_code NUMBER;
46 l_api_error_msg VARCHAR2(100);
47 l_message VARCHAR2(255);
48
49 l_odm_lot_number VARCHAR2(80);
50 l_parent_lot_number VARCHAR2(80);
51
52 CURSOR get_opm_trans IS
53 SELECT trans_id
54 , line_id
55 , line_detail_id
56 , item_id
57 , lot_id
58 , whse_code
59 , orgn_code
60 , location
61 FROM ic_tran_pnd ictran
62 WHERE ictran.doc_type = 'OMSO'
63 AND ictran.delete_mark = 0
64 AND ictran.staged_ind = 1 -- only for staged wdds, others lot_number is null
65 AND abs(ictran.trans_qty) > 0
66 AND (ictran.lot_id >0 )
67 ;
68 BEGIN
69 /* Begin by logging a message that reason_code migration has started */
70 gma_common_logging.gma_migration_central_log (
71 p_run_id => p_migration_run_id
72 , p_log_level => FND_LOG.LEVEL_PROCEDURE
73 , p_app_short_name => 'WSH'
74 , p_message_token => 'WSH_MIGRATION_TABLE_STARTED'
75 , p_table_name => 'wsh_delivery_details'
76 , p_context => 'LOT_NUMBER_UPDATES'
77 );
78
79 l_table_name := 'WSH_DELIVERY_DETAILS';
80 l_opm_table_name := 'IC_TRAN_PND';
81
82 /* Get all the transaction record related to OM lines to be processed */
83 FOR opm_trans_rec IN get_opm_trans LOOP
84 GMI_RESERVATION_UTIL.println('NNNNn In transaction loop');
85
86 BEGIN
87 l_so_line_id := opm_trans_rec.line_id;
88 l_wdd_id := opm_trans_rec.line_detail_id;
89 GMI_RESERVATION_UTIL.println('ic_tran_pnd wdd id , so_line_id'||'l_wdd_id:'||l_so_line_id);
90 /* get the new convention for lot_Number in R12 */
91 INV_OPM_LOT_MIGRATION.get_ODM_lot
92 ( p_migration_run_id => p_migration_run_id
93 , p_item_id => opm_trans_rec.item_id
94 , p_lot_id => opm_trans_rec.lot_id
95 , p_whse_code => opm_trans_rec.whse_code
96 , p_orgn_code => '' -- orgn code is null
97 , p_location => opm_trans_rec.location
98 , p_commit => p_commit
99 , x_lot_number => l_odm_lot_number
100 , x_parent_lot_number => l_parent_lot_number
101 , x_failure_count => x_failure_count
102 );
103
104 GMI_RESERVATION_UTIL.println('ODM lot_number'||l_odm_lot_number);
105 GMI_RESERVATION_UTIL.println('ODM parent lot number '||l_parent_lot_number);
106 /* update wdd with the new lot_number */
107 /* if the API get_odm_lot can not get the odm lot number, these wdds would not be updated */
108 if (l_odm_lot_number is not null) and nvl(l_wdd_id, 0) <> 0 then
109 Update wsh_delivery_details
110 set lot_number = l_odm_lot_number
111 where delivery_detail_id = l_wdd_id
112 and lot_number is not null
113 ;
114 l_success_count := l_success_count + 1;
115 GMI_RESERVATION_UTIL.println('update wdd successful id '||l_wdd_id);
116 end if;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 /* Failure count goes up by 1 */
121 l_failure_count := l_failure_count+1;
122 gma_common_logging.gma_migration_central_log (
123 p_run_id => p_migration_run_id
124 , p_log_level => FND_LOG.LEVEL_UNEXPECTED
125 , p_app_short_name =>'WSH'
126 , p_message_token => 'WSH_MIGRATION_DB_ERROR'
127 , p_db_error => sqlerrm
128 , p_table_name => 'wsh_delivery_details'
129 , p_context => 'LOT_NUMBER_UPDATES'
130 );
131 End;
132 End Loop;
133 /* End by logging a message that the migration has been succesful */
134 gma_common_logging.gma_migration_central_log (
135 p_run_id => p_migration_run_id
136 , p_log_level => FND_LOG.LEVEL_PROCEDURE
137 , p_app_short_name => 'WSH'
138 , p_message_token => 'WSH_MIGRATION_TABLE_SUCCESS'
139 , p_table_name => NULL
140 , p_context => 'LOT_NUMBER_UPDTAES'
141 , p_param1 => l_success_count
142 , p_param2 => l_failure_count
143 );
144
145 x_failure_count := l_failure_count;
146
147 if p_commit = FND_API.G_TRUE then
148 commit;
149 end if;
150
151 End WSH_LOT_NUMBERS;
152 End WSH_OPM_CONV_MIG_PKG;