1 PACKAGE BODY PSP_PQH_INTEGRATION as
2 /* $Header: PSPENPQHB.pls 120.0 2005/06/02 16:00:25 appldev noship $ */
3
4 PROCEDURE get_asg_encumbrances(p_assignment_id IN NUMBER,
5 p_encumbrance_start_date IN DATE,
6 p_encumbrance_end_date IN DATE,
7 p_encumbrance_table OUT NOCOPY ENCUMBRANCE_TABLE_REC_COL,
8 p_asg_psp_encumbered OUT NOCOPY BOOLEAN,
9 p_return_status OUT NOCOPY VARCHAR2) IS
10
11
12 CURSOR get_asg_enc_cur IS
13 SELECT pelh.enc_element_type_id , sum(decode(pelh.gl_project_flag , 'G',
14 encumbrance_amount , 0))gl_enc_amount, sum(decode(pelh.gl_project_flag, 'P',
15 encumbrance_amount, 0))gms_enc_amount
16 from psp_enc_summary_lines pesl,
17 psp_enc_lines_history pelh
18 WHERE
19 pelh.assignment_id=p_assignment_id and
20 pesl.enc_summary_line_id=pelh.enc_summary_line_id
21 and pesl.effective_date between p_encumbrance_start_date and
22 p_encumbrance_end_date
23 and pesl.status_code='A'
24 group by pelh.enc_element_type_id ;
25
26 BEGIN
27
28 p_asg_psp_encumbered :=TRUE;
29 p_encumbrance_table.r_gl_enc_amount.delete;
30 p_encumbrance_table.r_gms_enc_amount.delete;
31 p_encumbrance_table.r_element_type_id.delete;
32
33 OPEN GET_ASG_ENC_CUR;
34 FETCH GET_ASG_ENC_CUR BULK COLLECT into p_encumbrance_table.r_element_type_id,
35 p_encumbrance_table.r_gl_enc_amount,
36 p_encumbrance_table.r_gms_enc_amount;
37 CLOSE GET_ASG_ENC_CUR;
38
39 p_return_status:=FND_API.G_RET_STS_SUCCESS;
40
41 EXCEPTION
42
43 WHEN NO_DATA_FOUND THEN
44 p_asg_psp_encumbered:=FALSE;
45 p_return_status:=FND_API.G_RET_STS_SUCCESS;
46 CLOSE GET_ASG_ENC_CUR;
47
48
49 WHEN OTHERS THEN
50
51 p_return_Status:=FND_API.G_RET_STS_ERROR;
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 CLOSE GET_ASG_ENC_CUR;
54
55
56 END get_asg_encumbrances;
57
58 PROCEDURE get_encumbrance_details (
59 p_calling_process IN VARCHAR2,
60 p_assignment_enc_ld_table OUT NOCOPY assignment_enc_ld_col,
61 p_psp_encumbered OUT NOCOPY BOOLEAN,
62 p_return_status OUT NOCOPY VARCHAR2) IS
63
64 CURSOR get_enc_details_sum_cur IS /* Cursor for Enc S and Transfer */
65 SELECT assignment_id,enc_element_type_id , sum(encumbrance_amount),min(time_period_id),
66 max(time_period_id) from PSP_ENC_LINES
67 WHERE enc_control_id in
68 (SELECT enc_control_id from psp_enc_controls where action_code='I' and
69 run_id = psp_enc_sum_tran.g_run_id)
70 GROUP BY assignment_id, enc_element_type_id;
71
72 CURSOR get_enc_details_liq_cur is
73 SELECT pelh.assignment_id,enc_element_type_id, sum(encumbrance_amount),min(pelh.time_period_id),
74 max(pelh.time_period_id) FROM PSP_ENC_SUMMARY_LINES
75 PESL, PSP_ENC_LINES_HISTORY PELH WHERE
76 PESL.status_code='A' and PELH.enc_summary_line_id=pesl.enc_summary_line_id
77 AND PELH.CHANGE_FLAG ='N' and EXISTS
78 (SELECT '1' from PSP_ENC_CHANGED_ASSIGNMENTS PECA where PECA.ASSIGNMENT_ID=
79 PESL.ASSIGNMENT_ID)
80 AND PESL.ENC_CONTROL_ID IN -- Changed PELH to PESL for 11510_CU2 Consolidated performance fixes.
81 (SELECT ENC_CONTROL_ID FROM PSP_ENC_CONTROLS WHERE ACTION_CODE='IU'
82 AND RUN_ID=PSP_ENC_LIQ_TRAN.G_RUN_ID)
83 GROUP BY PELH.ASSIGNMENT_ID,ENC_ELEMENT_TYPE_ID;
84
85 BEGIN
86
87 p_assignment_enc_ld_table.r_assignment_id.delete;
88 p_assignment_enc_ld_table.r_element_type_id.delete;
89 p_assignment_enc_ld_table.r_encumbrance_amount.delete;
90 p_assignment_enc_ld_table.r_begin_time_period_id.delete;
91 p_assignment_enc_ld_table.r_end_time_period_id.delete;
92
93 p_psp_encumbered:=TRUE;
94
95 IF p_calling_process ='S' THEN /* calling process in Enc S and T */
96
97 OPEN GET_ENC_DETAILS_SUM_CUR;
98 FETCH GET_ENC_DETAILS_SUM_CUR
99 BULK COLLECT into p_assignment_enc_ld_table.r_assignment_id,
100 p_assignment_enc_ld_table.r_element_type_id,
101 p_assignment_enc_ld_table.r_encumbrance_amount,
102 p_assignment_enc_ld_table.r_begin_time_period_id,
103 p_assignment_enc_ld_table.r_end_time_period_id;
104
105 p_return_status:=FND_API.G_RET_STS_SUCCESS;
106
107
108 ELSE
109
110 OPEN GET_ENC_DETAILS_LIQ_CUR;
111 FETCH GET_ENC_DETAILS_LIQ_CUR
112 BULK COLLECT into p_assignment_enc_ld_table.r_assignment_id,
113 p_assignment_enc_ld_table.r_element_type_id,
114 p_assignment_enc_ld_table.r_encumbrance_amount,
115 p_assignment_enc_ld_table.r_begin_time_period_id,
116 p_assignment_enc_ld_table.r_end_time_period_id;
117
118 p_return_status:=FND_API.G_RET_STS_SUCCESS;
119 CLOSE GET_ENC_DETAILS_LIQ_CUR;
120
121 p_return_status:=FND_API.G_RET_STS_SUCCESS;
122
123 END IF;
124
125 EXCEPTION
126
127 WHEN NO_DATA_FOUND THEN
128 p_psp_encumbered:=FALSE;
129
130 if p_calling_process='S' then
131 CLOSE GET_ENC_DETAILS_SUM_CUR;
132 else
133 CLOSE GET_ENC_DETAILS_LIQ_CUR;
134 end if;
135
136 p_return_status:=FND_API.G_RET_STS_SUCCESS;
137
138
139 WHEN OTHERS THEN
140 p_return_Status:=FND_API.G_RET_STS_ERROR;
141
142 if p_calling_process='S' then
143 CLOSE GET_ENC_DETAILS_SUM_CUR;
144 else
145 CLOSE GET_ENC_DETAILS_LIQ_CUR;
146 end if;
147
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149
150 END get_Encumbrance_details;
151
152 END PSP_PQH_INTEGRATION;
[Home] [Help]