APPS.MSC_ANALYSIS_PKG dependencies on MSC_FORM_QUERY

Line 78: update msc_form_query

74: l_plan_type := p_plan_type;
75: l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
76: l_cat_set_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, l_plan_type);
77:
78: update msc_form_query
79: set number1 = l_cat_set_id,
80: number2 = p_plan_type
81: where query_id = l_mfq_query_id;
82: if (sql%rowcount = 0) then

Line 83: INSERT INTO msc_form_query (QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, NUMBER1, NUMBER2) values

79: set number1 = l_cat_set_id,
80: number2 = p_plan_type
81: where query_id = l_mfq_query_id;
82: if (sql%rowcount = 0) then
83: INSERT INTO msc_form_query (QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, NUMBER1, NUMBER2) values
84: (l_mfq_query_id, sysdate, -1, sysdate, -1, l_cat_set_id, p_plan_type);
85: end if;
86: end store_user_pref;
87:

Line 185: select msc_form_query_s.nextval

181: END get_cat_list;
182:
183: FUNCTION get_form_seq_id RETURN NUMBER is
184: cursor c_seq is
185: select msc_form_query_s.nextval
186: from dual;
187:
188: l_seq_id number;
189: BEGIN

Line 353: l_select := ' INSERT INTO msc_form_query ( '||

349: l_calendar_type := g_calendar_type_mfg;
350: end if;
351:
352:
353: l_select := ' INSERT INTO msc_form_query ( '||
354: ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
355: ' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
356: ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
357: ' NUMBER11, NUMBER12, CHAR5) ';

Line 401: --Form query to select from msc_form_query (used for view by plan, org, categ, plan-org-categ)

397: l_pr_groupby := ' GROUP BY detail_date, detail_level, period_type, period_type_url,plan_id, plan_name ';
398: l_plan_org_cate_groupby := ' GROUP BY detail_date, plan_id, plan_name, '
399: ||' sr_instance_id, organization_id, org_code, category_id, category_name ';
400:
401: --Form query to select from msc_form_query (used for view by plan, org, categ, plan-org-categ)
402: --Inventory value has to be sumed across categories, orgs but has to be averaged across periods
403: --To achieve this, we have 2 floow a 2 step process
404: --first insert into mfq inv values whuch are sumed for the view by (plan , org or categ)
405: --group by the period.

Line 409: l_mfq_select := ' INSERT INTO msc_form_query ( '||

405: --group by the period.
406: --second avg the the inv value inserted into mfq -using the total number of periods in the plan
407: --this calcualted avg in values are inserted into mfq to be used in the java layer.
408:
409: l_mfq_select := ' INSERT INTO msc_form_query ( '||
410: ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
411: ' NUMBER5, NUMBER6, NUMBER7, NUMBER8, NUMBER9, NUMBER10, '||
412: ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, NUMBER4, CHAR3, DATE1, CHAR4, '||
413: ' NUMBER11, NUMBER12, CHAR5 ) ';

Line 423: l_mfq_from := ' from msc_form_query mfq1 ';

419: ' round(sum(mfq1.NUMBER8)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
420: ' round(sum(mfq1.NUMBER9)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||' ), '||
421: ' round(sum(mfq1.NUMBER10)/MSC_ANALYSIS_PKG.get_num_periods(mfq1.NUMBER1, '||l_calendar_type||'), '|| l_round ||'), ';
422:
423: l_mfq_from := ' from msc_form_query mfq1 ';
424:
425: l_mfq_plan_insert := ' mfq1.number1, mfq1.char1, '||
426: ' to_number(null), to_number(null), to_char(null), '||
427: ' to_number(null), to_char(null), to_date(null), mfq1.char1, '||

Line 651: INSERT INTO msc_form_query (

647: --multiple plan selected, insert last record of first plan
648: if(l_cur_plan_id <> l_plan_id) and (l_end_date is not null) then
649:
650: l_temp_date := l_end_date +1;
651: INSERT INTO msc_form_query (
652: QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
653: NUMBER1, number2, number3, date1, date2 )
654: values
655: ( l_mfq_query_id , sysdate, -1, sysdate, -1,

Line 667: INSERT INTO msc_form_query (

663: l_start_date := l_date;
664: else
665: l_end_date := l_date - 1;
666:
667: INSERT INTO msc_form_query (
668: QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
669: NUMBER1, number2, number3, date1, date2 )
670: values
671: ( l_mfq_query_id , sysdate, -1, sysdate, -1,

Line 682: INSERT INTO msc_form_query (

678: --to insert the last record for mbid on l_end_date
679: if(l_end_date is not null) then
680: l_end_date := l_end_date +1;
681:
682: INSERT INTO msc_form_query (
683: QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
684: NUMBER1, number2, number3, date1, date2 )
685: values
686: ( l_mfq_query_id , sysdate, -1, sysdate, -1,

Line 757: l_select := ' INSERT INTO msc_form_query ( '||

753: return;
754: end if;
755: l_round := get_valid_rounding(arg_round, -23453);
756:
757: l_select := ' INSERT INTO msc_form_query ( '||
758: ' QUERY_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, '||
759: ' NUMBER10, NUMBER11, NUMBER12,'||
760: ' NUMBER1, CHAR1, NUMBER2, NUMBER3, CHAR2, '||
761: ' NUMBER4, CHAR3, '||

Line 1094: l_select := ' insert into msc_form_query ( '||

1090: l_round := get_valid_rounding(arg_round, -23453);
1091:
1092: l_mfq_query_id := populate_bis_dates(arg_plan_list, arg_viewby, arg_period_type, arg_detail_level);
1093:
1094: l_select := ' insert into msc_form_query ( '||
1095: ' query_id, last_update_date, last_updated_by, creation_date, created_by, '||
1096: ' number6, number7, number8, number9, number10, number11, number12, number13, number16,'||
1097: ' number17, number18, number19, number20,'||
1098: ' number1, char1, number2, number3, char2, '||

Line 1466: INSERT INTO MSC_FORM_QUERY

1462: l_inv_value := l_inv_value/ l_period_count;
1463: end if;
1464: l_round := get_valid_rounding(arg_round, -23453);
1465:
1466: INSERT INTO MSC_FORM_QUERY
1467: (
1468: query_id,
1469: last_update_date,
1470: last_updated_by,