1 PACKAGE AHL_OSP_UTIL_PKG AS
2 /* $Header: AHLVOPUS.pls 120.4 2008/05/05 15:22:17 mpothuku ship $ */
3
4 -- Start of Comments --
5 -- Procedure name : Log_Transaction
6 -- Type : Private
7 -- Function : Writes the details about a transaction in the Log Table
8 -- Pre-reqs :
9 -- Parameters :
10 --
11 -- Log_Transaction Parameters:
12 -- p_trans_type_code IN VARCHAR2 Required
13 -- p_src_doc_id IN VARCHAR2 Required
14 -- p_src_doc_type_code IN VARCHAR2 Required
15 -- p_dest_doc_id IN VARCHAR2 Required
16 -- p_dest_doc_type_code IN VARCHAR2 Required
17 -- p_attribute_category IN VARCHAR2 Default NULL
18 -- p_attribute1 IN VARCHAR2 Default NULL
19 -- p_attribute2 IN VARCHAR2 Default NULL
20 -- p_attribute3 IN VARCHAR2 Default NULL
21 -- p_attribute4 IN VARCHAR2 Default NULL
22 -- p_attribute5 IN VARCHAR2 Default NULL
23 -- p_attribute6 IN VARCHAR2 Default NULL
24 -- p_attribute7 IN VARCHAR2 Default NULL
25 -- p_attribute8 IN VARCHAR2 Default NULL
26 -- p_attribute9 IN VARCHAR2 Default NULL
27 -- p_attribute10 IN VARCHAR2 Default NULL
28 -- p_attribute11 IN VARCHAR2 Default NULL
29 -- p_attribute12 IN VARCHAR2 Default NULL
30 -- p_attribute13 IN VARCHAR2 Default NULL
31 -- p_attribute14 IN VARCHAR2 Default NULL
32 -- p_attribute15 IN VARCHAR2 Default NULL
33 --
34 -- Version :
35 -- Initial Version 1.0
36 --
37 -- End of Comments.
38 PROCEDURE Log_Transaction
39 (
40 p_trans_type_code IN VARCHAR2,
41 p_src_doc_id IN NUMBER,
42 p_src_doc_type_code IN VARCHAR2,
43 p_dest_doc_id IN NUMBER,
44 p_dest_doc_type_code IN VARCHAR2,
45 p_attribute_category IN VARCHAR2 := null,
46 p_attribute1 IN VARCHAR2 := null,
47 p_attribute2 IN VARCHAR2 := null,
48 p_attribute3 IN VARCHAR2 := null,
49 p_attribute4 IN VARCHAR2 := null,
50 p_attribute5 IN VARCHAR2 := null,
51 p_attribute6 IN VARCHAR2 := null,
52 p_attribute7 IN VARCHAR2 := null,
53 p_attribute8 IN VARCHAR2 := null,
54 p_attribute9 IN VARCHAR2 := null,
55 p_attribute10 IN VARCHAR2 := null,
56 p_attribute11 IN VARCHAR2 := null,
57 p_attribute12 IN VARCHAR2 := null,
58 p_attribute13 IN VARCHAR2 := null,
59 p_attribute14 IN VARCHAR2 := null,
60 p_attribute15 IN VARCHAR2 := null
61 );
62
63 /******** The following are used to facilitate dynamic binding *********/
64 -- The REF CURSOR type to be used for all large dynamic binding queries
65 TYPE ahl_search_csr is REF CURSOR;
66
67 -- The array to be used to pass binding values
68 TYPE ahl_conditions_tbl IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
69
70 -- Start of Comments --
71 -- Procedure name : OPEN_SEARCH_CURSOR
72 -- Type : Public
73 -- Function : Opens a ref cursor that may have zero to a maximum of 16
74 -- dynamic binding variables
75 -- Pre-reqs :
76 -- Parameters :
77 --
78 -- OPEN_SEARCH_CURSOR Parameters:
79 -- p_x_csr IN OUT ahl_search_csr Required
80 -- This is the cursor to be opened
81 -- p_conditions_tbl IN ahl_conditions_tbl Required
82 -- This is the array containing the binding values
83 -- p_sql_str IN VARCHAR2 Required
84 -- This is the sql string with the bind parameters
85 --
86 -- Version :
87 -- Initial Version 1.0
88 --
89 -- End of Comments.
90 PROCEDURE OPEN_SEARCH_CURSOR(p_x_csr IN OUT NOCOPY ahl_search_csr,
91 p_conditions_tbl IN ahl_conditions_tbl,
92 p_sql_str IN VARCHAR2);
93
94 -- Start of Comments --
95 -- Procedure name : EXEC_IMMEDIATE
96 -- Type : Public
97 -- Function : Does an execute immediate of a SQL statement that returns
98 -- a single number value and that has up to 16 bind variables (0 to 16)
99 -- Pre-reqs :
100 -- Parameters :
101 --
102 -- EXEC_IMMEDIATE Parameters:
103 -- p_conditions_tbl IN ahl_conditions_tbl Required
104 -- This is the array containing the binding values
105 -- p_sql_str IN VARCHAR2 Required
106 -- This is the sql string with the bind parameters
107 -- x_results_count OUT NUMBER Required
108 -- This is the result of the execute immediate operation
109 --
110 -- Version :
111 -- Initial Version 1.0
112 --
113 -- End of Comments.
114 PROCEDURE EXEC_IMMEDIATE(p_conditions_tbl IN ahl_conditions_tbl,
115 p_sql_str IN VARCHAR2,
116 x_results_count OUT NOCOPY NUMBER);
117
118
119 /* Following two functions added by jaramana on March 14, 2006 for fixing Perf Bug 4914529 */
120 /* These two functions are used in ShipmentLinesVO.xml to get the Instance number and *
121 /* the Serial number respectively of a Shipment Line */
122
123 FUNCTION GET_SHIP_LINE_INSTANCE_NUMBER(p_ship_line_id IN NUMBER,
124 p_osp_line_id IN NUMBER) RETURN VARCHAR2;
125
126 FUNCTION GET_SHIP_LINE_SERIAL_NUMBER(p_ship_line_id IN NUMBER,
127 p_osp_line_id IN NUMBER) RETURN VARCHAR2;
128
129 --Added by mpothuku on 09-Jan-2008 to implement the Osp Receiving feature
130 FUNCTION GET_SHIP_LINE_INSTANCE_ID(p_ship_line_id IN NUMBER,
131 p_osp_line_id IN NUMBER) RETURN NUMBER;
132
133 --Added by mpothuku on 05-May-2008 to fix the Bug 6322216
134 FUNCTION GET_SHIP_LINE_LOT_NUMBER(p_ship_line_id IN NUMBER,
135 p_osp_line_id IN NUMBER) RETURN VARCHAR2;
136
137
138 End AHL_OSP_UTIL_PKG;
[Home] [Help]