eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_SDA_UTILS dependencies on MSC_FORM_QUERY

Line 86: select msc_form_query_s.nextval

82: end addToOutStream;
83:
84: function getNewFormQueryId return number is
85: cursor c_query is
86: select msc_form_query_s.nextval
87: from dual;
88: l_query_id number;
89: begin
90: open c_query;

Line 136: from msc_form_query

132: number1,
133: number2,
134: date1,
135: date2
136: from msc_form_query
137: where query_id = ll_query_id
138: and number4 = ll_level;
139:
140: cursor c_chain_cur(ll_query_id number) is

Line 146: from msc_form_query

142: number1,
143: number2,
144: date1,
145: date2
146: from msc_form_query
147: where query_id = ll_query_id;
148:
149: cursor c_ods_data_cur(l_number1 number, l_number2 number) is
150: select b.effective_date,

Line 168: insert into msc_form_query(query_id,

164: l_found boolean := false;
165: begin
166: l_query_id := getNewFormQueryId;
167:
168: insert into msc_form_query(query_id,
169: creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
170: number5, number6, number7, date1, date2)
171: select l_query_id,sysdate, -1, -1, sysdate,
172: b.lower_item_id, b.higher_item_id, b.highest_item_id, l_level, b.reciprocal_flag,

Line 193: insert into msc_form_query(query_id,

189: l_found := false;
190: l_level := l_level + 1;
191: for c_curr_row in c_curr_level_cur(l_query_id, l_level-1)
192: loop
193: insert into msc_form_query(query_id,
194: creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
195: number5, number6, number7, date1, date2)
196: select l_query_id,sysdate, -1, -1, sysdate,
197: b.lower_item_id, b.higher_item_id, b.highest_item_id, l_level, b.reciprocal_flag,

Line 231: update msc_form_query

227: open c_ods_data_cur(c_chain_row.number1, c_chain_row.number2);
228: fetch c_ods_data_cur into l_st_date, l_end_date;
229: close c_ods_data_cur;
230:
231: update msc_form_query
232: set date1=l_st_date, date2=l_end_date
233: where query_id = l_query_id
234: and number1 = c_chain_row.number1
235: and number2 = c_chain_row.number2;

Line 267: from msc_form_query mfq

263:
264: function is_chain_duplicate(ll_qid number, l_highest_item_id number, ll_chain_id number) return number is
265: cursor c_check_cur is
266: select count(*)
267: from msc_form_query mfq
268: where mfq.query_id = ll_qid
269: and mfq.number8 = 100
270: and mfq.number9 <> ll_chain_id
271: and l_highest_item_id in (mfq.number1, mfq.number2, mfq.number3);

Line 326: msc_form_query mfq

322:
323: cursor c_highest_item_cur (ll_query_id number) is
324: select distinct mis.highest_item_id
325: from msc_item_substitutes mis,
326: msc_form_query mfq
327: where mis.plan_id = p_plan
328: and mis.relationship_type = c_mis_supersession_type
329: and mis.inferred_flag = 2
330: and mis.forward_rule = 1

Line 341: from msc_form_query mfq

337: --order by mis.effective_date desc;
338:
339: cursor c_chain_cur (ll_query_id number) is
340: select distinct mfq.number3, mfq.number9
341: from msc_form_query mfq
342: where mfq.query_id = ll_query_id
343: and mfq.number8 = 100
344: and mfq.number2 = mfq.number3
345: order by 2,1;

Line 359: insert into msc_form_query(query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number8)

355: --get all items with same group id and insert into l_qid1 and number8 with values 1,2
356: for c_row in c_grouped_items_cur
357: loop
358: l_flag := is_item_in_chain(p_plan, c_row.inventory_item_id);
359: insert into msc_form_query(query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number8)
360: values (l_qid1, sysdate, -1, -1, sysdate, c_row.inventory_item_id, l_flag);
361: println('c_grouped_items_cur item_id name '||c_row.inventory_item_id||msc_get_name.item_name(c_row.inventory_item_id, null, null, null));
362: end loop;
363:

Line 371: insert into msc_form_query(query_id,

367: println('c_highest_item_cur item_id name '||c_row.highest_item_id||msc_get_name.item_name(c_row.highest_item_id, null, null, null));
368: l_level := l_level + 1000;
369: l_chain_id := l_chain_id +1;
370: l_query_id := flushSupersessionChainNew(p_plan, c_row.highest_item_id);
371: insert into msc_form_query(query_id,
372: creation_date, created_by, last_updated_by, last_update_date,
373: number1, number2, number3, number4,
374: number5, number6, number7, date1, date2, number8, number9,number10)
375: select l_qid1, sysdate, -1, -1, sysdate,

Line 378: from msc_form_query

374: number5, number6, number7, date1, date2, number8, number9,number10)
375: select l_qid1, sysdate, -1, -1, sysdate,
376: number1, number2, number3, number4+l_level,
377: number5, number6, number7, date1, date2, 100, l_chain_id,2
378: from msc_form_query
379: where query_id = l_query_id
380: order by number3, number4 desc;
381: end loop;
382:

Line 389: update msc_form_query

385: loop
386: l_flag := is_chain_duplicate(l_qid1, c_row.number3, c_row.number9);
387: println('c_chain_cur qid number3, name number9 l_flag '||l_qid1||' - '||c_row.number3||' - '||msc_get_name.item_name(c_row.number3, null, null, null)||' - '||c_row.number9||' - '||l_flag);
388: if l_flag = 1 then
389: update msc_form_query
390: set number10 = 1
391: where query_id = l_qid1
392: and number9 = c_row.number9;
393: end if;

Line 397: insert into msc_form_query(query_id,

393: end if;
394: end loop;
395:
396: --insert unique chains
397: insert into msc_form_query(query_id,
398: creation_date, created_by, last_updated_by, last_update_date,
399: number1, number2, number3, number4,
400: number5, number6, number7, date1, date2, number8, number9,number10)
401: select l_related_query_id, sysdate, -1, -1, sysdate,

Line 404: from msc_form_query

400: number5, number6, number7, date1, date2, number8, number9,number10)
401: select l_related_query_id, sysdate, -1, -1, sysdate,
402: number1, number2, number3, number4,
403: number5, number6, number7, date1, date2, number8, number9,number10
404: from msc_form_query
405: where query_id = l_qid1
406: and number8 = 100
407: and number10 = 2
408: order by number3, number4 desc;

Line 411: insert into msc_form_query(query_id,

407: and number10 = 2
408: order by number3, number4 desc;
409: /*
410: --insert dangling items [not part of any chain]
411: insert into msc_form_query(query_id,
412: creation_date, created_by, last_updated_by, last_update_date,
413: number1, number2, number3, number4,
414: number5, number6, number7, date1, date2, number8, number9,number10)
415: select l_related_query_id, sysdate, -1, -1, sysdate,

Line 418: from msc_form_query

414: number5, number6, number7, date1, date2, number8, number9,number10)
415: select l_related_query_id, sysdate, -1, -1, sysdate,
416: number1, number2, number1 number3, -1,
417: number5, number6, number7, date1, date2, number8, number9,number10
418: from msc_form_query
419: where query_id = l_qid1
420: and number8 = 2
421: order by number1 desc;
422: */

Line 434: insert into msc_form_query(query_id,

430: for c_row in c_highest_cur
431: loop
432: l_level := l_level + 1000;
433: l_query_id := flushSupersessionChainNew(p_plan, c_row.highest_item_id);
434: insert into msc_form_query(query_id,
435: creation_date, created_by, last_updated_by, last_update_date,
436: number1, number2, number3, number4,
437: number5, number6, number7, date1, date2)
438: select l_related_query_id, sysdate, -1, -1, sysdate,

Line 441: from msc_form_query

437: number5, number6, number7, date1, date2)
438: select l_related_query_id, sysdate, -1, -1, sysdate,
439: number1, number2, number3, number4+l_level,
440: number5, number6, number7, date1, date2
441: from msc_form_query
442: where query_id = l_query_id
443: order by number3, number4 desc;
444: end loop;
445: return l_related_query_id;

Line 455: insert into msc_form_query(query_id,

451:
452: --not used code below
453: l_query_id := getNewFormQueryId;
454:
455: insert into msc_form_query(query_id,
456: creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
457: number5, number6, number7, date1, date2)
458: select l_query_id,sysdate, -1, -1, sysdate,
459: b.lower_item_id, b.higher_item_id, b.highest_item_id, level,

Line 720: msc_form_query mfq

716: md.organization_id org_id,
717: msc_get_name.org_code(md.organization_id, md.sr_instance_id) org_code,
718: md.inventory_item_id
719: from msc_demands md,
720: msc_form_query mfq
721: where mfq.query_id = p_region_query_id
722: and md.plan_id = p_plan_id
723: and md.zone_id = mfq.number2;
724: */

Line 736: msc_form_query mfq

732: --msc_region_locations mrl,
733: --msc_location_associations mla,
734: msc_trading_partners mtp,
735: msc_plan_organizations mpo,
736: msc_form_query mfq
737: --,msc_zone_regions mzr
738: where mfq.query_id = p_region_query_id
739: and nvl(mfq.number2,-1) > 0
740: --and mzr.parent_region_id = mfq.number2

Line 802: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

798:
799: for c_regions in c_regions_cur(ll_view_type)
800: loop
801: println('regions loop in');
802: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
803: number1, char1, number2, char2, number3)
804: values (p_region_query_id, sysdate, -1, -1, sysdate,
805: c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
806:

Line 808: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

804: values (p_region_query_id, sysdate, -1, -1, sysdate,
805: c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
806:
807: --insert a global org for each region
808: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
809: number1, number2, number3, char1, number4)
810: values (p_org_query_id, sysdate, -1, -1, sysdate,
811: c_regions.region_id, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
812:

Line 817: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

813: println('regions loop out');
814: end loop;
815:
816: --insert one global orgs for global without region_id
817: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
818: number1, number2, number3, char1, number4)
819: values (p_org_query_id, sysdate, -1, -1, sysdate,
820: c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
821:

Line 823: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

819: values (p_org_query_id, sysdate, -1, -1, sysdate,
820: c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
821:
822: --add global region
823: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
824: number1, char1, number2, char2, number3)
825: values (p_region_query_id, sysdate, -1, -1, sysdate,
826: to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
827:

Line 829: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

825: values (p_region_query_id, sysdate, -1, -1, sysdate,
826: to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
827:
828: --add local region
829: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
830: number1, char1, number2, char2, number3)
831: values (p_region_query_id, sysdate, -1, -1, sysdate,
832: to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
833:

Line 835: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

831: values (p_region_query_id, sysdate, -1, -1, sysdate,
832: to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
833:
834: --insert one global orgs for local with region_id for usage based forecast local
835: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
836: number1, number2, number3, char1, number4)
837: values (p_org_query_id, sysdate, -1, -1, sysdate,
838: c_local_reg_type, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
839:

Line 844: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

840: for c_orgs in c_orgs_cur(ll_view_type, ll_inst_id, ll_org_id)
841: loop
842: println('region-orgs loop in');
843: --insert orgs for regions
844: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
845: number1, number2, number3, char1, number4)
846: values (p_org_query_id, sysdate, -1, -1, sysdate,
847: c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
848:

Line 850: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

846: values (p_org_query_id, sysdate, -1, -1, sysdate,
847: c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
848:
849: --insert orgs for global
850: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
851: number1, number2, number3, char1, number4)
852: values (p_org_query_id, sysdate, -1, -1, sysdate,
853: c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
854:

Line 856: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

852: values (p_org_query_id, sysdate, -1, -1, sysdate,
853: c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
854:
855: --insert orgs for local
856: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
857: number1, number2, number3, char1, number4)
858: values (p_org_query_id, sysdate, -1, -1, sysdate,
859: c_local_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
860: end loop;

Line 908: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

904: l_query_id := getNewFormQueryId;
905: for c_orgs in c_orgs_cur
906: loop
907: println('inserting +');
908: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
909: number1, char1, number2, number3, char4, number4)
910: values (l_query_id, sysdate, -1, -1, sysdate,
911: c_orgs.org_list_id, c_orgs.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.sort_column);
912: end loop;

Line 997: from msc_form_query mfq

993: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
994: mfq.number2 item_id,
995: msc_get_name.item_name(mfq.number2,null, null, null) item_name,
996: mfq.number3 sort_column
997: from msc_form_query mfq
998: where mfq.query_id = l_rqid_sorted
999: order by sort_column desc;
1000:
1001: l_query_id number;

Line 1008: from msc_form_query

1004: l_sysdate date := trunc(sysdate);
1005:
1006: cursor c_related_cur is
1007: select number1, number2, number3
1008: from msc_form_query
1009: where query_id = l_rqid
1010: order by number1, number4 desc;
1011:
1012: cursor c_related_check_cur(ll_query_id number, ll_item_id number) is

Line 1014: from msc_form_query

1010: order by number1, number4 desc;
1011:
1012: cursor c_related_check_cur(ll_query_id number, ll_item_id number) is
1013: select count(*)
1014: from msc_form_query
1015: where query_id = ll_query_id
1016: and number2 = ll_item_id;
1017:
1018: l_temp number;

Line 1032: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)

1028: fetch c_related_check_cur into l_temp;
1029: close c_related_check_cur;
1030: if (l_temp = 0) then
1031: l_order := l_order + 1;
1032: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
1033: values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number1, l_order);
1034: end if;
1035:
1036: open c_related_check_cur(l_rqid_sorted, c_rchain.number2);

Line 1041: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)

1037: fetch c_related_check_cur into l_temp;
1038: close c_related_check_cur;
1039: if (l_temp = 0) then
1040: l_order := l_order + 1;
1041: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
1042: values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number2, l_order);
1043: end if;
1044:
1045: if (c_rchain.number2 = c_rchain.number3) then

Line 1051: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)

1047: fetch c_related_check_cur into l_temp;
1048: close c_related_check_cur;
1049: if (l_temp = 0) then
1050: l_order := l_order + 1;
1051: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3)
1052: values (l_rqid_sorted, sysdate, -1, -1, sysdate, p_item_id, c_rchain.number3, l_order);
1053: end if;
1054: end if;
1055:

Line 1063: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)

1059: l_query_id := getNewFormQueryId;
1060: for c_sschain in c_sschain_cur
1061: loop
1062: println(' populating into chain '||c_sschain.item_name);
1063: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
1064: values (l_query_id, sysdate, -1, -1, sysdate, c_sschain.top_item_id, c_sschain.top_item_name, c_sschain.item_id, c_sschain.item_name,
1065: c_sschain.sort_column);
1066: if (c_sschain.item_id = p_item_id) then
1067: l_found := true;

Line 1074: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)

1070:
1071: if (p_item_view_type in (c_prime_view, c_supersession_view, c_ritems_view) ) then
1072: if (l_found = false) then
1073: l_item_name := msc_get_name.item_name(p_item_id, null, null, null);
1074: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
1075: values (l_query_id, sysdate, -1, -1, sysdate, p_item_id, l_item_name, p_item_id, l_item_name, 1);
1076: end if;
1077: end if;
1078: return l_query_id;

Line 1104: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,

1100: loop
1101: l_first_day := to_date( '01/' || l_month || '/' ||l_year, 'DD/MM/YYYY');
1102: l_last_day := last_day(l_first_day);
1103:
1104: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
1105: date1, date2, number1)
1106: values (l_query_id, sysdate, -1, -1, sysdate, l_first_day, l_last_day, l_date_index);
1107:
1108: if (l_last_day > l_end_date) then

