1 PACKAGE WSH_USA_ACTIONS_PVT AUTHID CURRENT_USER as
2 /* $Header: WSHUSAAS.pls 120.2 2010/10/08 08:11:28 brana ship $ */
3
4 TYPE Split_Table_Type is RECORD (
5 source_line_id NUMBER ,
6 original_source_line_id NUMBER ,
7 changed_Attributes_index NUMBER ,
8 direction_flag VARCHAR2(1) default 'F' ,
9 date_requested DATE );
10
11 TYPE Split_Table_Tab_Type IS TABLE OF Split_Table_Type
12 INDEX BY BINARY_INTEGER;
13
14 --bugfix 8915868 added record structure
15
16 TYPE Cancel_Reservation_Table_Type is RECORD (
17 source_code wsh_delivery_details.source_code%type,
18 source_header_id NUMBER,
19 source_line_id NUMBER,
20 delivery_detail_id NUMBER,
21 organization_id NUMBER,
22 cancelled_quantity NUMBER,
23 cancelled_quantity2 NUMBER
24 );
25
26 TYPE Cancel_Reservation_Tab_Type IS TABLE OF Cancel_Reservation_Table_Type
27 INDEX BY BINARY_INTEGER;
28
29 TYPE direction_flag_tab_type IS TABLE OF VARCHAR2(1)
30 INDEX BY BINARY_INTEGER;
31
32
33 CURSOR c_check_ship_sets (c_p_set_id NUMBER,
34 c_p_source_header_id NUMBER) IS -- 2373131
35 select wdd.ship_set_id from
36 wsh_delivery_details wdd where
37 wdd.ship_set_id = c_p_set_id and
38 wdd.source_code = 'OE' and
39 wdd.source_header_id = c_p_source_header_id and
40 ((wdd.released_status = 'C') or exists (select wda.delivery_detail_id
41 from wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
42 where wda.delivery_detail_id = wdd.delivery_detail_id
43 and wda.delivery_id = wnd.delivery_id
44 and wnd.status_code in ('CO', 'IT', 'CL', 'SR', 'SC')))
45 AND rownum = 1;
46
47 --
48 -- Procedure: Import_Records
49 -- Parameters:
50 -- p_source_code source system of records to import
51 -- p_changed_attributes list of records to process
52 -- x_return_status return status
53 --
54 -- Description:
55 -- Import source lines where action_flag = 'I'
56 --
57 PROCEDURE Import_Records(
58 p_source_code IN VARCHAR2,
59 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
60 x_return_status OUT NOCOPY VARCHAR2);
61
62
63 --
64 -- Procedure: Split_Records
65 -- Parameters:
66 -- p_source_code source system of records to split
67 -- p_changed_attributes list of records to process
68 -- p_interface_flag flag identifying the session
69 -- 'Y' - called during OM Interface,
70 -- 'N' - normal session
71 -- x_return_status return status
72 --
73 -- Description:
74 -- Determine whether the call is initated by OM Interface
75 -- or a normal process.
76 -- Then split the records accordingly where action_flag = 'S'.
77 --
78 PROCEDURE Split_Records(
79 p_source_code IN VARCHAR2,
80 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
81 p_interface_flag IN VARCHAR2,
82 x_return_status OUT NOCOPY VARCHAR2);
83
84
85 --
86 -- Procedure: Update_Records
87 -- Parameters:
88 -- p_source_code source system of records to update
89 -- p_changed_attributes list of records to process
90 -- p_interface_flag flag identifying the session
91 -- 'Y' - called during OM Interface,
92 -- 'N' - normal session
93 -- x_return_status return status
94 --
95 -- Description:
96 -- Based on p_interface_flag and action_flag,
97 -- check attributes being changed
98 -- and calls Update_Attributes to update the delivery details
99 -- with the new values.
100 --
101 PROCEDURE Update_Records(
102 p_source_code IN VARCHAR2,
103 p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
104 p_interface_flag IN VARCHAR2,
105 x_return_status OUT NOCOPY VARCHAR2);
106
107
108 -- Procedure: Update_Attributes
109 -- Parameters:
110 -- p_source_code source system to update
111 -- p_attributes_rec record of attributes to change
112 -- p_changed_detail Detail record of attributes to change
113 -- x_return_status return status
114 -- Description:
115 -- Maps the attributes to shipping values as needed
116 -- and updates WSH_DELIVERY_DETAILS
117 --
118 PROCEDURE Update_Attributes(
119 p_source_code IN VARCHAR2,
120 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
121 p_changed_detail IN WSH_USA_CATEGORIES_PVT.ChangedDetailRec,
122 p_interface_flag IN VARCHAR2 , --bugfix 10175902
123 x_return_status OUT NOCOPY VARCHAR2);
124
125
126
127 -- Procedure: Import_Delivery_Details
128 -- Parameters:
129 -- p_source_line_id source line to import
130 -- if NULL, import all source lines
131 -- p_source_code must be 'OE' (Order Management)
132 -- x_return_status return status
133 --
134 -- Description: Pulls source line to create new delivery details
135 --
136 PROCEDURE Import_Delivery_Details(
137 p_source_line_id IN NUMBER,
138 p_source_code IN VARCHAR2,
139 x_return_status OUT NOCOPY VARCHAR2);
140
141
142 END WSH_USA_ACTIONS_PVT;