[Home] [Help]
25:
26: PROCEDURE LOAD_CATEGORY IS
27: BEGIN
28:
29: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
30:
31: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
32: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
33: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
27: BEGIN
28:
29: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
30:
31: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
32: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
33: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
34: v_sql_stmt:=
35: ' insert into MSC_ST_ITEM_CATEGORIES'
28:
29: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
30:
31: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
32: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
33: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
34: v_sql_stmt:=
35: ' insert into MSC_ST_ITEM_CATEGORIES'
36: ||'( INVENTORY_ITEM_ID,'
29: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
30:
31: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
32: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
33: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
34: v_sql_stmt:=
35: ' insert into MSC_ST_ITEM_CATEGORIES'
36: ||'( INVENTORY_ITEM_ID,'
37: ||' ORGANIZATION_ID,'
47: ||' x.CATEGORY_ID,'
48: ||' 1,'
49: ||' :v_refresh_id,'
50: ||' :v_instance_id'
51: ||' from MRP_AD_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
52: ||' WHERE x.RN> :v_lrn ';
53:
54: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
55:
50: ||' :v_instance_id'
51: ||' from MRP_AD_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
52: ||' WHERE x.RN> :v_lrn ';
53:
54: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
55:
56: COMMIT;
57: END IF; --- Incremental refresh
58:
56: COMMIT;
57: END IF; --- Incremental refresh
58:
59:
60: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
61: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_CATEGORIES_V';
62:
63: /* Bug 4365337 - don't need the diff. RNs since Rn1, RN3 and RN4 are anyway 0.
64: We need to check only for RN2 (both in 11i and rel 11.0)
57: END IF; --- Incremental refresh
58:
59:
60: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
61: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_CATEGORIES_V';
62:
63: /* Bug 4365337 - don't need the diff. RNs since Rn1, RN3 and RN4 are anyway 0.
64: We need to check only for RN2 (both in 11i and rel 11.0)
65: Hence commenting the entire v_union_sql. The v_sql_stmt itself can handle this
64: We need to check only for RN2 (both in 11i and rel 11.0)
65: Hence commenting the entire v_union_sql. The v_sql_stmt itself can handle this
66: */
67:
68: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
69: v_union_sql :=
70: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
71: ||' UNION '
72: ||' select'
66: */
67:
68: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
69: v_union_sql :=
70: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
71: ||' UNION '
72: ||' select'
73: ||' x.INVENTORY_ITEM_ID,'
74: ||' x.ORGANIZATION_ID,'
83: ||' x.END_DATE_ACTIVE- :v_dgmt,'
84: ||' 2,'
85: ||' :v_refresh_id,'
86: ||' :v_instance_id'
87: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
88: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
89: --||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
90: ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')';
91:
86: ||' :v_instance_id'
87: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
88: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
89: --||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
90: ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')';
91:
92: /*
93: ||' UNION '
94: ||' select'
105: ||' x.END_DATE_ACTIVE- :v_dgmt,'
106: ||' 2,'
107: ||' :v_refresh_id,'
108: ||' :v_instance_id'
109: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
110: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
111: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
112: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
113: ||' UNION '
108: ||' :v_instance_id'
109: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
110: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
111: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
112: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
113: ||' UNION '
114: ||' select'
115: ||' x.INVENTORY_ITEM_ID,'
116: ||' x.ORGANIZATION_ID,'
125: ||' x.END_DATE_ACTIVE- :v_dgmt,'
126: ||' 2,'
127: ||' :v_refresh_id,'
128: ||' :v_instance_id'
129: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
130: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
131: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
132: ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')' ;
133: */
128: ||' :v_instance_id'
129: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
130: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
131: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
132: ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')' ;
133: */
134: ELSE
135: /*
136: v_union_sql :=
133: */
134: ELSE
135: /*
136: v_union_sql :=
137: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
139: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
140: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
141: */
134: ELSE
135: /*
136: v_union_sql :=
137: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
139: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
140: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
141: */
142: v_union_sql := ' ';
135: /*
136: v_union_sql :=
137: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
139: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
140: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
141: */
142: v_union_sql := ' ';
143: END IF;
136: v_union_sql :=
137: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
139: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
140: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
141: */
142: v_union_sql := ' ';
143: END IF;
144:
173: ||' x.END_DATE_ACTIVE- :v_dgmt,'
174: ||' 2,'
175: ||' :v_refresh_id,'
176: ||' :v_instance_id'
177: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
178: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
179: -- bug 4365337 remove lang cond ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
180: --bug 4365337 remove v_union_sql and instead add cond. on RN2
181: || v_union_sql ;
178: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
179: -- bug 4365337 remove lang cond ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
180: --bug 4365337 remove v_union_sql and instead add cond. on RN2
181: || v_union_sql ;
182: --||' AND x.RN2 >'||MSC_CL_PULL.v_lrn;
183:
184: --bug 4365337 remove MSC_CL_PULL.v_lang bind parameters since the stmt. does not have it.
185: /* bug 4365337 - no need to check incremental or not since it is the same stmt.
186: Hence commenting out the foll. stmt*/
180: --bug 4365337 remove v_union_sql and instead add cond. on RN2
181: || v_union_sql ;
182: --||' AND x.RN2 >'||MSC_CL_PULL.v_lrn;
183:
184: --bug 4365337 remove MSC_CL_PULL.v_lang bind parameters since the stmt. does not have it.
185: /* bug 4365337 - no need to check incremental or not since it is the same stmt.
186: Hence commenting out the foll. stmt*/
187: /* Uncommenting check for incremental refresh */
188:
185: /* bug 4365337 - no need to check incremental or not since it is the same stmt.
186: Hence commenting out the foll. stmt*/
187: /* Uncommenting check for incremental refresh */
188:
189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
190:
191: EXECUTE IMMEDIATE v_sql_stmt
192: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
188:
189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
190:
191: EXECUTE IMMEDIATE v_sql_stmt
192: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
190:
191: EXECUTE IMMEDIATE v_sql_stmt
192: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
190:
191: EXECUTE IMMEDIATE v_sql_stmt
192: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: EXECUTE IMMEDIATE v_sql_stmt
192: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
201: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
201: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
202: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
201: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
202: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
203: */
196: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
201: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
202: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
203: */
204: ELSE
197: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
201: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
202: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
203: */
204: ELSE
205:
198: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
199: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
200: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
201: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
202: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
203: */
204: ELSE
205:
206:
204: ELSE
205:
206:
207: EXECUTE IMMEDIATE v_sql_stmt
208: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
209: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
210:
211: -- bug 4365337
212: END IF;
205:
206:
207: EXECUTE IMMEDIATE v_sql_stmt
208: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
209: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
210:
211: -- bug 4365337
212: END IF;
213: COMMIT;
211: -- bug 4365337
212: END IF;
213: COMMIT;
214:
215: MSC_CL_PULL.v_table_name:= 'MSC_ST_CATEGORY_SETS';
216: MSC_CL_PULL.v_view_name := 'MRP_AP_CATEGORY_SETS_V';
217:
218: v_sql_stmt:=
219: ' insert into MSC_ST_CATEGORY_SETS'
212: END IF;
213: COMMIT;
214:
215: MSC_CL_PULL.v_table_name:= 'MSC_ST_CATEGORY_SETS';
216: MSC_CL_PULL.v_view_name := 'MRP_AP_CATEGORY_SETS_V';
217:
218: v_sql_stmt:=
219: ' insert into MSC_ST_CATEGORY_SETS'
220: ||' ( SR_CATEGORY_SET_ID,'
233: ||' x.DEFAULT_FLAG,'
234: ||' 2,'
235: ||' :v_refresh_id,'
236: ||' :v_instance_id'
237: ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
238: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
239: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
240:
241: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang;
235: ||' :v_refresh_id,'
236: ||' :v_instance_id'
237: ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
238: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
239: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
240:
241: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang;
242:
243: COMMIT;
237: ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
238: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
239: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
240:
241: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang;
242:
243: COMMIT;
244:
245: END IF;
260: v_dblink_a2m VARCHAR2(128);
261:
262: cursor org IS
263: select /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */ organization_id org_id,
264: DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
265: p_worker_num, MSC_UTIL.SYS_YES,
266: MSC_UTIL.SYS_NO) yes_flag
267: from msc_instance_orgs
268: where sr_instance_id= MSC_CL_PULL.v_instance_id
264: DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
265: p_worker_num, MSC_UTIL.SYS_YES,
266: MSC_UTIL.SYS_NO) yes_flag
267: from msc_instance_orgs
268: where sr_instance_id= MSC_CL_PULL.v_instance_id
269: and enabled_flag= 1
270: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group))
271: order by
272: organization_id;
266: MSC_UTIL.SYS_NO) yes_flag
267: from msc_instance_orgs
268: where sr_instance_id= MSC_CL_PULL.v_instance_id
269: and enabled_flag= 1
270: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group))
271: order by
272: organization_id;
273:
274: lv_org_count NUMBER:=0;
274: lv_org_count NUMBER:=0;
275:
276: BEGIN
277:
278: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
279:
280: select count(mio.organization_id)
281: into lv_org_count
282: from msc_instance_orgs mio,
280: select count(mio.organization_id)
281: into lv_org_count
282: from msc_instance_orgs mio,
283: msc_coll_parameters mcp
284: where mio.sr_instance_id= MSC_CL_PULL.v_instance_id
285: and mcp.instance_id = MSC_CL_PULL.v_instance_id
286: and mio.enabled_flag= 1
287: and (( mcp.org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or
288: (mio.org_group=mcp.org_group));
281: into lv_org_count
282: from msc_instance_orgs mio,
283: msc_coll_parameters mcp
284: where mio.sr_instance_id= MSC_CL_PULL.v_instance_id
285: and mcp.instance_id = MSC_CL_PULL.v_instance_id
286: and mio.enabled_flag= 1
287: and (( mcp.org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or
288: (mio.org_group=mcp.org_group));
289:
291: lv_in_org_str := ' IN (select a.organization_id '
292: || ' from '
293: || ' ( select organization_id ,rownum seqnum '
294: || ' from msc_instance_orgs '
295: || ' where sr_instance_id= ' || MSC_CL_PULL.v_instance_id
296: || ' and enabled_flag= 1 '
297: || ' and (('''
298: || MSC_CL_PULL.v_org_group ||''' = '''
299: || MSC_UTIL.G_ALL_ORGANIZATIONS
294: || ' from msc_instance_orgs '
295: || ' where sr_instance_id= ' || MSC_CL_PULL.v_instance_id
296: || ' and enabled_flag= 1 '
297: || ' and (('''
298: || MSC_CL_PULL.v_org_group ||''' = '''
299: || MSC_UTIL.G_ALL_ORGANIZATIONS
300: ||''' ) or (org_group = '''|| MSC_CL_PULL.v_org_group
301: ||''' ))) a '
302: ||' where mod(seqnum,' || MSC_CL_PULL.TOTAL_IWN ||') = '
296: || ' and enabled_flag= 1 '
297: || ' and (('''
298: || MSC_CL_PULL.v_org_group ||''' = '''
299: || MSC_UTIL.G_ALL_ORGANIZATIONS
300: ||''' ) or (org_group = '''|| MSC_CL_PULL.v_org_group
301: ||''' ))) a '
302: ||' where mod(seqnum,' || MSC_CL_PULL.TOTAL_IWN ||') = '
303: || p_worker_num
304: || ')' ;
298: || MSC_CL_PULL.v_org_group ||''' = '''
299: || MSC_UTIL.G_ALL_ORGANIZATIONS
300: ||''' ) or (org_group = '''|| MSC_CL_PULL.v_org_group
301: ||''' ))) a '
302: ||' where mod(seqnum,' || MSC_CL_PULL.TOTAL_IWN ||') = '
303: || p_worker_num
304: || ')' ;
305:
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Item Org String : '||lv_in_org_str);
326: ELSE
327: RETURN;
328: END IF;
329: end if; -- For Huge Number of Org
330: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
331:
332: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
333: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
328: END IF;
329: end if; -- For Huge Number of Org
330: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
331:
332: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
333: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
335: else
336: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
329: end if; -- For Huge Number of Org
330: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
331:
332: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
333: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
335: else
336: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
337: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
330: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
331:
332: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
333: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
335: else
336: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
337: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
338: end if;
332: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
333: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
335: else
336: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
337: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
338: end if;
339:
340: BEGIN
333: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
335: else
336: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
337: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
338: end if;
339:
340: BEGIN
341:
338: end if;
339:
340: BEGIN
341:
342: IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
343:
344: v_temp_sql := 'NULL, NULL, NULL, NULL, NULL, x.DESCRIPTION, x.LIST_PRICE ,x.ITEM_NAME, '
345: ||'NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,'
346: ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL,NULL, NULL,NULL,'; /* ds change added null*/
344: v_temp_sql := 'NULL, NULL, NULL, NULL, NULL, x.DESCRIPTION, x.LIST_PRICE ,x.ITEM_NAME, '
345: ||'NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,'
346: ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL,NULL, NULL,NULL,'; /* ds change added null*/
347:
348: ELSIF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110 THEN
349:
350: v_temp_sql := 'x.REPLENISH_TO_ORDER_FLAG,x.PICK_COMPONENTS_FLAG ,NULL, NULL,NULL, '
351: ||' x.DESCRIPTION, x.LIST_PRICE , x.ITEM_NAME, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, '
352: ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL, NULL, NULL,NULL,'; /* ds change added null for eam_item_type*/
350: v_temp_sql := 'x.REPLENISH_TO_ORDER_FLAG,x.PICK_COMPONENTS_FLAG ,NULL, NULL,NULL, '
351: ||' x.DESCRIPTION, x.LIST_PRICE , x.ITEM_NAME, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, '
352: ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL, NULL, NULL,NULL,'; /* ds change added null for eam_item_type*/
353:
354: ELSIF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS115) THEN
355:
356: v_temp_sql := ' x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG ,';
357:
358: BEGIN
357:
358: BEGIN
359: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
360: v_sql_stmt := ' select uom_code '
361: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
362: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
363: ||' and base_uom_flag = ''Y'' ';
364:
365: execute immediate v_sql_stmt into lv_uom_code;
358: BEGIN
359: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
360: v_sql_stmt := ' select uom_code '
361: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
362: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
363: ||' and base_uom_flag = ''Y'' ';
364:
365: execute immediate v_sql_stmt into lv_uom_code;
366: v_temp_sql := v_temp_sql ||' x.CONV_FACTOR, ';
378: BEGIN
379: -- check if the base language is different than the installed lang in source instance
380: -- If the base installed_flag is same as userenv lang- then dont go to TL table
381: v_sql_stmt := ' select 1 '
382: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
383: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
384: ||' and installed_flag = ''B'' ';
385:
386: execute immediate v_sql_stmt into lv_base_lang_diff;
379: -- check if the base language is different than the installed lang in source instance
380: -- If the base installed_flag is same as userenv lang- then dont go to TL table
381: v_sql_stmt := ' select 1 '
382: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
383: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
384: ||' and installed_flag = ''B'' ';
385:
386: execute immediate v_sql_stmt into lv_base_lang_diff;
387:
402: END IF;
403:
404: -- If Profile does not contain a Price List then for discrete list_price is null here but in ODS Load
405: -- the nvl(list_price,item_cost) is performed, for process get the List Price.
406: IF MSC_CL_PULL.v_mrp_bis_price_list is null THEN
407: v_temp_sql := v_temp_sql ||' x.LIST_PRICE, ';
408: ELSE
409: v_temp_sql := v_temp_sql ||' x.MRP_BIS_LIST_PRICE, ';
410: END IF;
414: -- If the base installed_flag is same as userenv lang- then dont go to TL table
415:
416: select instance_code, DECODE(A2M_DBLINK, NULL, MSC_UTIL.NULL_DBLINK, A2M_DBLINK)
417: into lv_icode, v_dblink_a2m
418: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
419:
420: v_sql_stmt := ' select item_name_from_kfv '
421: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
422: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
417: into lv_icode, v_dblink_a2m
418: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
419:
420: v_sql_stmt := ' select item_name_from_kfv '
421: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
422: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
423: ||' AND INSTANCE_CODE= '''||lv_icode||''''
424: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
425:
418: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
419:
420: v_sql_stmt := ' select item_name_from_kfv '
421: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
422: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
423: ||' AND INSTANCE_CODE= '''||lv_icode||''''
424: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
425:
426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt - ' || v_sql_stmt);
446:
447:
448: /* # For Bug 5606037 SRP Changes NULL Colums to collect Item Attribute Data */
449:
450: ELSIF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN --# For Bug 5606037 SRP Changes
451:
452: v_temp_sql := ' x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG ,';
453:
454: BEGIN
453:
454: BEGIN
455: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
456: v_sql_stmt := ' select uom_code '
457: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
458: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
459: ||' and base_uom_flag = ''Y'' ';
460:
461: execute immediate v_sql_stmt into lv_uom_code;
454: BEGIN
455: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
456: v_sql_stmt := ' select uom_code '
457: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
458: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
459: ||' and base_uom_flag = ''Y'' ';
460:
461: execute immediate v_sql_stmt into lv_uom_code;
462: v_temp_sql := v_temp_sql ||' x.CONV_FACTOR, ';
474: BEGIN
475: -- check if the base language is different than the installed lang in source instance
476: -- If the base installed_flag is same as userenv lang- then dont go to TL table
477: v_sql_stmt := ' select 1 '
478: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
479: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
480: ||' and installed_flag = ''B'' ';
481:
482: execute immediate v_sql_stmt into lv_base_lang_diff;
475: -- check if the base language is different than the installed lang in source instance
476: -- If the base installed_flag is same as userenv lang- then dont go to TL table
477: v_sql_stmt := ' select 1 '
478: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
479: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
480: ||' and installed_flag = ''B'' ';
481:
482: execute immediate v_sql_stmt into lv_base_lang_diff;
483:
498: END IF;
499:
500: -- If Profile does not contain a Price List then for discrete list_price is null here but in ODS Load
501: -- the nvl(list_price,item_cost) is performed, for process get the List Price.
502: IF MSC_CL_PULL.v_mrp_bis_price_list is null THEN
503: v_temp_sql := v_temp_sql ||' x.LIST_PRICE, ';
504: ELSE
505: v_temp_sql := v_temp_sql ||' x.MRP_BIS_LIST_PRICE, ';
506: END IF;
510: -- If the base installed_flag is same as userenv lang- then dont go to TL table
511:
512: select instance_code, DECODE(A2M_DBLINK, NULL, MSC_UTIL.NULL_DBLINK, A2M_DBLINK)
513: into lv_icode, v_dblink_a2m
514: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
515:
516: v_sql_stmt := ' select item_name_from_kfv '
517: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
518: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
513: into lv_icode, v_dblink_a2m
514: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
515:
516: v_sql_stmt := ' select item_name_from_kfv '
517: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
518: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
519: ||' AND INSTANCE_CODE= '''||lv_icode||''''
520: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
521:
514: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
515:
516: v_sql_stmt := ' select item_name_from_kfv '
517: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
518: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
519: ||' AND INSTANCE_CODE= '''||lv_icode||''''
520: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
521:
522: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt - ' || v_sql_stmt);
545:
546: End;
547:
548:
549: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
550: lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
552: else
553: lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
546: End;
547:
548:
549: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
550: lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
552: else
553: lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
554: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
549: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
550: lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
552: else
553: lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
554: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
555: end if;
556:
557:
802: ||' WHERE x.ORGANIZATION_ID'||lv_in_org_str;
803:
804:
805:
806: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
807:
808: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
809: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
810: EXECUTE IMMEDIATE v_sql_stmt
807:
808: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
809: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
810: EXECUTE IMMEDIATE v_sql_stmt
811: USING MSC_CL_PULL.V_ICODE,
812: MSC_CL_PULL.v_mso_item_dmd_penalty,
813: MSC_CL_PULL.v_mso_item_cap_penalty,
814: MSC_CL_PULL.v_mrp_bis_av_discount,
815: MSC_CL_PULL.v_refresh_id,
808: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
809: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
810: EXECUTE IMMEDIATE v_sql_stmt
811: USING MSC_CL_PULL.V_ICODE,
812: MSC_CL_PULL.v_mso_item_dmd_penalty,
813: MSC_CL_PULL.v_mso_item_cap_penalty,
814: MSC_CL_PULL.v_mrp_bis_av_discount,
815: MSC_CL_PULL.v_refresh_id,
816: MSC_CL_PULL.v_instance_id;
809: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
810: EXECUTE IMMEDIATE v_sql_stmt
811: USING MSC_CL_PULL.V_ICODE,
812: MSC_CL_PULL.v_mso_item_dmd_penalty,
813: MSC_CL_PULL.v_mso_item_cap_penalty,
814: MSC_CL_PULL.v_mrp_bis_av_discount,
815: MSC_CL_PULL.v_refresh_id,
816: MSC_CL_PULL.v_instance_id;
817:
810: EXECUTE IMMEDIATE v_sql_stmt
811: USING MSC_CL_PULL.V_ICODE,
812: MSC_CL_PULL.v_mso_item_dmd_penalty,
813: MSC_CL_PULL.v_mso_item_cap_penalty,
814: MSC_CL_PULL.v_mrp_bis_av_discount,
815: MSC_CL_PULL.v_refresh_id,
816: MSC_CL_PULL.v_instance_id;
817:
818: ELSE -- net change
811: USING MSC_CL_PULL.V_ICODE,
812: MSC_CL_PULL.v_mso_item_dmd_penalty,
813: MSC_CL_PULL.v_mso_item_cap_penalty,
814: MSC_CL_PULL.v_mrp_bis_av_discount,
815: MSC_CL_PULL.v_refresh_id,
816: MSC_CL_PULL.v_instance_id;
817:
818: ELSE -- net change
819:
812: MSC_CL_PULL.v_mso_item_dmd_penalty,
813: MSC_CL_PULL.v_mso_item_cap_penalty,
814: MSC_CL_PULL.v_mrp_bis_av_discount,
815: MSC_CL_PULL.v_refresh_id,
816: MSC_CL_PULL.v_instance_id;
817:
818: ELSE -- net change
819:
820: v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
819:
820: v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
821:
822: EXECUTE IMMEDIATE v_sql_stmt
823: USING MSC_CL_PULL.V_ICODE,
824: MSC_CL_PULL.v_mso_item_dmd_penalty,
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
820: v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
821:
822: EXECUTE IMMEDIATE v_sql_stmt
823: USING MSC_CL_PULL.V_ICODE,
824: MSC_CL_PULL.v_mso_item_dmd_penalty,
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
828: MSC_CL_PULL.v_instance_id,
821:
822: EXECUTE IMMEDIATE v_sql_stmt
823: USING MSC_CL_PULL.V_ICODE,
824: MSC_CL_PULL.v_mso_item_dmd_penalty,
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
828: MSC_CL_PULL.v_instance_id,
829: MSC_CL_PULL.v_lrn;
822: EXECUTE IMMEDIATE v_sql_stmt
823: USING MSC_CL_PULL.V_ICODE,
824: MSC_CL_PULL.v_mso_item_dmd_penalty,
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
828: MSC_CL_PULL.v_instance_id,
829: MSC_CL_PULL.v_lrn;
830:
823: USING MSC_CL_PULL.V_ICODE,
824: MSC_CL_PULL.v_mso_item_dmd_penalty,
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
828: MSC_CL_PULL.v_instance_id,
829: MSC_CL_PULL.v_lrn;
830:
831: END IF;
824: MSC_CL_PULL.v_mso_item_dmd_penalty,
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
828: MSC_CL_PULL.v_instance_id,
829: MSC_CL_PULL.v_lrn;
830:
831: END IF;
832:
825: MSC_CL_PULL.v_mso_item_cap_penalty,
826: MSC_CL_PULL.v_mrp_bis_av_discount,
827: MSC_CL_PULL.v_refresh_id,
828: MSC_CL_PULL.v_instance_id,
829: MSC_CL_PULL.v_lrn;
830:
831: END IF;
832:
833: COMMIT;
831: END IF;
832:
833: COMMIT;
834:
835: END IF; -- MSC_CL_PULL.ITEM_ENABLED
836:
837: END LOAD_ITEM;
838:
839:
843: lv_last_asl_collection_date DATE ;
844: BEGIN
845:
846:
847: IF MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED=MSC_UTIL.ASL_YES_RETAIN_CP THEN
848:
849: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
850:
851: /*ASL */
845:
846:
847: IF MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED=MSC_UTIL.ASL_YES_RETAIN_CP THEN
848:
849: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
850:
851: /*ASL */
852: IF MSC_CL_PULL.v_lrnn = -1 THEN
853: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
848:
849: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
850:
851: /*ASL */
852: IF MSC_CL_PULL.v_lrnn = -1 THEN
853: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
854: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
855: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
856: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
849: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
850:
851: /*ASL */
852: IF MSC_CL_PULL.v_lrnn = -1 THEN
853: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
854: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
855: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
856: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
857: ||' From msc_instance_orgs '
850:
851: /*ASL */
852: IF MSC_CL_PULL.v_lrnn = -1 THEN
853: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
854: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
855: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
856: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
857: ||' From msc_instance_orgs '
858: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
851: /*ASL */
852: IF MSC_CL_PULL.v_lrnn = -1 THEN
853: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
854: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
855: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
856: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
857: ||' From msc_instance_orgs '
858: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
859: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
854: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
855: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
856: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
857: ||' From msc_instance_orgs '
858: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
859: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
860:
861: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
862:
869:
870: /* Added this to collect Item Price information for a supplier
871: from 11i/110 source */
872: Begin
873: Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS107,' NULL,', ' x.ITEM_PRICE,')
874: into v_temp_sql
875: from dual;
876: End;
877:
875: from dual;
876: End;
877:
878: /* Added this code for VMI changes */
879: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
880: v_temp_sql1 := ' x.ENABLE_VMI_FLAG, x.VMI_MIN_QTY, x.VMI_MAX_QTY, x.ENABLE_VMI_AUTO_REPLENISH_FLAG, x.VMI_REPLENISHMENT_APPROVAL,'
881: || ' x.REPLENISHMENT_METHOD,x.MIN_MINMAX_DAYS,x.MAX_MINMAX_DAYS,x.FORECAST_HORIZON,x.FIXED_ORDER_QUANTITY, ';
882:
883: ELSE
884: v_temp_sql1 := 'NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL,';
885: END IF;
886:
887:
888: IF MSC_CL_PULL.v_lrnn =-1 THEN -- complete refresh
889:
890: v_sql_stmt:=
891: ' insert into MSC_ST_ITEM_SUPPLIERS'
892: ||' ( INVENTORY_ITEM_ID,'
958: /* Get partner_item_name */
959: ||' x.PRIMARY_VENDOR_ITEM,'
960: /* SCE Change end */
961: ||' :v_instance_id'
962: ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
963: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
964:
965: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
966: MSC_CL_PULL.v_mso_sup_cap_penalty,
961: ||' :v_instance_id'
962: ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
963: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
964:
965: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
966: MSC_CL_PULL.v_mso_sup_cap_penalty,
967: MSC_CL_PULL.v_instance_id,
968: MSC_CL_PULL.v_refresh_id,
969: MSC_CL_PULL.v_instance_id;
962: ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
963: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
964:
965: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
966: MSC_CL_PULL.v_mso_sup_cap_penalty,
967: MSC_CL_PULL.v_instance_id,
968: MSC_CL_PULL.v_refresh_id,
969: MSC_CL_PULL.v_instance_id;
970:
963: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
964:
965: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
966: MSC_CL_PULL.v_mso_sup_cap_penalty,
967: MSC_CL_PULL.v_instance_id,
968: MSC_CL_PULL.v_refresh_id,
969: MSC_CL_PULL.v_instance_id;
970:
971: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
964:
965: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
966: MSC_CL_PULL.v_mso_sup_cap_penalty,
967: MSC_CL_PULL.v_instance_id,
968: MSC_CL_PULL.v_refresh_id,
969: MSC_CL_PULL.v_instance_id;
970:
971: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
972:
965: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
966: MSC_CL_PULL.v_mso_sup_cap_penalty,
967: MSC_CL_PULL.v_instance_id,
968: MSC_CL_PULL.v_refresh_id,
969: MSC_CL_PULL.v_instance_id;
970:
971: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
972:
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL net change ');
967: MSC_CL_PULL.v_instance_id,
968: MSC_CL_PULL.v_refresh_id,
969: MSC_CL_PULL.v_instance_id;
970:
971: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
972:
973: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL net change ');
974: v_sql_stmt:=
975: ' insert into MSC_ST_ITEM_SUPPLIERS'
1042: /* Get partner_item_name */
1043: ||' x.PRIMARY_VENDOR_ITEM,'
1044: /* SCE Change end */
1045: ||' :v_instance_id'
1046: ||' from MRP_AN_PO_GLOBAL_ASL_V '||MSC_CL_PULL.v_dblink||' x'
1047: ||' WHERE (x.USING_ORGANIZATION_ID = -1 or x.USING_ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str ||')'
1048: ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1049:
1050: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1049:
1050: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1051:
1052:
1053: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1054: MSC_CL_PULL.v_mso_sup_cap_penalty,
1055: MSC_CL_PULL.v_instance_id,
1056: MSC_CL_PULL.v_refresh_id,
1057: MSC_CL_PULL.v_instance_id,
1050: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1051:
1052:
1053: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1054: MSC_CL_PULL.v_mso_sup_cap_penalty,
1055: MSC_CL_PULL.v_instance_id,
1056: MSC_CL_PULL.v_refresh_id,
1057: MSC_CL_PULL.v_instance_id,
1058: lv_last_asl_collection_date,
1051:
1052:
1053: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1054: MSC_CL_PULL.v_mso_sup_cap_penalty,
1055: MSC_CL_PULL.v_instance_id,
1056: MSC_CL_PULL.v_refresh_id,
1057: MSC_CL_PULL.v_instance_id,
1058: lv_last_asl_collection_date,
1059: lv_last_asl_collection_date;
1052:
1053: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1054: MSC_CL_PULL.v_mso_sup_cap_penalty,
1055: MSC_CL_PULL.v_instance_id,
1056: MSC_CL_PULL.v_refresh_id,
1057: MSC_CL_PULL.v_instance_id,
1058: lv_last_asl_collection_date,
1059: lv_last_asl_collection_date;
1060:
1053: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1054: MSC_CL_PULL.v_mso_sup_cap_penalty,
1055: MSC_CL_PULL.v_instance_id,
1056: MSC_CL_PULL.v_refresh_id,
1057: MSC_CL_PULL.v_instance_id,
1058: lv_last_asl_collection_date,
1059: lv_last_asl_collection_date;
1060:
1061: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1070: only global ASLs are in this view and the local ASLs are in
1071: MRP_AP_PO_LOCAL_ASL_V. Hence adding another insert stmt. to insert local ASLs.
1072: */
1073:
1074: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 ) THEN
1075:
1076: IF MSC_CL_PULL.v_lrnn =-1 THEN -- COMPLETE REFRESH
1077: v_sql_stmt:=
1078: ' insert into MSC_ST_ITEM_SUPPLIERS'
1072: */
1073:
1074: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 ) THEN
1075:
1076: IF MSC_CL_PULL.v_lrnn =-1 THEN -- COMPLETE REFRESH
1077: v_sql_stmt:=
1078: ' insert into MSC_ST_ITEM_SUPPLIERS'
1079: ||' ( INVENTORY_ITEM_ID,'
1080: ||' ORGANIZATION_ID,'
1145: /* Get partner_item_name */
1146: ||' x.PRIMARY_VENDOR_ITEM,'
1147: /* SCE Change end */
1148: ||' :v_instance_id'
1149: ||' from MRP_AP_PO_LOCAL_ASL_V'||MSC_CL_PULL.v_dblink||' x'
1150: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1151:
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1153:
1150: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1151:
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1153:
1154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1155: MSC_CL_PULL.v_mso_sup_cap_penalty,
1156: MSC_CL_PULL.v_instance_id,
1157: MSC_CL_PULL.v_refresh_id,
1158: MSC_CL_PULL.v_instance_id;
1151:
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1153:
1154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1155: MSC_CL_PULL.v_mso_sup_cap_penalty,
1156: MSC_CL_PULL.v_instance_id,
1157: MSC_CL_PULL.v_refresh_id,
1158: MSC_CL_PULL.v_instance_id;
1159: COMMIT;
1152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1153:
1154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1155: MSC_CL_PULL.v_mso_sup_cap_penalty,
1156: MSC_CL_PULL.v_instance_id,
1157: MSC_CL_PULL.v_refresh_id,
1158: MSC_CL_PULL.v_instance_id;
1159: COMMIT;
1160: ELSE -- LOCAL ASL NET CHANGE
1153:
1154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1155: MSC_CL_PULL.v_mso_sup_cap_penalty,
1156: MSC_CL_PULL.v_instance_id,
1157: MSC_CL_PULL.v_refresh_id,
1158: MSC_CL_PULL.v_instance_id;
1159: COMMIT;
1160: ELSE -- LOCAL ASL NET CHANGE
1161: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL net change ');
1154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1155: MSC_CL_PULL.v_mso_sup_cap_penalty,
1156: MSC_CL_PULL.v_instance_id,
1157: MSC_CL_PULL.v_refresh_id,
1158: MSC_CL_PULL.v_instance_id;
1159: COMMIT;
1160: ELSE -- LOCAL ASL NET CHANGE
1161: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL net change ');
1162: v_sql_stmt:=
1230: /* Get partner_item_name */
1231: ||' x.PRIMARY_VENDOR_ITEM,'
1232: /* SCE Change end */
1233: ||' :v_instance_id'
1234: ||' from MRP_AN_PO_LOCAL_ASL_V '||MSC_CL_PULL.v_dblink||' x'
1235: ||' WHERE x.USING_ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str
1236: ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1237:
1238: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1236: ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1237:
1238: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1239:
1240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1241: MSC_CL_PULL.v_mso_sup_cap_penalty,
1242: MSC_CL_PULL.v_instance_id,
1243: MSC_CL_PULL.v_refresh_id,
1244: MSC_CL_PULL.v_instance_id,
1237:
1238: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1239:
1240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1241: MSC_CL_PULL.v_mso_sup_cap_penalty,
1242: MSC_CL_PULL.v_instance_id,
1243: MSC_CL_PULL.v_refresh_id,
1244: MSC_CL_PULL.v_instance_id,
1245: lv_last_asl_collection_date,
1238: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1239:
1240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1241: MSC_CL_PULL.v_mso_sup_cap_penalty,
1242: MSC_CL_PULL.v_instance_id,
1243: MSC_CL_PULL.v_refresh_id,
1244: MSC_CL_PULL.v_instance_id,
1245: lv_last_asl_collection_date,
1246: lv_last_asl_collection_date;
1239:
1240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1241: MSC_CL_PULL.v_mso_sup_cap_penalty,
1242: MSC_CL_PULL.v_instance_id,
1243: MSC_CL_PULL.v_refresh_id,
1244: MSC_CL_PULL.v_instance_id,
1245: lv_last_asl_collection_date,
1246: lv_last_asl_collection_date;
1247:
1240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1241: MSC_CL_PULL.v_mso_sup_cap_penalty,
1242: MSC_CL_PULL.v_instance_id,
1243: MSC_CL_PULL.v_refresh_id,
1244: MSC_CL_PULL.v_instance_id,
1245: lv_last_asl_collection_date,
1246: lv_last_asl_collection_date;
1247:
1248: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1247:
1248: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1249: COMMIT ;
1250: END IF ;
1251: END IF; ---- MSC_CL_PULL.v_apps_ver === 115
1252:
1253: END IF;
1254:
1255:
1252:
1253: END IF;
1254:
1255:
1256: IF (MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES_RETAIN_CP) AND
1257: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1258: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1259:
1260: --- LOAD NET_CHAGE for DELETE --------
1253: END IF;
1254:
1255:
1256: IF (MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES_RETAIN_CP) AND
1257: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1258: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1259:
1260: --- LOAD NET_CHAGE for DELETE --------
1261:
1254:
1255:
1256: IF (MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES_RETAIN_CP) AND
1257: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1258: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1259:
1260: --- LOAD NET_CHAGE for DELETE --------
1261:
1262: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1258: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1259:
1260: --- LOAD NET_CHAGE for DELETE --------
1261:
1262: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1263:
1264: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1265: MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1266:
1260: --- LOAD NET_CHAGE for DELETE --------
1261:
1262: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1263:
1264: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1265: MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1266:
1267: v_sql_stmt:=
1268: 'Insert into MSC_ST_SUPPLIER_CAPACITIES'
1261:
1262: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1263:
1264: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1265: MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1266:
1267: v_sql_stmt:=
1268: 'Insert into MSC_ST_SUPPLIER_CAPACITIES'
1269: ||' ( INVENTORY_ITEM_ID,'
1284: ||' x.FROM_DATE- :v_dgmt,'
1285: ||' 1,'
1286: ||' :v_refresh_id,'
1287: ||' :v_instance_id'
1288: ||' from MRP_AD_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1289: ||' WHERE x.RN> :v_lrn '
1290: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1291:
1292: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1288: ||' from MRP_AD_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1289: ||' WHERE x.RN> :v_lrn '
1290: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1291:
1292: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1293:
1294: COMMIT;
1295:
1296: END IF;
1294: COMMIT;
1295:
1296: END IF;
1297:
1298: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1299: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1300:
1301: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1302:
1295:
1296: END IF;
1297:
1298: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1299: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1300:
1301: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1302:
1303: v_union_sql :=
1297:
1298: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1299: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1300:
1301: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1302:
1303: v_union_sql :=
1304: ' AND ( x.RN1 > :v_lrn )'
1305: ||' UNION '
1314: ||' x.CAPACITY,'
1315: ||' 2,'
1316: ||' :v_refresh_id,'
1317: ||' :v_instance_id'
1318: ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1319: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1320: ||' AND (x.RN2 > :v_lrn)';
1321:
1322: ELSE
1350: ||' x.CAPACITY,'
1351: ||' 2,'
1352: ||' :v_refresh_id,'
1353: ||' :v_instance_id'
1354: ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1355: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1356: || v_union_sql;
1357:
1358:
1355: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1356: || v_union_sql;
1357:
1358:
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360:
1361: EXECUTE IMMEDIATE v_sql_stmt
1362: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1363: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1358:
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360:
1361: EXECUTE IMMEDIATE v_sql_stmt
1362: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1363: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1364:
1365: ELSE
1366: EXECUTE IMMEDIATE v_sql_stmt
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360:
1361: EXECUTE IMMEDIATE v_sql_stmt
1362: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1363: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1364:
1365: ELSE
1366: EXECUTE IMMEDIATE v_sql_stmt
1367: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1363: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1364:
1365: ELSE
1366: EXECUTE IMMEDIATE v_sql_stmt
1367: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1368: END IF;
1369:
1370:
1371: COMMIT;
1369:
1370:
1371: COMMIT;
1372:
1373: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1374:
1375: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1376: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1377:
1371: COMMIT;
1372:
1373: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1374:
1375: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1376: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1377:
1378: v_sql_stmt:=
1379: ' insert into MSC_ST_SUPPLIER_FLEX_FENCES'
1372:
1373: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1374:
1375: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1376: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1377:
1378: v_sql_stmt:=
1379: ' insert into MSC_ST_SUPPLIER_FLEX_FENCES'
1380: ||' ( INVENTORY_ITEM_ID,'
1397: ||' x.TOLERANCE_PERCENTAGE,'
1398: ||' 2,'
1399: ||' :v_refresh_id,'
1400: ||' :v_instance_id'
1401: ||' from MRP_AP_SUPPLIER_FLEX_FENCES_V'||MSC_CL_PULL.v_dblink||' x'
1402: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str ;
1403:
1404: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1405:
1400: ||' :v_instance_id'
1401: ||' from MRP_AP_SUPPLIER_FLEX_FENCES_V'||MSC_CL_PULL.v_dblink||' x'
1402: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str ;
1403:
1404: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1405:
1406: COMMIT;
1407:
1408: END IF; -- complete refresh
1406: COMMIT;
1407:
1408: END IF; -- complete refresh
1409:
1410: END IF; -- MSC_CL_PULL.v_apps_ver
1411:
1412: END LOAD_SUPPLIER_CAPACITY;
1413:
1414:
1416: PROCEDURE LOAD_ITEM_SUBSTITUTES IS
1417: v_condition varchar2(1000);
1418: BEGIN
1419:
1420: IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1421:
1422: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1423:
1424: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN --For Bug 5632379 SRP Changes
1418: BEGIN
1419:
1420: IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1421:
1422: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1423:
1424: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN --For Bug 5632379 SRP Changes
1425: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1426: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1420: IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1421:
1422: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1423:
1424: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN --For Bug 5632379 SRP Changes
1425: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1426: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1427: v_sql_stmt:=
1428: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1421:
1422: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1423:
1424: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN --For Bug 5632379 SRP Changes
1425: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1426: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1427: v_sql_stmt:=
1428: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1429: ||'( HIGHER_ITEM_ID,'
1422: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1423:
1424: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN --For Bug 5632379 SRP Changes
1425: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1426: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1427: v_sql_stmt:=
1428: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1429: ||'( HIGHER_ITEM_ID,'
1430: ||' LOWER_ITEM_ID,'
1448: ||' x.DISABLE_DATE,'
1449: ||' :v_instance_id,'
1450: ||' 2,'
1451: ||' x.ORGANIZATION_ID'
1452: ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1453: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1454:
1455: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1456:
1449: ||' :v_instance_id,'
1450: ||' 2,'
1451: ||' x.ORGANIZATION_ID'
1452: ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1453: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1454:
1455: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1456:
1457: COMMIT;
1451: ||' x.ORGANIZATION_ID'
1452: ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1453: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1454:
1455: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1456:
1457: COMMIT;
1458:
1459: END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1458:
1459: END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1460:
1461:
1462: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1463: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUBSTITUTES_V';
1464:
1465: v_sql_stmt:=
1466: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1459: END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1460:
1461:
1462: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1463: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUBSTITUTES_V';
1464:
1465: v_sql_stmt:=
1466: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1467: ||'( HIGHER_ITEM_ID,'
1490: ||' x.DISABLE_DATE,'
1491: ||' :v_instance_id,'
1492: ||' 2,'
1493: ||' x.ORGANIZATION_ID'
1494: ||' FROM MRP_AP_ITEM_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
1495: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1496: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1497:
1498:
1491: ||' :v_instance_id,'
1492: ||' 2,'
1493: ||' x.ORGANIZATION_ID'
1494: ||' FROM MRP_AP_ITEM_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
1495: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1496: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1497:
1498:
1499: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1495: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1496: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1497:
1498:
1499: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1500:
1501: COMMIT;
1502:
1503: --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1501: COMMIT;
1502:
1503: --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1504:
1505: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1506:
1507: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1508: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1509: v_condition:=null;
1503: --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1504:
1505: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1506:
1507: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1508: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1509: v_condition:=null;
1510:
1511: --For Bug 5702475 SRP Changes
1504:
1505: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1506:
1507: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1508: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1509: v_condition:=null;
1510:
1511: --For Bug 5702475 SRP Changes
1512: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
1508: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1509: v_condition:=null;
1510:
1511: --For Bug 5702475 SRP Changes
1512: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
1513: v_condition:= ' and x.relationship_type_id in (2,5,8,18 ) ';
1514: else
1515: v_condition:= ' and x.relationship_type_id in (2) ';
1516: end if;
1532: ||' x.RELATIONSHIP_TYPE_ID,'
1533: ||' :v_refresh_id,'
1534: ||' 1,'
1535: ||' :v_instance_id'
1536: ||' FROM MRP_AD_ITEM_RELATIONSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1537: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1538: || v_condition
1539: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1540:
1533: ||' :v_refresh_id,'
1534: ||' 1,'
1535: ||' :v_instance_id'
1536: ||' FROM MRP_AD_ITEM_RELATIONSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1537: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1538: || v_condition
1539: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1540:
1541:
1538: || v_condition
1539: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1540:
1541:
1542: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1543:
1544: COMMIT;
1545:
1546: END IF ; --INCREMENTAL REFRESH
1544: COMMIT;
1545:
1546: END IF ; --INCREMENTAL REFRESH
1547:
1548: END IF; -- MSC_CL_PULL.ITEM_SUBST_ENABLED
1549:
1550: END LOAD_ITEM_SUBSTITUTES;
1551:
1552:
1556: BEGIN
1557:
1558: DELETE from MSC_ST_SYSTEM_ITEMS st_item
1559: where st_item.SR_INVENTORY_ITEM_ID in ( -1000,-1001,-1002,-1003,-1004) and
1560: st_item.sr_instance_id = MSC_CL_PULL.v_instance_id and
1561: st_item.organization_id in
1562: ( select x.organization_id
1563: FROM msc_instance_orgs x
1564: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1560: st_item.sr_instance_id = MSC_CL_PULL.v_instance_id and
1561: st_item.organization_id in
1562: ( select x.organization_id
1563: FROM msc_instance_orgs x
1564: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1565: and x.enabled_flag= 1
1566: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1567: );
1568: commit;
1562: ( select x.organization_id
1563: FROM msc_instance_orgs x
1564: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1565: and x.enabled_flag= 1
1566: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1567: );
1568: commit;
1569:
1570: lv_item_name := NVL(FND_PROFILE.VALUE('MSC_EAM_NO_ACTIVITY_ITEM'),'No Activity Item');
1593: ORGANIZATION_CODE)
1594: SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1595: x.ORGANIZATION_ID ,
1596: -1000,
1597: MSC_CL_PULL.v_instance_id,
1598: 1, --LOT_CONTROL_CODE 1 no control, 2 full control
1599: 1, --ROUNDING_CONTROL_TYPE 1 round order qty, 2 no
1600: 2, --IN_SOURCE_PLAN
1601: 3, --MRP_PLANNING_CODE 3 mrp planning, 6 not planned,
1615: lv_item_name ,
1616: org.ORGANIZATION_CODE
1617: FROM msc_instance_orgs x,
1618: MTL_PARAMETERS org
1619: WHERE sr_instance_id= MSC_CL_PULL.v_instance_id
1620: and enabled_flag= 1
1621: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1622: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group));
1623:
1618: MTL_PARAMETERS org
1619: WHERE sr_instance_id= MSC_CL_PULL.v_instance_id
1620: and enabled_flag= 1
1621: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1622: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group));
1623:
1624: commit;
1625:
1626: lv_item_name := NVL(FND_PROFILE.VALUE('MSC_WIP_NONSTD_JOB_ITEM'),'Non Standard Job Item');
1649: ORGANIZATION_CODE)
1650: SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1651: x.ORGANIZATION_ID ,
1652: -1001,
1653: MSC_CL_PULL.v_instance_id,
1654: 1, --LOT_CONTROL_CODE
1655: 1, --ROUNDING_CONTROL_TYPE
1656: 2, --IN_SOURCE_PLAN
1657: 3, --MRP_PLANNING_CODE
1670: lv_item_name ,
1671: org.ORGANIZATION_CODE
1672: FROM msc_instance_orgs x,
1673: MTL_PARAMETERS org
1674: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1675: and x.enabled_flag= 1
1676: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1677: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group));
1678:
1673: MTL_PARAMETERS org
1674: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1675: and x.enabled_flag= 1
1676: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1677: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group));
1678:
1679: commit;
1680:
1681: /*inserting dummy items for aggregation */
1703: ||' ORGANIZATION_CODE) '
1704: ||' SELECT '
1705: ||' x.ORGANIZATION_ID , '
1706: ||' -1003, '
1707: || MSC_CL_PULL.v_instance_id||', '
1708: ||' 1, '
1709: ||' 1, '
1710: ||' 2, '
1711: ||' 3, '
1723: ||' 2, '
1724: ||' ''CMRO UMP'' , '
1725: ||' :V_ICODE||org.ORGANIZATION_CODE'
1726: ||' FROM msc_instance_orgs x, '
1727: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1728: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1729: ||' and x.enabled_flag= 1 '
1730: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1731: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1724: ||' ''CMRO UMP'' , '
1725: ||' :V_ICODE||org.ORGANIZATION_CODE'
1726: ||' FROM msc_instance_orgs x, '
1727: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1728: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1729: ||' and x.enabled_flag= 1 '
1730: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1731: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1732:
1727: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1728: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1729: ||' and x.enabled_flag= 1 '
1730: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1731: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1732:
1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||lv_ins_stmt);
1734:
1735: EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_PULL.V_ICODE;
1731: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1732:
1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||lv_ins_stmt);
1734:
1735: EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_PULL.V_ICODE;
1736: commit;
1737:
1738: lv_ins_stmt := ' insert into MSC_ST_SYSTEM_ITEMS ( '
1739: ||' ORGANIZATION_ID, '
1759: ||' ORGANIZATION_CODE) '
1760: ||' SELECT '
1761: ||' x.ORGANIZATION_ID , '
1762: ||' -1002, '
1763: || MSC_CL_PULL.v_instance_id||', '
1764: ||' 1, '
1765: ||' 1, '
1766: ||' 2, '
1767: ||' 3, '
1779: ||' 2, '
1780: ||' ''EAM Forecast'' , '
1781: ||' :V_ICODE||org.ORGANIZATION_CODE'
1782: ||' FROM msc_instance_orgs x, '
1783: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1784: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1785: ||' and x.enabled_flag= 1 '
1786: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1787: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1780: ||' ''EAM Forecast'' , '
1781: ||' :V_ICODE||org.ORGANIZATION_CODE'
1782: ||' FROM msc_instance_orgs x, '
1783: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1784: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1785: ||' and x.enabled_flag= 1 '
1786: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1787: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1788:
1783: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1784: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1785: ||' and x.enabled_flag= 1 '
1786: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1787: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1788:
1789: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||lv_ins_stmt);
1790: EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_PULL.V_ICODE;
1791: commit;
1786: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1787: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1788:
1789: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||lv_ins_stmt);
1790: EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_PULL.V_ICODE;
1791: commit;
1792:
1793: lv_ins_stmt := ' insert into MSC_ST_SYSTEM_ITEMS ( '
1794: ||' ORGANIZATION_ID, '
1814: ||' ORGANIZATION_CODE) '
1815: ||' SELECT '
1816: ||' x.ORGANIZATION_ID , '
1817: ||' -1004, '
1818: || MSC_CL_PULL.v_instance_id||', '
1819: ||' 1, '
1820: ||' 1, '
1821: ||' 2, '
1822: ||' 3, '
1834: ||' 2, '
1835: ||' ''Maintenance'' , '
1836: ||' :V_ICODE||org.ORGANIZATION_CODE'
1837: ||' FROM msc_instance_orgs x, '
1838: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1839: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1840: ||' and x.enabled_flag= 1 '
1841: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1842: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1835: ||' ''Maintenance'' , '
1836: ||' :V_ICODE||org.ORGANIZATION_CODE'
1837: ||' FROM msc_instance_orgs x, '
1838: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1839: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1840: ||' and x.enabled_flag= 1 '
1841: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1842: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1843:
1838: ||' MTL_PARAMETERS'||MSC_CL_PULL.v_dblink||' org '
1839: ||' WHERE x.sr_instance_id= '|| MSC_CL_PULL.v_instance_id
1840: ||' and x.enabled_flag= 1 '
1841: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1842: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1843:
1844: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||lv_ins_stmt);
1845: EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_PULL.V_ICODE ;
1846: commit;
1841: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID '
1842: ||' and (('''||MSC_CL_PULL.v_org_group||''' = '''||MSC_UTIL.G_ALL_ORGANIZATIONS ||''') or (x.org_group = '''|| MSC_CL_PULL.v_org_group ||'''))';
1843:
1844: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt ' ||lv_ins_stmt);
1845: EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_PULL.V_ICODE ;
1846: commit;
1847:
1848: END INSERT_DUMMY_ITEMS;
1849:
1864: lv_deleted_flag PLS_INTEGER := 2;
1865: BEGIN
1866:
1867: delete from MSC_ST_CATEGORY_SETS st_item_category_set
1868: where st_item_category_set.sr_instance_id = MSC_CL_PULL.v_instance_id
1869: and st_item_category_set.SR_CATEGORY_SET_ID = -5000;
1870: commit;
1871: lv_category_set_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY_SET'),'Unspecified Items');
1872: BEGIN
1869: and st_item_category_set.SR_CATEGORY_SET_ID = -5000;
1870: commit;
1871: lv_category_set_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY_SET'),'Unspecified Items');
1872: BEGIN
1873: lv_table_name:= 'MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink;
1874: lv_sql_stmt:= 'SELECT category_set_id FROM '||lv_table_name
1875: ||' WHERE category_set_name = '''||lv_category_set_name
1876: ||''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''';
1877:
1872: BEGIN
1873: lv_table_name:= 'MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink;
1874: lv_sql_stmt:= 'SELECT category_set_id FROM '||lv_table_name
1875: ||' WHERE category_set_name = '''||lv_category_set_name
1876: ||''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''';
1877:
1878: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug1 - ' || lv_sql_stmt);
1879:
1880: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_category_set_id ;
1899: lv_category_set_description ,
1900: 2,
1901: 2,
1902: 2,
1903: MSC_CL_PULL.v_instance_id ) ;
1904: commit ;
1905: END IF ;
1906:
1907: lv_table_name:= 'MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1903: MSC_CL_PULL.v_instance_id ) ;
1904: commit ;
1905: END IF ;
1906:
1907: lv_table_name:= 'MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1908:
1909:
1910: DELETE from MSC_ST_ITEM_CATEGORIES st_item_category
1911: where st_item_category.INVENTORY_ITEM_ID in ( -1000,-1001,-1002,-1003,-1004) and
1910: DELETE from MSC_ST_ITEM_CATEGORIES st_item_category
1911: where st_item_category.INVENTORY_ITEM_ID in ( -1000,-1001,-1002,-1003,-1004) and
1912: st_item_category.SR_CATEGORY_SET_ID in (-5000)and
1913: st_item_category.SR_CATEGORY_ID in (-5001) and
1914: st_item_category.sr_instance_id = MSC_CL_PULL.v_instance_id and
1915: st_item_category.organization_id in
1916: ( select x.organization_id
1917: FROM msc_instance_orgs x
1918: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1914: st_item_category.sr_instance_id = MSC_CL_PULL.v_instance_id and
1915: st_item_category.organization_id in
1916: ( select x.organization_id
1917: FROM msc_instance_orgs x
1918: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1919: and x.enabled_flag= 1
1920: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1921: );
1922: commit;
1916: ( select x.organization_id
1917: FROM msc_instance_orgs x
1918: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1919: and x.enabled_flag= 1
1920: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1921: );
1922: commit;
1923:
1924: lv_category_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY'),'NEW.MISC');
1922: commit;
1923:
1924: lv_category_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY'),'NEW.MISC');
1925: BEGIN
1926: lv_table_name:= 'MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink;
1927: lv_sql_stmt:= 'SELECT category_id,description FROM ' ||lv_table_name
1928: || ' WHERE category_name = '''||lv_category_name
1929: || ''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''and rownum=1';
1930:
1925: BEGIN
1926: lv_table_name:= 'MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink;
1927: lv_sql_stmt:= 'SELECT category_id,description FROM ' ||lv_table_name
1928: || ' WHERE category_name = '''||lv_category_name
1929: || ''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''and rownum=1';
1930:
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug2 - ' || lv_sql_stmt);
1932:
1933: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_category_id,lv_category_description ;
1935: WHEN NO_DATA_FOUND THEN
1936: lv_category_id := -5001;
1937: END ;
1938:
1939: lv_table_name :='MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1940: lv_sql_stmt := 'insert into MSC_ST_ITEM_CATEGORIES ('
1941: ||'INVENTORY_ITEM_ID,'
1942: ||'ORGANIZATION_ID,'
1943: ||'SR_CATEGORY_SET_ID,'
1963: ||''''|| lv_enabled_flag||'''' ||','
1964: || 'null,'
1965: || 'null,'
1966: || lv_deleted_flag ||','
1967: || MSC_CL_PULL.v_instance_id
1968: || ' FROM msc_instance_orgs x,'
1969: || lv_table_name ||' org '
1970: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1971: ||' and x.enabled_flag= 1'
1966: || lv_deleted_flag ||','
1967: || MSC_CL_PULL.v_instance_id
1968: || ' FROM msc_instance_orgs x,'
1969: || lv_table_name ||' org '
1970: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1971: ||' and x.enabled_flag= 1'
1972: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID'
1973: ||' and (('''||MSC_CL_PULL.v_org_group ||'''='''|| MSC_UTIL.G_ALL_ORGANIZATIONS||''' ) or (org_group ='''|| MSC_CL_PULL.v_org_group||'''))';
1974:
1969: || lv_table_name ||' org '
1970: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1971: ||' and x.enabled_flag= 1'
1972: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID'
1973: ||' and (('''||MSC_CL_PULL.v_org_group ||'''='''|| MSC_UTIL.G_ALL_ORGANIZATIONS||''' ) or (org_group ='''|| MSC_CL_PULL.v_org_group||'''))';
1974:
1975:
1976:
1977: for lv_item_id in -1004..-1000 loop
1985: and SR_CATEGORY_ID = lv_category_id
1986: and organization_id in
1987: ( select x.organization_id
1988: FROM msc_instance_orgs x
1989: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1990: and x.enabled_flag= 1
1991: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1992: );
1993:
1987: ( select x.organization_id
1988: FROM msc_instance_orgs x
1989: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1990: and x.enabled_flag= 1
1991: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1992: );
1993:
1994: commit;
1995:
1999:
2000: /*added for bug:4765403*/
2001: PROCEDURE LOAD_ABC_CLASSES IS
2002: BEGIN
2003: MSC_CL_PULL.v_table_name:= 'MSC_ST_ABC_CLASSES';
2004: MSC_CL_PULL.v_view_name:= 'MRP_AP_ABC_CLASSES';
2005:
2006: v_sql_stmt:=
2007: 'insert into MSC_ST_ABC_CLASSES'
2000: /*added for bug:4765403*/
2001: PROCEDURE LOAD_ABC_CLASSES IS
2002: BEGIN
2003: MSC_CL_PULL.v_table_name:= 'MSC_ST_ABC_CLASSES';
2004: MSC_CL_PULL.v_view_name:= 'MRP_AP_ABC_CLASSES';
2005:
2006: v_sql_stmt:=
2007: 'insert into MSC_ST_ABC_CLASSES'
2008: ||' (ABC_CLASS_ID,'
2015: ||' x.ABC_CLASS_NAME,'
2016: ||' x.ORGANIZATION_ID,'
2017: ||' x.ASSIGNMENT_GROUP_ID,'
2018: ||' :v_instance_id'
2019: ||' from MRP_AP_ABC_CLASSES_V'||MSC_CL_PULL.v_dblink||' x'
2020: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2021:
2022:
2023: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
2019: ||' from MRP_AP_ABC_CLASSES_V'||MSC_CL_PULL.v_dblink||' x'
2020: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2021:
2022:
2023: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
2024:
2025: COMMIT;
2026:
2027: END LOAD_ABC_CLASSES;