[Home] [Help]
PACKAGE BODY: APPS.MSC_SET_DEMAND_PRIORITY
Source
1 PACKAGE BODY MSC_SET_DEMAND_PRIORITY AS
2 /* $Header: MSCDMPRB.pls 120.1 2005/07/06 11:45:54 pabram noship $ */
3
4
5 LOWEST_PRIORITY CONSTANT INTEGER := 100000;
6
7 FUNCTION MSC_DEMAND_PRIORITY(arg_plan_id IN NUMBER )
8 return NUMBER IS
9
10 TYPE char3000_arr IS TABLE of VARCHAR2(3000);
11
12 TYPE t_DemandSort is RECORD (
13 criteria char3000_arr );
14
15 v_DemandSort t_DemandSort;
16
17 DemandId MSC_DEMANDS.DEMAND_ID%TYPE;
18
19 TYPE RefCurType IS REF CURSOR;
20 cv RefCurType;
21
22
23 v_count NUMBER;
24 v_OrderClause VARCHAR2(2000) := NULL;
25 v_SelectClause VARCHAR2(100);
26 v_FromClause VARCHAR2(200);
27 v_WhereClause1 VARCHAR2(200);
28 v_WhereClause2 VARCHAR2(200);
29 v_WhereClause3 VARCHAR2(200);
30 v_SqlString VARCHAR2(3000);
31 demand_priority NUMBER := 1;
32 def_rule_id NUMBER;
33
34
35 BEGIN
36
37 /****************************************************/
38 /* Delete records in Demand_sort if found */
39 /****************************************************/
40 v_DemandSort.criteria.DELETE;
41
42 /****************************************************/
43 /* Get the default demand priority rule id */
44 /****************************************************/
45 SELECT RULE_ID
46 INTO def_rule_id
47 FROM msc_scheduling_rules
48 WHERE DEFAULT_FLAG = 'Y';
49
50
51 /****************************************************/
52 /* Get the criteria for ordering demands */
53 /****************************************************/
54 SELECT DECODE(MSR.meaning,
55 'Gross Margin',
56 '-md.USING_REQUIREMENT_QUANTITY*md.SELLING_PRICE',
57 'Sales Order Priority',
58 'DECODE(MSC_DEMANDS.ORIGINATION_TYPE,
59 6, NVL(md.SALES_ORDER_PRIORITY, 10000),
60 7, NVL(md.FORECAST_PRIORITY, 100000),
61 100000)',
62 'Schedule Date',
63 'md.USING_ASSEMBLY_DEMAND_DATE',
64 'Promise Date',
65 'NVL(md.PROMISE_DATE,
66 md.USING_ASSEMBLY_DEMAND_DATE)',
67 'Request Date',
68 'NVL(md.REQUEST_DATE,
69 md.USING_ASSEMBLY_DEMAND_DATE)',
70 'NULL')
71 bulk collect INTO v_DemandSort.criteria
72 FROM MSC_SCHEDULING_RULES MSR, MSC_PLANS MP
73 WHERE MSR.rule_id = NVL(MP.dem_priority_rule_id, def_rule_id)
74 AND MP.plan_id = arg_plan_id
75 ORDER BY MSR.sequence_number;
76
77
78 /****************************************************/
79 /* Now build the ORDER BY clause */
80 /****************************************************/
81 IF (v_DemandSort.criteria.count > 0)
82 THEN
83 v_OrderClause := v_DemandSort.criteria( 1 );
84 END IF;
85
86 IF (v_DemandSort.criteria.count > 1)
87 THEN
88 FOR v_count in 2 .. v_DemandSort.criteria.count LOOP
89 v_OrderClause :=
90 v_OrderClause || ', ' || v_DemandSort.criteria( v_count );
91 END LOOP;
92 END IF;
93
94 /****************************************************/
95 /* Get the SELECT, FROM and WHERE clause */
96 /****************************************************/
97 v_SelectClause := 'SELECT demand_id ';
98 v_FromClause := 'FROM msc_demands md, msc_plan_schedules mp ';
99 v_WhereClause1 := 'WHERE md.SCHEDULE_DESIGNATOR_ID =
100 mp.INPUT_SCHEDULE_ID ';
101 v_WhereClause2 := 'AND mp.PLAN_ID = :arg_plan_id ';
102 v_WhereClause3 := 'AND md.PLAN_ID = -1 ';
103
104 v_SqlString := v_SelectClause || v_FromClause || v_WhereClause1 ||
105 v_WhereClause2 || v_WhereClause3 ||
106 ' ORDER BY ' || v_OrderClause;
107
108 /****************************************************/
109 /* Open cursor for getting list of demands */
110 /****************************************************/
111 OPEN cv FOR v_SqlString USING arg_plan_id;
112 LOOP
113 FETCH cv INTO DemandId;
114 EXIT WHEN cv%NOTFOUND;
115
116 /****************************************************/
117 /* Update demand_priority in MSC_DEMANDS for this */
118 /* demand_id */
119 /****************************************************/
120 UPDATE msc_demands
121 SET DEMAND_PRIORITY = demand_priority
122 WHERE DEMAND_ID = DemandId
123 AND PLAN_ID = -1;
124
125 /****************************************************/
126 /* Increment demand priority by 1 for next demand */
127 /****************************************************/
128 demand_priority := demand_priority + 1;
129
130 END LOOP;
131
132 CLOSE cv;
133
134 return 1;
135
136 EXCEPTION WHEN NO_DATA_FOUND THEN
137 return 2;
138
139 END;
140
141 FUNCTION GET_INTERPLANT_DEMAND_PRIORITY(arg_plan_id IN NUMBER,
142 arg_trans_id IN NUMBER)
143 return NUMBER IS
144 p_dem_priority NUMBER := 0;
145 BEGIN
146
147 SELECT NVL(MIN(end_dem.demand_priority),
148 100000)
149 INTO p_dem_priority
150 FROM
151 msc_demands end_dem,
152 msc_full_pegging end_peg,
153 msc_full_pegging pegging,
154 msc_supplies sup
155 WHERE
156 end_dem.demand_id = end_peg.demand_id
157 AND end_dem.plan_id = end_peg.plan_id
158 AND end_peg.plan_id = pegging.plan_id
159 AND end_peg.pegging_id = pegging.end_pegging_id
160 AND pegging.plan_id = sup.plan_id
161 AND pegging.transaction_id = sup.transaction_id
162 AND sup.transaction_id = arg_trans_id
163 AND sup.plan_id = arg_plan_id;
164 return p_dem_priority;
165
166 END;
167
168 END MSC_SET_DEMAND_PRIORITY;
169