[Home] [Help]
PACKAGE BODY: APPS.OKE_MDS_RELIEF_EXT
Source
1 PACKAGE BODY OKE_MDS_RELIEF_EXT AS
2 /* $Header: OKEXMRFB.pls 115.0 2002/12/04 08:45:26 alaw noship $ */
3
4 Procedure write_log ( mesg IN VARCHAR2 ) IS
5 Begin
6 fnd_file.put_line( fnd_file.log , mesg );
7 End write_log;
8
9
10 --
11 -- Name : Relief_Demand
12 -- Pre-reqs :
13 -- Function : This function returns the cost of sales account
14 -- for a given shipping delivery detail
15 --
16 --
17 -- Parameters :
18 -- IN : P_MDS_Rec OKE_MDS_RELIEF_PKG.mds_rec_type
19 --
20 -- OUT : None
21 --
22 -- Returns : None
23 --
24
25 PROCEDURE Relief_Demand
26 ( P_MDS_Rec IN OKE_MDS_RELIEF_PKG.mds_rec_type
27 ) IS
28
29 --
30 -- This cursor selects MDS schedules in a given organization.
31 --
32 -- You can modify the matching conditions to meet your requirements,
33 -- for example, searching for one or more specific MDS schedules.
34 --
35 CURSOR p IS
36 SELECT schedule_designator
37 FROM mrp_schedule_designators s
38 WHERE organization_id = P_mds_rec.organization_id
39 AND schedule_type = 1
40 AND nvl(disable_date , trunc(sysdate) + 1) > trunc(sysdate)
41 AND NOT EXISTS (
42 SELECT null
43 FROM mrp_schedule_dates
44 WHERE mps_transaction_id = P_mds_rec.mps_transaction_id
45 AND schedule_designator = s.schedule_designator )
46 /*
47 AND schedule_designator IN ('<schedule 1>' , '<schedule 2'>)
48 */
49 ORDER BY schedule_designator;
50
51 --
52 -- This cursor selects MDS entries for a given MDS that matches the
53 -- original demand that is being processed. The entry represented
54 -- by the value in MPS_TRANSACTION_ID is already processed by the base
55 -- program so there is no need to re-relieve the same demand.
56 --
57 -- You can modify the matching conditions to meet your requirements.
58 --
59 CURSOR e ( X_schedule_designator VARCHAR2 ) IS
60 SELECT mps_transaction_id
61 , schedule_quantity
62 FROM mrp_schedule_dates
63 WHERE schedule_designator = X_schedule_designator
64 AND organization_id = P_mds_rec.organization_id
65 AND inventory_item_id = P_mds_rec.inventory_item_id
66 AND mps_transaction_id <> P_mds_rec.mps_transaction_id
67 AND schedule_date = P_mds_rec.transaction_date
68 AND project_id = P_mds_rec.project_id
69 AND task_id = P_mds_rec.task_id
70 AND end_item_unit_number = P_mds_rec.unit_number
71 AND schedule_level = 2
72 AND schedule_quantity > 0
73 ORDER BY schedule_date , mps_transaction_id
74 FOR UPDATE OF schedule_quantity;
75
76 l_remain_qty number;
77 l_relief_qty number;
78 l_new_demand_qty number;
79
80 BEGIN
81 --
82 -- To enable this extension, please comment the following return
83 -- statement and make the necessary changes to the sample processing
84 -- logic.
85 --
86 return;
87
88 write_log( '+++ Invoking custom extension...' );
89
90 for prec in p loop
91
92 write_log( '+++ Processing schedule ' || prec.schedule_designator );
93
94 l_remain_qty := (-1) * P_mds_rec.primary_quantity;
95
96 for erec in e ( prec.schedule_designator ) loop
97
98 write_log( '+++ MDS Txn ID ......... ' || erec.mps_transaction_id );
99 --
100 -- The following logic determines the quantity to be relieved.
101 -- If the shipment quantity is greater than the remaining schedule
102 -- quantity, then the lesser of the two is used.
103 --
104 if ( erec.schedule_quantity < l_remain_qty ) then
105 l_relief_qty := erec.schedule_quantity;
106 else
107 l_relief_qty := l_remain_qty;
108 end if;
109 l_new_demand_qty := erec.schedule_quantity - l_relief_qty;
110
111 write_log( '+++ Original MDS qty ... ' || erec.schedule_quantity );
112 write_log( '+++ Relief qty ......... ' || l_relief_qty );
113 write_log( '+++ New MDS qty ........ ' || l_new_demand_qty );
114
115 --
116 -- The following updates the MDS record with the new quantity
117 --
118 update mrp_schedule_dates
119 set schedule_quantity = l_new_demand_qty
120 , last_update_date = sysdate
121 , last_updated_by = fnd_global.user_id
122 , request_id = fnd_global.conc_request_id
123 , program_application_id = fnd_global.prog_appl_id
124 , program_id = fnd_global.conc_program_id
125 , program_update_date = sysdate
126 where mps_transaction_id = erec.mps_transaction_id
127 and schedule_level = 2;
128
129 write_log( '+++ MDS entry updated' );
130
131 --
132 -- The following creates the relief record. The relief quantity
133 -- is based on the entire shipment quantity, not the actual
134 -- quantity relieved as calculated previously.
135 --
136 INSERT INTO mrp_schedule_consumptions
137 ( transaction_id
138 , relief_type
139 , disposition_type
140 , disposition_id
141 , line_num
142 , last_update_date
143 , last_updated_by
144 , creation_date
145 , created_by
146 , last_update_login
147 , request_id
148 , program_application_id
149 , program_id
150 , program_update_date
151 , order_date
152 , order_quantity
153 , relief_quantity
154 , schedule_date
155 ) VALUES
156 ( erec.mps_transaction_id
157 , 1 -- MDS_RELIEF 1, MPS_RELIEF 2
158 , 3 -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
159 , null
160 , null
161 , sysdate
162 , fnd_global.user_id
163 , sysdate
164 , fnd_global.user_id
165 , fnd_global.login_id
166 , fnd_global.conc_request_id
167 , fnd_global.prog_appl_id
168 , fnd_global.conc_program_id
169 , sysdate
170 , P_mds_rec.transaction_date
171 , P_mds_rec.order_quantity
172 , l_relief_qty
173 , P_mds_rec.transaction_date
174 );
175
176 write_log( '+++ Relief record created' );
177
178 l_remain_qty := l_remain_qty - l_relief_qty;
179
180 EXIT WHEN l_remain_qty = 0;
181
182 end loop; -- cursor e
183
184 write_log( '+++ Done processing schedule ' || prec.schedule_designator );
185
186 end loop; -- cursor p
187
188 write_log( '+++ End of custom extension' || fnd_global.newline );
189
190 END Relief_Demand;
191
192 END OKE_MDS_RELIEF_EXT;