[Home] [Help]
187: AND INV.INVOICE_ID = a.INVOICE_ID
188: AND gpt.sponsored_flag = 'Y' ;
189:
190: cursor c_adl is
191: select * from gms_award_distributions where award_set_id = l_award_set_idX
192: and adl_line_num = 1 ;
193: l_adl_rec c_adl%ROWTYPE ;
194:
195: cursor c2 is
200: where a.invoice_id = p_invoice_id
201: and a.project_id = p.project_id
202: and p.project_type = gpt.project_type
203: and gpt.sponsored_flag = 'Y'
204: and not exists ( select 1 from gms_award_distributions adl
205: where adl.invoice_id = p_invoice_id
206: and adl.distribution_line_number = A.distribution_line_number
207: and adl.document_type = 'AP'
208: and adl.award_set_id = NVL(a.award_id,0)
219: AND GPT.SPONSORED_FLAG = 'Y'
220: AND b.LINE_TYPE_LOOKUP_CODE='NONREC_TAX'
221: AND NOT EXISTS
222: (SELECT 1
223: FROM GMS_AWARD_DISTRIBUTIONS ADL
224: WHERE ADL.INVOICE_ID = p_invoice_id
225: AND adl.distribution_line_number = b.distribution_line_number
226: AND ADL.DOCUMENT_TYPE = 'AP'
227: AND adl.award_set_id = NVL(b.award_id,0)
244: A.created_by CREATED_BY,
245: NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
246: gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
247: FROM ap_invoice_distributions_all A,
248: GMS_AWARD_DISTRIBUTIONS ADL
249: WHERE a.invoice_id = p_invoice_id
250: AND adl.award_set_id = a.award_id
251: AND adl.adl_line_num = 1
252: AND a.AWARD_ID IS NOT NULL
264: a.CREATED_BY CREATED_BY,
265: NVL(a.LAST_UPDATE_LOGIN,0) LAST_UPDATE_LOGIN,
266: gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
267: FROM ap_self_assessed_tax_dist_all A,
268: GMS_AWARD_DISTRIBUTIONS ADL
269: WHERE a.invoice_id = p_invoice_id
270: AND ADL.AWARD_SET_ID = a.AWARD_ID
271: AND line_type_lookup_code='NONREC_TAX'
272: and ADL.ADL_LINE_NUM = 1
274:
275: cursor C4(p_invoice_id in number)
276: IS
277: select adl2.award_set_id
278: from gms_award_distributions adl2,
279: AP_INVOICE_DISTRIBUTIONS_ALL APD
280: where apd.invoice_id = p_invoice_id
281: and apd.award_id is not null
282: and adl2.award_set_id = apd.award_id
286: and ADL2.INVOICE_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
287: and ADL2.ADL_STATUS = 'I'
288: union
289: select adl2.award_set_id
290: from gms_award_distributions adl2,
291: AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
292: where apsat.invoice_id = p_invoice_id
293: and APSAT.AWARD_ID is not null
294: and line_type_lookup_code='NONREC_TAX'
301:
302: cursor C5(p_invoice_id in number)
303: IS
304: select adl2.award_set_id
305: from gms_award_distributions adl2,
306: AP_INVOICE_DISTRIBUTIONS_ALL APD
307: where apd.invoice_id = p_invoice_id
308: and APD.AWARD_ID is not null
309: and adl2.award_set_id <> apd.award_id
313: and ADL2.INVOICE_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
314: and adl2.adl_status = 'A'
315: UNION
316: select ADL2.AWARD_SET_ID
317: from GMS_AWARD_DISTRIBUTIONS ADL2,
318: AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
319: where apsat.invoice_id = p_invoice_id
320: and APSAT.AWARD_ID is not null
321: and adl2.award_set_id <> apsat.award_id
392: l_created_by ,
393: l_last_update_login ,
394: l_new_award_set_id
395: from ap_invoice_distributions_all A,
396: gms_award_distributions adl
397: where a.invoice_id = p_invoice_id
398: and adl.award_set_id = a.award_id
399: and adl.adl_line_num = 1
400: and a.award_id IS NOT NULL;*/
439: l_dummy_tab.delete ;
440: /* BUG 14216205 : Commented the below code : Starts */
441: /*select adl2.award_set_id
442: bulk collect into l_dummy_tab
443: from gms_award_distributions adl2,
444: ap_invoice_distributions_all apd
445: where apd.invoice_id = p_invoice_id
446: and apd.award_id is not null
447: and adl2.award_set_id = apd.award_id
460:
461: IF l_dummy_tab.count > 0 THEN
462:
463: FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
464: UPDATE gms_award_distributions
465: SET adl_status = 'A'
466: where award_set_id = l_dummy_tab(i) ;
467:
468: END IF ;
485: -- ==============================================================================================
486: /* BUG 14216205 : Commented the below code : Starts */
487: /*select adl2.award_set_id
488: bulk collect into l_dummy_tab
489: from gms_award_distributions adl2,
490: ap_invoice_distributions_all apd
491: where apd.invoice_id = p_invoice_id
492: and apd.award_id is not null
493: and adl2.award_set_id <> apd.award_id
506:
507: IF l_dummy_tab.count > 0 THEN
508:
509: FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
510: UPDATE gms_award_distributions
511: SET adl_status = 'I'
512: where award_set_id = l_dummy_tab(i) ;
513:
514: END IF ;
629: -- scenarions : POETA ap distribution line was changed to gl related.
630: -- Distribution line is deleted but all other distributions
631: -- has correct adls.
632: -- ADLS are in sych so we can delete orphan adls now.
633: delete from gms_award_distributions
634: where invoice_id = p_invoice_id
635: and document_type = 'AP'
636: and award_set_id not in ( select award_id from ap_invoice_distributions_all
637: where invoice_id = p_invoice_id
650: -- End of comment
651:
652:
653: FORALL i in l_invoice_distribution_id.FIRST..l_invoice_distribution_id.LAST
654: INSERT into gms_award_distributions ( award_set_id ,
655: adl_line_num,
656: document_type,
657: distribution_value,
658: project_id ,
728: -- ARE DELETED.
729: -- delete orphan adls for a given invoice id.
730: -- ADLS are in sych so we can delete orphan adls now.
731: --
732: delete from gms_award_distributions
733: where invoice_id = p_invoice_id
734: and document_type = 'AP'
735: and award_set_id not in ( select award_id from ap_invoice_distributions_all
736: where invoice_id = p_invoice_id
818: X_msg_count OUT nocopy NUMBER,
819: X_msg_data OUT nocopy VARCHAR2 ) is
820: cursor c1 is
821: select award_id
822: from gms_award_distributions
823: where award_set_id = x_award_id
824: and adl_line_num = 1 ;
825:
826: l_award_id number ;