Line 1195: msc_form_query mfq

1191: nvl(mun.last_update_date, mun.creation_date) comment_date,
1192: msc_get_name.item_name(mun.inventory_item_id, null, null, null) item_name,
1193: substr(mun.note_text1,1,80) comment_text
1194: from msc_user_notes mun,
1195: msc_form_query mfq
1196: where
1197: mun.entity_type = c_comment_entity_type
1198: and mun.inventory_item_id in (mfq.number1, number2)
1199: and mfq.query_id = p_chain_query_id

Line 1294: msc_form_query mfq, --items

1290: msc_get_name.lookup_meaning('SYS_YES_NO', nvl(msiv.INTERMITTENT_DEMAND,2)) INTERMITTENT_DEMAND,
1291: msiv.sr_instance_id,
1292: msiv.inventory_item_id
1293: from msc_system_items_sc_v msiv,
1294: msc_form_query mfq, --items
1295: msc_form_query mfq1 --orgs
1296: where plan_id = p_plan_id
1297: and category_set_id = l_category_set_id
1298: and inventory_item_id in (mfq.number2) --, number1)

Line 1295: msc_form_query mfq1 --orgs

1291: msiv.sr_instance_id,
1292: msiv.inventory_item_id
1293: from msc_system_items_sc_v msiv,
1294: msc_form_query mfq, --items
1295: msc_form_query mfq1 --orgs
1296: where plan_id = p_plan_id
1297: and category_set_id = l_category_set_id
1298: and inventory_item_id in (mfq.number2) --, number1)
1299: and mfq.query_id = p_chain_query_id

