6:
7: /*** Globals ***/
8: g_object_name CONSTANT VARCHAR2(30) := 'CSL_DEBRIEF_PKG'; -- package name
9: g_pub_name CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_LINES'; -- publication item name
10: g_pub_name2 CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_HEADERS'; -- publication item name
11: g_debug_level NUMBER; -- debug level
12: g_header_id NUMBER := NULL;
13: TYPE Deferred_Line_Tbl_Type IS TABLE OF CSF_DEBRIEF_LINES.DEBRIEF_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
14: g_deferred_line_id_tbl Deferred_Line_Tbl_Type;
19: ***/
20: CURSOR c_debrief ( b_user_name VARCHAR2, b_tranid NUMBER) is
21: SELECT dbl.*
22: FROM CSL_CSF_DEBRIEF_LINES_INQ dbl
23: , CSL_CSF_DEBRIEF_HEADERS_INQ dbh
24: WHERE dbl.debrief_header_id = dbh.debrief_header_id
25: AND dbl.tranid$$ = b_tranid
26: AND dbl.clid$$cs = b_user_name
27: ORDER BY dbl.debrief_header_id;
39: AND clid$$cs = b_user_name;
40:
41: CURSOR c_debrief_no_lines( b_user_name VARCHAR2, b_tranid NUMBER) is
42: SELECT *
43: FROM CSL_CSF_DEBRIEF_HEADERS_INQ
44: WHERE tranid$$ = b_tranid
45: AND clid$$cs = b_user_name;
46:
47: /***
48: This procedure is called from APPLY_RECORD and creates a debrief header.
49: ***/
50: PROCEDURE CREATE_DEBRIEF_HEADER
51: (
52: p_debrief_header_id IN CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE,
53: p_error_msg OUT NOCOPY VARCHAR2,
54: x_return_status IN OUT NOCOPY VARCHAR2
55: )
56: IS
58: Name:
59: CREATE_DEBRIEF_HEADER
60:
61: Purpose:
62: Insert new header record into CSF_DEBRIEF_HEADERS
63:
64: Arguments:
65: p_debrief_header_id The debrief header id comes from
66: the debrief line inqueue record.
68: from the debrief headers inqueue
69: which is used to call the API.
70: ********************************************************/
71:
72: CURSOR c_csf_debrief_headers (b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) is
73: SELECT *
74: FROM CSL_CSF_DEBRIEF_HEADERS_INQ
75: WHERE debrief_header_id = b_debrief_header_id;
76:
70: ********************************************************/
71:
72: CURSOR c_csf_debrief_headers (b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) is
73: SELECT *
74: FROM CSL_CSF_DEBRIEF_HEADERS_INQ
75: WHERE debrief_header_id = b_debrief_header_id;
76:
77: r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
78:
73: SELECT *
74: FROM CSL_CSF_DEBRIEF_HEADERS_INQ
75: WHERE debrief_header_id = b_debrief_header_id;
76:
77: r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
78:
79: l_deb_rec csf_debrief_pub.debrief_rec_type;
80: l_line_tbl csf_debrief_pub.debrief_line_tbl_type;
81:
78:
79: l_deb_rec csf_debrief_pub.debrief_rec_type;
80: l_line_tbl csf_debrief_pub.debrief_line_tbl_type;
81:
82: l_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE;
83:
84: l_date DATE;
85: l_process_status NUMBER;
86: l_msg_count NUMBER;
90: l_date := SYSDATE;
91:
92: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
93: jtm_message_log_pkg.Log_Msg
94: ( v_object_id => r_csf_debrief_headers.debrief_header_id -- put PK column here
95: , v_object_name => g_object_name
96: , v_message => 'Entering ' || g_object_name || '.CREATE_DEBRIEF_HEADER'
97: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
98: END IF;
97: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
98: END IF;
99:
100: -- Open cursor to retrieve debrief header for debrief line
101: OPEN c_csf_debrief_headers (p_debrief_header_id);
102: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
103: CLOSE c_csf_debrief_headers;
104:
105: -- Create a new debrief header record
98: END IF;
99:
100: -- Open cursor to retrieve debrief header for debrief line
101: OPEN c_csf_debrief_headers (p_debrief_header_id);
102: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
103: CLOSE c_csf_debrief_headers;
104:
105: -- Create a new debrief header record
106: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
99:
100: -- Open cursor to retrieve debrief header for debrief line
101: OPEN c_csf_debrief_headers (p_debrief_header_id);
102: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
103: CLOSE c_csf_debrief_headers;
104:
105: -- Create a new debrief header record
106: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
107: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
102: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
103: CLOSE c_csf_debrief_headers;
104:
105: -- Create a new debrief header record
106: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
107: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
108: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
109: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
110: l_deb_rec.last_update_date := l_date;
103: CLOSE c_csf_debrief_headers;
104:
105: -- Create a new debrief header record
106: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
107: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
108: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
109: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
110: l_deb_rec.last_update_date := l_date;
111: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
104:
105: -- Create a new debrief header record
106: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
107: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
108: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
109: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
110: l_deb_rec.last_update_date := l_date;
111: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
112: l_deb_rec.creation_date := l_date;
105: -- Create a new debrief header record
106: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
107: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
108: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
109: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
110: l_deb_rec.last_update_date := l_date;
111: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
112: l_deb_rec.creation_date := l_date;
113: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
111: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
112: l_deb_rec.creation_date := l_date;
113: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
114: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
115: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
116: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
112: l_deb_rec.creation_date := l_date;
113: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
114: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
115: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
116: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
113: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
114: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
115: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
116: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
114: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
115: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
116: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
115: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
116: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
116: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
117: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
118: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
119: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
120: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
121: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
122: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
130: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
123: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
130: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
131:
124: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
130: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
131:
132: csf_debrief_pub.create_debrief
125: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
130: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
131:
132: csf_debrief_pub.create_debrief
133: ( p_api_version_number => 1.0
126: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
130: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
131:
132: csf_debrief_pub.create_debrief
133: ( p_api_version_number => 1.0
134: , p_init_msg_list => FND_API.G_TRUE
150: END IF;
151:
152: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
153: jtm_message_log_pkg.Log_Msg
154: ( v_object_id => r_csf_debrief_headers.debrief_header_id -- put PK column here
155: , v_object_name => g_object_name
156: , v_message => 'Leaving ' || g_object_name || '.CREATE_DEBRIEF_HEADER'
157: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
158: END IF;
165: );
166:
167: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
168: jtm_message_log_pkg.Log_Msg
169: ( v_object_id => r_csf_debrief_headers.debrief_header_id -- put PK column here
170: , v_object_name => g_object_name
171: , v_message => 'Leaving ' || g_object_name || '.CREATE_DEBRIEF_HEADER : ' || p_error_msg
172: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
173: END IF;
180: This function checks whether a header id already exists in the backend or not.
181: ***/
182: FUNCTION DEBRIEF_HEADER_EXISTS
183: (
184: p_debrief_header_id IN OUT NOCOPY CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE,
185: p_task_assignment_id IN NUMBER
186: )
187: RETURN BOOLEAN
188: IS
186: )
187: RETURN BOOLEAN
188: IS
189:
190: CURSOR c_debrief_header_exists ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE ) is
191: SELECT null
192: FROM csf_debrief_headers
193: WHERE debrief_header_id = b_debrief_header_id;
194:
188: IS
189:
190: CURSOR c_debrief_header_exists ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE ) is
191: SELECT null
192: FROM csf_debrief_headers
193: WHERE debrief_header_id = b_debrief_header_id;
194:
195: CURSOR c_debrief_header_id ( b_task_assignment_id CSF_DEBRIEF_HEADERS.TASK_ASSIGNMENT_ID%TYPE ) is
196: SELECT debrief_header_id
191: SELECT null
192: FROM csf_debrief_headers
193: WHERE debrief_header_id = b_debrief_header_id;
194:
195: CURSOR c_debrief_header_id ( b_task_assignment_id CSF_DEBRIEF_HEADERS.TASK_ASSIGNMENT_ID%TYPE ) is
196: SELECT debrief_header_id
197: FROM csf_debrief_headers
198: WHERE task_assignment_id = b_task_assignment_id;
199:
193: WHERE debrief_header_id = b_debrief_header_id;
194:
195: CURSOR c_debrief_header_id ( b_task_assignment_id CSF_DEBRIEF_HEADERS.TASK_ASSIGNMENT_ID%TYPE ) is
196: SELECT debrief_header_id
197: FROM csf_debrief_headers
198: WHERE task_assignment_id = b_task_assignment_id;
199:
200:
201: r_debrief_header_exists c_debrief_header_exists%ROWTYPE;
294: an record created on the backend for this header.
295: ***/
296: CURSOR c_debrief_assignment_id ( b_debrief_header_id NUMBER) is
297: SELECT task_assignment_id, seqno$$
298: FROM CSL_CSF_DEBRIEF_HEADERS_INQ dbh
299: WHERE debrief_header_id = b_debrief_header_id;
300: r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
301:
302: l_return_status VARCHAR2(1);
516:
517: -- Cursor to check if the Assignment Status is either of the
518: -- following rejected, on_hold, cancelled, closed or completed
519: CURSOR c_chk_task_status
520: ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
521: ) IS
522: SELECT tst.rejected_flag, tst.on_hold_flag, tst.cancelled_flag,
523: tst.closed_flag, tst.completed_flag
524: FROM csf_debrief_headers dh, jtf_task_assignments tas,
520: ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
521: ) IS
522: SELECT tst.rejected_flag, tst.on_hold_flag, tst.cancelled_flag,
523: tst.closed_flag, tst.completed_flag
524: FROM csf_debrief_headers dh, jtf_task_assignments tas,
525: jtf_task_statuses_b tst
526: WHERE dh.task_assignment_id = tas.task_assignment_id
527: AND tas.assignment_status_id = tst.task_status_id
528: AND dh.debrief_header_id = b_debrief_header_id;
564: ) IS
565:
566: -- Retrieve source_object_type_code
567: CURSOR c_task_obj_code
568: ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
569: )
570: IS
571: SELECT source_object_type_code
572: FROM jtf_tasks_b jtb
570: IS
571: SELECT source_object_type_code
572: FROM jtf_tasks_b jtb
573: , jtf_task_assignments jta
574: , csf_debrief_headers dbh
575: WHERE jtb.task_id = jta.task_id
576: AND jta.task_assignment_id = dbh.task_assignment_id
577: AND dbh.debrief_header_id = b_debrief_header_id;
578:
595: AND msi.ORGANIZATION_ID = p_inv_organization_id;
596:
597: -- For Bug Fix 3168617
598: CURSOR c_debrief_header
599: ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
600: ) IS
601: SELECT debrief_number
602: FROM csf_debrief_headers
603: WHERE debrief_header_id = b_debrief_header_id;
598: CURSOR c_debrief_header
599: ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
600: ) IS
601: SELECT debrief_number
602: FROM csf_debrief_headers
603: WHERE debrief_header_id = b_debrief_header_id;
604:
605: r_material_transaction c_material_transaction%ROWTYPE;
606: */
615: l_transaction_type_id NUMBER;
616: l_inventory_org_id p_record.issuing_inventory_org_id%TYPE;
617: l_sub_inventory_code p_record.issuing_sub_inventory_code%TYPE;
618: l_locator_id p_record.issuing_locator_id%TYPE;
619: l_debrief_number CSF_DEBRIEF_HEADERS.DEBRIEF_NUMBER%TYPE;
620: l_transaction_id NUMBER;
621: l_transaction_header_id NUMBER;
622:
623: -- l_line_rec and l_line_tbl are record/table types from the debrief API
906: ***/
907:
908: CURSOR c_debrief_assignment_id ( b_debrief_header_id NUMBER) is
909: SELECT task_assignment_id, seqno$$
910: FROM CSL_CSF_DEBRIEF_HEADERS_INQ dbh
911: WHERE debrief_header_id = b_debrief_header_id;
912: r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
913:
914: CURSOR c_csf_debrief_headers (
910: FROM CSL_CSF_DEBRIEF_HEADERS_INQ dbh
911: WHERE debrief_header_id = b_debrief_header_id;
912: r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
913:
914: CURSOR c_csf_debrief_headers (
915: b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) IS
916: SELECT * FROM CSL_CSF_DEBRIEF_HEADERS_INQ
917: WHERE debrief_header_id = b_debrief_header_id;
918: r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
911: WHERE debrief_header_id = b_debrief_header_id;
912: r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
913:
914: CURSOR c_csf_debrief_headers (
915: b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) IS
916: SELECT * FROM CSL_CSF_DEBRIEF_HEADERS_INQ
917: WHERE debrief_header_id = b_debrief_header_id;
918: r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
919:
912: r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
913:
914: CURSOR c_csf_debrief_headers (
915: b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) IS
916: SELECT * FROM CSL_CSF_DEBRIEF_HEADERS_INQ
917: WHERE debrief_header_id = b_debrief_header_id;
918: r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
919:
920: l_deb_rec csf_debrief_pub.debrief_rec_type;
914: CURSOR c_csf_debrief_headers (
915: b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) IS
916: SELECT * FROM CSL_CSF_DEBRIEF_HEADERS_INQ
917: WHERE debrief_header_id = b_debrief_header_id;
918: r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
919:
920: l_deb_rec csf_debrief_pub.debrief_rec_type;
921: l_date DATE;
922:
974:
975: ELSE
976: SAVEPOINT save_rec;
977: -- update debrief header.
978: OPEN c_csf_debrief_headers (l_header_id);
979: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
980: CLOSE c_csf_debrief_headers;
981:
982: -- update existing debrief header record
975: ELSE
976: SAVEPOINT save_rec;
977: -- update debrief header.
978: OPEN c_csf_debrief_headers (l_header_id);
979: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
980: CLOSE c_csf_debrief_headers;
981:
982: -- update existing debrief header record
983: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
976: SAVEPOINT save_rec;
977: -- update debrief header.
978: OPEN c_csf_debrief_headers (l_header_id);
979: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
980: CLOSE c_csf_debrief_headers;
981:
982: -- update existing debrief header record
983: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
984: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
979: FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
980: CLOSE c_csf_debrief_headers;
981:
982: -- update existing debrief header record
983: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
984: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
985: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
986: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
987: l_deb_rec.last_update_date := l_date;
980: CLOSE c_csf_debrief_headers;
981:
982: -- update existing debrief header record
983: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
984: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
985: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
986: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
987: l_deb_rec.last_update_date := l_date;
988: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
981:
982: -- update existing debrief header record
983: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
984: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
985: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
986: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
987: l_deb_rec.last_update_date := l_date;
988: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
989: l_deb_rec.creation_date := l_date;
982: -- update existing debrief header record
983: l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
984: l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
985: l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
986: l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
987: l_deb_rec.last_update_date := l_date;
988: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
989: l_deb_rec.creation_date := l_date;
990: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
988: l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
989: l_deb_rec.creation_date := l_date;
990: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
991: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
992: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
993: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
989: l_deb_rec.creation_date := l_date;
990: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
991: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
992: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
993: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
990: l_deb_rec.created_by := FND_GLOBAL.USER_ID;
991: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
992: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
993: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
991: l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
992: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
993: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
992: l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
993: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
993: l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
994: l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
995: l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
996: l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
997: l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
998: l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
999: l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
1007: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
1000: l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
1007: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
1008:
1001: l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
1007: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
1008:
1009: csf_debrief_pub.update_debrief
1002: l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
1007: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
1008:
1009: csf_debrief_pub.update_debrief
1010: ( p_api_version_number => 1.0
1003: l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004: l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005: l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006: l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
1007: l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
1008:
1009: csf_debrief_pub.update_debrief
1010: ( p_api_version_number => 1.0
1011: , p_init_msg_list => FND_API.G_TRUE