1 PACKAGE WSH_DCP_PVT AUTHID CURRENT_USER as
2 /* $Header: WSHDCPPS.pls 120.0 2005/05/26 19:32:18 appldev noship $ */
3
4
5 TYPE g_dc_rec_type IS RECORD(
6 source_header_number VARCHAR2(100),
7 source_header_id NUMBER,
8 source_line_number VARCHAR2(100),
9 source_line_id NUMBER,
10 delivery_detail_id NUMBER,
11 dcp_script VARCHAR2(32767));
12
13
14
15 TYPE g_dc_tbl_type IS TABLE OF g_dc_rec_type INDEX BY BINARY_INTEGER;
16
17 TYPE t_dc_columns_rec_type IS RECORD(
18 wdd_source_header_number wsh_delivery_details.source_header_number%TYPE,
19 wdd_source_line_number wsh_delivery_details.source_line_number%TYPE,
20 wdd_delivery_detail_id wsh_delivery_details.delivery_detail_id%TYPE,
21 wdd_released_status wsh_delivery_details.released_status%TYPE,
22 wdd_requested_quantity wsh_delivery_details.requested_quantity%TYPE,
23 wdd_source_code wsh_delivery_details.source_code%TYPE,
24 wdd_batch_id wsh_delivery_details.batch_id%TYPE,
25 wdd_source_line_id wsh_delivery_details.source_line_id%TYPE,
26 wdd_source_header_id wsh_delivery_details.source_header_id%TYPE,
27 wdd_oe_interfaced_flag wsh_delivery_details.oe_interfaced_flag%TYPE,
28 wdd_inv_interfaced_flag wsh_delivery_details.inv_interfaced_flag%TYPE,
29 wdd_ship_set_id wsh_delivery_details.ship_set_id%TYPE,
30 wdd_date_requested wsh_delivery_details.date_requested%TYPE,
31 wdd_date_scheduled wsh_delivery_details.date_scheduled%TYPE,
32 wdd_ship_to_contact_id wsh_delivery_details.ship_to_contact_id%TYPE,
33 wdd_ship_to_site_use_id wsh_delivery_details.ship_to_site_use_id%TYPE,
34 wdd_org_id wsh_delivery_details.org_id%TYPE,
35 wdd_organization_id wsh_delivery_details.organization_id%TYPE,
36 wdd_ship_tolerance_above wsh_delivery_details.ship_tolerance_above%TYPE,
37 wdd_ship_tolerance_below wsh_delivery_details.ship_tolerance_below%TYPE,
38 wdd_picked_quantity wsh_delivery_details.picked_quantity%TYPE,
39 wdd_cycle_count_quantity wsh_delivery_details.cycle_count_quantity%TYPE,
40 wdd_shipped_quantity wsh_delivery_details.shipped_quantity%TYPE,
41 ol_line_id oe_order_lines_all.line_id%TYPE,
42 --Line number here is a concatenation of five different numbers
43 ol_line_number VARCHAR2(32767),
44 ol_ordered_quantity oe_order_lines_all.ordered_quantity%TYPE,
45 ol_cancelled_flag oe_order_lines_all.cancelled_flag%TYPE,
46 ol_ship_set_id oe_order_lines_all.ship_set_id%TYPE,
47 ol_shipped_quantity oe_order_lines_all.shipped_quantity%TYPE,
48 ol_flow_status_code oe_order_lines_all.flow_status_code%TYPE,
49 ol_open_flag oe_order_lines_all.open_flag%TYPE,
50 ol_ship_from_org_id oe_order_lines_all.ship_from_org_id%TYPE,
51 ol_org_id oe_order_lines_all.org_id%TYPE,
52 ol_schedule_ship_date oe_order_lines_all.schedule_ship_date%TYPE,
53 ol_request_date oe_order_lines_all.request_date%TYPE,
54 ol_shipping_interfaced_flag oe_order_lines_all.shipping_interfaced_flag%TYPE,
55 ol_header_id oe_order_lines_all.header_id%TYPE,
56 ol_ship_to_contact_id oe_order_lines_all.ship_to_contact_id%TYPE,
57 ol_ship_to_org_id oe_order_lines_all.ship_to_org_id%TYPE,
58 ol_fulfilled_quantity oe_order_lines_all.fulfilled_quantity%TYPE,
59 ol_invoiced_quantity oe_order_lines_all.invoiced_quantity%TYPE,
60 oh_order_number oe_order_headers_all.order_number%TYPE,
61 oh_ship_from_org_id oe_order_headers_all.ship_from_org_id%TYPE,
62 wnd_delivery_id wsh_new_deliveries.delivery_id%TYPE,
63 wnd_status_code wsh_new_deliveries.status_code%TYPE,
64 wts_status_code wsh_trip_stops.status_code%TYPE,
65 wdl_delivery_leg_id wsh_delivery_legs.delivery_leg_id%TYPE
66 );
67
68
69 --Records are added to this table g_dc_table for each instance
70 --of data inconsistency detection.
71 g_dc_table g_dc_tbl_type;
72
73 --This global g_add_to_debug determines whether the contents of g_dc_table
74 --are added to the debug file.
75 --For cases where rollback is allowed and transaction is re-run, this global
76 --will have 0 in the first run and when the exception is raised, this is
77 --incremented.
78 g_add_to_debug NUMBER := 0;
79
80 --This global is used by Pick-Release and by OM. This is to make sure that
81 --there are no duplicate or there are no unnecessary calls to dcp procedure.
82 G_CALL_DCP_CHECK VARCHAR2(1) := 'Y';
83
84 --Whenever DCP code starts debugger, this global is set.
85 --Value of Y means debug is started
86 --Value of R means debug is reset.
87 G_DEBUG_STARTED VARCHAR2(1):= 'N';
88
89 --This global holds the message count
90 --This is used by Delivery Detail and Delivery Group APIs and also by
91 --Procedure Processs_Stop_to_OM in WSHDDSHB.pls for ITS.
92 --This global is used to compare the initial count of messages with the
93 --count of messages after first run(where re-run is possible) and to remove
94 --the duplicate set of messages from the message stack.
95 G_INIT_MSG_COUNT NUMBER := 0;
96
97 G_CHECK_DCP NUMBER;
98
99 G_EMAIL_SERVER VARCHAR2(32767);
100 G_EMAIL_ADDRESS VARCHAR2(32767);
101
102 --This is the exception raised by outer-level DCP procedures and is used
103 --by callers like delivery-detail and delivery group APIs, ITS code etc.
104 data_inconsistency_exception EXCEPTION;
105
106 dcp_caught EXCEPTION;
107
108 Procedure Send_Mail(sender IN VARCHAR2 DEFAULT NULL,
109 recipient1 IN VARCHAR2 DEFAULT NULL,
110 recipient2 IN VARCHAR2 DEFAULT NULL,
111 recipient3 IN VARCHAR2 DEFAULT NULL,
112 recipient4 IN VARCHAR2 DEFAULT NULL,
113 message IN VARCHAR2);
114
115 Procedure check_ITS(p_bulk_mode IN VARCHAR2,
116 p_start_index IN NUMBER DEFAULT NULL,
117 p_end_index IN NUMBER DEFAULT NULL,
118 p_its_rec IN OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type,
119 p_raise_exception IN VARCHAR2 DEFAULT 'Y');
120
121 Procedure Check_Detail(p_action_code IN VARCHAR2,
122 p_dtl_table IN wsh_glbl_var_strct_grp.Delivery_Details_Attr_Tbl_Type);
123
124 Procedure Check_Delivery(p_action_code IN VARCHAR2,
125 p_dlvy_table IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type);
126
127 Procedure Check_Pick_Release(p_batch_id IN NUMBER);
128
129
130 Procedure Check_Scripts(p_source_header_id IN NUMBER DEFAULT NULL,
131 p_source_line_id IN NUMBER DEFAULT NULL,
132 p_delivery_id IN NUMBER DEFAULT NULL,
133 p_batch_id IN NUMBER DEFAULT NULL,
134 x_data_inconsistent OUT NOCOPY VARCHAR2);
135
136 PROCEDURE Post_Process(p_action_code IN VARCHAR2 DEFAULT NULL,
137 p_raise_exception IN VARCHAR2 DEFAULT 'Y');
138
139 FUNCTION Is_dcp_enabled RETURN NUMBER;
140
141 END WSH_DCP_PVT;
[Home] [Help]