1 PACKAGE MSD_WS_DEM_RENAME_FORECAST AUTHID DEFINER AS
2 /* $Header: MSDWDRFS.pls 120.16.12020000.5 2013/02/04 07:47:42 kkhatri ship $ */
3
4 /*** CONSTANTS ***/
5 C_DUMMY_SCENARIO_ID_OFFSET NUMBER := 8888888;
6 C_ASSOCIATE_PARAMETER VARCHAR2(100) := 'Assign Plan Name';
7 C_YES NUMBER := 1;
8 C_NO NUMBER := 2;
9
10
11
12 /*** PROCEDURES & FUNCTIONS ***
13 * ASSIGN_PLAN_NAME_TO_FORECAST
14 * GET_PLAN_SCENARIO_MEMBER_ID
15 * REFRESH_MVIEW
16 */
17
18 /*
19 * Procedure Name - ASSIGN_PLAN_NAME_TO_FORECAST
20 * This procedure assigns a user specified name to the forecast
21 * uploaded from Demantra into the MSD_DP_SCN_ENTRIES_DENORM table.
22 *
23 * Parameters -
24 * NewPlanName - Name to be assigned to the recently exported
25 * forecast output.
26 * DataProfileName - Name of the Data Profile used to export
27 * forecast out of Demantra
28 *
29 * Given the parameter NewPlanName, create (or replace) an entry in the table MSD_DP_SCENARIOS.
30 * Demand Plan Id - (Hardcoded to) 5555555
31 * Scenario Id - Sequence starting from 8888888
32 *
33 * Using the DataProfileName, populate the table MSD_DP_SCENARIO_OUTPUT_LEVELS with
34 * the levels at which the forecast has been exported.
35 *
36 * Update the scenario id in the MSD_DP_SCN_ENTRIES_DENORM table to the Scenario Id generated
37 * for the given Plan Name.
38 *
39 * Return Values -
40 * The procedure returns a status. The possible return statuses are:
41 * SUCCESS, ERROR, INVALID_DATA_PROFILE
42 *
43 */
44 PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST (
45 status OUT NOCOPY VARCHAR2,
46 NewPlanName IN VARCHAR2,
47 DataProfileName IN VARCHAR2,
48 ArchiveFlag IN NUMBER );
49
50 PROCEDURE ASSIGN_PLAN_NAME_PUBLIC (
51 status OUT NOCOPY VARCHAR2,
52 UserName IN VARCHAR2,
53 RespName IN VARCHAR2,
54 RespApplName IN VARCHAR2,
55 SecurityGroupName IN VARCHAR2,
56 Language IN VARCHAR2,
57 NewPlanName IN VARCHAR2,
58 DataProfileName IN VARCHAR2,
59 ArchiveFlag IN NUMBER );
60
61 /*
62 * Procedure Name - ASSIGN_PLAN_NAME_TO_FORECAST
63 * This is made use of in the assign plan name concurrent program
64 */
65
66 PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST_C (
67 errbuf out NOCOPY varchar2,
68 retcode out NOCOPY varchar2,
69 NewPlanName IN VARCHAR2,
70 DataProfileName IN VARCHAR2,
71 ArchiveFlag IN NUMBER default 1,
72 p_parallelism_degree IN NUMBER DEFAULT 4);
73
74 /*
75 * Procedure Name - PUSH_ODS_DATA
76 * This procedure is made use of in the workflow- Export OBI Data
77 * to push demantra ODS data from export profile mview to the APCC table - msc_demantra_ods_f.
78 * It is a wrapper to APCC procedure - msc_phub_pkg.populate_demantra_ods.
79 */
80
81 PROCEDURE PUSH_ODS_DATA;
82
83 /*
84 * Function Name - GET_PLAN_SCENARIO_MEMBER_ID
85 * Given the id of a supply plan in ASCP, this function gets the plan scenario member id
86 * from Demantra.
87 *
88 * Parameters -
89 * PlanId - ID of the supply plan from the table MSC_PLANS.
90 *
91 * Return Values -
92 * The procedure returns the plan scenario member ID in Demantra. If not found or in case
93 * of any error it returns -1.
94 *
95 */
96 FUNCTION GET_PLAN_SCENARIO_MEMBER_ID (
97 PlanId IN NUMBER )
98 RETURN NUMBER;
99
100 /*
101 * Procedure Name - REFRESH_MVIEW
102 * Given the name of a materialized view, this procedure refreshed the mview
103 *
104 * Parameters -
105 * MviewName - ame of the materialized view
106 *
107 */
108 PROCEDURE REFRESH_MVIEW (
109 MviewName IN VARCHAR2 );
110
111 /*
112 * Procedure Name - DROP_MVIEW
113 * Given the name of a materialized view, this procedure drops the mview
114 *
115 * Parameters -
116 * MviewName - Name of the materialized view
117 *
118 */
119 PROCEDURE drop_mview(
120 mviewname IN VARCHAR2);
121
122 /*
123 * Procedure Name - DELETE_PLAN
124 * This procedure deletes the given assigned plan name
125 *
126 * 1. If the plan_name is assocaited to supply plans and p_delete_assoc is set NO
127 * then ERROR out with message
128 *
129 * 2. If the plan_name is assocaited to supply plans and p_delete_assoc is set YES
130 * (OR)
131 * If the plan_name is assocaited NOT to any supply plan
132 * then delete all the data for the plan_name from
133 * msd_dp_scenarios, msd_dp_scenario_output_levels,
134 * msd_dp_scn_entries_denorm, msc_plan_schedules tables
135 *
136 */
137 PROCEDURE DELETE_PLAN(
138 errbuf OUT NOCOPY VARCHAR2,
139 retcode OUT NOCOPY VARCHAR2,
140 p_plan_scn_id IN NUMBER,
141 p_delete_assoc IN NUMBER );
142
143
144 /*
145 * Procedure Name - UPDATE_DEM_APCC_SYNONYM
146 * This procedure creates the required dummy objets for APCC
147 * 1) Checks if demantra is installed and the mview created
148 * 1.1.a) If mview is available, drop it.
149 * 1.1.b) Create a new mview with the same name - BIEO_OBI_MV
150 * 1.2) If demantra is not installed, and dummy table available
151 * 1.2.a) Drop the dummy table
152 * 1.2.b) Create the dummy table - MSD_DEM_BIEO_OBI_MV_DUMMY
153 * 2) Create synonym MSD_DEM_BIEO_OBI_MV_SYN accordingly.
154 *
155 */
156
157 END MSD_WS_DEM_RENAME_FORECAST;
[Home] [Help]