[Home] [Help]
1: PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2: /* $Header: MSCLSTPB.pls 120.9.12010000.3 2009/01/15 06:32:56 lsindhur ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
1: PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2: /* $Header: MSCLSTPB.pls 120.9.12010000.3 2009/01/15 06:32:56 lsindhur ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
2: /* $Header: MSCLSTPB.pls 120.9.12010000.3 2009/01/15 06:32:56 lsindhur ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
21: FUNCTION LINK_SUPPLY_TOP_LINK_ID
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
21: FUNCTION LINK_SUPPLY_TOP_LINK_ID
22: RETURN BOOLEAN
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18: -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19: -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20:
21: FUNCTION LINK_SUPPLY_TOP_LINK_ID
22: RETURN BOOLEAN
23: IS
26: begin
27:
28: lv_task_start_time := SYSDATE;
29:
30: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
31: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
32:
33: ELSE
34: lv_tbl:= 'MSC_SUPPLIES';
27:
28: lv_task_start_time := SYSDATE;
29:
30: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
31: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
32:
33: ELSE
34: lv_tbl:= 'MSC_SUPPLIES';
35: END IF;
45: || ' and nwk.top_transaction_id is not null '
46: || ' and nwk.plan_id = -1 '
47: || ' and rownum = 1 )'
48: || ' WHERE s.plan_id = -1 '
49: || ' and s.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
50: || ' and s.order_type = 70'; /* eam supply order type */
51:
52: COMMIT;
53:
82:
83: SELECT NVL( CLEANSED_FLAG, MSC_UTIL.SYS_NO)
84: INTO CLEANSED_FLAG
85: FROM MSC_APPS_INSTANCES mai
86: WHERE mai.INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
87:
88: IF CLEANSED_FLAG= MSC_UTIL.SYS_NO THEN
89:
90: MSC_CL_CLEANSE.CLEANSE( ERRBUF,
88: IF CLEANSED_FLAG= MSC_UTIL.SYS_NO THEN
89:
90: MSC_CL_CLEANSE.CLEANSE( ERRBUF,
91: RETCODE,
92: MSC_CL_COLLECTION.v_instance_id);
93:
94: IF RETCODE= MSC_UTIL.G_SUCCESS OR RETCODE= MSC_UTIL.G_WARNING THEN
95:
96: UPDATE MSC_APPS_INSTANCES mai
94: IF RETCODE= MSC_UTIL.G_SUCCESS OR RETCODE= MSC_UTIL.G_WARNING THEN
95:
96: UPDATE MSC_APPS_INSTANCES mai
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
102:
95:
96: UPDATE MSC_APPS_INSTANCES mai
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
102:
103: COMMIT;
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
102:
103: COMMIT;
104:
105: ELSE
105: ELSE
106:
107: ROLLBACK;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
106:
107: ROLLBACK;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
107: ROLLBACK;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
116: ERRBUF);
117:
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
116: ERRBUF);
117:
118: RETURN FALSE;
119:
151: FROM dual;
152:
153: /* for bug: 2605884, added this piece of code to set the so_tbl_status to NO */
154:
155: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=2 , no matter type of collections
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
154:
155: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=2 , no matter type of collections
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
189: SET so_tbl_status= MSC_UTIL.SYS_NO
190: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
191: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
189: SET so_tbl_status= MSC_UTIL.SYS_NO
190: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
191: commit;
192: END IF;
193: END IF;
194: END IF;
201: 'MSCITTK',
202: NULL, -- description
203: NULL, -- start date
204: FALSE, -- TRUE,
205: MSC_CL_COLLECTION.v_instance_id);
206: COMMIT;
207:
208: IF lvs_request_id(1) = 0 THEN
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
206: COMMIT;
207:
208: IF lvs_request_id(1) = 0 THEN
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
210: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_ITEM_KEYS');
211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
212: RETURN FALSE;
213: ELSE
214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Items and Category Sets Key Generation');
220: 'MSCTPTK',
221: NULL, -- description
222: NULL, -- start date
223: FALSE, -- TRUE,
224: MSC_CL_COLLECTION.v_instance_id);
225:
226: COMMIT;
227:
228: IF lvs_request_id(2) = 0 THEN
226: COMMIT;
227:
228: IF lvs_request_id(2) = 0 THEN
229: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
230: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_TRADING_PARTNER_KEYS');
231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
232: RETURN FALSE;
233: ELSE
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Trading Parteners Key Generation');
250:
251:
252: /* for bug: 2605884, added this piece of code to set the so_tbl_status to YES after the Key transformations */
253:
254: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=1
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
253:
254: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=1
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
288: SET so_tbl_status= MSC_UTIL.SYS_YES
289: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
290: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
288: SET so_tbl_status= MSC_UTIL.SYS_YES
289: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
290: commit;
291: END IF;
292: END IF;
293: END IF;
341: AND til.PARTNER_TYPE(+)= msca.PARTNER_TYPE
342: AND tsil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
343: AND tsil.SR_TP_SITE_ID(+)= msca.PARTNER_SITE_ID
344: AND tsil.PARTNER_TYPE(+)= msca.PARTNER_TYPE
345: AND msca.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
346: AND mtil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
347: AND mtil.SR_TP_ID(+)= msca.CARRIER_PARTNER_ID
348: AND mtil.PARTNER_TYPE(+)=4;
349:
356: mscd.CALENDAR_END_DATE,
357: mscd.DESCRIPTION,
358: mscd.SR_INSTANCE_ID
359: FROM MSC_ST_CALENDAR_DATES mscd
360: WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
361:
362:
363:
364: --Calculate the first and last working days for each of the calendars in MSC_CALENDARS
368: Max(CALENDAR_DATE) LAST_WORKING_DATE,
369: CALENDAR_CODE,
370: SR_INSTANCE_ID
371: FROM MSC_CALENDAR_DATES mscd
372: WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
373: and seq_num is not null
374: GROUP BY CALENDAR_CODE, SR_INSTANCE_ID;
375:
376:
389: mscd.DESCRIPTION,
390: mscd.EXCEPTION_SET_ID,
391: mscd.SR_INSTANCE_ID
392: FROM MSC_ST_CALENDAR_DATES mscd
393: WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
394:
395: CURSOR c2 IS
396: SELECT
397: mspsd.CALENDAR_CODE,
402: mspsd.NEXT_DATE,
403: mspsd.PRIOR_DATE,
404: mspsd.SR_INSTANCE_ID
405: FROM MSC_ST_PERIOD_START_DATES mspsd
406: WHERE mspsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
407:
408: CURSOR c3 IS
409: SELECT
410: mscysd.CALENDAR_CODE,
411: mscysd.EXCEPTION_SET_ID,
412: mscysd.YEAR_START_DATE,
413: mscysd.SR_INSTANCE_ID
414: FROM MSC_ST_CAL_YEAR_START_DATES mscysd
415: WHERE mscysd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
416:
417: CURSOR c4 IS
418: SELECT
419: mscwsd.CALENDAR_CODE,
423: mscwsd.PRIOR_DATE,
424: mscwsd.SEQ_NUM,
425: mscwsd.SR_INSTANCE_ID
426: FROM MSC_ST_CAL_WEEK_START_DATES mscwsd
427: WHERE mscwsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
428:
429: c_count NUMBER:= 0;
430:
431: lv_sql_stmt VARCHAR2(5000);
453:
454: BEGIN
455:
456:
457: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458:
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
455:
456:
457: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458:
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462:
463: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
457: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458:
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462:
463: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
464:
465: END IF;
459: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460:
461: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462:
463: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
464:
465: END IF;
466:
467: -- Calendar Dates --
465: END IF;
466:
467: -- Calendar Dates --
468:
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
468:
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
475:
476:
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
475:
476:
477: COMMIT;
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
475:
476:
477: COMMIT;
478: lv_sql_stmt :=
560: FOR j IN 1..lb_CALENDAR_CODE.COUNT LOOP
561:
562: BEGIN
563:
564: --IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
565:
566: UPDATE MSC_CALENDAR_DATES
567: SET
568: SEQ_NUM= lb_SEQ_NUM(j),
572: PRIOR_DATE= lb_PRIOR_DATE(j),
573: CALENDAR_START_DATE= lb_CALENDAR_START_DATE(j),
574: CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
575: DESCRIPTION= lb_DESCRIPTION(j),
576: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
577: Deleted_Flag= MSC_UTIL.SYS_NO,
578: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
579: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
580: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
574: CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
575: DESCRIPTION= lb_DESCRIPTION(j),
576: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
577: Deleted_Flag= MSC_UTIL.SYS_NO,
578: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
579: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
580: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
581: AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
582: AND EXCEPTION_SET_ID= lb_EXCEPTION_SET_ID(j)
575: DESCRIPTION= lb_DESCRIPTION(j),
576: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
577: Deleted_Flag= MSC_UTIL.SYS_NO,
578: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
579: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
580: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
581: AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
582: AND EXCEPTION_SET_ID= lb_EXCEPTION_SET_ID(j)
583: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
598: lb_CALENDAR_END_DATE(j),
599: lb_DESCRIPTION(j),
600: lb_EXCEPTION_SET_ID(j),
601: lb_SR_INSTANCE_ID(j),
602: MSC_CL_COLLECTION.v_last_collection_id,
603: MSC_UTIL.SYS_NO,
604: MSC_CL_COLLECTION.v_current_date,
605: MSC_CL_COLLECTION.v_current_user,
606: MSC_CL_COLLECTION.v_current_date,
600: lb_EXCEPTION_SET_ID(j),
601: lb_SR_INSTANCE_ID(j),
602: MSC_CL_COLLECTION.v_last_collection_id,
603: MSC_UTIL.SYS_NO,
604: MSC_CL_COLLECTION.v_current_date,
605: MSC_CL_COLLECTION.v_current_user,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user ;
608:
601: lb_SR_INSTANCE_ID(j),
602: MSC_CL_COLLECTION.v_last_collection_id,
603: MSC_UTIL.SYS_NO,
604: MSC_CL_COLLECTION.v_current_date,
605: MSC_CL_COLLECTION.v_current_user,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user ;
608:
609: END IF;
602: MSC_CL_COLLECTION.v_last_collection_id,
603: MSC_UTIL.SYS_NO,
604: MSC_CL_COLLECTION.v_current_date,
605: MSC_CL_COLLECTION.v_current_user,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user ;
608:
609: END IF;
610:
603: MSC_UTIL.SYS_NO,
604: MSC_CL_COLLECTION.v_current_date,
605: MSC_CL_COLLECTION.v_current_user,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user ;
608:
609: END IF;
610:
611:
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
625: RAISE;
626:
627: ELSE
628: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
629:
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
631: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
632: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
661:
662:
663: DELETE MSC_CALENDAR_DATES
664: WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
665: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
666:
667: COMMIT;
668:
669: msc_analyse_tables_pk.analyse_table( 'MSC_CALENDAR_DATES');
673:
674: --If it is complete or partial or continuous refresh, delete existing calendars in the current instance from MSC_CALENDARS
675:
676:
677: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
678: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, NULL);
679: END IF;
680:
681: FOR c_rec IN c5 LOOP
674: --If it is complete or partial or continuous refresh, delete existing calendars in the current instance from MSC_CALENDARS
675:
676:
677: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
678: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, NULL);
679: END IF;
680:
681: FOR c_rec IN c5 LOOP
682:
681: FOR c_rec IN c5 LOOP
682:
683: BEGIN
684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
685: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
686:
687: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
688:
689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
683: BEGIN
684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
685: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
686:
687: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
688:
689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
690:
691: INSERT INTO MSC_CALENDARS
704: c_rec.DESCRIPTION,
705: c_rec.CALENDAR_START_DATE,
706: c_rec.CALENDAR_END_DATE,
707: c_rec.SR_INSTANCE_ID,
708: MSC_CL_COLLECTION.v_last_collection_id,
709: MSC_CL_COLLECTION.v_current_date,
710: MSC_CL_COLLECTION.v_current_user,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user );
705: c_rec.CALENDAR_START_DATE,
706: c_rec.CALENDAR_END_DATE,
707: c_rec.SR_INSTANCE_ID,
708: MSC_CL_COLLECTION.v_last_collection_id,
709: MSC_CL_COLLECTION.v_current_date,
710: MSC_CL_COLLECTION.v_current_user,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user );
713:
706: c_rec.CALENDAR_END_DATE,
707: c_rec.SR_INSTANCE_ID,
708: MSC_CL_COLLECTION.v_last_collection_id,
709: MSC_CL_COLLECTION.v_current_date,
710: MSC_CL_COLLECTION.v_current_user,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user );
713:
714: END IF;
707: c_rec.SR_INSTANCE_ID,
708: MSC_CL_COLLECTION.v_last_collection_id,
709: MSC_CL_COLLECTION.v_current_date,
710: MSC_CL_COLLECTION.v_current_user,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user );
713:
714: END IF;
715:
708: MSC_CL_COLLECTION.v_last_collection_id,
709: MSC_CL_COLLECTION.v_current_date,
710: MSC_CL_COLLECTION.v_current_user,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user );
713:
714: END IF;
715:
716: END IF;
727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
728: RAISE;
729:
730: ELSE
731: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
732:
733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
734: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
735: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
770: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
771: RAISE;
772:
773: ELSE
774: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
775:
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
777: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
778: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
790:
791:
792: --Collection of Calendar Assignments in case of complete, targeted and continuous refresh modes only..
793:
794: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
795:
796: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_ASSIGNMENTS', MSC_CL_COLLECTION.v_instance_id, NULL);
797:
798:
792: --Collection of Calendar Assignments in case of complete, targeted and continuous refresh modes only..
793:
794: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
795:
796: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_ASSIGNMENTS', MSC_CL_COLLECTION.v_instance_id, NULL);
797:
798:
799: FOR c_rec IN c7 LOOP
800:
829: c_rec.CARRIER_PARTNER_ID,
830: c_rec.PARTNER_TYPE,
831: c_rec.ASSOCIATION_LEVEL,
832: c_rec.SHIP_METHOD_CODE,
833: MSC_CL_COLLECTION.v_last_collection_id,
834: MSC_CL_COLLECTION.v_current_date,
835: MSC_CL_COLLECTION.v_current_user,
836: MSC_CL_COLLECTION.v_current_date,
837: MSC_CL_COLLECTION.v_current_user,
830: c_rec.PARTNER_TYPE,
831: c_rec.ASSOCIATION_LEVEL,
832: c_rec.SHIP_METHOD_CODE,
833: MSC_CL_COLLECTION.v_last_collection_id,
834: MSC_CL_COLLECTION.v_current_date,
835: MSC_CL_COLLECTION.v_current_user,
836: MSC_CL_COLLECTION.v_current_date,
837: MSC_CL_COLLECTION.v_current_user,
838: MSC_CL_COLLECTION.v_current_user);
831: c_rec.ASSOCIATION_LEVEL,
832: c_rec.SHIP_METHOD_CODE,
833: MSC_CL_COLLECTION.v_last_collection_id,
834: MSC_CL_COLLECTION.v_current_date,
835: MSC_CL_COLLECTION.v_current_user,
836: MSC_CL_COLLECTION.v_current_date,
837: MSC_CL_COLLECTION.v_current_user,
838: MSC_CL_COLLECTION.v_current_user);
839:
832: c_rec.SHIP_METHOD_CODE,
833: MSC_CL_COLLECTION.v_last_collection_id,
834: MSC_CL_COLLECTION.v_current_date,
835: MSC_CL_COLLECTION.v_current_user,
836: MSC_CL_COLLECTION.v_current_date,
837: MSC_CL_COLLECTION.v_current_user,
838: MSC_CL_COLLECTION.v_current_user);
839:
840:
833: MSC_CL_COLLECTION.v_last_collection_id,
834: MSC_CL_COLLECTION.v_current_date,
835: MSC_CL_COLLECTION.v_current_user,
836: MSC_CL_COLLECTION.v_current_date,
837: MSC_CL_COLLECTION.v_current_user,
838: MSC_CL_COLLECTION.v_current_user);
839:
840:
841: EXCEPTION
834: MSC_CL_COLLECTION.v_current_date,
835: MSC_CL_COLLECTION.v_current_user,
836: MSC_CL_COLLECTION.v_current_date,
837: MSC_CL_COLLECTION.v_current_user,
838: MSC_CL_COLLECTION.v_current_user);
839:
840:
841: EXCEPTION
842: WHEN OTHERS THEN
851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
852: RAISE;
853:
854: ELSE
855: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
856:
857: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
858: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
859: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
876: FOR c_rec IN c2 LOOP
877:
878: BEGIN
879:
880: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
881:
882: UPDATE MSC_PERIOD_START_DATES
883: SET
884: PERIOD_SEQUENCE_NUM= c_rec.PERIOD_SEQUENCE_NUM,
884: PERIOD_SEQUENCE_NUM= c_rec.PERIOD_SEQUENCE_NUM,
885: PERIOD_NAME= c_rec.PERIOD_NAME,
886: NEXT_DATE= c_rec.NEXT_DATE,
887: PRIOR_DATE= c_rec.PRIOR_DATE,
888: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
889: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
890: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
891: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
892: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
885: PERIOD_NAME= c_rec.PERIOD_NAME,
886: NEXT_DATE= c_rec.NEXT_DATE,
887: PRIOR_DATE= c_rec.PRIOR_DATE,
888: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
889: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
890: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
891: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
892: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
893: AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
886: NEXT_DATE= c_rec.NEXT_DATE,
887: PRIOR_DATE= c_rec.PRIOR_DATE,
888: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
889: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
890: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
891: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
892: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
893: AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
894: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
895:
896: END IF;
897:
898:
899: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
900:
901: INSERT INTO MSC_PERIOD_START_DATES
902: ( CALENDAR_CODE,
903: EXCEPTION_SET_ID,
920: c_rec.PERIOD_NAME,
921: c_rec.NEXT_DATE,
922: c_rec.PRIOR_DATE,
923: c_rec.SR_INSTANCE_ID,
924: MSC_CL_COLLECTION.v_last_collection_id,
925: MSC_CL_COLLECTION.v_current_date,
926: MSC_CL_COLLECTION.v_current_user,
927: MSC_CL_COLLECTION.v_current_date,
928: MSC_CL_COLLECTION.v_current_user );
921: c_rec.NEXT_DATE,
922: c_rec.PRIOR_DATE,
923: c_rec.SR_INSTANCE_ID,
924: MSC_CL_COLLECTION.v_last_collection_id,
925: MSC_CL_COLLECTION.v_current_date,
926: MSC_CL_COLLECTION.v_current_user,
927: MSC_CL_COLLECTION.v_current_date,
928: MSC_CL_COLLECTION.v_current_user );
929:
922: c_rec.PRIOR_DATE,
923: c_rec.SR_INSTANCE_ID,
924: MSC_CL_COLLECTION.v_last_collection_id,
925: MSC_CL_COLLECTION.v_current_date,
926: MSC_CL_COLLECTION.v_current_user,
927: MSC_CL_COLLECTION.v_current_date,
928: MSC_CL_COLLECTION.v_current_user );
929:
930: END IF;
923: c_rec.SR_INSTANCE_ID,
924: MSC_CL_COLLECTION.v_last_collection_id,
925: MSC_CL_COLLECTION.v_current_date,
926: MSC_CL_COLLECTION.v_current_user,
927: MSC_CL_COLLECTION.v_current_date,
928: MSC_CL_COLLECTION.v_current_user );
929:
930: END IF;
931:
924: MSC_CL_COLLECTION.v_last_collection_id,
925: MSC_CL_COLLECTION.v_current_date,
926: MSC_CL_COLLECTION.v_current_user,
927: MSC_CL_COLLECTION.v_current_date,
928: MSC_CL_COLLECTION.v_current_user );
929:
930: END IF;
931:
932: c_count:= c_count+1;
930: END IF;
931:
932: c_count:= c_count+1;
933:
934: IF c_count> MSC_CL_COLLECTION.PBS THEN
935: COMMIT;
936: c_count:= 0;
937: END IF;
938:
951: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
952: RAISE;
953:
954: ELSE
955: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
956:
957: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
958: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
959: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
989: FOR c_rec IN c3 LOOP
990:
991: BEGIN
992:
993: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
994:
995: UPDATE MSC_CAL_YEAR_START_DATES
996: SET
997: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
993: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
994:
995: UPDATE MSC_CAL_YEAR_START_DATES
996: SET
997: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
998: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
999: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1000: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1001: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
994:
995: UPDATE MSC_CAL_YEAR_START_DATES
996: SET
997: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
998: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
999: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1000: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1001: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1002: AND YEAR_START_DATE= c_rec.YEAR_START_DATE
995: UPDATE MSC_CAL_YEAR_START_DATES
996: SET
997: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
998: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
999: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1000: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1001: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1002: AND YEAR_START_DATE= c_rec.YEAR_START_DATE
1003: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1004:
1005: END IF;
1006:
1007:
1008: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1009:
1010: INSERT INTO MSC_CAL_YEAR_START_DATES
1011: ( CALENDAR_CODE,
1012: EXCEPTION_SET_ID,
1021: ( c_rec.CALENDAR_CODE,
1022: c_rec.EXCEPTION_SET_ID,
1023: c_rec.YEAR_START_DATE,
1024: c_rec.SR_INSTANCE_ID,
1025: MSC_CL_COLLECTION.v_last_collection_id,
1026: MSC_CL_COLLECTION.v_current_date,
1027: MSC_CL_COLLECTION.v_current_user,
1028: MSC_CL_COLLECTION.v_current_date,
1029: MSC_CL_COLLECTION.v_current_user );
1022: c_rec.EXCEPTION_SET_ID,
1023: c_rec.YEAR_START_DATE,
1024: c_rec.SR_INSTANCE_ID,
1025: MSC_CL_COLLECTION.v_last_collection_id,
1026: MSC_CL_COLLECTION.v_current_date,
1027: MSC_CL_COLLECTION.v_current_user,
1028: MSC_CL_COLLECTION.v_current_date,
1029: MSC_CL_COLLECTION.v_current_user );
1030:
1023: c_rec.YEAR_START_DATE,
1024: c_rec.SR_INSTANCE_ID,
1025: MSC_CL_COLLECTION.v_last_collection_id,
1026: MSC_CL_COLLECTION.v_current_date,
1027: MSC_CL_COLLECTION.v_current_user,
1028: MSC_CL_COLLECTION.v_current_date,
1029: MSC_CL_COLLECTION.v_current_user );
1030:
1031: END IF;
1024: c_rec.SR_INSTANCE_ID,
1025: MSC_CL_COLLECTION.v_last_collection_id,
1026: MSC_CL_COLLECTION.v_current_date,
1027: MSC_CL_COLLECTION.v_current_user,
1028: MSC_CL_COLLECTION.v_current_date,
1029: MSC_CL_COLLECTION.v_current_user );
1030:
1031: END IF;
1032:
1025: MSC_CL_COLLECTION.v_last_collection_id,
1026: MSC_CL_COLLECTION.v_current_date,
1027: MSC_CL_COLLECTION.v_current_user,
1028: MSC_CL_COLLECTION.v_current_date,
1029: MSC_CL_COLLECTION.v_current_user );
1030:
1031: END IF;
1032:
1033: c_count:= c_count+1;
1031: END IF;
1032:
1033: c_count:= c_count+1;
1034:
1035: IF c_count> MSC_CL_COLLECTION.PBS THEN
1036: COMMIT;
1037: c_count:= 0;
1038: END IF;
1039:
1050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1051: RAISE;
1052:
1053: ELSE
1054: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1055:
1056: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1057: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1058: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1088: FOR c_rec IN c4 LOOP
1089:
1090: BEGIN
1091:
1092: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1093:
1094: UPDATE MSC_CAL_WEEK_START_DATES
1095: SET
1096: NEXT_DATE= c_rec.NEXT_DATE,
1095: SET
1096: NEXT_DATE= c_rec.NEXT_DATE,
1097: PRIOR_DATE= c_rec.PRIOR_DATE,
1098: SEQ_NUM= c_rec.SEQ_NUM,
1099: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1100: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1101: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1102: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1103: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1096: NEXT_DATE= c_rec.NEXT_DATE,
1097: PRIOR_DATE= c_rec.PRIOR_DATE,
1098: SEQ_NUM= c_rec.SEQ_NUM,
1099: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1100: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1101: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1102: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1103: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1104: AND WEEK_START_DATE= c_rec.WEEK_START_DATE
1097: PRIOR_DATE= c_rec.PRIOR_DATE,
1098: SEQ_NUM= c_rec.SEQ_NUM,
1099: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1100: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1101: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1102: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1103: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1104: AND WEEK_START_DATE= c_rec.WEEK_START_DATE
1105: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1105: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1106:
1107: END IF;
1108:
1109: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1110:
1111: INSERT INTO MSC_CAL_WEEK_START_DATES
1112: ( CALENDAR_CODE,
1113: EXCEPTION_SET_ID,
1128: c_rec.NEXT_DATE,
1129: c_rec.PRIOR_DATE,
1130: c_rec.SEQ_NUM,
1131: c_rec.SR_INSTANCE_ID,
1132: MSC_CL_COLLECTION.v_last_collection_id,
1133: MSC_CL_COLLECTION.v_current_date,
1134: MSC_CL_COLLECTION.v_current_user,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user );
1129: c_rec.PRIOR_DATE,
1130: c_rec.SEQ_NUM,
1131: c_rec.SR_INSTANCE_ID,
1132: MSC_CL_COLLECTION.v_last_collection_id,
1133: MSC_CL_COLLECTION.v_current_date,
1134: MSC_CL_COLLECTION.v_current_user,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user );
1137:
1130: c_rec.SEQ_NUM,
1131: c_rec.SR_INSTANCE_ID,
1132: MSC_CL_COLLECTION.v_last_collection_id,
1133: MSC_CL_COLLECTION.v_current_date,
1134: MSC_CL_COLLECTION.v_current_user,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user );
1137:
1138: END IF;
1131: c_rec.SR_INSTANCE_ID,
1132: MSC_CL_COLLECTION.v_last_collection_id,
1133: MSC_CL_COLLECTION.v_current_date,
1134: MSC_CL_COLLECTION.v_current_user,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user );
1137:
1138: END IF;
1139:
1132: MSC_CL_COLLECTION.v_last_collection_id,
1133: MSC_CL_COLLECTION.v_current_date,
1134: MSC_CL_COLLECTION.v_current_user,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user );
1137:
1138: END IF;
1139:
1140: c_count:= c_count+1;
1138: END IF;
1139:
1140: c_count:= c_count+1;
1141:
1142: IF c_count> MSC_CL_COLLECTION.PBS THEN
1143: COMMIT;
1144: c_count:= 0;
1145: END IF;
1146:
1157: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1158: RAISE;
1159:
1160: ELSE
1161: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1162:
1163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1164: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1165: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1204: msrs.SHIFT_NUM,
1205: msrs.CAPACITY_UNITS,
1206: msrs.SR_INSTANCE_ID
1207: FROM MSC_ST_RESOURCE_SHIFTS msrs
1208: WHERE msrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1209:
1210: CURSOR c6 IS
1211: SELECT
1212: mscs.CALENDAR_CODE,
1215: mscs.DAYS_OFF,
1216: mscs.DESCRIPTION,
1217: mscs.SR_INSTANCE_ID
1218: FROM MSC_ST_CALENDAR_SHIFTS mscs
1219: WHERE mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1220:
1221: CURSOR c7 IS
1222: SELECT
1223: mssd.CALENDAR_CODE,
1230: mssd.NEXT_DATE,
1231: mssd.PRIOR_DATE,
1232: mssd.SR_INSTANCE_ID
1233: FROM MSC_ST_SHIFT_DATES mssd
1234: WHERE mssd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1235:
1236: CURSOR c8 IS
1237: SELECT
1238: msrc.DEPARTMENT_ID,
1247: msrc.ACTION_TYPE,
1248: msrc.DELETED_FLAG,
1249: msrc.SR_INSTANCE_ID
1250: FROM MSC_ST_RESOURCE_CHANGES msrc
1251: WHERE msrc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1252: ORDER BY
1253: msrc.DELETED_FLAG;
1254:
1255: CURSOR c9 IS
1259: msst.FROM_TIME,
1260: msst.TO_TIME,
1261: msst.SR_INSTANCE_ID
1262: FROM MSC_ST_SHIFT_TIMES msst
1263: WHERE msst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1264:
1265:
1266: CURSOR c10 IS
1267: SELECT
1271: msse.EXCEPTION_DATE,
1272: msse.EXCEPTION_TYPE,
1273: msse.SR_INSTANCE_ID
1274: FROM MSC_ST_SHIFT_EXCEPTIONS msse
1275: WHERE msse.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1276:
1277: c_count NUMBER:= 0;
1278: lv_sql_stmt varchar2(500);
1279: lv_dblink varchar2(50);
1286: ex_calc_res_avail EXCEPTION;
1287:
1288: BEGIN
1289:
1290: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1291: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1292:
1293: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294:
1287:
1288: BEGIN
1289:
1290: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1291: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1292:
1293: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294:
1295: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1289:
1290: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1291: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1292:
1293: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294:
1295: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1296:
1297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1291: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1292:
1293: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294:
1295: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1296:
1297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1293: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294:
1295: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1296:
1297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1295: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1296:
1297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1297: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1298:
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1299: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1300:
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1306:
1307: END IF;
1301: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1302:
1303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304:
1305: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1306:
1307: END IF;
1308:
1309: c_count:= 0;
1311: FOR c_rec IN c5 LOOP
1312:
1313: BEGIN
1314:
1315: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1316:
1317: UPDATE MSC_RESOURCE_SHIFTS
1318: SET
1319: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1315: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1316:
1317: UPDATE MSC_RESOURCE_SHIFTS
1318: SET
1319: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1320: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1321: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1322: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1323: AND RESOURCE_ID= c_rec.RESOURCE_ID
1316:
1317: UPDATE MSC_RESOURCE_SHIFTS
1318: SET
1319: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1320: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1321: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1322: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1323: AND RESOURCE_ID= c_rec.RESOURCE_ID
1324: AND SHIFT_NUM= c_rec.SHIFT_NUM
1317: UPDATE MSC_RESOURCE_SHIFTS
1318: SET
1319: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1320: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1321: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1322: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1323: AND RESOURCE_ID= c_rec.RESOURCE_ID
1324: AND SHIFT_NUM= c_rec.SHIFT_NUM
1325: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1325: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1326:
1327: END IF;
1328:
1329: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1330:
1331: INSERT INTO MSC_RESOURCE_SHIFTS
1332: ( DEPARTMENT_ID,
1333: RESOURCE_ID,
1344: c_rec.RESOURCE_ID,
1345: c_rec.SHIFT_NUM,
1346: c_rec.CAPACITY_UNITS,
1347: c_rec.SR_INSTANCE_ID,
1348: MSC_CL_COLLECTION.v_last_collection_id,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user,
1351: MSC_CL_COLLECTION.v_current_date,
1352: MSC_CL_COLLECTION.v_current_user );
1345: c_rec.SHIFT_NUM,
1346: c_rec.CAPACITY_UNITS,
1347: c_rec.SR_INSTANCE_ID,
1348: MSC_CL_COLLECTION.v_last_collection_id,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user,
1351: MSC_CL_COLLECTION.v_current_date,
1352: MSC_CL_COLLECTION.v_current_user );
1353:
1346: c_rec.CAPACITY_UNITS,
1347: c_rec.SR_INSTANCE_ID,
1348: MSC_CL_COLLECTION.v_last_collection_id,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user,
1351: MSC_CL_COLLECTION.v_current_date,
1352: MSC_CL_COLLECTION.v_current_user );
1353:
1354: END IF;
1347: c_rec.SR_INSTANCE_ID,
1348: MSC_CL_COLLECTION.v_last_collection_id,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user,
1351: MSC_CL_COLLECTION.v_current_date,
1352: MSC_CL_COLLECTION.v_current_user );
1353:
1354: END IF;
1355:
1348: MSC_CL_COLLECTION.v_last_collection_id,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user,
1351: MSC_CL_COLLECTION.v_current_date,
1352: MSC_CL_COLLECTION.v_current_user );
1353:
1354: END IF;
1355:
1356: c_count:= c_count+1;
1354: END IF;
1355:
1356: c_count:= c_count+1;
1357:
1358: IF c_count> MSC_CL_COLLECTION.PBS THEN
1359: COMMIT;
1360: c_count:= 0;
1361: END IF;
1362:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1374: RAISE;
1375:
1376: ELSE
1377: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1378:
1379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1380: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1381: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1411: FOR c_rec IN c6 LOOP
1412:
1413: BEGIN
1414:
1415: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1416:
1417: UPDATE MSC_CALENDAR_SHIFTS
1418: SET
1419: DAYS_ON= c_rec.DAYS_ON,
1418: SET
1419: DAYS_ON= c_rec.DAYS_ON,
1420: DAYS_OFF= c_rec.DAYS_OFF,
1421: DESCRIPTION= c_rec.DESCRIPTION,
1422: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1423: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1424: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1425: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1426: AND SHIFT_NUM= c_rec.SHIFT_NUM
1419: DAYS_ON= c_rec.DAYS_ON,
1420: DAYS_OFF= c_rec.DAYS_OFF,
1421: DESCRIPTION= c_rec.DESCRIPTION,
1422: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1423: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1424: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1425: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1426: AND SHIFT_NUM= c_rec.SHIFT_NUM
1427: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1420: DAYS_OFF= c_rec.DAYS_OFF,
1421: DESCRIPTION= c_rec.DESCRIPTION,
1422: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1423: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1424: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1425: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1426: AND SHIFT_NUM= c_rec.SHIFT_NUM
1427: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1428:
1427: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1428:
1429: END IF;
1430:
1431: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1432:
1433: INSERT INTO MSC_CALENDAR_SHIFTS
1434: ( CALENDAR_CODE,
1435: SHIFT_NUM,
1448: c_rec.DAYS_ON,
1449: c_rec.DAYS_OFF,
1450: c_rec.DESCRIPTION,
1451: c_rec.SR_INSTANCE_ID,
1452: MSC_CL_COLLECTION.v_last_collection_id,
1453: MSC_CL_COLLECTION.v_current_date,
1454: MSC_CL_COLLECTION.v_current_user,
1455: MSC_CL_COLLECTION.v_current_date,
1456: MSC_CL_COLLECTION.v_current_user );
1449: c_rec.DAYS_OFF,
1450: c_rec.DESCRIPTION,
1451: c_rec.SR_INSTANCE_ID,
1452: MSC_CL_COLLECTION.v_last_collection_id,
1453: MSC_CL_COLLECTION.v_current_date,
1454: MSC_CL_COLLECTION.v_current_user,
1455: MSC_CL_COLLECTION.v_current_date,
1456: MSC_CL_COLLECTION.v_current_user );
1457:
1450: c_rec.DESCRIPTION,
1451: c_rec.SR_INSTANCE_ID,
1452: MSC_CL_COLLECTION.v_last_collection_id,
1453: MSC_CL_COLLECTION.v_current_date,
1454: MSC_CL_COLLECTION.v_current_user,
1455: MSC_CL_COLLECTION.v_current_date,
1456: MSC_CL_COLLECTION.v_current_user );
1457:
1458: END IF;
1451: c_rec.SR_INSTANCE_ID,
1452: MSC_CL_COLLECTION.v_last_collection_id,
1453: MSC_CL_COLLECTION.v_current_date,
1454: MSC_CL_COLLECTION.v_current_user,
1455: MSC_CL_COLLECTION.v_current_date,
1456: MSC_CL_COLLECTION.v_current_user );
1457:
1458: END IF;
1459:
1452: MSC_CL_COLLECTION.v_last_collection_id,
1453: MSC_CL_COLLECTION.v_current_date,
1454: MSC_CL_COLLECTION.v_current_user,
1455: MSC_CL_COLLECTION.v_current_date,
1456: MSC_CL_COLLECTION.v_current_user );
1457:
1458: END IF;
1459:
1460: c_count:= c_count+1;
1458: END IF;
1459:
1460: c_count:= c_count+1;
1461:
1462: IF c_count> MSC_CL_COLLECTION.PBS THEN
1463: COMMIT;
1464: c_count:= 0;
1465: END IF;
1466:
1478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1479: RAISE;
1480:
1481: ELSE
1482: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1483:
1484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1485: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1486: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1511: FOR c_rec IN c7 LOOP
1512:
1513: BEGIN
1514:
1515: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1516:
1517: UPDATE MSC_SHIFT_DATES
1518: SET
1519: SEQ_NUM= c_rec.SEQ_NUM,
1520: NEXT_SEQ_NUM= c_rec.NEXT_SEQ_NUM,
1521: PRIOR_SEQ_NUM= c_rec.PRIOR_SEQ_NUM,
1522: NEXT_DATE= c_rec.NEXT_DATE,
1523: PRIOR_DATE= c_rec.PRIOR_DATE,
1524: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1525: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1526: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1527: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1528: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1521: PRIOR_SEQ_NUM= c_rec.PRIOR_SEQ_NUM,
1522: NEXT_DATE= c_rec.NEXT_DATE,
1523: PRIOR_DATE= c_rec.PRIOR_DATE,
1524: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1525: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1526: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1527: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1528: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1529: AND SHIFT_NUM= c_rec.SHIFT_NUM
1522: NEXT_DATE= c_rec.NEXT_DATE,
1523: PRIOR_DATE= c_rec.PRIOR_DATE,
1524: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1525: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1526: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1527: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1528: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1529: AND SHIFT_NUM= c_rec.SHIFT_NUM
1530: AND SHIFT_DATE= c_rec.SHIFT_DATE
1531: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1532:
1533: END IF;
1534:
1535: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1536:
1537: INSERT INTO MSC_SHIFT_DATES
1538: ( CALENDAR_CODE,
1539: EXCEPTION_SET_ID,
1560: c_rec.PRIOR_SEQ_NUM,
1561: c_rec.NEXT_DATE,
1562: c_rec.PRIOR_DATE,
1563: c_rec.SR_INSTANCE_ID,
1564: MSC_CL_COLLECTION.v_last_collection_id,
1565: MSC_CL_COLLECTION.v_current_date,
1566: MSC_CL_COLLECTION.v_current_user,
1567: MSC_CL_COLLECTION.v_current_date,
1568: MSC_CL_COLLECTION.v_current_user );
1561: c_rec.NEXT_DATE,
1562: c_rec.PRIOR_DATE,
1563: c_rec.SR_INSTANCE_ID,
1564: MSC_CL_COLLECTION.v_last_collection_id,
1565: MSC_CL_COLLECTION.v_current_date,
1566: MSC_CL_COLLECTION.v_current_user,
1567: MSC_CL_COLLECTION.v_current_date,
1568: MSC_CL_COLLECTION.v_current_user );
1569:
1562: c_rec.PRIOR_DATE,
1563: c_rec.SR_INSTANCE_ID,
1564: MSC_CL_COLLECTION.v_last_collection_id,
1565: MSC_CL_COLLECTION.v_current_date,
1566: MSC_CL_COLLECTION.v_current_user,
1567: MSC_CL_COLLECTION.v_current_date,
1568: MSC_CL_COLLECTION.v_current_user );
1569:
1570: END IF;
1563: c_rec.SR_INSTANCE_ID,
1564: MSC_CL_COLLECTION.v_last_collection_id,
1565: MSC_CL_COLLECTION.v_current_date,
1566: MSC_CL_COLLECTION.v_current_user,
1567: MSC_CL_COLLECTION.v_current_date,
1568: MSC_CL_COLLECTION.v_current_user );
1569:
1570: END IF;
1571:
1564: MSC_CL_COLLECTION.v_last_collection_id,
1565: MSC_CL_COLLECTION.v_current_date,
1566: MSC_CL_COLLECTION.v_current_user,
1567: MSC_CL_COLLECTION.v_current_date,
1568: MSC_CL_COLLECTION.v_current_user );
1569:
1570: END IF;
1571:
1572: c_count:= c_count+1;
1570: END IF;
1571:
1572: c_count:= c_count+1;
1573:
1574: IF c_count> MSC_CL_COLLECTION.PBS THEN
1575: COMMIT;
1576: c_count:= 0;
1577: END IF;
1578:
1589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1590: RAISE;
1591:
1592: ELSE
1593: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1594:
1595: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1596: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1597: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1632: FOR c_rec IN c8 LOOP
1633:
1634: BEGIN
1635:
1636: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
1637:
1638: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1639:
1640: DELETE MSC_RESOURCE_CHANGES
1679: c_rec.CAPACITY_CHANGE,
1680: c_rec.SIMULATION_SET,
1681: c_rec.ACTION_TYPE,
1682: c_rec.SR_INSTANCE_ID,
1683: MSC_CL_COLLECTION.v_last_collection_id,
1684: MSC_CL_COLLECTION.v_current_date,
1685: MSC_CL_COLLECTION.v_current_user,
1686: MSC_CL_COLLECTION.v_current_date,
1687: MSC_CL_COLLECTION.v_current_user );
1680: c_rec.SIMULATION_SET,
1681: c_rec.ACTION_TYPE,
1682: c_rec.SR_INSTANCE_ID,
1683: MSC_CL_COLLECTION.v_last_collection_id,
1684: MSC_CL_COLLECTION.v_current_date,
1685: MSC_CL_COLLECTION.v_current_user,
1686: MSC_CL_COLLECTION.v_current_date,
1687: MSC_CL_COLLECTION.v_current_user );
1688:
1681: c_rec.ACTION_TYPE,
1682: c_rec.SR_INSTANCE_ID,
1683: MSC_CL_COLLECTION.v_last_collection_id,
1684: MSC_CL_COLLECTION.v_current_date,
1685: MSC_CL_COLLECTION.v_current_user,
1686: MSC_CL_COLLECTION.v_current_date,
1687: MSC_CL_COLLECTION.v_current_user );
1688:
1689: END IF;
1682: c_rec.SR_INSTANCE_ID,
1683: MSC_CL_COLLECTION.v_last_collection_id,
1684: MSC_CL_COLLECTION.v_current_date,
1685: MSC_CL_COLLECTION.v_current_user,
1686: MSC_CL_COLLECTION.v_current_date,
1687: MSC_CL_COLLECTION.v_current_user );
1688:
1689: END IF;
1690:
1683: MSC_CL_COLLECTION.v_last_collection_id,
1684: MSC_CL_COLLECTION.v_current_date,
1685: MSC_CL_COLLECTION.v_current_user,
1686: MSC_CL_COLLECTION.v_current_date,
1687: MSC_CL_COLLECTION.v_current_user );
1688:
1689: END IF;
1690:
1691: c_count:= c_count+1;
1689: END IF;
1690:
1691: c_count:= c_count+1;
1692:
1693: IF c_count> MSC_CL_COLLECTION.PBS THEN
1694: COMMIT;
1695: c_count:= 0;
1696: END IF;
1697:
1709: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1710: RAISE;
1711:
1712: ELSE
1713: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1714:
1715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1761: FOR c_rec IN c9 LOOP
1762:
1763: BEGIN
1764:
1765: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1766:
1767: UPDATE MSC_SHIFT_TIMES
1768: SET
1769: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1765: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1766:
1767: UPDATE MSC_SHIFT_TIMES
1768: SET
1769: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1770: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1771: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1772: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1773: AND SHIFT_NUM= c_rec.SHIFT_NUM
1766:
1767: UPDATE MSC_SHIFT_TIMES
1768: SET
1769: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1770: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1771: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1772: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1773: AND SHIFT_NUM= c_rec.SHIFT_NUM
1774: AND FROM_TIME= c_rec.FROM_TIME
1767: UPDATE MSC_SHIFT_TIMES
1768: SET
1769: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1770: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1771: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1772: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1773: AND SHIFT_NUM= c_rec.SHIFT_NUM
1774: AND FROM_TIME= c_rec.FROM_TIME
1775: AND TO_TIME= c_rec.TO_TIME
1776: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1777:
1778: END IF;
1779:
1780: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1781:
1782: INSERT INTO MSC_SHIFT_TIMES
1783: ( CALENDAR_CODE,
1784: SHIFT_NUM,
1795: c_rec.SHIFT_NUM,
1796: c_rec.FROM_TIME,
1797: c_rec.TO_TIME,
1798: c_rec.SR_INSTANCE_ID,
1799: MSC_CL_COLLECTION.v_last_collection_id,
1800: MSC_CL_COLLECTION.v_current_date,
1801: MSC_CL_COLLECTION.v_current_user,
1802: MSC_CL_COLLECTION.v_current_date,
1803: MSC_CL_COLLECTION.v_current_user );
1796: c_rec.FROM_TIME,
1797: c_rec.TO_TIME,
1798: c_rec.SR_INSTANCE_ID,
1799: MSC_CL_COLLECTION.v_last_collection_id,
1800: MSC_CL_COLLECTION.v_current_date,
1801: MSC_CL_COLLECTION.v_current_user,
1802: MSC_CL_COLLECTION.v_current_date,
1803: MSC_CL_COLLECTION.v_current_user );
1804:
1797: c_rec.TO_TIME,
1798: c_rec.SR_INSTANCE_ID,
1799: MSC_CL_COLLECTION.v_last_collection_id,
1800: MSC_CL_COLLECTION.v_current_date,
1801: MSC_CL_COLLECTION.v_current_user,
1802: MSC_CL_COLLECTION.v_current_date,
1803: MSC_CL_COLLECTION.v_current_user );
1804:
1805: END IF;
1798: c_rec.SR_INSTANCE_ID,
1799: MSC_CL_COLLECTION.v_last_collection_id,
1800: MSC_CL_COLLECTION.v_current_date,
1801: MSC_CL_COLLECTION.v_current_user,
1802: MSC_CL_COLLECTION.v_current_date,
1803: MSC_CL_COLLECTION.v_current_user );
1804:
1805: END IF;
1806:
1799: MSC_CL_COLLECTION.v_last_collection_id,
1800: MSC_CL_COLLECTION.v_current_date,
1801: MSC_CL_COLLECTION.v_current_user,
1802: MSC_CL_COLLECTION.v_current_date,
1803: MSC_CL_COLLECTION.v_current_user );
1804:
1805: END IF;
1806:
1807: c_count:= c_count+1;
1805: END IF;
1806:
1807: c_count:= c_count+1;
1808:
1809: IF c_count> MSC_CL_COLLECTION.PBS THEN
1810: COMMIT;
1811: c_count:= 0;
1812: END IF;
1813:
1824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1825: RAISE;
1826:
1827: ELSE
1828: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1829:
1830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1831: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1832: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1867: FOR c_rec IN c10 LOOP
1868:
1869: BEGIN
1870:
1871: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1872:
1873: UPDATE MSC_SHIFT_EXCEPTIONS
1874: SET
1875: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1872:
1873: UPDATE MSC_SHIFT_EXCEPTIONS
1874: SET
1875: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1876: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1877: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1878: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1879: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1880: AND SHIFT_NUM= c_rec.SHIFT_NUM
1873: UPDATE MSC_SHIFT_EXCEPTIONS
1874: SET
1875: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1876: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1877: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1878: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1879: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1880: AND SHIFT_NUM= c_rec.SHIFT_NUM
1881: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1874: SET
1875: EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1876: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1877: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1878: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1879: WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1880: AND SHIFT_NUM= c_rec.SHIFT_NUM
1881: AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1882: AND EXCEPTION_DATE= c_rec.EXCEPTION_DATE
1883: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1884:
1885: END IF;
1886:
1887: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1888:
1889: INSERT INTO MSC_SHIFT_EXCEPTIONS
1890: ( CALENDAR_CODE,
1891: SHIFT_NUM,
1904: c_rec.EXCEPTION_SET_ID,
1905: c_rec.EXCEPTION_DATE,
1906: c_rec.EXCEPTION_TYPE,
1907: c_rec.SR_INSTANCE_ID,
1908: MSC_CL_COLLECTION.v_last_collection_id,
1909: MSC_CL_COLLECTION.v_current_date,
1910: MSC_CL_COLLECTION.v_current_user,
1911: MSC_CL_COLLECTION.v_current_date,
1912: MSC_CL_COLLECTION.v_current_user );
1905: c_rec.EXCEPTION_DATE,
1906: c_rec.EXCEPTION_TYPE,
1907: c_rec.SR_INSTANCE_ID,
1908: MSC_CL_COLLECTION.v_last_collection_id,
1909: MSC_CL_COLLECTION.v_current_date,
1910: MSC_CL_COLLECTION.v_current_user,
1911: MSC_CL_COLLECTION.v_current_date,
1912: MSC_CL_COLLECTION.v_current_user );
1913:
1906: c_rec.EXCEPTION_TYPE,
1907: c_rec.SR_INSTANCE_ID,
1908: MSC_CL_COLLECTION.v_last_collection_id,
1909: MSC_CL_COLLECTION.v_current_date,
1910: MSC_CL_COLLECTION.v_current_user,
1911: MSC_CL_COLLECTION.v_current_date,
1912: MSC_CL_COLLECTION.v_current_user );
1913:
1914: END IF;
1907: c_rec.SR_INSTANCE_ID,
1908: MSC_CL_COLLECTION.v_last_collection_id,
1909: MSC_CL_COLLECTION.v_current_date,
1910: MSC_CL_COLLECTION.v_current_user,
1911: MSC_CL_COLLECTION.v_current_date,
1912: MSC_CL_COLLECTION.v_current_user );
1913:
1914: END IF;
1915:
1908: MSC_CL_COLLECTION.v_last_collection_id,
1909: MSC_CL_COLLECTION.v_current_date,
1910: MSC_CL_COLLECTION.v_current_user,
1911: MSC_CL_COLLECTION.v_current_date,
1912: MSC_CL_COLLECTION.v_current_user );
1913:
1914: END IF;
1915:
1916: c_count:= c_count+1;
1914: END IF;
1915:
1916: c_count:= c_count+1;
1917:
1918: IF c_count> MSC_CL_COLLECTION.PBS THEN
1919: COMMIT;
1920: c_count:= 0;
1921: END IF;
1922:
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1934: RAISE;
1935:
1936: ELSE
1937: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1940: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1941: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1970:
1971: COMMIT;
1972: end if;
1973:
1974: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1975: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1976:
1977: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1978: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1971: COMMIT;
1972: end if;
1973:
1974: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1975: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1976:
1977: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1978: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1976:
1977: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1978: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1980: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1981: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982: end if ;
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1980: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1981: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982: end if ;
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1981: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982: end if ;
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1990: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991: MSC_CL_COLLECTION.v_instance_id, -1);
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1990: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991: MSC_CL_COLLECTION.v_instance_id, -1);
1992: END IF;
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1990: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991: MSC_CL_COLLECTION.v_instance_id, -1);
1992: END IF;
1993:
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1990: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991: MSC_CL_COLLECTION.v_instance_id, -1);
1992: END IF;
1993:
1994: SELECT DECODE(M2A_DBLINK,
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1990: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991: MSC_CL_COLLECTION.v_instance_id, -1);
1992: END IF;
1993:
1994: SELECT DECODE(M2A_DBLINK,
1995: NULL,'',
2001: INTO lv_dblink,
2002: lv_dest_a2m,
2003: lv_instance_code
2004: FROM MSC_APPS_INSTANCES
2005: WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2006:
2007: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2008: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2009: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2004: FROM MSC_APPS_INSTANCES
2005: WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2006:
2007: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2008: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2009: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2010: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2011: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2012: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2007: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2008: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2009: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2010: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2011: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2012: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2013: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2014: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2015: END IF;
2012: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2013: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2014: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2015: END IF;
2016: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2017: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2018: END IF;
2019:
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2017: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2018: END IF;
2019:
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2021: lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2022:
2023: IF lv_ret_res_ava = 2 THEN
2024: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2022:
2023: IF lv_ret_res_ava = 2 THEN
2024: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2026: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2027: ELSIF lv_ret_res_ava <> 0 THEN
2028: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2030: RAISE ex_calc_res_avail;
2053:
2054: CURSOR c1 IS
2055: SELECT
2056: /* SCE Change starts */
2057: decode(mc.COMPANY_ID, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.COMPANY_ID) COMPANY_ID ,
2058: /* SCE change ends */
2059: mst.ORGANIZATION_CODE,
2060: mst.ORGANIZATION_TYPE,
2061: mst.SR_TP_ID,
2106: MSC_ST_TRADING_PARTNERS mst,
2107: MSC_COMPANIES MC
2108: WHERE mst.PARTNER_TYPE= 3
2109: /* SCE Change starts */
2110: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2111: -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2112: /* SCE Change Ends */
2113: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2114: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2109: /* SCE Change starts */
2110: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2111: -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2112: /* SCE Change Ends */
2113: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2114: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2115: AND tilc.PARTNER_TYPE(+)= 2
2116: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2117: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2110: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2111: -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2112: /* SCE Change Ends */
2113: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2114: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2115: AND tilc.PARTNER_TYPE(+)= 2
2116: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2117: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2118: AND tils.PARTNER_TYPE(+)= 1
2113: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2114: AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2115: AND tilc.PARTNER_TYPE(+)= 2
2116: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2117: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2118: AND tils.PARTNER_TYPE(+)= 1
2119: AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2120: AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121: AND tsilc.PARTNER_TYPE(+)= 2
2116: AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2117: AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2118: AND tils.PARTNER_TYPE(+)= 1
2119: AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2120: AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121: AND tsilc.PARTNER_TYPE(+)= 2
2122: AND tsilc.SR_TP_SITE_ID(+)= mst.MODELED_CUSTOMER_SITE_ID
2123: AND tsils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2124: AND tsils.PARTNER_TYPE(+)= 1
2119: AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2120: AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121: AND tsilc.PARTNER_TYPE(+)= 2
2122: AND tsilc.SR_TP_SITE_ID(+)= mst.MODELED_CUSTOMER_SITE_ID
2123: AND tsils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2124: AND tsils.PARTNER_TYPE(+)= 1
2125: AND tsils.SR_TP_SITE_ID(+)= mst.MODELED_SUPPLIER_SITE_ID;
2126:
2127:
2138: msts.LATITUDE
2139: FROM MSC_TRADING_PARTNERS mtp,
2140: MSC_ST_TRADING_PARTNER_SITES msts
2141: WHERE msts.PARTNER_TYPE= 3
2142: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2143: AND mtp.SR_TP_ID= msts.SR_TP_ID
2144: AND mtp.PARTNER_TYPE= 3
2145: AND mtp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2146:
2141: WHERE msts.PARTNER_TYPE= 3
2142: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2143: AND mtp.SR_TP_ID= msts.SR_TP_ID
2144: AND mtp.PARTNER_TYPE= 3
2145: AND mtp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2146:
2147: /* For bug#2198339 modified this cursor to bring data only for Vendors-Customers */
2148: CURSOR c3 IS
2149: SELECT DISTINCT
2161: AND til.PARTNER_TYPE= msta.PARTNER_TYPE
2162: AND tsil.SR_INSTANCE_ID= msta.SR_INSTANCE_ID
2163: AND tsil.SR_TP_SITE_ID= msta.SR_TP_SITE_ID
2164: AND tsil.PARTNER_TYPE= msta.PARTNER_TYPE
2165: AND msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2166: AND msta.PARTNER_TYPE IN (1,2);
2167:
2168: CURSOR c4 IS
2169: SELECT
2184: pc.DELETED_FLAG
2185: FROM MSC_TP_ID_LID til,
2186: MSC_TP_SITE_ID_LID tsil,
2187: MSC_ST_PARTNER_CONTACTS pc
2188: WHERE pc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2189: AND pc.DELETED_FLAG in (1, 2)
2190: AND til.sr_tp_id(+)= pc.partner_id
2191: AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2192: AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2188: WHERE pc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2189: AND pc.DELETED_FLAG in (1, 2)
2190: AND til.sr_tp_id(+)= pc.partner_id
2191: AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2192: AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2193: AND tsil.sr_tp_site_id(+)= pc.partner_site_id
2194: AND tsil.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2195: AND tsil.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2196: ORDER BY
2191: AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2192: AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2193: AND tsil.sr_tp_site_id(+)= pc.partner_site_id
2194: AND tsil.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2195: AND tsil.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2196: ORDER BY
2197: 1,2,3,4 ASC;
2198:
2199: /* For bug#2198339 added this cursor to bring Locations associations data only for Organizations */
2206: msta.LOCATION_ID PARTNER_SITE_ID,
2207: msta.SR_INSTANCE_ID
2208: FROM MSC_TRADING_PARTNERS mtps,
2209: MSC_ST_LOCATION_ASSOCIATIONS msta
2210: WHERE msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2211: AND msta.partner_type = 3
2212: AND msta.SR_INSTANCE_ID= mtps.SR_INSTANCE_ID
2213: AND msta.SR_TP_ID= mtps.SR_TP_ID
2214: AND msta.PARTNER_TYPE= mtps.PARTNER_TYPE;
2226: BEGIN
2227:
2228: -- Organization
2229:
2230: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2231:
2232: DELETE MSC_TRADING_PARTNERS
2233: WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2234: AND partner_type=3
2229:
2230: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2231:
2232: DELETE MSC_TRADING_PARTNERS
2233: WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2234: AND partner_type=3
2235: AND nvl(ORG_SUPPLIER_MAPPED,'N') <> 'Y';
2236:
2237: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', MSC_CL_COLLECTION.v_instance_id, NULL,
2233: WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2234: AND partner_type=3
2235: AND nvl(ORG_SUPPLIER_MAPPED,'N') <> 'Y';
2236:
2237: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', MSC_CL_COLLECTION.v_instance_id, NULL,
2238: -- 'AND PARTNER_TYPE=3');
2239:
2240: END IF;
2241:
2336: c_rec.CHART_OF_ACCOUNTS_ID,
2337: c_rec.BUSINESS_GROUP_NAME,
2338: c_rec.LEGAL_ENTITY_NAME,
2339: c_rec.OPERATING_UNIT_NAME,
2340: MSC_CL_COLLECTION.v_last_collection_id,
2341: MSC_CL_COLLECTION.v_current_date,
2342: MSC_CL_COLLECTION.v_current_user,
2343: c_rec.SR_TP_ID,
2344: c_rec.SR_INSTANCE_ID,
2337: c_rec.BUSINESS_GROUP_NAME,
2338: c_rec.LEGAL_ENTITY_NAME,
2339: c_rec.OPERATING_UNIT_NAME,
2340: MSC_CL_COLLECTION.v_last_collection_id,
2341: MSC_CL_COLLECTION.v_current_date,
2342: MSC_CL_COLLECTION.v_current_user,
2343: c_rec.SR_TP_ID,
2344: c_rec.SR_INSTANCE_ID,
2345: c_rec.PARTNER_TYPE;
2338: c_rec.LEGAL_ENTITY_NAME,
2339: c_rec.OPERATING_UNIT_NAME,
2340: MSC_CL_COLLECTION.v_last_collection_id,
2341: MSC_CL_COLLECTION.v_current_date,
2342: MSC_CL_COLLECTION.v_current_user,
2343: c_rec.SR_TP_ID,
2344: c_rec.SR_INSTANCE_ID,
2345: c_rec.PARTNER_TYPE;
2346:
2446: c_rec.CHART_OF_ACCOUNTS_ID,
2447: c_rec.BUSINESS_GROUP_NAME,
2448: c_rec.LEGAL_ENTITY_NAME,
2449: c_rec.OPERATING_UNIT_NAME,
2450: MSC_CL_COLLECTION.v_last_collection_id,
2451: c_rec.INHERIT_OC_OP_SEQ_NUM,
2452: MSC_CL_COLLECTION.v_current_date,
2453: MSC_CL_COLLECTION.v_current_user,
2454: MSC_CL_COLLECTION.v_current_date,
2448: c_rec.LEGAL_ENTITY_NAME,
2449: c_rec.OPERATING_UNIT_NAME,
2450: MSC_CL_COLLECTION.v_last_collection_id,
2451: c_rec.INHERIT_OC_OP_SEQ_NUM,
2452: MSC_CL_COLLECTION.v_current_date,
2453: MSC_CL_COLLECTION.v_current_user,
2454: MSC_CL_COLLECTION.v_current_date,
2455: MSC_CL_COLLECTION.v_current_user );
2456:
2449: c_rec.OPERATING_UNIT_NAME,
2450: MSC_CL_COLLECTION.v_last_collection_id,
2451: c_rec.INHERIT_OC_OP_SEQ_NUM,
2452: MSC_CL_COLLECTION.v_current_date,
2453: MSC_CL_COLLECTION.v_current_user,
2454: MSC_CL_COLLECTION.v_current_date,
2455: MSC_CL_COLLECTION.v_current_user );
2456:
2457: /************** LEGACY_CHANGE_START*************************/
2450: MSC_CL_COLLECTION.v_last_collection_id,
2451: c_rec.INHERIT_OC_OP_SEQ_NUM,
2452: MSC_CL_COLLECTION.v_current_date,
2453: MSC_CL_COLLECTION.v_current_user,
2454: MSC_CL_COLLECTION.v_current_date,
2455: MSC_CL_COLLECTION.v_current_user );
2456:
2457: /************** LEGACY_CHANGE_START*************************/
2458:
2451: c_rec.INHERIT_OC_OP_SEQ_NUM,
2452: MSC_CL_COLLECTION.v_current_date,
2453: MSC_CL_COLLECTION.v_current_user,
2454: MSC_CL_COLLECTION.v_current_date,
2455: MSC_CL_COLLECTION.v_current_user );
2456:
2457: /************** LEGACY_CHANGE_START*************************/
2458:
2459: -- added for Legacy and Exchange
2457: /************** LEGACY_CHANGE_START*************************/
2458:
2459: -- added for Legacy and Exchange
2460:
2461: IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN -- change for l-flow
2462:
2463: lv_exist := 0;
2464:
2465: OPEN c_org_exist FOR
2481: ENABLED_FLAG)
2482: VALUES
2483: (c_rec.SR_INSTANCE_ID,
2484: c_rec.SR_TP_ID,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user,
2489: 1);
2482: VALUES
2483: (c_rec.SR_INSTANCE_ID,
2484: c_rec.SR_TP_ID,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user,
2489: 1);
2490: END IF;
2483: (c_rec.SR_INSTANCE_ID,
2484: c_rec.SR_TP_ID,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user,
2489: 1);
2490: END IF;
2491:
2484: c_rec.SR_TP_ID,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user,
2489: 1);
2490: END IF;
2491:
2492: lv_exist:= 0;
2531: 1,
2532: 1,
2533: 1,
2534: 1,
2535: MSC_CL_COLLECTION.v_current_date,
2536: MSC_CL_COLLECTION.v_current_user,
2537: MSC_CL_COLLECTION.v_current_date,
2538: MSC_CL_COLLECTION.v_current_user );
2539: END IF;
2532: 1,
2533: 1,
2534: 1,
2535: MSC_CL_COLLECTION.v_current_date,
2536: MSC_CL_COLLECTION.v_current_user,
2537: MSC_CL_COLLECTION.v_current_date,
2538: MSC_CL_COLLECTION.v_current_user );
2539: END IF;
2540:
2533: 1,
2534: 1,
2535: MSC_CL_COLLECTION.v_current_date,
2536: MSC_CL_COLLECTION.v_current_user,
2537: MSC_CL_COLLECTION.v_current_date,
2538: MSC_CL_COLLECTION.v_current_user );
2539: END IF;
2540:
2541: END IF;
2534: 1,
2535: MSC_CL_COLLECTION.v_current_date,
2536: MSC_CL_COLLECTION.v_current_user,
2537: MSC_CL_COLLECTION.v_current_date,
2538: MSC_CL_COLLECTION.v_current_user );
2539: END IF;
2540:
2541: END IF;
2542:
2557: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2558: RAISE;
2559:
2560: ELSE
2561: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2562:
2563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2564: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2565: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2586: COMMIT;
2587:
2588: -- Organization Site
2589:
2590: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2591:
2592: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', MSC_CL_COLLECTION.v_instance_id, NULL,
2593: 'AND PARTNER_TYPE=3');
2594:
2588: -- Organization Site
2589:
2590: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2591:
2592: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', MSC_CL_COLLECTION.v_instance_id, NULL,
2593: 'AND PARTNER_TYPE=3');
2594:
2595: END IF;
2596:
2597: FOR c_rec IN c2 LOOP
2598:
2599: BEGIN
2600:
2601: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2602:
2603: UPDATE MSC_TRADING_PARTNER_SITES
2604: SET
2605: PARTNER_ADDRESS= c_rec.PARTNER_ADDRESS,
2607: LOCATION= c_rec.LOCATION,
2608: LONGITUDE= c_rec.LONGITUDE,
2609: LATITUDE= c_rec.LATITUDE,
2610: DELETED_FLAG= MSC_UTIL.SYS_NO,
2611: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2612: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2613: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2614: WHERE PARTNER_TYPE= 3
2615: AND SR_TP_ID= c_rec.SR_TP_ID
2608: LONGITUDE= c_rec.LONGITUDE,
2609: LATITUDE= c_rec.LATITUDE,
2610: DELETED_FLAG= MSC_UTIL.SYS_NO,
2611: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2612: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2613: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2614: WHERE PARTNER_TYPE= 3
2615: AND SR_TP_ID= c_rec.SR_TP_ID
2616: AND SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID
2609: LATITUDE= c_rec.LATITUDE,
2610: DELETED_FLAG= MSC_UTIL.SYS_NO,
2611: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2612: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2613: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2614: WHERE PARTNER_TYPE= 3
2615: AND SR_TP_ID= c_rec.SR_TP_ID
2616: AND SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID
2617: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2617: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2618:
2619: END IF;
2620:
2621: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2622:
2623: INSERT INTO MSC_Trading_Partner_Sites
2624: ( PARTNER_ID,
2625: PARTNER_SITE_ID,
2650: c_rec.SR_INSTANCE_ID,
2651: c_rec.TP_SITE_CODE,
2652: c_rec.LOCATION,
2653: MSC_UTIL.SYS_NO,
2654: MSC_CL_COLLECTION.v_last_collection_id,
2655: MSC_CL_COLLECTION.v_current_date,
2656: MSC_CL_COLLECTION.v_current_user,
2657: MSC_CL_COLLECTION.v_current_date,
2658: MSC_CL_COLLECTION.v_current_user );
2651: c_rec.TP_SITE_CODE,
2652: c_rec.LOCATION,
2653: MSC_UTIL.SYS_NO,
2654: MSC_CL_COLLECTION.v_last_collection_id,
2655: MSC_CL_COLLECTION.v_current_date,
2656: MSC_CL_COLLECTION.v_current_user,
2657: MSC_CL_COLLECTION.v_current_date,
2658: MSC_CL_COLLECTION.v_current_user );
2659:
2652: c_rec.LOCATION,
2653: MSC_UTIL.SYS_NO,
2654: MSC_CL_COLLECTION.v_last_collection_id,
2655: MSC_CL_COLLECTION.v_current_date,
2656: MSC_CL_COLLECTION.v_current_user,
2657: MSC_CL_COLLECTION.v_current_date,
2658: MSC_CL_COLLECTION.v_current_user );
2659:
2660: END IF;
2653: MSC_UTIL.SYS_NO,
2654: MSC_CL_COLLECTION.v_last_collection_id,
2655: MSC_CL_COLLECTION.v_current_date,
2656: MSC_CL_COLLECTION.v_current_user,
2657: MSC_CL_COLLECTION.v_current_date,
2658: MSC_CL_COLLECTION.v_current_user );
2659:
2660: END IF;
2661:
2654: MSC_CL_COLLECTION.v_last_collection_id,
2655: MSC_CL_COLLECTION.v_current_date,
2656: MSC_CL_COLLECTION.v_current_user,
2657: MSC_CL_COLLECTION.v_current_date,
2658: MSC_CL_COLLECTION.v_current_user );
2659:
2660: END IF;
2661:
2662: EXCEPTION
2673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2674: RAISE;
2675:
2676: ELSE
2677: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2678:
2679: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2680: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2681: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2700: END LOOP;
2701:
2702: COMMIT;
2703:
2704: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2705:
2706: DELETE MSC_LOCATION_ASSOCIATIONS
2707: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2708:
2703:
2704: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2705:
2706: DELETE MSC_LOCATION_ASSOCIATIONS
2707: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2708:
2709: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
2710:
2711: END IF;
2705:
2706: DELETE MSC_LOCATION_ASSOCIATIONS
2707: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2708:
2709: -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
2710:
2711: END IF;
2712:
2713: FOR c_rec IN c3 LOOP
2713: FOR c_rec IN c3 LOOP
2714:
2715: BEGIN
2716:
2717: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2718:
2719: INSERT INTO MSC_LOCATION_ASSOCIATIONS
2720: ( LOCATION_ID,
2721: LOCATION_CODE,
2733: c_rec.PARTNER_ID,
2734: c_rec.PARTNER_SITE_ID,
2735: c_rec.organization_id,
2736: c_rec.SR_INSTANCE_ID,
2737: MSC_CL_COLLECTION.v_current_date,
2738: MSC_CL_COLLECTION.v_current_user,
2739: MSC_CL_COLLECTION.v_current_date,
2740: MSC_CL_COLLECTION.v_current_user );
2741:
2734: c_rec.PARTNER_SITE_ID,
2735: c_rec.organization_id,
2736: c_rec.SR_INSTANCE_ID,
2737: MSC_CL_COLLECTION.v_current_date,
2738: MSC_CL_COLLECTION.v_current_user,
2739: MSC_CL_COLLECTION.v_current_date,
2740: MSC_CL_COLLECTION.v_current_user );
2741:
2742: END IF;
2735: c_rec.organization_id,
2736: c_rec.SR_INSTANCE_ID,
2737: MSC_CL_COLLECTION.v_current_date,
2738: MSC_CL_COLLECTION.v_current_user,
2739: MSC_CL_COLLECTION.v_current_date,
2740: MSC_CL_COLLECTION.v_current_user );
2741:
2742: END IF;
2743:
2736: c_rec.SR_INSTANCE_ID,
2737: MSC_CL_COLLECTION.v_current_date,
2738: MSC_CL_COLLECTION.v_current_user,
2739: MSC_CL_COLLECTION.v_current_date,
2740: MSC_CL_COLLECTION.v_current_user );
2741:
2742: END IF;
2743:
2744: EXCEPTION
2756: RAISE;
2757:
2758: ELSE
2759:
2760: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2761:
2762: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2763: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2764: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2789: FOR c_rec IN c5 LOOP
2790:
2791: BEGIN
2792:
2793: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2794:
2795: INSERT INTO MSC_LOCATION_ASSOCIATIONS
2796: ( LOCATION_ID,
2797: LOCATION_CODE,
2809: c_rec.PARTNER_ID,
2810: c_rec.PARTNER_SITE_ID,
2811: c_rec.ORGANIZATION_ID,
2812: c_rec.SR_INSTANCE_ID,
2813: MSC_CL_COLLECTION.v_current_date,
2814: MSC_CL_COLLECTION.v_current_user,
2815: MSC_CL_COLLECTION.v_current_date,
2816: MSC_CL_COLLECTION.v_current_user);
2817:
2810: c_rec.PARTNER_SITE_ID,
2811: c_rec.ORGANIZATION_ID,
2812: c_rec.SR_INSTANCE_ID,
2813: MSC_CL_COLLECTION.v_current_date,
2814: MSC_CL_COLLECTION.v_current_user,
2815: MSC_CL_COLLECTION.v_current_date,
2816: MSC_CL_COLLECTION.v_current_user);
2817:
2818: END IF;
2811: c_rec.ORGANIZATION_ID,
2812: c_rec.SR_INSTANCE_ID,
2813: MSC_CL_COLLECTION.v_current_date,
2814: MSC_CL_COLLECTION.v_current_user,
2815: MSC_CL_COLLECTION.v_current_date,
2816: MSC_CL_COLLECTION.v_current_user);
2817:
2818: END IF;
2819:
2812: c_rec.SR_INSTANCE_ID,
2813: MSC_CL_COLLECTION.v_current_date,
2814: MSC_CL_COLLECTION.v_current_user,
2815: MSC_CL_COLLECTION.v_current_date,
2816: MSC_CL_COLLECTION.v_current_user);
2817:
2818: END IF;
2819:
2820: EXCEPTION
2830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2831: RAISE;
2832:
2833: ELSE
2834: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2835:
2836: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2837: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2838: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2859: COMMIT;
2860:
2861: -- Partner Contacts
2862:
2863: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2864:
2865: IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2866: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2867: END IF;
2861: -- Partner Contacts
2862:
2863: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2864:
2865: IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2866: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2867: END IF;
2868:
2869: FOR c_rec IN c4 LOOP
2862:
2863: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2864:
2865: IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2866: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2867: END IF;
2868:
2869: FOR c_rec IN c4 LOOP
2870:
2867: END IF;
2868:
2869: FOR c_rec IN c4 LOOP
2870:
2871: if(MSC_CL_COLLECTION.v_is_legacy_refresh) then
2872:
2873: if (c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES) then
2874:
2875: delete from MSC_PARTNER_CONTACTS where
2875: delete from MSC_PARTNER_CONTACTS where
2876: partner_id=c_rec.PARTNER_ID
2877: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2878: and partner_type=c_rec.PARTNER_TYPE
2879: and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
2880: and c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES;
2881: else
2882: UPDATE MSC_PARTNER_CONTACTS
2883: set Name=c_rec.NAME,
2884: DISPLAY_NAME =c_rec.DISPLAY_NAME,
2885: EMAIL = c_rec.EMAIL,
2886: FAX = c_rec.FAX,
2887: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2888: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2889: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2890: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2891: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2892: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2885: EMAIL = c_rec.EMAIL,
2886: FAX = c_rec.FAX,
2887: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2888: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2889: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2890: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2891: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2892: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2893: where
2886: FAX = c_rec.FAX,
2887: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2888: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2889: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2890: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2891: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2892: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2893: where
2894: partner_id=c_rec.PARTNER_ID
2887: ENABLED_FLAG =c_rec.ENABLED_FLAG,
2888: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2889: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2890: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2891: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2892: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2893: where
2894: partner_id=c_rec.PARTNER_ID
2895: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2888: REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2889: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2890: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2891: CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2892: CREATED_BY =MSC_CL_COLLECTION.v_current_user
2893: where
2894: partner_id=c_rec.PARTNER_ID
2895: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2896: and partner_type=c_rec.PARTNER_TYPE
2893: where
2894: partner_id=c_rec.PARTNER_ID
2895: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2896: and partner_type=c_rec.PARTNER_TYPE
2897: and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id;
2898: END IF ;
2899:
2900: END IF ;
2901:
2899:
2900: END IF ;
2901:
2902:
2903: IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh OR (SQL%NOTFOUND and c_rec.DELETED_FLAG =MSC_UTIL.SYS_NO)
2904: THEN
2905: IF lv_old_partner_id <> NVL(c_rec.partner_id,0) OR
2906: lv_old_partner_site_id <> NVL(c_rec.partner_site_id,0) OR
2907: lv_old_partner_type <> c_rec.partner_type THEN
2931: c_rec.DISPLAY_NAME,
2932: c_rec.EMAIL,
2933: c_rec.FAX,
2934: c_rec.ENABLED_FLAG,
2935: MSC_CL_COLLECTION.v_instance_id,
2936: MSC_CL_COLLECTION.v_last_collection_id,
2937: MSC_CL_COLLECTION.v_current_date,
2938: MSC_CL_COLLECTION.v_current_user,
2939: MSC_CL_COLLECTION.v_current_date,
2932: c_rec.EMAIL,
2933: c_rec.FAX,
2934: c_rec.ENABLED_FLAG,
2935: MSC_CL_COLLECTION.v_instance_id,
2936: MSC_CL_COLLECTION.v_last_collection_id,
2937: MSC_CL_COLLECTION.v_current_date,
2938: MSC_CL_COLLECTION.v_current_user,
2939: MSC_CL_COLLECTION.v_current_date,
2940: MSC_CL_COLLECTION.v_current_user );
2933: c_rec.FAX,
2934: c_rec.ENABLED_FLAG,
2935: MSC_CL_COLLECTION.v_instance_id,
2936: MSC_CL_COLLECTION.v_last_collection_id,
2937: MSC_CL_COLLECTION.v_current_date,
2938: MSC_CL_COLLECTION.v_current_user,
2939: MSC_CL_COLLECTION.v_current_date,
2940: MSC_CL_COLLECTION.v_current_user );
2941:
2934: c_rec.ENABLED_FLAG,
2935: MSC_CL_COLLECTION.v_instance_id,
2936: MSC_CL_COLLECTION.v_last_collection_id,
2937: MSC_CL_COLLECTION.v_current_date,
2938: MSC_CL_COLLECTION.v_current_user,
2939: MSC_CL_COLLECTION.v_current_date,
2940: MSC_CL_COLLECTION.v_current_user );
2941:
2942: EXCEPTION
2935: MSC_CL_COLLECTION.v_instance_id,
2936: MSC_CL_COLLECTION.v_last_collection_id,
2937: MSC_CL_COLLECTION.v_current_date,
2938: MSC_CL_COLLECTION.v_current_user,
2939: MSC_CL_COLLECTION.v_current_date,
2940: MSC_CL_COLLECTION.v_current_user );
2941:
2942: EXCEPTION
2943: WHEN OTHERS THEN
2936: MSC_CL_COLLECTION.v_last_collection_id,
2937: MSC_CL_COLLECTION.v_current_date,
2938: MSC_CL_COLLECTION.v_current_user,
2939: MSC_CL_COLLECTION.v_current_date,
2940: MSC_CL_COLLECTION.v_current_user );
2941:
2942: EXCEPTION
2943: WHEN OTHERS THEN
2944: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2953: RAISE;
2954:
2955: ELSE
2956: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2957:
2958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2959: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2960: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
3025: msp.REPETITIVE_BUCKET_SIZE3,
3026: msp.REPETITIVE_ANCHOR_DATE,
3027: msp.SR_INSTANCE_ID
3028: FROM MSC_ST_PARAMETERS msp
3029: WHERE msp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3030:
3031: c_count NUMBER:= 0;
3032:
3033: BEGIN
3031: c_count NUMBER:= 0;
3032:
3033: BEGIN
3034:
3035: --IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3036:
3037: --DELETE FROM MSC_PARAMETERS
3038: -- WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3039: --END IF;
3034:
3035: --IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3036:
3037: --DELETE FROM MSC_PARAMETERS
3038: -- WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3039: --END IF;
3040:
3041: FOR c_rec IN c1 LOOP
3042:
3064: REPETITIVE_BUCKET_SIZE1= c_rec.REPETITIVE_BUCKET_SIZE1,
3065: REPETITIVE_BUCKET_SIZE2= c_rec.REPETITIVE_BUCKET_SIZE2,
3066: REPETITIVE_BUCKET_SIZE3= c_rec.REPETITIVE_BUCKET_SIZE3,
3067: REPETITIVE_ANCHOR_DATE= c_rec.REPETITIVE_ANCHOR_DATE,
3068: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3069: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3070: WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3071: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3072: /* Bug: 1993151 remove the collected flag from the update statement */
3065: REPETITIVE_BUCKET_SIZE2= c_rec.REPETITIVE_BUCKET_SIZE2,
3066: REPETITIVE_BUCKET_SIZE3= c_rec.REPETITIVE_BUCKET_SIZE3,
3067: REPETITIVE_ANCHOR_DATE= c_rec.REPETITIVE_ANCHOR_DATE,
3068: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3069: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3070: WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3071: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3072: /* Bug: 1993151 remove the collected flag from the update statement */
3073: -- AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3129: c_rec.REPETITIVE_ANCHOR_DATE,
3130: 1,
3131: MSC_UTIL.SYS_YES,
3132: c_rec.SR_INSTANCE_ID,
3133: MSC_CL_COLLECTION.v_current_date,
3134: MSC_CL_COLLECTION.v_current_user,
3135: MSC_CL_COLLECTION.v_current_date,
3136: MSC_CL_COLLECTION.v_current_user );
3137:
3130: 1,
3131: MSC_UTIL.SYS_YES,
3132: c_rec.SR_INSTANCE_ID,
3133: MSC_CL_COLLECTION.v_current_date,
3134: MSC_CL_COLLECTION.v_current_user,
3135: MSC_CL_COLLECTION.v_current_date,
3136: MSC_CL_COLLECTION.v_current_user );
3137:
3138: END IF;
3131: MSC_UTIL.SYS_YES,
3132: c_rec.SR_INSTANCE_ID,
3133: MSC_CL_COLLECTION.v_current_date,
3134: MSC_CL_COLLECTION.v_current_user,
3135: MSC_CL_COLLECTION.v_current_date,
3136: MSC_CL_COLLECTION.v_current_user );
3137:
3138: END IF;
3139:
3132: c_rec.SR_INSTANCE_ID,
3133: MSC_CL_COLLECTION.v_current_date,
3134: MSC_CL_COLLECTION.v_current_user,
3135: MSC_CL_COLLECTION.v_current_date,
3136: MSC_CL_COLLECTION.v_current_user );
3137:
3138: END IF;
3139:
3140: EXCEPTION
3153: RAISE;
3154:
3155: ELSE
3156:
3157: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3158:
3159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3160: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3161: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3165: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167: FND_MESSAGE.SET_TOKEN('VALUE',
3168: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3169: MSC_CL_COLLECTION.v_instance_id));
3170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3171:
3172: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3173: END IF;
3194: msuom.DISABLE_DATE,
3195: msuom.DESCRIPTION,
3196: msuom.SR_INSTANCE_ID
3197: from MSC_ST_UNITS_OF_MEASURE msuom
3198: where msuom.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3199: order by UNIT_OF_MEASURE; /* use order by to avoid dead locking */
3200:
3201: CURSOR c2 IS
3202: select
3215: MSC_ST_UOM_CLASS_CONVERSIONS msucc
3216: WHERE t1.SR_INVENTORY_ITEM_ID(+)= msucc.Inventory_Item_ID
3217: AND t1.sr_instance_id(+)= msucc.sr_instance_id
3218: AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msucc.Inventory_ITEM_ID,0 )= 0
3219: AND msucc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3220: ORDER BY
3221: 1,
3222: msucc.FROM_UNIT_OF_MEASURE,
3223: msucc.TO_UNIT_OF_MEASURE;
3237: MSC_ST_UOM_CONVERSIONS msuc
3238: WHERE t1.SR_INVENTORY_ITEM_ID(+)= msuc.Inventory_Item_ID
3239: AND t1.sr_instance_id(+)= msuc.sr_instance_id
3240: AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msuc.Inventory_ITEM_ID,0 )= 0
3241: AND msuc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3242: ORDER BY
3243: 4,1;
3244:
3245: c_count NUMBER:= 0;
3246:
3247: BEGIN
3248:
3249: /*
3250: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3251:
3252: DELETE FROM MSC_UNITS_OF_MEASURE
3253: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3254:
3249: /*
3250: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3251:
3252: DELETE FROM MSC_UNITS_OF_MEASURE
3253: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3254:
3255: END IF;
3256: */
3257:
3267: muom.BASE_UOM_FLAG= c_rec.BASE_UOM_FLAG,
3268: muom.DISABLE_DATE= c_rec.DISABLE_DATE,
3269: muom.DESCRIPTION= c_rec.DESCRIPTION,
3270: muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3271: muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3272: muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3273: muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3274: WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3275:
3268: muom.DISABLE_DATE= c_rec.DISABLE_DATE,
3269: muom.DESCRIPTION= c_rec.DESCRIPTION,
3270: muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3271: muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3272: muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3273: muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3274: WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3275:
3276: IF SQL%NOTFOUND THEN
3269: muom.DESCRIPTION= c_rec.DESCRIPTION,
3270: muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3271: muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3272: muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3273: muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3274: WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3275:
3276: IF SQL%NOTFOUND THEN
3277:
3295: c_rec.BASE_UOM_FLAG,
3296: c_rec.DISABLE_DATE,
3297: c_rec.DESCRIPTION,
3298: c_rec.SR_INSTANCE_ID,
3299: MSC_CL_COLLECTION.v_last_collection_id,
3300: MSC_CL_COLLECTION.v_current_date,
3301: MSC_CL_COLLECTION.v_current_user,
3302: MSC_CL_COLLECTION.v_current_date,
3303: MSC_CL_COLLECTION.v_current_user );
3296: c_rec.DISABLE_DATE,
3297: c_rec.DESCRIPTION,
3298: c_rec.SR_INSTANCE_ID,
3299: MSC_CL_COLLECTION.v_last_collection_id,
3300: MSC_CL_COLLECTION.v_current_date,
3301: MSC_CL_COLLECTION.v_current_user,
3302: MSC_CL_COLLECTION.v_current_date,
3303: MSC_CL_COLLECTION.v_current_user );
3304:
3297: c_rec.DESCRIPTION,
3298: c_rec.SR_INSTANCE_ID,
3299: MSC_CL_COLLECTION.v_last_collection_id,
3300: MSC_CL_COLLECTION.v_current_date,
3301: MSC_CL_COLLECTION.v_current_user,
3302: MSC_CL_COLLECTION.v_current_date,
3303: MSC_CL_COLLECTION.v_current_user );
3304:
3305: END IF;
3298: c_rec.SR_INSTANCE_ID,
3299: MSC_CL_COLLECTION.v_last_collection_id,
3300: MSC_CL_COLLECTION.v_current_date,
3301: MSC_CL_COLLECTION.v_current_user,
3302: MSC_CL_COLLECTION.v_current_date,
3303: MSC_CL_COLLECTION.v_current_user );
3304:
3305: END IF;
3306:
3299: MSC_CL_COLLECTION.v_last_collection_id,
3300: MSC_CL_COLLECTION.v_current_date,
3301: MSC_CL_COLLECTION.v_current_user,
3302: MSC_CL_COLLECTION.v_current_date,
3303: MSC_CL_COLLECTION.v_current_user );
3304:
3305: END IF;
3306:
3307: c_count:= c_count+1;
3305: END IF;
3306:
3307: c_count:= c_count+1;
3308:
3309: IF c_count> MSC_CL_COLLECTION.PBS THEN
3310: COMMIT;
3311: c_count:= 0;
3312: END IF;
3313:
3326: RAISE;
3327:
3328: ELSE
3329:
3330: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3331:
3332: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3333: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3334: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3354:
3355: COMMIT;
3356:
3357: /*
3358: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3359:
3360: DELETE FROM MSC_UOM_CLASS_CONVERSIONS
3361: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3362:
3357: /*
3358: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3359:
3360: DELETE FROM MSC_UOM_CLASS_CONVERSIONS
3361: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3362:
3363: COMMIT;
3364:
3365: END IF;
3378: mucc.TO_UOM_CLASS= c_rec.TO_UOM_CLASS,
3379: mucc.CONVERSION_RATE= c_rec.CONVERSION_RATE,
3380: mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3381: mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3382: mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3383: mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3384: mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3385: WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3386: AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3379: mucc.CONVERSION_RATE= c_rec.CONVERSION_RATE,
3380: mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3381: mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3382: mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3383: mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3384: mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3385: WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3386: AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3387: AND mucc.TO_UNIT_OF_MEASURE= c_rec.TO_UNIT_OF_MEASURE;
3380: mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3381: mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3382: mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3383: mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3384: mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3385: WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3386: AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3387: AND mucc.TO_UNIT_OF_MEASURE= c_rec.TO_UNIT_OF_MEASURE;
3388:
3414: c_rec.TO_UOM_CLASS,
3415: c_rec.CONVERSION_RATE,
3416: c_rec.DISABLE_DATE,
3417: c_rec.SR_INSTANCE_ID,
3418: MSC_CL_COLLECTION.v_last_collection_id,
3419: MSC_CL_COLLECTION.v_current_date,
3420: MSC_CL_COLLECTION.v_current_user,
3421: MSC_CL_COLLECTION.v_current_date,
3422: MSC_CL_COLLECTION.v_current_user );
3415: c_rec.CONVERSION_RATE,
3416: c_rec.DISABLE_DATE,
3417: c_rec.SR_INSTANCE_ID,
3418: MSC_CL_COLLECTION.v_last_collection_id,
3419: MSC_CL_COLLECTION.v_current_date,
3420: MSC_CL_COLLECTION.v_current_user,
3421: MSC_CL_COLLECTION.v_current_date,
3422: MSC_CL_COLLECTION.v_current_user );
3423:
3416: c_rec.DISABLE_DATE,
3417: c_rec.SR_INSTANCE_ID,
3418: MSC_CL_COLLECTION.v_last_collection_id,
3419: MSC_CL_COLLECTION.v_current_date,
3420: MSC_CL_COLLECTION.v_current_user,
3421: MSC_CL_COLLECTION.v_current_date,
3422: MSC_CL_COLLECTION.v_current_user );
3423:
3424: END IF;
3417: c_rec.SR_INSTANCE_ID,
3418: MSC_CL_COLLECTION.v_last_collection_id,
3419: MSC_CL_COLLECTION.v_current_date,
3420: MSC_CL_COLLECTION.v_current_user,
3421: MSC_CL_COLLECTION.v_current_date,
3422: MSC_CL_COLLECTION.v_current_user );
3423:
3424: END IF;
3425:
3418: MSC_CL_COLLECTION.v_last_collection_id,
3419: MSC_CL_COLLECTION.v_current_date,
3420: MSC_CL_COLLECTION.v_current_user,
3421: MSC_CL_COLLECTION.v_current_date,
3422: MSC_CL_COLLECTION.v_current_user );
3423:
3424: END IF;
3425:
3426: c_count:= c_count+1;
3424: END IF;
3425:
3426: c_count:= c_count+1;
3427:
3428: IF c_count> MSC_CL_COLLECTION.PBS THEN
3429: COMMIT;
3430: c_count:= 0;
3431: END IF;
3432:
3445: RAISE;
3446:
3447: ELSE
3448:
3449: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3450:
3451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3452: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3453: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3478:
3479: COMMIT;
3480:
3481: /*
3482: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3483:
3484: DELETE FROM MSC_UOM_CONVERSIONS
3485: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3486:
3481: /*
3482: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3483:
3484: DELETE FROM MSC_UOM_CONVERSIONS
3485: WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3486:
3487: COMMIT;
3488:
3489: END IF;
3506: CONVERSION_RATE= c_rec.CONVERSION_RATE,
3507: DEFAULT_CONVERSION_FLAGS= c_rec.DEFAULT_CONVERSION_FLAG,
3508: DISABLE_DATE= c_rec.DISABLE_DATE,
3509: SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3510: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3511: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3512: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3513: WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3514: AND muc.UOM_CODE = c_rec.UOM_CODE;
3507: DEFAULT_CONVERSION_FLAGS= c_rec.DEFAULT_CONVERSION_FLAG,
3508: DISABLE_DATE= c_rec.DISABLE_DATE,
3509: SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3510: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3511: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3512: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3513: WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3514: AND muc.UOM_CODE = c_rec.UOM_CODE;
3515:
3508: DISABLE_DATE= c_rec.DISABLE_DATE,
3509: SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3510: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3511: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3512: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3513: WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3514: AND muc.UOM_CODE = c_rec.UOM_CODE;
3515:
3516: IF SQL%NOTFOUND THEN
3537: c_rec.CONVERSION_RATE,
3538: c_rec.DEFAULT_CONVERSION_FLAG,
3539: c_rec.DISABLE_DATE,
3540: c_rec.SR_INSTANCE_ID,
3541: MSC_CL_COLLECTION.v_last_collection_id,
3542: MSC_CL_COLLECTION.v_current_date,
3543: MSC_CL_COLLECTION.v_current_user,
3544: MSC_CL_COLLECTION.v_current_date,
3545: MSC_CL_COLLECTION.v_current_user);
3538: c_rec.DEFAULT_CONVERSION_FLAG,
3539: c_rec.DISABLE_DATE,
3540: c_rec.SR_INSTANCE_ID,
3541: MSC_CL_COLLECTION.v_last_collection_id,
3542: MSC_CL_COLLECTION.v_current_date,
3543: MSC_CL_COLLECTION.v_current_user,
3544: MSC_CL_COLLECTION.v_current_date,
3545: MSC_CL_COLLECTION.v_current_user);
3546:
3539: c_rec.DISABLE_DATE,
3540: c_rec.SR_INSTANCE_ID,
3541: MSC_CL_COLLECTION.v_last_collection_id,
3542: MSC_CL_COLLECTION.v_current_date,
3543: MSC_CL_COLLECTION.v_current_user,
3544: MSC_CL_COLLECTION.v_current_date,
3545: MSC_CL_COLLECTION.v_current_user);
3546:
3547: END IF;
3540: c_rec.SR_INSTANCE_ID,
3541: MSC_CL_COLLECTION.v_last_collection_id,
3542: MSC_CL_COLLECTION.v_current_date,
3543: MSC_CL_COLLECTION.v_current_user,
3544: MSC_CL_COLLECTION.v_current_date,
3545: MSC_CL_COLLECTION.v_current_user);
3546:
3547: END IF;
3548:
3541: MSC_CL_COLLECTION.v_last_collection_id,
3542: MSC_CL_COLLECTION.v_current_date,
3543: MSC_CL_COLLECTION.v_current_user,
3544: MSC_CL_COLLECTION.v_current_date,
3545: MSC_CL_COLLECTION.v_current_user);
3546:
3547: END IF;
3548:
3549: c_count:= c_count+1;
3547: END IF;
3548:
3549: c_count:= c_count+1;
3550:
3551: IF c_count> MSC_CL_COLLECTION.PBS THEN
3552: COMMIT;
3553: c_count:= 0;
3554: END IF;
3555:
3568: RAISE;
3569:
3570: ELSE
3571:
3572: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3573:
3574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3575: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3576: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3624: from
3625: msc_asl_auth_details
3626: where
3627: plan_id = -1 and
3628: sr_instance_id = MSC_CL_COLLECTION.v_instance_id ;
3629:
3630:
3631:
3632:
3641: p.ORGANIZATION_ID = a.ORGANIZATION_ID and
3642: p.SR_INSTANCE_ID = a.SR_INSTANCE_ID and
3643: p.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and
3644: p.using_organization_id =a.using_organization_id and
3645: p.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
3646: p.plan_id = -1
3647: )
3648: and sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3649: and plan_id = -1 ;
3644: p.using_organization_id =a.using_organization_id and
3645: p.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
3646: p.plan_id = -1
3647: )
3648: and sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3649: and plan_id = -1 ;
3650: commit ;
3651:
3652:
3710: FROM MSC_TP_ID_LID mtil,
3711: MSC_ST_TRADING_PARTNERS mst
3712: WHERE mtil.SR_TP_ID= mst.SR_TP_ID
3713: AND mtil.SR_INSTANCE_ID= mst.SR_INSTANCE_ID
3714: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3715: AND mtil.Partner_Type= mst.Partner_Type
3716: AND mst.Partner_Type IN (1,2,4) --Vendor/Customer/Carrier
3717: /* SCE Change Starts */
3718: AND nvl(mst.company_id, -1) = mtil.sr_company_id
3737: FROM MSC_TP_SITE_ID_LID mtsil,
3738: MSC_ST_TRADING_PARTNER_SITES msts
3739: WHERE mtsil.SR_TP_SITE_ID= msts.SR_TP_SITE_ID
3740: AND mtsil.SR_INSTANCE_ID= msts.SR_INSTANCE_ID
3741: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3742: AND mtsil.Partner_Type= msts.Partner_Type
3743: AND msts.Partner_Type IN (1,2) --Vendor/Customer
3744: /* SCE Change Starts */
3745: AND nvl(msts.company_id, -1) = mtsil.sr_company_id
3754: WHERE NOT EXISTS ( select 1
3755: from MSC_TRADING_PARTNERS mtp
3756: where mtp.Partner_Name= mst.Partner_Name
3757: and mtp.Partner_Type= mst.Partner_Type)
3758: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3759: AND mst.Partner_type IN (1,2)
3760: ORDER BY mst.Partner_Type,
3761: mst.Partner_Name; -- using ORDER BY to avoid dead lock
3762: */
3762: */
3763:
3764: -- ==== New Customers and Suppliers ====
3765: Cursor c5 IS
3766: SELECT decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.company_id) company_id1,
3767: mst.partner_name partner_name,
3768: mst.partner_type partner_type
3769: from MSC_ST_TRADING_PARTNERS mst,
3770: MSC_COMPANIES MC
3767: mst.partner_name partner_name,
3768: mst.partner_type partner_type
3769: from MSC_ST_TRADING_PARTNERS mst,
3770: MSC_COMPANIES MC
3771: where nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3772: and mst.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3773: and mst.partner_type IN (1,2,4) --Vendor/Customer/Carrier
3774: /* SCE CHANGE STARTS */
3775: and nvl(mst.company_id , -1) = -1
3768: mst.partner_type partner_type
3769: from MSC_ST_TRADING_PARTNERS mst,
3770: MSC_COMPANIES MC
3771: where nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3772: and mst.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3773: and mst.partner_type IN (1,2,4) --Vendor/Customer/Carrier
3774: /* SCE CHANGE STARTS */
3775: and nvl(mst.company_id , -1) = -1
3776: /* SCE CHANGE ENDS */
3785: partner_name ;
3786:
3787: Cursor c5_tpname IS
3788: SELECT distinct mst.Partner_Name, mst.sr_tp_id, mst.sr_instance_id, mst.Partner_Type,
3789: decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, -1, mc.company_id) company_id1
3790: FROM MSC_ST_TRADING_PARTNERS mst,
3791: MSC_COMPANIES MC
3792: WHERE EXISTS ( select 1
3793: from MSC_TRADING_PARTNERS mtp
3793: from MSC_TRADING_PARTNERS mtp
3794: where mtp.sr_tp_id= mst.sr_tp_id
3795: and mtp.sr_instance_id= mst.sr_instance_id
3796: and mtp.Partner_Type= mst.Partner_Type
3797: and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3798: and mtp.Partner_Name <> mst.Partner_Name)
3799: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3800: AND mst.Partner_type IN (1,2)
3801: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3795: and mtp.sr_instance_id= mst.sr_instance_id
3796: and mtp.Partner_Type= mst.Partner_Type
3797: and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3798: and mtp.Partner_Name <> mst.Partner_Name)
3799: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3800: AND mst.Partner_type IN (1,2)
3801: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3802: AND nvl(mst.company_id , -1) = -1
3803: ORDER BY mst.Partner_Name; -- using ORDER BY to avoid dead lock
3797: and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3798: and mtp.Partner_Name <> mst.Partner_Name)
3799: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3800: AND mst.Partner_type IN (1,2)
3801: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3802: AND nvl(mst.company_id , -1) = -1
3803: ORDER BY mst.Partner_Name; -- using ORDER BY to avoid dead lock
3804:
3805:
3816:
3817: /* SCE CHANGE */
3818: AND nvl(msts.company_id, -1) = mtil.sr_company_id
3819:
3820: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3821: AND msts.Partner_Type=1
3822: AND mtil.Partner_type=1
3823: ORDER BY mtil.TP_ID,
3824: msts.TP_Site_Code; -- using ORDER BY to avoid dead lock
3838: and mtps.TP_Site_Code= msts.TP_Site_Code
3839: and mtps.Location= msts.Location
3840: and mtps.Partner_ID= mtil.TP_ID)
3841: AND msts.SR_Instance_ID= mtil.SR_INSTANCE_ID
3842: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3843: AND msts.SR_TP_ID= mtil.SR_TP_ID
3844: /* SCE CHANGE starts*/
3845: AND nvl(msts.company_id, -1) = mtil.sr_company_id
3846: /* SCE CHANGE ends*/
3867: and nvl(msts.company_id, -1) = mtsil.sr_company_id)
3868: AND msts.TP_Site_Code= mtp.TP_Site_Code
3869: AND msts.SR_TP_ID= mtil.SR_TP_ID
3870: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3871: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3872: /* SCE Change starts */
3873: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
3874: /* SCE changes ends */
3875: AND mtil.TP_ID= mtp.Partner_ID
3902: AND msts.TP_Site_Code= mtp.TP_Site_Code
3903: AND nvl(msts.Location, ' ')= nvl(mtp.Location, ' ')
3904: AND msts.SR_TP_ID= mtil.SR_TP_ID
3905: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3906: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3907: /* SCE Change stars*/
3908: /* Added sr_company_id for SCE purpose */
3909: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
3910: /* SCE Change stars*/
3938: and nvl( mst.company_id, -1) = nvl(mtil.sr_company_id, -1)
3939: and nvl( mst.company_id, -1) = -1)
3940: AND mst.Partner_NAME= mtp.Partner_NAME
3941: AND mst.Partner_Type= mtp.Partner_Type
3942: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3943: AND mst.Partner_Type IN ( 1, 2)
3944: /* SCE Change starts */
3945: -- Add join with msc_companies
3946: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3942: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3943: AND mst.Partner_Type IN ( 1, 2)
3944: /* SCE Change starts */
3945: -- Add join with msc_companies
3946: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3947: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID );
3948: -- AND nvl( mst.company_id, -1) = -1; -- commented for aerox
3949: /* SCE Change ends */
3950:
3943: AND mst.Partner_Type IN ( 1, 2)
3944: /* SCE Change starts */
3945: -- Add join with msc_companies
3946: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3947: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID );
3948: -- AND nvl( mst.company_id, -1) = -1; -- commented for aerox
3949: /* SCE Change ends */
3950:
3951:
3952: -- ============ Cursor for UPDATE MSC_TP_ID_LID SRP Changes ==================== --
3953: CURSOR c13 IS
3954: Select resource_type, sr_instance_id , partner_type,sr_tp_id
3955: From msc_st_trading_partners
3956: Where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3957: And partner_type=2;
3958:
3959: lv_control_flag NUMBER;
3960: lv_msc_tp_coll_window NUMBER;
3971: BEGIN
3972:
3973:
3974: GET_COLL_PARAM (pINSTANCE_ID);
3975: MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
3976: MSC_CL_COLLECTION.v_my_company_name := MSC_CL_SCE_COLLECTION.GET_MY_COMPANY;
3977:
3978:
3979: SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
3972:
3973:
3974: GET_COLL_PARAM (pINSTANCE_ID);
3975: MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
3976: MSC_CL_COLLECTION.v_my_company_name := MSC_CL_SCE_COLLECTION.GET_MY_COMPANY;
3977:
3978:
3979: SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
3980: INTO lv_control_flag
3986: WHEN OTHERS THEN
3987: lv_msc_tp_coll_window := 0;
3988: END;
3989:
3990: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS107) OR (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110) OR lv_msc_tp_coll_window IS NULL THEN
3991: lv_msc_tp_coll_window := 0;
3992: END IF;
3993:
3994: begin
4009: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4010: end;
4011:
4012: /* if complete refresh, regen the key mapping data */
4013: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4014:
4015: IF lv_control_flag = 2 THEN
4016: IF lv_msc_tp_coll_window = 0 THEN
4017: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4013: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4014:
4015: IF lv_control_flag = 2 THEN
4016: IF lv_msc_tp_coll_window = 0 THEN
4017: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4018: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4019: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
4020: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4021: END IF;
4014:
4015: IF lv_control_flag = 2 THEN
4016: IF lv_msc_tp_coll_window = 0 THEN
4017: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4018: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4019: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
4020: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4021: END IF;
4022:
4021: END IF;
4022:
4023: ELSE
4024: IF lv_msc_tp_coll_window = 0 THEN
4025: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4026: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4027: END IF;
4028:
4029: END IF;
4022:
4023: ELSE
4024: IF lv_msc_tp_coll_window = 0 THEN
4025: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4026: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4027: END IF;
4028:
4029: END IF;
4030: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4037: --In case of continuous and targetted collections, delete carrier records from MSC_TP_ID_LID
4038: --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4039: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4040:
4041: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4042: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4043: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4044: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4045: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4038: --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4039: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4040:
4041: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4042: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4043: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4044: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4045: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4046: END IF;
4039: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4040:
4041: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4042: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4043: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4044: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4045: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4046: END IF;
4047: END IF;
4045: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4046: END IF;
4047: END IF;
4048:
4049: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4050:
4051: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4052: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4053:
4047: END IF;
4048:
4049: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4050:
4051: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4052: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4053:
4054: -- Note now vendor or customer cannot be refreshed
4055: -- separately. If that functionality needs to be provided
4048:
4049: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4050:
4051: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4052: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4053:
4054: -- Note now vendor or customer cannot be refreshed
4055: -- separately. If that functionality needs to be provided
4056: -- in future then the the ID_LID tables will have to
4061: -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4062:
4063: IF lv_msc_tp_coll_window = 0 THEN
4064: IF lv_control_flag = 2 THEN
4065: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4066: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4067: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4068: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4069: ELSE
4062:
4063: IF lv_msc_tp_coll_window = 0 THEN
4064: IF lv_control_flag = 2 THEN
4065: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4066: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4067: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4068: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4069: ELSE
4070: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4066: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4067: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4068: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4069: ELSE
4070: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4071: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4072: END IF;
4073: END IF;
4074: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4067: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4068: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4069: ELSE
4070: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4071: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4072: END IF;
4073: END IF;
4074: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4075: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4079:
4080: /*************** PREPLACE CHANGE END *****************/
4081:
4082: --agmcont
4083: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4084:
4085: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4086: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4087:
4081:
4082: --agmcont
4083: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4084:
4085: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4086: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4087:
4088: -- Note now vendor or customer cannot be refreshed
4089: -- separately. If that functionality needs to be provided
4082: --agmcont
4083: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4084:
4085: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4086: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4087:
4088: -- Note now vendor or customer cannot be refreshed
4089: -- separately. If that functionality needs to be provided
4090: -- in future then the the ID_LID tables will have to
4094: -- data using the partner_type as a filter.
4095: -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4096: IF lv_msc_tp_coll_window = 0 THEN
4097: IF lv_control_flag = 2 THEN
4098: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4099: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4100: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4101: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4102: ELSE
4095: -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4096: IF lv_msc_tp_coll_window = 0 THEN
4097: IF lv_control_flag = 2 THEN
4098: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4099: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4100: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4101: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4102: ELSE
4103: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4099: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4100: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4101: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4102: ELSE
4103: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4104: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4105: END IF;
4106: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4107: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4100: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4101: DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4102: ELSE
4103: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4104: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4105: END IF;
4106: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4107: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4108: END IF;
4194: */
4195:
4196: WHEN OTHERS THEN
4197:
4198: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4199:
4200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4201: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4202: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4259: c_rec.Partner_Type,
4260: -1,
4261: -1,
4262: -1,
4263: MSC_CL_COLLECTION.v_last_collection_id,
4264: MSC_CL_COLLECTION.v_current_date,
4265: MSC_CL_COLLECTION.v_current_user,
4266: MSC_CL_COLLECTION.v_current_date,
4267: MSC_CL_COLLECTION.v_current_user );
4260: -1,
4261: -1,
4262: -1,
4263: MSC_CL_COLLECTION.v_last_collection_id,
4264: MSC_CL_COLLECTION.v_current_date,
4265: MSC_CL_COLLECTION.v_current_user,
4266: MSC_CL_COLLECTION.v_current_date,
4267: MSC_CL_COLLECTION.v_current_user );
4268:
4261: -1,
4262: -1,
4263: MSC_CL_COLLECTION.v_last_collection_id,
4264: MSC_CL_COLLECTION.v_current_date,
4265: MSC_CL_COLLECTION.v_current_user,
4266: MSC_CL_COLLECTION.v_current_date,
4267: MSC_CL_COLLECTION.v_current_user );
4268:
4269: EXCEPTION
4262: -1,
4263: MSC_CL_COLLECTION.v_last_collection_id,
4264: MSC_CL_COLLECTION.v_current_date,
4265: MSC_CL_COLLECTION.v_current_user,
4266: MSC_CL_COLLECTION.v_current_date,
4267: MSC_CL_COLLECTION.v_current_user );
4268:
4269: EXCEPTION
4270:
4263: MSC_CL_COLLECTION.v_last_collection_id,
4264: MSC_CL_COLLECTION.v_current_date,
4265: MSC_CL_COLLECTION.v_current_user,
4266: MSC_CL_COLLECTION.v_current_date,
4267: MSC_CL_COLLECTION.v_current_user );
4268:
4269: EXCEPTION
4270:
4271: WHEN DUP_VAL_ON_INDEX THEN
4298: COMMIT;
4299:
4300: lv_ins_records := 0;
4301: -- ==== populate msc_tp_id_lid with newly created Suppliers / Customers ====
4302: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4303:
4304: /* For 107 and 110 the vendor_id and vendor
4305: site id can be duplicate, therefore we use
4306: the cursors to handle such exceptions, but for 11i we can use a straight
4359: RAISE;
4360:
4361: ELSE
4362:
4363: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4364:
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4366: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4367: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4422: and nvl( mst.company_id, -1) = -1
4423: and mst.Partner_Type= mtil.Partner_Type)
4424: AND mst.Partner_NAME= mtp.Partner_NAME
4425: AND mst.Partner_Type= mtp.Partner_Type
4426: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4427: AND mst.Partner_Type IN ( 1, 2,4)
4428: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4429: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4430: and nvl( mst.company_id, -1) = -1;
4424: AND mst.Partner_NAME= mtp.Partner_NAME
4425: AND mst.Partner_Type= mtp.Partner_Type
4426: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4427: AND mst.Partner_Type IN ( 1, 2,4)
4428: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4429: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4430: and nvl( mst.company_id, -1) = -1;
4431: lv_ins_records := SQL%ROWCOUNT;
4432: END IF;
4425: AND mst.Partner_Type= mtp.Partner_Type
4426: AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4427: AND mst.Partner_Type IN ( 1, 2,4)
4428: AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4429: AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4430: and nvl( mst.company_id, -1) = -1;
4431: lv_ins_records := SQL%ROWCOUNT;
4432: END IF;
4433:
4447: set
4448: resource_type = c_rec.resource_type
4449: WHERE sr_tp_id= c_rec.sr_tp_id And
4450: partner_type=2 And
4451: sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
4452:
4453: END;
4454:
4455: END LOOP; --c13 crec loop
4480: c_rec.TP_ID,
4481: MSC_Trading_Partner_Sites_S.NEXTVAL,
4482: MSC_Trading_Partner_Sites_S.NEXTVAL,
4483: 1,
4484: MSC_CL_COLLECTION.v_instance_id,
4485: MSC_CL_COLLECTION.v_last_collection_id,
4486: MSC_CL_COLLECTION.v_current_date,
4487: MSC_CL_COLLECTION.v_current_user,
4488: MSC_CL_COLLECTION.v_current_date,
4481: MSC_Trading_Partner_Sites_S.NEXTVAL,
4482: MSC_Trading_Partner_Sites_S.NEXTVAL,
4483: 1,
4484: MSC_CL_COLLECTION.v_instance_id,
4485: MSC_CL_COLLECTION.v_last_collection_id,
4486: MSC_CL_COLLECTION.v_current_date,
4487: MSC_CL_COLLECTION.v_current_user,
4488: MSC_CL_COLLECTION.v_current_date,
4489: MSC_CL_COLLECTION.v_current_user);
4482: MSC_Trading_Partner_Sites_S.NEXTVAL,
4483: 1,
4484: MSC_CL_COLLECTION.v_instance_id,
4485: MSC_CL_COLLECTION.v_last_collection_id,
4486: MSC_CL_COLLECTION.v_current_date,
4487: MSC_CL_COLLECTION.v_current_user,
4488: MSC_CL_COLLECTION.v_current_date,
4489: MSC_CL_COLLECTION.v_current_user);
4490:
4483: 1,
4484: MSC_CL_COLLECTION.v_instance_id,
4485: MSC_CL_COLLECTION.v_last_collection_id,
4486: MSC_CL_COLLECTION.v_current_date,
4487: MSC_CL_COLLECTION.v_current_user,
4488: MSC_CL_COLLECTION.v_current_date,
4489: MSC_CL_COLLECTION.v_current_user);
4490:
4491: EXCEPTION
4484: MSC_CL_COLLECTION.v_instance_id,
4485: MSC_CL_COLLECTION.v_last_collection_id,
4486: MSC_CL_COLLECTION.v_current_date,
4487: MSC_CL_COLLECTION.v_current_user,
4488: MSC_CL_COLLECTION.v_current_date,
4489: MSC_CL_COLLECTION.v_current_user);
4490:
4491: EXCEPTION
4492:
4485: MSC_CL_COLLECTION.v_last_collection_id,
4486: MSC_CL_COLLECTION.v_current_date,
4487: MSC_CL_COLLECTION.v_current_user,
4488: MSC_CL_COLLECTION.v_current_date,
4489: MSC_CL_COLLECTION.v_current_user);
4490:
4491: EXCEPTION
4492:
4493: WHEN DUP_VAL_ON_INDEX THEN
4524:
4525: COMMIT;
4526: lv_ins_records := 0;
4527: -- ==== Populate msc_tp_site_id_lid with new Supplier Sites ====
4528: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4529:
4530: /* For 107 and 110 the vendor_id and vendor
4531: site id can be duplicate, therefore we use
4532: the cursors to handle such exceptions, but for 11i we can use a straight
4554: EXCEPTION
4555:
4556: WHEN DUP_VAL_ON_INDEX THEN
4557:
4558: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4559:
4560: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4561: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4562: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4644: and nvl(msts.company_id, -1) = mtsil.sr_company_id)
4645: AND msts.TP_Site_Code= mtp.TP_Site_Code
4646: AND msts.SR_TP_ID= mtil.SR_TP_ID
4647: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4648: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4649: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
4650: AND mtil.TP_ID= mtp.Partner_ID
4651: AND mtp.partner_type = mtil.partner_type
4652: AND mtil.Partner_Type= msts.partner_type
4689: c_rec.TP_ID,
4690: MSC_Trading_Partner_Sites_S.NEXTVAL,
4691: MSC_Trading_Partner_Sites_S.NEXTVAL,
4692: 2,
4693: MSC_CL_COLLECTION.v_instance_id,
4694: MSC_CL_COLLECTION.v_last_collection_id,
4695: MSC_CL_COLLECTION.v_current_date,
4696: MSC_CL_COLLECTION.v_current_user,
4697: MSC_CL_COLLECTION.v_current_date,
4690: MSC_Trading_Partner_Sites_S.NEXTVAL,
4691: MSC_Trading_Partner_Sites_S.NEXTVAL,
4692: 2,
4693: MSC_CL_COLLECTION.v_instance_id,
4694: MSC_CL_COLLECTION.v_last_collection_id,
4695: MSC_CL_COLLECTION.v_current_date,
4696: MSC_CL_COLLECTION.v_current_user,
4697: MSC_CL_COLLECTION.v_current_date,
4698: MSC_CL_COLLECTION.v_current_user );
4691: MSC_Trading_Partner_Sites_S.NEXTVAL,
4692: 2,
4693: MSC_CL_COLLECTION.v_instance_id,
4694: MSC_CL_COLLECTION.v_last_collection_id,
4695: MSC_CL_COLLECTION.v_current_date,
4696: MSC_CL_COLLECTION.v_current_user,
4697: MSC_CL_COLLECTION.v_current_date,
4698: MSC_CL_COLLECTION.v_current_user );
4699:
4692: 2,
4693: MSC_CL_COLLECTION.v_instance_id,
4694: MSC_CL_COLLECTION.v_last_collection_id,
4695: MSC_CL_COLLECTION.v_current_date,
4696: MSC_CL_COLLECTION.v_current_user,
4697: MSC_CL_COLLECTION.v_current_date,
4698: MSC_CL_COLLECTION.v_current_user );
4699:
4700: EXCEPTION
4693: MSC_CL_COLLECTION.v_instance_id,
4694: MSC_CL_COLLECTION.v_last_collection_id,
4695: MSC_CL_COLLECTION.v_current_date,
4696: MSC_CL_COLLECTION.v_current_user,
4697: MSC_CL_COLLECTION.v_current_date,
4698: MSC_CL_COLLECTION.v_current_user );
4699:
4700: EXCEPTION
4701:
4694: MSC_CL_COLLECTION.v_last_collection_id,
4695: MSC_CL_COLLECTION.v_current_date,
4696: MSC_CL_COLLECTION.v_current_user,
4697: MSC_CL_COLLECTION.v_current_date,
4698: MSC_CL_COLLECTION.v_current_user );
4699:
4700: EXCEPTION
4701:
4702: WHEN DUP_VAL_ON_INDEX THEN
4744:
4745: COMMIT;
4746:
4747: lv_ins_records := 0;
4748: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4749:
4750: /* For 107 and 110 the vendor_id and vendor
4751: site id can be duplicate, therefore we use
4752: the cursors to handle such exceptions, but for 11i we can use a straight
4775: EXCEPTION
4776:
4777: WHEN DUP_VAL_ON_INDEX THEN
4778:
4779: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4780:
4781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4782: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4783: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4868: AND msts.TP_Site_Code= mtp.TP_Site_Code
4869: AND msts.Location= mtp.Location
4870: AND msts.SR_TP_ID= mtil.SR_TP_ID
4871: AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4872: AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4873: AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
4874: AND mtil.TP_ID= mtp.Partner_ID
4875: AND mtp.partner_type = mtil.partner_type
4876: AND mtil.Partner_Type= msts.partner_type
4910: PROJECT_REFERENCE_ENABLED= c_rec.PROJECT_REFERENCE_ENABLED,
4911: PROJECT_CONTROL_LEVEL= c_rec.PROJECT_CONTROL_LEVEL,
4912: CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4913: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4914: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4915: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4916: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4917: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4918: WHERE mtp.Partner_ID= c_rec.TP_ID;
4911: PROJECT_CONTROL_LEVEL= c_rec.PROJECT_CONTROL_LEVEL,
4912: CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4913: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4914: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4915: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4916: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4917: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4918: WHERE mtp.Partner_ID= c_rec.TP_ID;
4919:
4912: CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4913: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4914: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4915: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4916: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4917: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4918: WHERE mtp.Partner_ID= c_rec.TP_ID;
4919:
4920: EXCEPTION
4913: CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4914: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4915: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4916: CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4917: CREATED_BY = MSC_CL_COLLECTION.v_current_user
4918: WHERE mtp.Partner_ID= c_rec.TP_ID;
4919:
4920: EXCEPTION
4921:
4950: RAISE;
4951:
4952: ELSE
4953:
4954: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4955:
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4957: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4997: mtps.STATE = c_rec.STATE,
4998: mtps.COUNTRY = c_rec.COUNTRY,
4999: mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5000: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5001: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5002: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5005: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
4999: mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5000: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5001: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5002: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5005: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5006: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5007: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5000: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5001: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5002: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5005: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5006: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5007: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5008:
5001: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5002: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5005: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5006: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5007: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5008:
5009: EXCEPTION
5002: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5005: mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5006: mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5007: WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5008:
5009: EXCEPTION
5010:
5035: RAISE;
5036:
5037: ELSE
5038:
5039: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5040:
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5042: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5043: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5093: BEGIN
5094:
5095: /* Initialize the global prec record variable */
5096:
5097: IF (MSC_CL_COLLECTION.v_prec_defined = FALSE) THEN
5098: SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5099: bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5100: kpi_targets_bis, mds, mps, oh, parameter, planners,
5101: projects, po, reservations, nra, safety_stock,
5112: ,suprep_sn_flag, trip,trip_sn_flag,ds_mode
5113: , sales_channel, fiscal_calendar,internal_repair,external_repair -- changed for bug 5909379 SRP addition
5114: ,payback_demand_supply
5115: ,currency_conversion,delivery_details
5116: INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
5117: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5113: , sales_channel, fiscal_calendar,internal_repair,external_repair -- changed for bug 5909379 SRP addition
5114: ,payback_demand_supply
5115: ,currency_conversion,delivery_details
5116: INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
5117: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5114: ,payback_demand_supply
5115: ,currency_conversion,delivery_details
5116: INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
5117: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5115: ,currency_conversion,delivery_details
5116: INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
5117: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5116: INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
5117: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5117: MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5118: MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5119: MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5120: MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5121: MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5122: MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5123: MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5124: MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5125: MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5126: MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5127: MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5128: MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5129: MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5130: MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5131: MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5132: MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5140: /* CP-ACK starts */
5133: MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5140: /* CP-ACK starts */
5141: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5134: MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5140: /* CP-ACK starts */
5141: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5142: /* CP-ACK ends */
5135: MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136: MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5140: /* CP-ACK starts */
5141: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5142: /* CP-ACK ends */
5143: /* CP-AUTO */
5137: MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138: MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139: MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5140: /* CP-ACK starts */
5141: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5142: /* CP-ACK ends */
5143: /* CP-AUTO */
5144: ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145: ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5140: /* CP-ACK starts */
5141: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5142: /* CP-ACK ends */
5143: /* CP-AUTO */
5144: ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145: ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5146: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5147: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148: ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5141: ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5142: /* CP-ACK ends */
5143: /* CP-AUTO */
5144: ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145: ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5146: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5147: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148: ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5149: FROM msc_coll_parameters
5142: /* CP-ACK ends */
5143: /* CP-AUTO */
5144: ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145: ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5146: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5147: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148: ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5149: FROM msc_coll_parameters
5150: WHERE instance_id = p_instance_id;
5143: /* CP-AUTO */
5144: ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145: ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5146: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5147: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148: ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5149: FROM msc_coll_parameters
5150: WHERE instance_id = p_instance_id;
5151: MSC_CL_COLLECTION.v_prec_defined := TRUE;
5144: ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145: ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5146: ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5147: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148: ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5149: FROM msc_coll_parameters
5150: WHERE instance_id = p_instance_id;
5151: MSC_CL_COLLECTION.v_prec_defined := TRUE;
5152: END IF;
5147: ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148: ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5149: FROM msc_coll_parameters
5150: WHERE instance_id = p_instance_id;
5151: MSC_CL_COLLECTION.v_prec_defined := TRUE;
5152: END IF;
5153:
5154: END GET_COLL_PARAM;
5155: