[Home] [Help]
23:
24: PROCEDURE LOAD_CATEGORY IS
25: BEGIN
26:
27: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
31: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
25: BEGIN
26:
27: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
31: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
32: v_sql_stmt:=
33: ' insert into MSC_ST_ITEM_CATEGORIES'
26:
27: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
31: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
32: v_sql_stmt:=
33: ' insert into MSC_ST_ITEM_CATEGORIES'
34: ||'( INVENTORY_ITEM_ID,'
27: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
31: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
32: v_sql_stmt:=
33: ' insert into MSC_ST_ITEM_CATEGORIES'
34: ||'( INVENTORY_ITEM_ID,'
35: ||' ORGANIZATION_ID,'
45: ||' x.CATEGORY_ID,'
46: ||' 1,'
47: ||' :v_refresh_id,'
48: ||' :v_instance_id'
49: ||' from MRP_AD_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
50: ||' WHERE x.RN> :v_lrn ';
51:
52: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
53:
48: ||' :v_instance_id'
49: ||' from MRP_AD_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
50: ||' WHERE x.RN> :v_lrn ';
51:
52: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
53:
54: COMMIT;
55: END IF; --- Incremental refresh
56:
54: COMMIT;
55: END IF; --- Incremental refresh
56:
57:
58: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
59: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_CATEGORIES_V';
60:
61: /* Bug 4365337 - don't need the diff. RNs since Rn1, RN3 and RN4 are anyway 0.
62: We need to check only for RN2 (both in 11i and rel 11.0)
55: END IF; --- Incremental refresh
56:
57:
58: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
59: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_CATEGORIES_V';
60:
61: /* Bug 4365337 - don't need the diff. RNs since Rn1, RN3 and RN4 are anyway 0.
62: We need to check only for RN2 (both in 11i and rel 11.0)
63: Hence commenting the entire v_union_sql. The v_sql_stmt itself can handle this
62: We need to check only for RN2 (both in 11i and rel 11.0)
63: Hence commenting the entire v_union_sql. The v_sql_stmt itself can handle this
64: */
65:
66: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
67: v_union_sql :=
68: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
69: ||' UNION '
70: ||' select'
64: */
65:
66: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
67: v_union_sql :=
68: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
69: ||' UNION '
70: ||' select'
71: ||' x.INVENTORY_ITEM_ID,'
72: ||' x.ORGANIZATION_ID,'
81: ||' x.END_DATE_ACTIVE- :v_dgmt,'
82: ||' 2,'
83: ||' :v_refresh_id,'
84: ||' :v_instance_id'
85: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
86: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
87: --||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
88: ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')';
89:
84: ||' :v_instance_id'
85: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
86: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
87: --||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
88: ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')';
89:
90: /*
91: ||' UNION '
92: ||' select'
103: ||' x.END_DATE_ACTIVE- :v_dgmt,'
104: ||' 2,'
105: ||' :v_refresh_id,'
106: ||' :v_instance_id'
107: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
108: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
109: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
110: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
111: ||' UNION '
106: ||' :v_instance_id'
107: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
108: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
109: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
110: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
111: ||' UNION '
112: ||' select'
113: ||' x.INVENTORY_ITEM_ID,'
114: ||' x.ORGANIZATION_ID,'
123: ||' x.END_DATE_ACTIVE- :v_dgmt,'
124: ||' 2,'
125: ||' :v_refresh_id,'
126: ||' :v_instance_id'
127: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
128: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
129: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
130: ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')' ;
131: */
126: ||' :v_instance_id'
127: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
128: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
129: ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
130: ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')' ;
131: */
132: ELSE
133: /*
134: v_union_sql :=
131: */
132: ELSE
133: /*
134: v_union_sql :=
135: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
136: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
137: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
139: */
132: ELSE
133: /*
134: v_union_sql :=
135: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
136: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
137: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
139: */
140: v_union_sql := ' ';
133: /*
134: v_union_sql :=
135: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
136: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
137: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
139: */
140: v_union_sql := ' ';
141: END IF;
134: v_union_sql :=
135: ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
136: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
137: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
138: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
139: */
140: v_union_sql := ' ';
141: END IF;
142:
171: ||' x.END_DATE_ACTIVE- :v_dgmt,'
172: ||' 2,'
173: ||' :v_refresh_id,'
174: ||' :v_instance_id'
175: ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
176: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
177: -- bug 4365337 remove lang cond ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
178: --bug 4365337 remove v_union_sql and instead add cond. on RN2
179: || v_union_sql ;
176: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
177: -- bug 4365337 remove lang cond ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
178: --bug 4365337 remove v_union_sql and instead add cond. on RN2
179: || v_union_sql ;
180: --||' AND x.RN2 >'||MSC_CL_PULL.v_lrn;
181:
182: --bug 4365337 remove MSC_CL_PULL.v_lang bind parameters since the stmt. does not have it.
183: /* bug 4365337 - no need to check incremental or not since it is the same stmt.
184: Hence commenting out the foll. stmt*/
178: --bug 4365337 remove v_union_sql and instead add cond. on RN2
179: || v_union_sql ;
180: --||' AND x.RN2 >'||MSC_CL_PULL.v_lrn;
181:
182: --bug 4365337 remove MSC_CL_PULL.v_lang bind parameters since the stmt. does not have it.
183: /* bug 4365337 - no need to check incremental or not since it is the same stmt.
184: Hence commenting out the foll. stmt*/
185: /* Uncommenting check for incremental refresh */
186:
183: /* bug 4365337 - no need to check incremental or not since it is the same stmt.
184: Hence commenting out the foll. stmt*/
185: /* Uncommenting check for incremental refresh */
186:
187: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
188:
189: EXECUTE IMMEDIATE v_sql_stmt
190: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
186:
187: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
188:
189: EXECUTE IMMEDIATE v_sql_stmt
190: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
187: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
188:
189: EXECUTE IMMEDIATE v_sql_stmt
190: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
188:
189: EXECUTE IMMEDIATE v_sql_stmt
190: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
189: EXECUTE IMMEDIATE v_sql_stmt
190: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
190: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
191: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
192: -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
200: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
193: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
200: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
201: */
194: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
200: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
201: */
202: ELSE
195: /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
200: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
201: */
202: ELSE
203:
196: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198: MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
200: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
201: */
202: ELSE
203:
204:
202: ELSE
203:
204:
205: EXECUTE IMMEDIATE v_sql_stmt
206: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
207: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
208:
209: -- bug 4365337
210: END IF;
203:
204:
205: EXECUTE IMMEDIATE v_sql_stmt
206: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
207: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
208:
209: -- bug 4365337
210: END IF;
211: COMMIT;
209: -- bug 4365337
210: END IF;
211: COMMIT;
212:
213: MSC_CL_PULL.v_table_name:= 'MSC_ST_CATEGORY_SETS';
214: MSC_CL_PULL.v_view_name := 'MRP_AP_CATEGORY_SETS_V';
215:
216: v_sql_stmt:=
217: ' insert into MSC_ST_CATEGORY_SETS'
210: END IF;
211: COMMIT;
212:
213: MSC_CL_PULL.v_table_name:= 'MSC_ST_CATEGORY_SETS';
214: MSC_CL_PULL.v_view_name := 'MRP_AP_CATEGORY_SETS_V';
215:
216: v_sql_stmt:=
217: ' insert into MSC_ST_CATEGORY_SETS'
218: ||' ( SR_CATEGORY_SET_ID,'
231: ||' x.DEFAULT_FLAG,'
232: ||' 2,'
233: ||' :v_refresh_id,'
234: ||' :v_instance_id'
235: ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
236: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
237: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
238:
239: 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;
233: ||' :v_refresh_id,'
234: ||' :v_instance_id'
235: ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
236: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
237: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
238:
239: 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;
240:
241: COMMIT;
235: ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
236: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
237: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
238:
239: 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;
240:
241: COMMIT;
242:
243: END IF;
258: v_dblink_a2m VARCHAR2(128);
259:
260: cursor org IS
261: select /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */ organization_id org_id,
262: DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
263: p_worker_num, MSC_UTIL.SYS_YES,
264: MSC_UTIL.SYS_NO) yes_flag
265: from msc_instance_orgs
266: where sr_instance_id= MSC_CL_PULL.v_instance_id
262: DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
263: p_worker_num, MSC_UTIL.SYS_YES,
264: MSC_UTIL.SYS_NO) yes_flag
265: from msc_instance_orgs
266: where sr_instance_id= MSC_CL_PULL.v_instance_id
267: and enabled_flag= 1
268: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group))
269: order by
270: organization_id;
264: MSC_UTIL.SYS_NO) yes_flag
265: from msc_instance_orgs
266: where sr_instance_id= MSC_CL_PULL.v_instance_id
267: and enabled_flag= 1
268: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group))
269: order by
270: organization_id;
271:
272: lv_org_count NUMBER:=0;
272: lv_org_count NUMBER:=0;
273:
274: BEGIN
275:
276: IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
277:
278: FOR lc_ins_org IN org LOOP
279:
280: IF lc_ins_org.yes_flag = MSC_UTIL.SYS_YES THEN
295: RETURN;
296: END IF;
297:
298:
299: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
300:
301: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
302: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
297:
298:
299: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
300:
301: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
302: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
304: else
305: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
298:
299: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
300:
301: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
302: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
304: else
305: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
299: MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
300:
301: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
302: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
304: else
305: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
307: end if;
301: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
302: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
304: else
305: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
307: end if;
308:
309: BEGIN
302: MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
304: else
305: MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
307: end if;
308:
309: BEGIN
310:
307: end if;
308:
309: BEGIN
310:
311: IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
312:
313: v_temp_sql := 'NULL, NULL, NULL, NULL, NULL, x.DESCRIPTION, x.LIST_PRICE ,x.ITEM_NAME, '
314: ||'NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,'
315: ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL,NULL, NULL,NULL,'; /* ds change added null*/
313: v_temp_sql := 'NULL, NULL, NULL, NULL, NULL, x.DESCRIPTION, x.LIST_PRICE ,x.ITEM_NAME, '
314: ||'NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,'
315: ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL,NULL, NULL,NULL,'; /* ds change added null*/
316:
317: ELSIF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110 THEN
318:
319: v_temp_sql := 'x.REPLENISH_TO_ORDER_FLAG,x.PICK_COMPONENTS_FLAG ,NULL, NULL,NULL, '
320: ||' x.DESCRIPTION, x.LIST_PRICE , x.ITEM_NAME, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, '
321: ||' 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*/
319: v_temp_sql := 'x.REPLENISH_TO_ORDER_FLAG,x.PICK_COMPONENTS_FLAG ,NULL, NULL,NULL, '
320: ||' x.DESCRIPTION, x.LIST_PRICE , x.ITEM_NAME, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, '
321: ||' 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*/
322:
323: ELSIF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS115) THEN
324:
325: v_temp_sql := ' x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG ,';
326:
327: BEGIN
326:
327: BEGIN
328: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
329: v_sql_stmt := ' select uom_code '
330: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
331: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
332: ||' and base_uom_flag = ''Y'' ';
333:
334: execute immediate v_sql_stmt into lv_uom_code;
327: BEGIN
328: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
329: v_sql_stmt := ' select uom_code '
330: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
331: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
332: ||' and base_uom_flag = ''Y'' ';
333:
334: execute immediate v_sql_stmt into lv_uom_code;
335: v_temp_sql := v_temp_sql ||' x.CONV_FACTOR, ';
347: BEGIN
348: -- check if the base language is different than the installed lang in source instance
349: -- If the base installed_flag is same as userenv lang- then dont go to TL table
350: v_sql_stmt := ' select 1 '
351: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
352: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
353: ||' and installed_flag = ''B'' ';
354:
355: execute immediate v_sql_stmt into lv_base_lang_diff;
348: -- check if the base language is different than the installed lang in source instance
349: -- If the base installed_flag is same as userenv lang- then dont go to TL table
350: v_sql_stmt := ' select 1 '
351: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
352: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
353: ||' and installed_flag = ''B'' ';
354:
355: execute immediate v_sql_stmt into lv_base_lang_diff;
356:
371: END IF;
372:
373: -- If Profile does not contain a Price List then for discrete list_price is null here but in ODS Load
374: -- the nvl(list_price,item_cost) is performed, for process get the List Price.
375: IF MSC_CL_PULL.v_mrp_bis_price_list is null THEN
376: v_temp_sql := v_temp_sql ||' x.LIST_PRICE, ';
377: ELSE
378: v_temp_sql := v_temp_sql ||' x.MRP_BIS_LIST_PRICE, ';
379: END IF;
383: -- If the base installed_flag is same as userenv lang- then dont go to TL table
384:
385: select instance_code, DECODE(A2M_DBLINK, NULL, MSC_UTIL.NULL_DBLINK, A2M_DBLINK)
386: into lv_icode, v_dblink_a2m
387: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
388:
389: v_sql_stmt := ' select item_name_from_kfv '
390: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
391: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
386: into lv_icode, v_dblink_a2m
387: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
388:
389: v_sql_stmt := ' select item_name_from_kfv '
390: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
391: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
392: ||' AND INSTANCE_CODE= '''||lv_icode||''''
393: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
394:
387: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
388:
389: v_sql_stmt := ' select item_name_from_kfv '
390: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
391: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
392: ||' AND INSTANCE_CODE= '''||lv_icode||''''
393: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
394:
395: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt - ' || v_sql_stmt);
415:
416:
417: /* # For Bug 5606037 SRP Changes NULL Colums to collect Item Attribute Data */
418:
419: ELSIF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN --# For Bug 5606037 SRP Changes
420:
421: v_temp_sql := ' x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG ,';
422:
423: BEGIN
422:
423: BEGIN
424: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
425: v_sql_stmt := ' select uom_code '
426: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
427: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
428: ||' and base_uom_flag = ''Y'' ';
429:
430: execute immediate v_sql_stmt into lv_uom_code;
423: BEGIN
424: -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
425: v_sql_stmt := ' select uom_code '
426: ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
427: ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
428: ||' and base_uom_flag = ''Y'' ';
429:
430: execute immediate v_sql_stmt into lv_uom_code;
431: v_temp_sql := v_temp_sql ||' x.CONV_FACTOR, ';
443: BEGIN
444: -- check if the base language is different than the installed lang in source instance
445: -- If the base installed_flag is same as userenv lang- then dont go to TL table
446: v_sql_stmt := ' select 1 '
447: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
448: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
449: ||' and installed_flag = ''B'' ';
450:
451: execute immediate v_sql_stmt into lv_base_lang_diff;
444: -- check if the base language is different than the installed lang in source instance
445: -- If the base installed_flag is same as userenv lang- then dont go to TL table
446: v_sql_stmt := ' select 1 '
447: ||' from fnd_languages'||MSC_CL_PULL.v_dblink
448: ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
449: ||' and installed_flag = ''B'' ';
450:
451: execute immediate v_sql_stmt into lv_base_lang_diff;
452:
467: END IF;
468:
469: -- If Profile does not contain a Price List then for discrete list_price is null here but in ODS Load
470: -- the nvl(list_price,item_cost) is performed, for process get the List Price.
471: IF MSC_CL_PULL.v_mrp_bis_price_list is null THEN
472: v_temp_sql := v_temp_sql ||' x.LIST_PRICE, ';
473: ELSE
474: v_temp_sql := v_temp_sql ||' x.MRP_BIS_LIST_PRICE, ';
475: END IF;
479: -- If the base installed_flag is same as userenv lang- then dont go to TL table
480:
481: select instance_code, DECODE(A2M_DBLINK, NULL, MSC_UTIL.NULL_DBLINK, A2M_DBLINK)
482: into lv_icode, v_dblink_a2m
483: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
484:
485: v_sql_stmt := ' select item_name_from_kfv '
486: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
487: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
482: into lv_icode, v_dblink_a2m
483: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
484:
485: v_sql_stmt := ' select item_name_from_kfv '
486: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
487: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
488: ||' AND INSTANCE_CODE= '''||lv_icode||''''
489: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
490:
483: from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
484:
485: v_sql_stmt := ' select item_name_from_kfv '
486: ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
487: ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
488: ||' AND INSTANCE_CODE= '''||lv_icode||''''
489: ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
490:
491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt - ' || v_sql_stmt);
514:
515: End;
516:
517:
518: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
519: lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
520: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
521: else
522: lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
515: End;
516:
517:
518: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
519: lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
520: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
521: else
522: lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
518: if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
519: lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
520: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
521: else
522: lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
524: end if;
525:
526:
771: ||' WHERE x.ORGANIZATION_ID'||lv_in_org_str;
772:
773:
774:
775: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
776:
777: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
778: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
779: EXECUTE IMMEDIATE v_sql_stmt
776:
777: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
778: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
779: EXECUTE IMMEDIATE v_sql_stmt
780: USING MSC_CL_PULL.V_ICODE,
781: MSC_CL_PULL.v_mso_item_dmd_penalty,
782: MSC_CL_PULL.v_mso_item_cap_penalty,
783: MSC_CL_PULL.v_mrp_bis_av_discount,
784: MSC_CL_PULL.v_refresh_id,
777: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
778: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
779: EXECUTE IMMEDIATE v_sql_stmt
780: USING MSC_CL_PULL.V_ICODE,
781: MSC_CL_PULL.v_mso_item_dmd_penalty,
782: MSC_CL_PULL.v_mso_item_cap_penalty,
783: MSC_CL_PULL.v_mrp_bis_av_discount,
784: MSC_CL_PULL.v_refresh_id,
785: MSC_CL_PULL.v_instance_id;
778: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
779: EXECUTE IMMEDIATE v_sql_stmt
780: USING MSC_CL_PULL.V_ICODE,
781: MSC_CL_PULL.v_mso_item_dmd_penalty,
782: MSC_CL_PULL.v_mso_item_cap_penalty,
783: MSC_CL_PULL.v_mrp_bis_av_discount,
784: MSC_CL_PULL.v_refresh_id,
785: MSC_CL_PULL.v_instance_id;
786:
779: EXECUTE IMMEDIATE v_sql_stmt
780: USING MSC_CL_PULL.V_ICODE,
781: MSC_CL_PULL.v_mso_item_dmd_penalty,
782: MSC_CL_PULL.v_mso_item_cap_penalty,
783: MSC_CL_PULL.v_mrp_bis_av_discount,
784: MSC_CL_PULL.v_refresh_id,
785: MSC_CL_PULL.v_instance_id;
786:
787: ELSE -- net change
780: USING MSC_CL_PULL.V_ICODE,
781: MSC_CL_PULL.v_mso_item_dmd_penalty,
782: MSC_CL_PULL.v_mso_item_cap_penalty,
783: MSC_CL_PULL.v_mrp_bis_av_discount,
784: MSC_CL_PULL.v_refresh_id,
785: MSC_CL_PULL.v_instance_id;
786:
787: ELSE -- net change
788:
781: MSC_CL_PULL.v_mso_item_dmd_penalty,
782: MSC_CL_PULL.v_mso_item_cap_penalty,
783: MSC_CL_PULL.v_mrp_bis_av_discount,
784: MSC_CL_PULL.v_refresh_id,
785: MSC_CL_PULL.v_instance_id;
786:
787: ELSE -- net change
788:
789: v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
788:
789: v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
790:
791: EXECUTE IMMEDIATE v_sql_stmt
792: USING MSC_CL_PULL.V_ICODE,
793: MSC_CL_PULL.v_mso_item_dmd_penalty,
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
789: v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
790:
791: EXECUTE IMMEDIATE v_sql_stmt
792: USING MSC_CL_PULL.V_ICODE,
793: MSC_CL_PULL.v_mso_item_dmd_penalty,
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
797: MSC_CL_PULL.v_instance_id,
790:
791: EXECUTE IMMEDIATE v_sql_stmt
792: USING MSC_CL_PULL.V_ICODE,
793: MSC_CL_PULL.v_mso_item_dmd_penalty,
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
797: MSC_CL_PULL.v_instance_id,
798: MSC_CL_PULL.v_lrn;
791: EXECUTE IMMEDIATE v_sql_stmt
792: USING MSC_CL_PULL.V_ICODE,
793: MSC_CL_PULL.v_mso_item_dmd_penalty,
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
797: MSC_CL_PULL.v_instance_id,
798: MSC_CL_PULL.v_lrn;
799:
792: USING MSC_CL_PULL.V_ICODE,
793: MSC_CL_PULL.v_mso_item_dmd_penalty,
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
797: MSC_CL_PULL.v_instance_id,
798: MSC_CL_PULL.v_lrn;
799:
800: END IF;
793: MSC_CL_PULL.v_mso_item_dmd_penalty,
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
797: MSC_CL_PULL.v_instance_id,
798: MSC_CL_PULL.v_lrn;
799:
800: END IF;
801:
794: MSC_CL_PULL.v_mso_item_cap_penalty,
795: MSC_CL_PULL.v_mrp_bis_av_discount,
796: MSC_CL_PULL.v_refresh_id,
797: MSC_CL_PULL.v_instance_id,
798: MSC_CL_PULL.v_lrn;
799:
800: END IF;
801:
802: COMMIT;
800: END IF;
801:
802: COMMIT;
803:
804: END IF; -- MSC_CL_PULL.ITEM_ENABLED
805:
806: END LOAD_ITEM;
807:
808:
812: lv_last_asl_collection_date DATE ;
813: BEGIN
814:
815:
816: 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
817:
818: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
819:
820: /*ASL */
814:
815:
816: 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
817:
818: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
819:
820: /*ASL */
821: IF MSC_CL_PULL.v_lrnn = -1 THEN
822: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
817:
818: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
819:
820: /*ASL */
821: IF MSC_CL_PULL.v_lrnn = -1 THEN
822: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
823: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
824: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
825: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
818: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
819:
820: /*ASL */
821: IF MSC_CL_PULL.v_lrnn = -1 THEN
822: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
823: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
824: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
825: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
826: ||' From msc_instance_orgs '
819:
820: /*ASL */
821: IF MSC_CL_PULL.v_lrnn = -1 THEN
822: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
823: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
824: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
825: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
826: ||' From msc_instance_orgs '
827: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
820: /*ASL */
821: IF MSC_CL_PULL.v_lrnn = -1 THEN
822: MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
823: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
824: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
825: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
826: ||' From msc_instance_orgs '
827: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
828: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
823: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
824: MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
825: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
826: ||' From msc_instance_orgs '
827: ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
828: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
829:
830: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
831:
838:
839: /* Added this to collect Item Price information for a supplier
840: from 11i/110 source */
841: Begin
842: Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS107,' NULL,', ' x.ITEM_PRICE,')
843: into v_temp_sql
844: from dual;
845: End;
846:
844: from dual;
845: End;
846:
847: /* Added this code for VMI changes */
848: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
849: 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,'
850: || ' x.REPLENISHMENT_METHOD,x.MIN_MINMAX_DAYS,x.MAX_MINMAX_DAYS,x.FORECAST_HORIZON,x.FIXED_ORDER_QUANTITY, ';
851:
852: ELSE
853: v_temp_sql1 := 'NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL,';
854: END IF;
855:
856:
857: IF MSC_CL_PULL.v_lrnn =-1 THEN -- complete refresh
858:
859: v_sql_stmt:=
860: ' insert into MSC_ST_ITEM_SUPPLIERS'
861: ||' ( INVENTORY_ITEM_ID,'
927: /* Get partner_item_name */
928: ||' x.PRIMARY_VENDOR_ITEM,'
929: /* SCE Change end */
930: ||' :v_instance_id'
931: ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
932: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
933:
934: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935: MSC_CL_PULL.v_mso_sup_cap_penalty,
930: ||' :v_instance_id'
931: ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
932: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
933:
934: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935: MSC_CL_PULL.v_mso_sup_cap_penalty,
936: MSC_CL_PULL.v_instance_id,
937: MSC_CL_PULL.v_refresh_id,
938: MSC_CL_PULL.v_instance_id;
931: ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
932: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
933:
934: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935: MSC_CL_PULL.v_mso_sup_cap_penalty,
936: MSC_CL_PULL.v_instance_id,
937: MSC_CL_PULL.v_refresh_id,
938: MSC_CL_PULL.v_instance_id;
939:
932: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
933:
934: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935: MSC_CL_PULL.v_mso_sup_cap_penalty,
936: MSC_CL_PULL.v_instance_id,
937: MSC_CL_PULL.v_refresh_id,
938: MSC_CL_PULL.v_instance_id;
939:
940: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
933:
934: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935: MSC_CL_PULL.v_mso_sup_cap_penalty,
936: MSC_CL_PULL.v_instance_id,
937: MSC_CL_PULL.v_refresh_id,
938: MSC_CL_PULL.v_instance_id;
939:
940: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
941:
934: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935: MSC_CL_PULL.v_mso_sup_cap_penalty,
936: MSC_CL_PULL.v_instance_id,
937: MSC_CL_PULL.v_refresh_id,
938: MSC_CL_PULL.v_instance_id;
939:
940: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
941:
942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL net change ');
936: MSC_CL_PULL.v_instance_id,
937: MSC_CL_PULL.v_refresh_id,
938: MSC_CL_PULL.v_instance_id;
939:
940: ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
941:
942: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL net change ');
943: v_sql_stmt:=
944: ' insert into MSC_ST_ITEM_SUPPLIERS'
1011: /* Get partner_item_name */
1012: ||' x.PRIMARY_VENDOR_ITEM,'
1013: /* SCE Change end */
1014: ||' :v_instance_id'
1015: ||' from MRP_AN_PO_GLOBAL_ASL_V '||MSC_CL_PULL.v_dblink||' x'
1016: ||' WHERE (x.USING_ORGANIZATION_ID = -1 or x.USING_ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str ||')'
1017: ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1018:
1019: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1018:
1019: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1020:
1021:
1022: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1023: MSC_CL_PULL.v_mso_sup_cap_penalty,
1024: MSC_CL_PULL.v_instance_id,
1025: MSC_CL_PULL.v_refresh_id,
1026: MSC_CL_PULL.v_instance_id,
1019: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1020:
1021:
1022: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1023: MSC_CL_PULL.v_mso_sup_cap_penalty,
1024: MSC_CL_PULL.v_instance_id,
1025: MSC_CL_PULL.v_refresh_id,
1026: MSC_CL_PULL.v_instance_id,
1027: lv_last_asl_collection_date,
1020:
1021:
1022: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1023: MSC_CL_PULL.v_mso_sup_cap_penalty,
1024: MSC_CL_PULL.v_instance_id,
1025: MSC_CL_PULL.v_refresh_id,
1026: MSC_CL_PULL.v_instance_id,
1027: lv_last_asl_collection_date,
1028: lv_last_asl_collection_date;
1021:
1022: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1023: MSC_CL_PULL.v_mso_sup_cap_penalty,
1024: MSC_CL_PULL.v_instance_id,
1025: MSC_CL_PULL.v_refresh_id,
1026: MSC_CL_PULL.v_instance_id,
1027: lv_last_asl_collection_date,
1028: lv_last_asl_collection_date;
1029:
1022: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1023: MSC_CL_PULL.v_mso_sup_cap_penalty,
1024: MSC_CL_PULL.v_instance_id,
1025: MSC_CL_PULL.v_refresh_id,
1026: MSC_CL_PULL.v_instance_id,
1027: lv_last_asl_collection_date,
1028: lv_last_asl_collection_date;
1029:
1030: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1039: only global ASLs are in this view and the local ASLs are in
1040: MRP_AP_PO_LOCAL_ASL_V. Hence adding another insert stmt. to insert local ASLs.
1041: */
1042:
1043: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 ) THEN
1044:
1045: IF MSC_CL_PULL.v_lrnn =-1 THEN -- COMPLETE REFRESH
1046: v_sql_stmt:=
1047: ' insert into MSC_ST_ITEM_SUPPLIERS'
1041: */
1042:
1043: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 ) THEN
1044:
1045: IF MSC_CL_PULL.v_lrnn =-1 THEN -- COMPLETE REFRESH
1046: v_sql_stmt:=
1047: ' insert into MSC_ST_ITEM_SUPPLIERS'
1048: ||' ( INVENTORY_ITEM_ID,'
1049: ||' ORGANIZATION_ID,'
1114: /* Get partner_item_name */
1115: ||' x.PRIMARY_VENDOR_ITEM,'
1116: /* SCE Change end */
1117: ||' :v_instance_id'
1118: ||' from MRP_AP_PO_LOCAL_ASL_V'||MSC_CL_PULL.v_dblink||' x'
1119: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1120:
1121: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1122:
1119: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1120:
1121: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1122:
1123: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1124: MSC_CL_PULL.v_mso_sup_cap_penalty,
1125: MSC_CL_PULL.v_instance_id,
1126: MSC_CL_PULL.v_refresh_id,
1127: MSC_CL_PULL.v_instance_id;
1120:
1121: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1122:
1123: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1124: MSC_CL_PULL.v_mso_sup_cap_penalty,
1125: MSC_CL_PULL.v_instance_id,
1126: MSC_CL_PULL.v_refresh_id,
1127: MSC_CL_PULL.v_instance_id;
1128: COMMIT;
1121: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1122:
1123: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1124: MSC_CL_PULL.v_mso_sup_cap_penalty,
1125: MSC_CL_PULL.v_instance_id,
1126: MSC_CL_PULL.v_refresh_id,
1127: MSC_CL_PULL.v_instance_id;
1128: COMMIT;
1129: ELSE -- LOCAL ASL NET CHANGE
1122:
1123: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1124: MSC_CL_PULL.v_mso_sup_cap_penalty,
1125: MSC_CL_PULL.v_instance_id,
1126: MSC_CL_PULL.v_refresh_id,
1127: MSC_CL_PULL.v_instance_id;
1128: COMMIT;
1129: ELSE -- LOCAL ASL NET CHANGE
1130: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL net change ');
1123: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1124: MSC_CL_PULL.v_mso_sup_cap_penalty,
1125: MSC_CL_PULL.v_instance_id,
1126: MSC_CL_PULL.v_refresh_id,
1127: MSC_CL_PULL.v_instance_id;
1128: COMMIT;
1129: ELSE -- LOCAL ASL NET CHANGE
1130: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL net change ');
1131: v_sql_stmt:=
1199: /* Get partner_item_name */
1200: ||' x.PRIMARY_VENDOR_ITEM,'
1201: /* SCE Change end */
1202: ||' :v_instance_id'
1203: ||' from MRP_AN_PO_LOCAL_ASL_V '||MSC_CL_PULL.v_dblink||' x'
1204: ||' WHERE x.USING_ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str
1205: ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1206:
1207: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1205: ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1206:
1207: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1208:
1209: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1210: MSC_CL_PULL.v_mso_sup_cap_penalty,
1211: MSC_CL_PULL.v_instance_id,
1212: MSC_CL_PULL.v_refresh_id,
1213: MSC_CL_PULL.v_instance_id,
1206:
1207: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1208:
1209: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1210: MSC_CL_PULL.v_mso_sup_cap_penalty,
1211: MSC_CL_PULL.v_instance_id,
1212: MSC_CL_PULL.v_refresh_id,
1213: MSC_CL_PULL.v_instance_id,
1214: lv_last_asl_collection_date,
1207: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1208:
1209: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1210: MSC_CL_PULL.v_mso_sup_cap_penalty,
1211: MSC_CL_PULL.v_instance_id,
1212: MSC_CL_PULL.v_refresh_id,
1213: MSC_CL_PULL.v_instance_id,
1214: lv_last_asl_collection_date,
1215: lv_last_asl_collection_date;
1208:
1209: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1210: MSC_CL_PULL.v_mso_sup_cap_penalty,
1211: MSC_CL_PULL.v_instance_id,
1212: MSC_CL_PULL.v_refresh_id,
1213: MSC_CL_PULL.v_instance_id,
1214: lv_last_asl_collection_date,
1215: lv_last_asl_collection_date;
1216:
1209: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1210: MSC_CL_PULL.v_mso_sup_cap_penalty,
1211: MSC_CL_PULL.v_instance_id,
1212: MSC_CL_PULL.v_refresh_id,
1213: MSC_CL_PULL.v_instance_id,
1214: lv_last_asl_collection_date,
1215: lv_last_asl_collection_date;
1216:
1217: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1216:
1217: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1218: COMMIT ;
1219: END IF ;
1220: END IF; ---- MSC_CL_PULL.v_apps_ver === 115
1221:
1222: END IF;
1223:
1224:
1221:
1222: END IF;
1223:
1224:
1225: 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
1226: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1227: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1228:
1229: --- LOAD NET_CHAGE for DELETE --------
1222: END IF;
1223:
1224:
1225: 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
1226: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1227: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1228:
1229: --- LOAD NET_CHAGE for DELETE --------
1230:
1223:
1224:
1225: 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
1226: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1227: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1228:
1229: --- LOAD NET_CHAGE for DELETE --------
1230:
1231: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1227: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1228:
1229: --- LOAD NET_CHAGE for DELETE --------
1230:
1231: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1232:
1233: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1234: MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1235:
1229: --- LOAD NET_CHAGE for DELETE --------
1230:
1231: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1232:
1233: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1234: MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1235:
1236: v_sql_stmt:=
1237: 'Insert into MSC_ST_SUPPLIER_CAPACITIES'
1230:
1231: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1232:
1233: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1234: MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1235:
1236: v_sql_stmt:=
1237: 'Insert into MSC_ST_SUPPLIER_CAPACITIES'
1238: ||' ( INVENTORY_ITEM_ID,'
1253: ||' x.FROM_DATE- :v_dgmt,'
1254: ||' 1,'
1255: ||' :v_refresh_id,'
1256: ||' :v_instance_id'
1257: ||' from MRP_AD_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1258: ||' WHERE x.RN> :v_lrn '
1259: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1260:
1261: 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;
1257: ||' from MRP_AD_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1258: ||' WHERE x.RN> :v_lrn '
1259: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1260:
1261: 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;
1262:
1263: COMMIT;
1264:
1265: END IF;
1263: COMMIT;
1264:
1265: END IF;
1266:
1267: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1268: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1269:
1270: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1271:
1264:
1265: END IF;
1266:
1267: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1268: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1269:
1270: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1271:
1272: v_union_sql :=
1266:
1267: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1268: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1269:
1270: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1271:
1272: v_union_sql :=
1273: ' AND ( x.RN1 > :v_lrn )'
1274: ||' UNION '
1283: ||' x.CAPACITY,'
1284: ||' 2,'
1285: ||' :v_refresh_id,'
1286: ||' :v_instance_id'
1287: ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1288: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1289: ||' AND (x.RN2 > :v_lrn)';
1290:
1291: ELSE
1319: ||' x.CAPACITY,'
1320: ||' 2,'
1321: ||' :v_refresh_id,'
1322: ||' :v_instance_id'
1323: ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1324: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1325: || v_union_sql;
1326:
1327:
1324: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1325: || v_union_sql;
1326:
1327:
1328: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1329:
1330: EXECUTE IMMEDIATE v_sql_stmt
1331: 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,
1332: 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;
1327:
1328: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1329:
1330: EXECUTE IMMEDIATE v_sql_stmt
1331: 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,
1332: 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;
1333:
1334: ELSE
1335: EXECUTE IMMEDIATE v_sql_stmt
1328: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1329:
1330: EXECUTE IMMEDIATE v_sql_stmt
1331: 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,
1332: 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;
1333:
1334: ELSE
1335: EXECUTE IMMEDIATE v_sql_stmt
1336: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1332: 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;
1333:
1334: ELSE
1335: EXECUTE IMMEDIATE v_sql_stmt
1336: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1337: END IF;
1338:
1339:
1340: COMMIT;
1338:
1339:
1340: COMMIT;
1341:
1342: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343:
1344: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1345: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1346:
1340: COMMIT;
1341:
1342: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343:
1344: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1345: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1346:
1347: v_sql_stmt:=
1348: ' insert into MSC_ST_SUPPLIER_FLEX_FENCES'
1341:
1342: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343:
1344: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1345: MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1346:
1347: v_sql_stmt:=
1348: ' insert into MSC_ST_SUPPLIER_FLEX_FENCES'
1349: ||' ( INVENTORY_ITEM_ID,'
1366: ||' x.TOLERANCE_PERCENTAGE,'
1367: ||' 2,'
1368: ||' :v_refresh_id,'
1369: ||' :v_instance_id'
1370: ||' from MRP_AP_SUPPLIER_FLEX_FENCES_V'||MSC_CL_PULL.v_dblink||' x'
1371: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str ;
1372:
1373: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1374:
1369: ||' :v_instance_id'
1370: ||' from MRP_AP_SUPPLIER_FLEX_FENCES_V'||MSC_CL_PULL.v_dblink||' x'
1371: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str ;
1372:
1373: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1374:
1375: COMMIT;
1376:
1377: END IF; -- complete refresh
1375: COMMIT;
1376:
1377: END IF; -- complete refresh
1378:
1379: END IF; -- MSC_CL_PULL.v_apps_ver
1380:
1381: END LOAD_SUPPLIER_CAPACITY;
1382:
1383:
1385: PROCEDURE LOAD_ITEM_SUBSTITUTES IS
1386: v_condition varchar2(1000);
1387: BEGIN
1388:
1389: IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1390:
1391: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1392:
1393: 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
1387: BEGIN
1388:
1389: IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1390:
1391: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1392:
1393: 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
1394: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1395: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1389: IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1390:
1391: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1392:
1393: 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
1394: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1395: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1396: v_sql_stmt:=
1397: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1390:
1391: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1392:
1393: 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
1394: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1395: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1396: v_sql_stmt:=
1397: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1398: ||'( HIGHER_ITEM_ID,'
1391: -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1392:
1393: 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
1394: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1395: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1396: v_sql_stmt:=
1397: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1398: ||'( HIGHER_ITEM_ID,'
1399: ||' LOWER_ITEM_ID,'
1417: ||' x.DISABLE_DATE,'
1418: ||' :v_instance_id,'
1419: ||' 2,'
1420: ||' x.ORGANIZATION_ID'
1421: ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1422: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1423:
1424: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1425:
1418: ||' :v_instance_id,'
1419: ||' 2,'
1420: ||' x.ORGANIZATION_ID'
1421: ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1422: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1423:
1424: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1425:
1426: COMMIT;
1420: ||' x.ORGANIZATION_ID'
1421: ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1422: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1423:
1424: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1425:
1426: COMMIT;
1427:
1428: END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1427:
1428: END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1429:
1430:
1431: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1432: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUBSTITUTES_V';
1433:
1434: v_sql_stmt:=
1435: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1428: END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1429:
1430:
1431: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1432: MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUBSTITUTES_V';
1433:
1434: v_sql_stmt:=
1435: ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1436: ||'( HIGHER_ITEM_ID,'
1459: ||' x.DISABLE_DATE,'
1460: ||' :v_instance_id,'
1461: ||' 2,'
1462: ||' x.ORGANIZATION_ID'
1463: ||' FROM MRP_AP_ITEM_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
1464: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1465: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1466:
1467:
1460: ||' :v_instance_id,'
1461: ||' 2,'
1462: ||' x.ORGANIZATION_ID'
1463: ||' FROM MRP_AP_ITEM_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
1464: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1465: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1466:
1467:
1468: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1464: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1465: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1466:
1467:
1468: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1469:
1470: COMMIT;
1471:
1472: --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1470: COMMIT;
1471:
1472: --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1473:
1474: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1475:
1476: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1477: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1478: v_condition:=null;
1472: --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1473:
1474: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1475:
1476: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1477: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1478: v_condition:=null;
1479:
1480: --For Bug 5702475 SRP Changes
1473:
1474: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1475:
1476: MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1477: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1478: v_condition:=null;
1479:
1480: --For Bug 5702475 SRP Changes
1481: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
1477: MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1478: v_condition:=null;
1479:
1480: --For Bug 5702475 SRP Changes
1481: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
1482: v_condition:= ' and x.relationship_type_id in (2,5,8,18 ) ';
1483: else
1484: v_condition:= ' and x.relationship_type_id in (2) ';
1485: end if;
1501: ||' x.RELATIONSHIP_TYPE_ID,'
1502: ||' :v_refresh_id,'
1503: ||' 1,'
1504: ||' :v_instance_id'
1505: ||' FROM MRP_AD_ITEM_RELATIONSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1506: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1507: || v_condition
1508: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1509:
1502: ||' :v_refresh_id,'
1503: ||' 1,'
1504: ||' :v_instance_id'
1505: ||' FROM MRP_AD_ITEM_RELATIONSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1506: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1507: || v_condition
1508: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1509:
1510:
1507: || v_condition
1508: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1509:
1510:
1511: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1512:
1513: COMMIT;
1514:
1515: END IF ; --INCREMENTAL REFRESH
1513: COMMIT;
1514:
1515: END IF ; --INCREMENTAL REFRESH
1516:
1517: END IF; -- MSC_CL_PULL.ITEM_SUBST_ENABLED
1518:
1519: END LOAD_ITEM_SUBSTITUTES;
1520:
1521:
1524: BEGIN
1525:
1526: DELETE from MSC_ST_SYSTEM_ITEMS st_item
1527: where st_item.SR_INVENTORY_ITEM_ID in ( -1000,-1001) and
1528: st_item.sr_instance_id = MSC_CL_PULL.v_instance_id and
1529: st_item.organization_id in
1530: ( select x.organization_id
1531: FROM msc_instance_orgs x
1532: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1528: st_item.sr_instance_id = MSC_CL_PULL.v_instance_id and
1529: st_item.organization_id in
1530: ( select x.organization_id
1531: FROM msc_instance_orgs x
1532: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1533: and x.enabled_flag= 1
1534: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1535: );
1536: commit;
1530: ( select x.organization_id
1531: FROM msc_instance_orgs x
1532: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1533: and x.enabled_flag= 1
1534: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1535: );
1536: commit;
1537:
1538: lv_item_name := NVL(FND_PROFILE.VALUE('MSC_EAM_NO_ACTIVITY_ITEM'),'No Activity Item');
1561: ORGANIZATION_CODE)
1562: SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1563: x.ORGANIZATION_ID ,
1564: -1000,
1565: MSC_CL_PULL.v_instance_id,
1566: 1, --LOT_CONTROL_CODE 1 no control, 2 full control
1567: 1, --ROUNDING_CONTROL_TYPE 1 round order qty, 2 no
1568: 2, --IN_SOURCE_PLAN
1569: 3, --MRP_PLANNING_CODE 3 mrp planning, 6 not planned,
1583: lv_item_name ,
1584: org.ORGANIZATION_CODE
1585: FROM msc_instance_orgs x,
1586: MTL_PARAMETERS org
1587: WHERE sr_instance_id= MSC_CL_PULL.v_instance_id
1588: and enabled_flag= 1
1589: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1590: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group));
1591:
1586: MTL_PARAMETERS org
1587: WHERE sr_instance_id= MSC_CL_PULL.v_instance_id
1588: and enabled_flag= 1
1589: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1590: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group));
1591:
1592: commit;
1593: lv_item_name := NVL(FND_PROFILE.VALUE('MSC_WIP_NONSTD_JOB_ITEM'),'Non Standard Job Item');
1594:
1616: ORGANIZATION_CODE)
1617: SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1618: x.ORGANIZATION_ID ,
1619: -1001,
1620: MSC_CL_PULL.v_instance_id,
1621: 1, --LOT_CONTROL_CODE
1622: 1, --ROUNDING_CONTROL_TYPE
1623: 2, --IN_SOURCE_PLAN
1624: 3, --MRP_PLANNING_CODE
1637: lv_item_name ,
1638: org.ORGANIZATION_CODE
1639: FROM msc_instance_orgs x,
1640: MTL_PARAMETERS org
1641: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1642: and x.enabled_flag= 1
1643: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1644: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group));
1645:
1640: MTL_PARAMETERS org
1641: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1642: and x.enabled_flag= 1
1643: and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1644: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group));
1645:
1646: commit;
1647:
1648: END INSERT_DUMMY_ITEMS;
1664: lv_deleted_flag PLS_INTEGER := 2;
1665: BEGIN
1666:
1667: delete from MSC_ST_CATEGORY_SETS st_item_category_set
1668: where st_item_category_set.sr_instance_id = MSC_CL_PULL.v_instance_id
1669: and st_item_category_set.SR_CATEGORY_SET_ID = -5000;
1670: commit;
1671: lv_category_set_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY_SET'),'Unspecified Items');
1672: BEGIN
1669: and st_item_category_set.SR_CATEGORY_SET_ID = -5000;
1670: commit;
1671: lv_category_set_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY_SET'),'Unspecified Items');
1672: BEGIN
1673: lv_table_name:= 'MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink;
1674: lv_sql_stmt:= 'SELECT category_set_id FROM '||lv_table_name
1675: ||' WHERE category_set_name = '''||lv_category_set_name
1676: ||''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''';
1677:
1672: BEGIN
1673: lv_table_name:= 'MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink;
1674: lv_sql_stmt:= 'SELECT category_set_id FROM '||lv_table_name
1675: ||' WHERE category_set_name = '''||lv_category_set_name
1676: ||''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''';
1677:
1678: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug1 - ' || lv_sql_stmt);
1679:
1680: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_category_set_id ;
1699: lv_category_set_description ,
1700: 2,
1701: 2,
1702: 2,
1703: MSC_CL_PULL.v_instance_id ) ;
1704: commit ;
1705: END IF ;
1706:
1707: lv_table_name:= 'MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1703: MSC_CL_PULL.v_instance_id ) ;
1704: commit ;
1705: END IF ;
1706:
1707: lv_table_name:= 'MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1708:
1709:
1710: DELETE from MSC_ST_ITEM_CATEGORIES st_item_category
1711: where st_item_category.INVENTORY_ITEM_ID in ( -1000,-1001) and
1710: DELETE from MSC_ST_ITEM_CATEGORIES st_item_category
1711: where st_item_category.INVENTORY_ITEM_ID in ( -1000,-1001) and
1712: st_item_category.SR_CATEGORY_SET_ID in (-5000)and
1713: st_item_category.SR_CATEGORY_ID in (-5001) and
1714: st_item_category.sr_instance_id = MSC_CL_PULL.v_instance_id and
1715: st_item_category.organization_id in
1716: ( select x.organization_id
1717: FROM msc_instance_orgs x
1718: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1714: st_item_category.sr_instance_id = MSC_CL_PULL.v_instance_id and
1715: st_item_category.organization_id in
1716: ( select x.organization_id
1717: FROM msc_instance_orgs x
1718: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1719: and x.enabled_flag= 1
1720: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1721: );
1722: commit;
1716: ( select x.organization_id
1717: FROM msc_instance_orgs x
1718: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1719: and x.enabled_flag= 1
1720: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1721: );
1722: commit;
1723:
1724: lv_category_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY'),'NEW.MISC');
1722: commit;
1723:
1724: lv_category_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY'),'NEW.MISC');
1725: BEGIN
1726: lv_table_name:= 'MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink;
1727: lv_sql_stmt:= 'SELECT category_id,description FROM ' ||lv_table_name
1728: || ' WHERE category_name = '''||lv_category_name
1729: || ''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''and rownum=1';
1730:
1725: BEGIN
1726: lv_table_name:= 'MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink;
1727: lv_sql_stmt:= 'SELECT category_id,description FROM ' ||lv_table_name
1728: || ' WHERE category_name = '''||lv_category_name
1729: || ''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''and rownum=1';
1730:
1731: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug2 - ' || lv_sql_stmt);
1732:
1733: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_category_id,lv_category_description ;
1735: WHEN NO_DATA_FOUND THEN
1736: lv_category_id := -5001;
1737: END ;
1738:
1739: lv_table_name :='MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1740: lv_sql_stmt := 'insert into MSC_ST_ITEM_CATEGORIES ('
1741: ||'INVENTORY_ITEM_ID,'
1742: ||'ORGANIZATION_ID,'
1743: ||'SR_CATEGORY_SET_ID,'
1763: ||''''|| lv_enabled_flag||'''' ||','
1764: || 'null,'
1765: || 'null,'
1766: || lv_deleted_flag ||','
1767: || MSC_CL_PULL.v_instance_id
1768: || ' FROM msc_instance_orgs x,'
1769: || lv_table_name ||' org '
1770: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1771: ||' and x.enabled_flag= 1'
1766: || lv_deleted_flag ||','
1767: || MSC_CL_PULL.v_instance_id
1768: || ' FROM msc_instance_orgs x,'
1769: || lv_table_name ||' org '
1770: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1771: ||' and x.enabled_flag= 1'
1772: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID'
1773: ||' and (('''||MSC_CL_PULL.v_org_group ||'''='''|| MSC_UTIL.G_ALL_ORGANIZATIONS||''' ) or (org_group ='''|| MSC_CL_PULL.v_org_group||'''))';
1774:
1769: || lv_table_name ||' org '
1770: ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1771: ||' and x.enabled_flag= 1'
1772: ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID'
1773: ||' and (('''||MSC_CL_PULL.v_org_group ||'''='''|| MSC_UTIL.G_ALL_ORGANIZATIONS||''' ) or (org_group ='''|| MSC_CL_PULL.v_org_group||'''))';
1774:
1775:
1776:
1777: for lv_item_id in -1001..-1000 loop
1785: and SR_CATEGORY_ID = lv_category_id
1786: and organization_id in
1787: ( select x.organization_id
1788: FROM msc_instance_orgs x
1789: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1790: and x.enabled_flag= 1
1791: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1792: );
1793:
1787: ( select x.organization_id
1788: FROM msc_instance_orgs x
1789: WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1790: and x.enabled_flag= 1
1791: and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1792: );
1793:
1794: commit;
1795:
1799:
1800: /*added for bug:4765403*/
1801: PROCEDURE LOAD_ABC_CLASSES IS
1802: BEGIN
1803: MSC_CL_PULL.v_table_name:= 'MSC_ST_ABC_CLASSES';
1804: MSC_CL_PULL.v_view_name:= 'MRP_AP_ABC_CLASSES';
1805:
1806: v_sql_stmt:=
1807: 'insert into MSC_ST_ABC_CLASSES'
1800: /*added for bug:4765403*/
1801: PROCEDURE LOAD_ABC_CLASSES IS
1802: BEGIN
1803: MSC_CL_PULL.v_table_name:= 'MSC_ST_ABC_CLASSES';
1804: MSC_CL_PULL.v_view_name:= 'MRP_AP_ABC_CLASSES';
1805:
1806: v_sql_stmt:=
1807: 'insert into MSC_ST_ABC_CLASSES'
1808: ||' (ABC_CLASS_ID,'
1815: ||' x.ABC_CLASS_NAME,'
1816: ||' x.ORGANIZATION_ID,'
1817: ||' x.ASSIGNMENT_GROUP_ID,'
1818: ||' :v_instance_id'
1819: ||' from MRP_AP_ABC_CLASSES_V'||MSC_CL_PULL.v_dblink||' x'
1820: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1821:
1822:
1823: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1819: ||' from MRP_AP_ABC_CLASSES_V'||MSC_CL_PULL.v_dblink||' x'
1820: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1821:
1822:
1823: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1824:
1825: COMMIT;
1826:
1827: END LOAD_ABC_CLASSES;