Line 1437: msc_form_query mfq, --items

1433: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type) exception_type_text,
1434: count(*) excp_all_count,
1435: sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
1436: from msc_exception_details med,
1437: msc_form_query mfq, --items
1438: msc_form_query mfq1 --orgs
1439: where med.plan_id = p_plan_id
1440: and med.inventory_item_id = mfq.number2
1441: and mfq.query_id = p_chain_query_id

Line 1438: msc_form_query mfq1 --orgs

1434: count(*) excp_all_count,
1435: sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
1436: from msc_exception_details med,
1437: msc_form_query mfq, --items
1438: msc_form_query mfq1 --orgs
1439: where med.plan_id = p_plan_id
1440: and med.inventory_item_id = mfq.number2
1441: and mfq.query_id = p_chain_query_id
1442: and mfq1.query_id = p_org_query_id

Line 1455: msc_form_query mfq, --items

1451: count(*) excp_all_count,
1452: 0 excp_no_count
1453: from msc_supplies ms,
1454: msc_system_items msi,
1455: msc_form_query mfq, --items
1456: msc_form_query mfq1 --orgs
1457: where ms.plan_id = p_plan_id
1458: and ms.inventory_item_id = mfq.number2
1459: and mfq.query_id = p_chain_query_id

Line 1456: msc_form_query mfq1 --orgs

1452: 0 excp_no_count
1453: from msc_supplies ms,
1454: msc_system_items msi,
1455: msc_form_query mfq, --items
1456: msc_form_query mfq1 --orgs
1457: where ms.plan_id = p_plan_id
1458: and ms.inventory_item_id = mfq.number2
1459: and mfq.query_id = p_chain_query_id
1460: and mfq1.query_id = p_org_query_id