[Home] [Help]
164: and inv.invoice_id = a.invoice_id
165: and gpt.sponsored_flag = 'Y' ;
166:
167: cursor c_adl is
168: select * from gms_award_distributions where award_set_id = l_award_set_idX
169: and adl_line_num = 1 ;
170: l_adl_rec c_adl%ROWTYPE ;
171:
172: cursor c2 is
177: where a.invoice_id = p_invoice_id
178: and a.project_id = p.project_id
179: and p.project_type = gpt.project_type
180: and gpt.sponsored_flag = 'Y'
181: and not exists ( select 1 from gms_award_distributions adl
182: where adl.invoice_id = p_invoice_id
183: and adl.distribution_line_number = A.distribution_line_number
184: and adl.document_type = 'AP'
185: and adl.award_set_id = NVL(a.award_id,0)
231: l_created_by ,
232: l_last_update_login ,
233: l_new_award_set_id
234: from ap_invoice_distributions_all A,
235: gms_award_distributions adl
236: where a.invoice_id = p_invoice_id
237: and adl.award_set_id = a.award_id
238: and adl.adl_line_num = 1
239: and a.award_id IS NOT NULL;
258: l_dummy_tab.delete ;
259:
260: select adl2.award_set_id
261: bulk collect into l_dummy_tab
262: from gms_award_distributions adl2,
263: ap_invoice_distributions_all apd
264: where apd.invoice_id = p_invoice_id
265: and apd.award_id is not null
266: and adl2.award_set_id = apd.award_id
272:
273: IF l_dummy_tab.count > 0 THEN
274:
275: FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
276: UPDATE gms_award_distributions
277: SET adl_status = 'A'
278: where award_set_id = l_dummy_tab(i) ;
279:
280: END IF ;
296: -- SQL ID : 14724956 Share Memory Size 444,106
297: -- ==============================================================================================
298: select adl2.award_set_id
299: bulk collect into l_dummy_tab
300: from gms_award_distributions adl2,
301: ap_invoice_distributions_all apd
302: where apd.invoice_id = p_invoice_id
303: and apd.award_id is not null
304: and adl2.award_set_id <> apd.award_id
311:
312: IF l_dummy_tab.count > 0 THEN
313:
314: FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
315: UPDATE gms_award_distributions
316: SET adl_status = 'I'
317: where award_set_id = l_dummy_tab(i) ;
318:
319: END IF ;
421: -- scenarions : POETA ap distribution line was changed to gl related.
422: -- Distribution line is deleted but all other distributions
423: -- has correct adls.
424: -- ADLS are in sych so we can delete orphan adls now.
425: delete from gms_award_distributions
426: where invoice_id = p_invoice_id
427: and document_type = 'AP'
428: and award_set_id not in ( select award_id from ap_invoice_distributions_all
429: where invoice_id = p_invoice_id
438: -- End of comment
439:
440:
441: FORALL i in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
442: INSERT into gms_award_distributions ( award_set_id ,
443: adl_line_num,
444: document_type,
445: distribution_value,
446: project_id ,
509: -- ARE DELETED.
510: -- delete orphan adls for a given invoice id.
511: -- ADLS are in sych so we can delete orphan adls now.
512: --
513: delete from gms_award_distributions
514: where invoice_id = p_invoice_id
515: and document_type = 'AP'
516: and award_set_id not in ( select award_id from ap_invoice_distributions_all
517: where invoice_id = p_invoice_id
595: X_msg_count OUT nocopy NUMBER,
596: X_msg_data OUT nocopy VARCHAR2 ) is
597: cursor c1 is
598: select award_id
599: from gms_award_distributions
600: where award_set_id = x_award_id
601: and adl_line_num = 1 ;
602:
603: l_award_id number ;