1 package CTO_CHANGE_ORDER_PK as
2 /* $Header: CTOCHODS.pls 120.5 2005/10/11 15:12:51 rkaza noship $ */
3
4 /********************************************************************************************************
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved, |
7 | Oracle Manufacturing |
8 | File Name : CTOCHODS.pls |
9 | |
10 | Description : Get the change order information from order management and send the |
11 | information to certain workflow users. |
12 | |
13 | History : Created on 10-aug-2000 by Renga Kannan |
14 | Modified 12/29/2000 by Renga Kannan |
15 | Modiefed 02/10/2001 by Renga Kannan -- Added new procedure for ML/MO case |
16 | Modified 02/17/2001 by Renga Kannan -- Changed the signature of |
17 | send_notification procedure added |
18 | Added multi level/multi org flag |
19 | 02/27/2001 by Renga Kannan -- Modified the signature for |
20 | change_notify made the line_id |
21 | argument as optional one as OM will |
22 | not pass the line_id in the case of |
23 | adding new option class/option item |
24 | in PTO-ATO |
25 | 03/13/2001 By Renga Kannan -- One more pkg variable is added |
26 | to handle the Delink action |
27 | from Sales order Pad |
28 | |
29 | |
30 | 09/20/2001 By Renga Kannan -- Modified the signature of the |
31 | Is_item_ml_or_mo, added source_type |
32 | as out variable |
33 |
34 | 02/04/2003 Kiran Konada
35 | Added a new paramter to pass conifg/ato item id
36 | to start_work_flow
37 | bugfix 2782394
38 |
39 | Modified on 14-MAR-2003 By Sushant Sawant
40 | Decimal-Qty Support for Option Items.
41 | Changed Signature of CHANGE_NOTIFY
42 |
43 | Modified on 01-Jun-2005 By Renga Kannan
44 | Added NOCOPY Hint
45
46 ********************************************************************************************************/
47
48
49
50
51 -- Change type constants
52 RD_CHANGE CONSTANT INTEGER := 10; -- Request date change
53 SSD_CHANGE CONSTANT INTEGER := 20; -- Schedule_ship_date change
54 SAD_CHANGE CONSTANT INTEGER := 30; -- Schedule_arrival_date change
55 QTY_CHANGE CONSTANT INTEGER := 40; -- Quantity Change
56 CONFIG_CHANGE CONSTANT INTEGER := 50; -- Configuration Change
57 DELINK_ACTION CONSTANT INTEGER := 60; -- Delink Action from Sales order pad
58 WAREHOUSE_CHANGE CONSTANT INTEGER := 70; -- Warehouse change
59 SPLIT_LINE_ACTION CONSTANT INTEGER := 80; -- Split Line Action
60 --new change type constants for R12 for OPM project
61 QTY2_CHANGE CONSTANT INTEGER :=90; --secondary quantity change
62 QTY2_UOM_CHANGE CONSTANT INTEGER :=100;--Secondary UOM chnage
63 QTY_UOM_CHANGE CONSTANT INTEGER := 110;--Primary quantity UOM change
64
65
66
67 -- The above Delink_action is added by Renga on 03/13/01 to send Notification during delink_item action.
68
69 -- Added by Renga Kannan on 03/13/2001. This pkg variable will act as an indicator
70 -- For the delink procedure. Whenever the delink procedure is called from CTO_CHANGE_ORDER_PKG
71 -- this variable will be set to 1 other wise this will have the default value as 0.
72 -- The delink procedure will look at this variable value and decide to call the notification.
73
74 CHANGE_ORDER_PK_STATE NUMBER := 0;
75
76
77 TYPE CHANGE_REC_TYPE IS RECORD(
78 change_type number(3),--change to 3 for new 3 digit constants
79 old_value varchar2(50),
80 new_value varchar2(50)
81 );
82
83
84 TYPE CHANGE_TABLE_TYPE IS TABLE OF CHANGE_REC_TYPE INDEX BY BINARY_INTEGER;
85
86
87
88 /* Added by Sushant for Decimal-Qty Support for Option Items */
89 /* for order quantity changes for option items */
90 TYPE OPTION_CHG_DETAILS_TYPE IS RECORD(
91 Line_id oe_order_lines.line_id%type ,
92 Action varchar2(50),
93 Old_Qty number ,
94 New_Qty number ,
95 Inventory_Item_id mtl_system_items.inventory_item_id%type
96 );
97
98 TYPE SPLIT_CHG_REC_TYPE IS RECORD(
99 Line_id oe_order_lines.line_id%type
100 );
101
102
103 /* Added by Sushant for Decimal-Qty Support for Option Items */
104 TYPE OPTION_CHG_TABLE_TYPE IS TABLE OF OPTION_CHG_DETAILS_TYPE INDEX BY BINARY_INTEGER;
105
106 TYPE SPLIT_CHG_TABLE_TYPE IS TABLE OF SPLIT_CHG_REC_TYPE INDEX BY BINARY_INTEGER;
107
108
109 v_option_chg_table OPTION_CHG_TABLE_TYPE ;
110 v_split_chg_table SPLIT_CHG_TABLE_TYPE ;
111
112
113 /********************************************************************************************************
114 + Name : Change_Notify +
115 + +
116 + Description : This funcation gets the input data from Order Management and look for the +
117 + type of change. Based on the type of change and the reservation exists for this +
118 + customer order this will invoke a notification workflow. +
119 + +
120 + Output : x_return_status will be success if this function is executed successfully +
121 + x_return_status will be error if this function errored out in some place +
122 + +
123 + +
124 + +
125 ********************************************************************************************************/
126
127 PROCEDURE Change_Notify(
128 pLineid in number DEFAULT NULL, -- Default clause is added by Renga on 02/27/01
129 Pchgtype in change_table_type,
130 X_return_status out Nocopy varchar2,
131 X_Msg_Count out NoCopy number,
132 X_Msg_Data out NoCopy varchar2,
133 PoptionChgDtls in OPTION_CHG_TABLE_TYPE default v_option_chg_table,
134 PsplitDtls in SPLIT_CHG_TABLE_TYPE default v_split_chg_table);
135
136
137 /********************************************************************************************************
138 + Name : Reservation_exists +
139 + +
140 + Description : This function checks whether any reservation made for this customer order +
141 + The reservation can be either inventory or wip. +
142 + +
143 + Output : x_result will be TRUE if reservation exists +
144 + x_result will be FALSE if reservation does not exists +
145 + +
146 ********************************************************************************************************/
147
148
149 PROCEDURE Reservation_Exists(
150 Pconfiglineid in number,
151 X_return_status out Nocopy varchar2,
152 X_result out Nocopy boolean,
153 X_Msg_Count out Nocopy number,
154 X_Msg_Data out Nocopy varchar2) ;
155
156 /********************************************************************************************************
157 + Name : Start_Work_Flow +
158 + +
159 + Description : This procedure set the attributes for workflow and start the workflow process +
160 + The workflow contains one activity of sending notification +
161 + +
162 ********************************************************************************************************/
163
164 PROCEDURE Start_Work_Flow(
165 pOrder_no in number,
166 pLine_no in number,
167 pchgtype in change_table_type,
168 pmlmo_flag in varchar2,
169 pconfig_id in number,
170 x_return_status out Nocopy varchar2,
171 X_Msg_Count out Nocopy number,
172 X_Msg_Data out Nocopy varchar2,
173 PsplitDtls in SPLIT_CHG_TABLE_TYPE default v_split_chg_table );
174
175
176
177
178 /***********************************************************************************************************
179 * *
180 * *
181 * Procedure Name : Is_item_ML_OR_MO *
182 * *
183 * Input : PInventory_item_id *
184 * porg_id *
185 * *
186 * Output : X_result -- TRUE/FALSE *
187 * *
188 * Description : This procedure will check whether the given inventory_item in the given org is *
189 * eithe Multi level or Multi org. If either of them is true it will return TRUE. *
190 * If it is neither Multi level/Multi Org it will return FALSE *
191 * *
192 * *
193 ************************************************************************************************************/
194
195
196
197 PROCEDURE Is_item_ML_OR_MO(
198 pInventory_item_id IN mtl_system_items.inventory_item_id%type,
199 pOrg_id IN mtl_system_items.organization_id%type,
200 x_result OUT Nocopy Varchar2,
201 x_source_type OUT Nocopy Number,
202 x_return_status OUT Nocopy Varchar2,
203 x_msg_count OUT Nocopy Number,
204 x_msg_data OUT Nocopy Varchar2);
205
206
207
208 -- rkaza. ireq project. 05/11/2005. Helper procedure to do delete a record from
209 -- req interface table.
210 -- Start of comments
211 -- API name : delete_from_req_interface
212 -- Type : Public
213 -- Pre-reqs : None.
214 -- Function : Given orer line id, it deletes the corresponding req interface
215 -- records.
216 -- Parameters:
217 -- IN : p_line_id IN NUMBER Required
218 -- order line id.
219 -- IN : p_item_id IN number required
220 -- Version :
221 -- Initial version 115.20
222 -- End of comments
223 Procedure delete_from_req_interface(p_line_id IN Number,
224 p_item_id IN Number,
225 x_return_status OUT NOCOPY varchar2);
226
227
228
229 END CTO_CHANGE_ORDER_PK;
230
[Home] [Help]