[Home] [Help]
PACKAGE BODY: APPS.GMS_PO_ADL_PKG
Source
1 PACKAGE BODY gms_po_adl_pkg AS
2 /* $Header: gmspoxab.pls 120.0 2005/05/29 11:59:47 appldev noship $ */
3
4
5 /* Declare procedure update_adls.
6 REQ_LINE_ID IN NUMBER ;
7 ERR_CODE IN OUT NOCOPY varchar2,
8 ERR_MSG IN OUT NOCOPY varchar2
9 */
10 PROCEDURE UPDATE_ADLS( p_req_line_id IN NUMBER,
11 err_code IN OUT NOCOPY VARCHAR2,
12 err_msg IN OUT NOCOPY VARCHAR2 ) is
13
14 -------------------------------------------------------------------------------
15 -- 3042946
16 -- and rd_old.award_id IS NOT NULL was added.
17 -- -------------------------------------------
18 --CURSOR C_REQ_REC is
19 -- SELECT rd_new.distribution_id new_distribution_id,
20 -- rd_old.distribution_id old_distribution_id,
21 -- rd_old.award_id award_set_id
22 -- FROM po_requisition_lines porl_new,
23 -- po_requisition_lines porl_old,
24 -- po_req_distributions rd_new,
25 -- po_req_distributions rd_old
26 -- WHERE porl_new.requisition_line_id = nvl(p_req_line_id, -1 )
27 -- AND porl_old.line_location_id = (-1) * porl_new.line_location_id
28 -- AND rd_old.requisition_line_id = porl_old.requisition_line_id
29 -- AND rd_new.requisition_line_id = porl_new.requisition_line_id
30 -- and rd_old.award_id IS NOT NULL
31 -- AND rd_new.source_req_distribution_id= rd_old.distribution_id ;
32 -------------------------------------------------------------------------------------
33 -- 3042946
34 -- and rd_old.award_id IS NOT NULL was added.
35 -- -------------------------------------------
36 /* Bug#2909181 : The following SQL two req distributions
37 for each of the newly created req line in the case of two requisition
38 lines autocreated to a same PO shipment, (partially received and then
39 cancelled) as the where clause will pick up both the old req lines for
40 each of the newly created req lines(because the join condition is based
41 on line location id, this will pick up both the old req line for each of
42 the new req lines). Hence,instead on the line location join, will use
43 the following join between PORL_OLD AND PORL_NEW: PORL_NEW.PARENT_REQ_LINE_ID
44 = PORL_OLD.REQUISITION_LINE_ID */
45
46 /* Bug 3315086: Modified the join condition to -1 * parent_req_line_id, as a fix
47 for the conflict between bug fix 2909181 and req. split/modify */
48 /************************************************************************
49 CURSOR c_req_rec is
50 select distinct rd_new.distribution_id new_distribution_id,
51 rd_old.distribution_id old_distribution_id,
52 rd_old.award_id award_set_id
53 from po_req_distributions_all rd_old,
54 po_req_distributions_all rd_new,
55 po_requisition_lines_all porl_old,
56 po_requisition_lines_all porl_new,
57 po_distributions pod,
58 po_headers_all blanket
59 where porl_new.requisition_line_id = nvl(p_req_line_id, -1 )
60 and rd_new.requisition_line_id = porl_new.requisition_line_id
61 and rd_new.award_id is null
62 and porl_old.requisition_line_id = (-1) * porl_new.parent_req_line_id
63 and rd_old.requisition_line_id = porl_old.requisition_line_id
64 and rd_old.award_id is not null
65 and pod.req_distribution_id = rd_old.distribution_id
66 and nvl(pod.quantity_cancelled,0) > 0
67 and porl_new.blanket_po_header_id = blanket.po_header_id(+) ;
68 **********************************************************************/
69 CURSOR c_req_rec is
70 select distinct rd_new.distribution_id new_distribution_id,
71 rd_old.distribution_id old_distribution_id,
72 rd_old.award_id award_set_id
73 from po_req_distributions_all rd_old,
74 po_req_distributions_all rd_new,
75 po_requisition_lines_all porl_new
76 where porl_new.requisition_line_id = nvl(p_req_line_id, -1 )
77 and rd_new.requisition_line_id = porl_new.requisition_line_id
78 and rd_new.award_id is null
79 and rd_old.award_id is not null
80 and rd_new.source_req_distribution_id= rd_old.distribution_id
81 and rd_old.award_id is not NULL ;
82
83 x_award_set_id NUMBER ;
84
85 BEGIN
86
87 -- --------------------------------------
88 -- Just a dummy package body.
89 -- Created to be used by grants to implement
90 -- award related details.
91 -- ------------------------------------------
92 FOR C_REC IN C_REQ_REC LOOP
93
94 x_award_set_id := gms_awards_dist_pkg.get_award_set_id ;
95
96 insert into gms_award_distributions
97 ( AWARD_SET_ID ,
98 ADL_LINE_NUM ,
99 FUNDING_PATTERN_ID ,
100 DISTRIBUTION_VALUE ,
101 RAW_COST ,
102 DOCUMENT_TYPE ,
103 PROJECT_ID ,
104 TASK_ID ,
105 AWARD_ID ,
106 EXPENDITURE_ITEM_ID ,
107 CDL_LINE_NUM ,
108 IND_COMPILED_SET_ID ,
109 GL_DATE ,
110 REQUEST_ID ,
111 LINE_NUM_REVERSED ,
112 RESOURCE_LIST_MEMBER_ID ,
113 OUTPUT_VAT_TAX_ID ,
114 OUTPUT_TAX_EXEMPT_FLAG ,
115 OUTPUT_TAX_EXEMPT_REASON_CODE ,
116 OUTPUT_TAX_EXEMPT_NUMBER ,
117 ADL_STATUS ,
118 FC_STATUS ,
119 LINE_TYPE ,
120 CAPITALIZED_FLAG ,
121 CAPITALIZABLE_FLAG ,
122 REVERSED_FLAG ,
123 REVENUE_DISTRIBUTED_FLAG ,
124 BILLED_FLAG ,
125 BILL_HOLD_FLAG ,
126 DISTRIBUTION_ID ,
127 PO_DISTRIBUTION_ID ,
128 INVOICE_DISTRIBUTION_ID ,
129 PARENT_AWARD_SET_ID ,
130 INVOICE_ID ,
131 PARENT_ADL_LINE_NUM ,
132 DISTRIBUTION_LINE_NUMBER ,
133 BURDENABLE_RAW_COST ,
134 COST_DISTRIBUTED_FLAG ,
135 LAST_UPDATE_DATE ,
136 LAST_UPDATED_BY ,
137 CREATED_BY ,
138 CREATION_DATE ,
139 LAST_UPDATE_LOGIN ,
140 BUD_TASK_ID )
141 select X_AWARD_SET_ID ,
142 ADL_LINE_NUM ,
143 FUNDING_PATTERN_ID ,
144 DISTRIBUTION_VALUE ,
145 RAW_COST ,
146 DOCUMENT_TYPE ,
147 PROJECT_ID ,
148 TASK_ID ,
149 AWARD_ID ,
150 EXPENDITURE_ITEM_ID ,
151 CDL_LINE_NUM ,
152 IND_COMPILED_SET_ID ,
153 GL_DATE ,
154 REQUEST_ID ,
155 LINE_NUM_REVERSED ,
156 RESOURCE_LIST_MEMBER_ID ,
157 OUTPUT_VAT_TAX_ID ,
158 OUTPUT_TAX_EXEMPT_FLAG ,
159 OUTPUT_TAX_EXEMPT_REASON_CODE ,
160 OUTPUT_TAX_EXEMPT_NUMBER ,
161 ADL_STATUS ,
162 FC_STATUS ,
163 LINE_TYPE ,
164 CAPITALIZED_FLAG ,
165 CAPITALIZABLE_FLAG ,
166 REVERSED_FLAG ,
167 REVENUE_DISTRIBUTED_FLAG ,
168 BILLED_FLAG ,
169 BILL_HOLD_FLAG ,
170 c_rec.new_distribution_id ,
171 PO_DISTRIBUTION_ID ,
172 INVOICE_DISTRIBUTION_ID ,
173 PARENT_AWARD_SET_ID ,
174 INVOICE_ID ,
175 PARENT_ADL_LINE_NUM ,
176 DISTRIBUTION_LINE_NUMBER ,
177 0 ,
178 COST_DISTRIBUTED_FLAG ,
179 SYSDATE ,
180 NVL(fnd_global.user_id,0) ,
181 NVL(fnd_global.user_id,0) ,
182 SYSDATE ,
183 LAST_UPDATE_LOGIN ,
184 BUD_TASK_ID
185 from gms_award_distributions adl
186 where adl.award_set_id = c_rec.award_set_id
187 and adl.adl_status = 'A'
188 and adl.document_type = 'REQ'
189 and adl.distribution_id = c_rec.old_distribution_id
190 and NOT EXISTS ( select 'X'
191 from gms_award_distributions adl2
192 where adl2.distribution_id = c_rec.new_distribution_id
193 and adl2.adl_status = 'A'
194 and adl2.document_type = 'REQ'
195 ) ;
196
197 UPDATE po_req_distributions rd_new
198 SET award_id = x_award_set_id
199 WHERE distribution_id = c_rec.new_distribution_id
200 AND award_id IS NULL
201 and EXISTS ( select 'X'
202 from gms_award_distributions adl2
203 where adl2.distribution_id = c_rec.new_distribution_id
204 and adl2.adl_status = 'A'
205 and award_set_id = x_award_set_id
206 and adl2.document_type = 'REQ'
207 ) ;
208
209 IF SQL%NOTFOUND THEN
210 raise no_data_found ;
211 END IF ;
212
213 END LOOP ;
214
215 err_code := 'S' ;
216 EXCEPTION
217 WHEN OTHERS THEN
218 err_code := 'F' ;
219 err_msg := substr(SQLERRM,1,200) ;
220 END UPDATE_ADLS;
221
222 END gms_po_adl_pkg ;