[Home] [Help]
PACKAGE BODY: APPS.WSH_SC_DEL_LINES_PVT
Source
1 PACKAGE BODY WSH_SC_DEL_LINES_PVT as
2 /* $Header: WSHSDLNB.pls 115.4 99/07/16 08:21:17 porting ship $ */
3
4
5 -- routine to unassign the delivery line from the delivery
6 -- parameter pld_id is picking_line_detail_id
7 FUNCTION unassign_delivery_line( pld_id IN NUMBER,
8 original_detail_id IN NUMBER,
9 del_id IN NUMBER,
10 so_reservations IN VARCHAR2)
11 RETURN BOOLEAN IS
12 cannot_transfer EXCEPTION;
13 online_no_manager EXCEPTION;
14 online_error EXCEPTION;
15 ret_val NUMBER := 0;
16 success BOOLEAN;
17 outcome VARCHAR2(30);
18 message VARCHAR2(128);
19 a1 VARCHAR2(80);
20 a2 VARCHAR2(30);
21 a3 VARCHAR2(30);
22 a4 VARCHAR2(30);
23 a5 VARCHAR2(30);
24 a6 VARCHAR2(30);
25 a7 VARCHAR2(30);
26 a8 VARCHAR2(30);
27 a9 VARCHAR2(30);
28 a10 VARCHAR2(30);
29 a11 VARCHAR2(30);
30 a12 VARCHAR2(30);
31 a13 VARCHAR2(30);
32 a14 VARCHAR2(30);
33 a15 VARCHAR2(30);
34 a16 VARCHAR2(30);
35 a17 VARCHAR2(30);
36 a18 VARCHAR2(30);
37 a19 VARCHAR2(30);
38 a20 VARCHAR2(30);
39 CURSOR c1( pld_id IN NUMBER) IS
40 SELECT REQUESTED_QUANTITY
41 FROM so_picking_line_details
42 WHERE picking_line_detail_id = pld_id;
43 transfer_qty NUMBER;
44 BEGIN
45
46 IF (( original_detail_id > 0) AND
47 ( so_reservations = 'Y')) THEN
48 -- call transaction manager for demand / reservation transfer
49 -- The transfer routine need to update so_picking_line_details
50 -- and delete rows from so_freight_charges also.
51 -- but we raise a exception for now
52
53 transfer_qty := 0;
54 OPEN c1( pld_id);
55 FETCH c1 INTO transfer_qty;
56 CLOSE c1;
57 ret_val := Fnd_Transaction.synchronous( 1000,
58 outcome,
59 message,
60 'OE',
61 'WSHURTF',
62 TO_CHAR(original_detail_id),
63 TO_CHAR(pld_id),
64 TO_CHAR(transfer_qty),
65 TO_CHAR(del_id));
66 If (ret_val = 2) then
67 RAISE online_no_manager;
68 elsif (ret_val <> 0) then
69 RAISE online_error;
70 else
71 if (message = 'FAILURE') then
72 RAISE cannot_transfer;
73 end if;
74 END IF;
75 ELSE
76
77 UPDATE so_picking_line_details
78 SET delivery_id = NULL,
79 departure_id = NULL,
80 shipped_quantity = NULL,
81 dpw_assigned_flag = 'N',
82 load_seq_number = NULL
83 -- transaction_temp_id = NULL
84 WHERE picking_line_detail_id = pld_id;
85
86 DELETE FROM so_freight_charges
87 WHERE delivery_id = del_id
88 AND picking_line_detail_id = pld_id;
89
90 COMMIT;
91 END IF;
92
93 RETURN TRUE;
94 EXCEPTION
95 WHEN online_no_manager THEN
96 FND_MESSAGE.SET_NAME('OE','SHP_ONLINE_NO_MANAGER');
97 RETURN FALSE;
98 WHEN online_error THEN
99 FND_MESSAGE.SET_NAME('OE','SHP_AOL_ONLINE_FAILED');
100 FND_MESSAGE.SET_TOKEN('PROGRAM', 'WSHURTF');
101 RETURN FALSE;
102 WHEN cannot_transfer THEN
103 ret_val := Fnd_Transaction.get_values( a1, a2, a3, a4, a5, a6,
104 a7, a8, a9, a10, a11, a12, a13, a14,
105 a15, a16, a17, a18, a19, a20);
106 FND_MESSAGE.Set_Name('OE','WSH_SC_CANNOT_TRANSFER_PLD');
107 FND_MESSAGE.Set_Token('PLD_ID', TO_CHAR(pld_id));
108 FND_MESSAGE.Set_Token('REASON', a1);
109 RETURN FALSE;
110 WHEN others THEN
111 ROLLBACK TO before_unassign;
112 WSH_UTIL.Default_Handler('WSH_SC_DEL_LINES.unassign_delivery_line',SQLERRM);
113 END unassign_delivery_line;
114
115 FUNCTION Trx_Id(
116 X_Mode IN VARCHAR2,
117 X_Pk_Hdr_Id IN NUMBER,
118 X_Pk_Line_Id IN NUMBER,
119 X_Order_Category IN VARCHAR2
120 )
121 RETURN NUMBER
122 IS
123 X_Stmt_Num NUMBER;
124 X_Dest_Type VARCHAR2(25);
125 X_From_Org NUMBER;
126 X_To_Org NUMBER;
127 BEGIN
128 X_Stmt_Num := 100;
129 If (X_Order_Category = 'R') then
130 If (X_Mode = 'TRX_ACTION_ID') then
131 Return(1); /* 1 = Issue */
132 Elsif (X_Mode = 'TRX_TYPE_ID') then
133 Return(33); /* 33 = Sales Order Issue */
134 End If;
135 Elsif (X_Order_Category = 'P') then
136 SELECT nvl(destination_type_code,'@'),source_organization_id,
137 destination_organization_id
138 INTO X_Dest_Type,X_From_Org,X_To_Org
139 FROM po_requisition_lines_all pl,po_requisition_headers_all ph,
140 po_req_distributions_all pd,so_lines_all sol,so_headers_all soh,
141 so_picking_lines_all pkl,so_picking_headers_all pkh
142 WHERE pl.line_num = to_number(sol.original_system_line_reference)
143 AND pl.requisition_header_id = ph.requisition_header_id
144 AND pd.requisition_line_id = pl.requisition_line_id
145 AND ph.segment1 = soh.original_system_reference
146 AND soh.header_id = pkh.order_header_id
147 AND sol.line_id = pkl.order_line_id
148 AND pkh.picking_header_id = X_Pk_Hdr_Id
149 AND pkl.picking_line_id = X_Pk_Line_Id;
150
151 If (X_Mode = 'TRX_TYPE_ID') then
152 If (X_Dest_Type = 'EXPENSE') then
153 Return(34); /* 34 = Stores Issue */
154 Elsif (X_From_Org = X_To_Org) then
155 Return(50); /* 50 = */
156 Elsif (X_From_Org <> X_To_Org) then
157 Return(62); /* 62 = Transit Shipment */
158 End If;
159 Elsif (X_Mode = 'TRX_ACTION_ID') then
160 If (X_Dest_Type = 'EXPENSE') then
161 Return(1); /* 1 = Issue */
162 Elsif (X_From_Org = X_To_Org) then
163 Return(2); /* 2 = Subinv transfer*/
164 Elsif (X_From_Org <> X_To_Org) then
165 Return(21); /* 62 = Interorg Transfer */
166 End If;
167 End If;
168 End If;
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 Return(0);
172 WHEN OTHERS THEN
173 Return(0);
174 END;
175
176 END WSH_SC_DEL_LINES_PVT;