[Home] [Help]
127: gbp.award_set_id,
128: transaction_source,
129: gbp.request_id
130: from gms_bc_packets gbp,
131: gms_award_distributions adl
132: where gbp.packet_id = p_packet_id
133: and gbp.expenditure_type = p_expenditure_type
134: and gbp.award_id = p_award_id
135: and gbp.document_header_id = adl.expenditure_item_id
255: adl.ind_compiled_set_id
256: from po_requisition_headers RH,
257: po_requisition_lines RL,
258: po_req_distributions RD,
259: gms_award_distributions ADL
260: where rh.type_lookup_code = 'PURCHASE'
261: and rh.requisition_header_id = rl.requisition_header_id
262: and nvl(rl.modified_by_agent_flag,'N') = 'N'
263: and rl.source_type_code = 'VENDOR'
316: , adl.bud_task_id
317: , adl.ind_compiled_set_id
318: from
319: po_distributions pod,
320: gms_award_distributions adl
321: where nvl(pod.encumbered_flag,'N')= 'Y'
322: and pod.award_id = adl.award_set_id
323: and adl.adl_line_num = 1
324: and adl.po_distribution_id = pod.po_distribution_id
376: , adl.bud_task_id
377: , adl.ind_compiled_set_id
378: from ap_invoices I,
379: ap_invoice_distributions D,
380: gms_award_distributions ADL,
381: GL_LEDGERS G
382: where i.invoice_id = d.invoice_id
383: and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
384: I.invoice_id,
456: , adl.bud_task_id
457: , adl.ind_compiled_set_id
458: from ap_invoices I,
459: ap_invoice_distributions D,
460: gms_award_distributions ADL,
461: GL_LEDGERS G
462: where i.invoice_id = d.invoice_id
463: and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
464: I.invoice_id,
514: , adl.bud_task_id
515: , adl.ind_compiled_set_id
516: from AP_INVOICES I,
517: AP_SELF_ASSESSED_TAX_DIST apsat,
518: gms_award_distributions ADL,
519: GL_LEDGERS G
520: where i.invoice_id = apsat.invoice_id
521: and pa_cmt_utils.get_apdist_amt(apsat.invoice_distribution_id,
522: I.invoice_id,
575: , adl.bud_task_id
576: , adl.ind_compiled_set_id
577: from ap_invoices I,
578: ap_invoice_distributions D,
579: gms_award_distributions ADL,
580: gl_ledgers g
581: where i.invoice_id = d.invoice_id
582: and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
583: I.invoice_id,
649: , adl.bud_task_id
650: , adl.ind_compiled_set_id
651: from ap_invoices I,
652: ap_invoice_distributions D,
653: gms_award_distributions ADL,
654: gl_ledgers g
655: where i.invoice_id = d.invoice_id
656: and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
657: I.invoice_id,
702: , adl.bud_task_id
703: , adl.ind_compiled_set_id
704: from AP_INVOICES I,
705: AP_SELF_ASSESSED_TAX_DIST_ALL apsat,
706: gms_award_distributions ADL,
707: gl_ledgers g
708: where i.invoice_id = apsat.invoice_id
709: and pa_cmt_utils.get_apdist_amt(apsat.invoice_distribution_id,
710: I.invoice_id,
751: , adl.resource_list_member_id
752: , adl.bud_task_id
753: , adl.ind_compiled_set_id
754: from gms_encumbrance_items enc,
755: gms_award_distributions adl,
756: gms_encumbrances_all ge
757: where enc.encumbrance_item_id = adl.expenditure_item_id
758: and nvl(enc.enc_distributed_flag,'N') = 'Y'
759: and adl.adl_status = 'A'
1426: -- BUG 1522671 - For REQ,PO,AP if there is any idc rate changes when REQ->PO->AP
1427: -- then the reversing committments should be based on the orginal
1428: -- idc rate. This is also applicable if the above committments are reversed.
1429: -- For this the orignal idc rate schedule (ind_compiled_set_id)
1430: -- is picked from gms_award_distributions for the committements
1431: -- ----------------------------------------------------------------------------------
1432:
1433: -- ==================================================================================
1434: -- bug : 1698738 - IDC RATE CHANGES CAUSE DISCREPENCIES IN S.I. INTERFACE TO PROJECTS.
1459:
1460: BEGIN
1461: select adl.ind_compiled_set_id
1462: into l_compiled_set_id
1463: from gms_award_distributions adl
1464: -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1465: --po_req_distributions_all por
1466: where adl.award_id = x_award_id
1467: and adl.task_id = x_task_id
1480: ELSIF x_document_type = 'PO' THEN
1481: BEGIN
1482: select adl.ind_compiled_set_id
1483: into l_compiled_set_id
1484: from gms_award_distributions adl
1485: -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1486: -- po_distributions_all pod
1487: where adl.award_id = x_award_id
1488: and adl.task_id = x_task_id
1502: ELSIF x_document_type = 'AP' THEN
1503: BEGIN
1504: select adl.ind_compiled_set_id
1505: into l_compiled_set_id
1506: from gms_award_distributions adl
1507: -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1508: -- ap_invoice_distributions_all apd
1509: where adl.award_id = x_award_id
1510: and adl.task_id = x_task_id
1525: ELSIF x_document_type = 'EXP' THEN
1526: BEGIN
1527: select ind_compiled_set_id
1528: into l_compiled_set_id
1529: from gms_award_distributions
1530: where award_id = x_award_id
1531: and task_id = x_task_id
1532: and expenditure_item_id = x_document_header_id
1533: and cdl_line_num = x_document_distribution_id
2356: CURSOR C_req is
2357: SELECT adl.burdenable_raw_cost burden_amount,
2358: adl.adl_line_num,
2359: adl.award_set_id
2360: FROM gms_award_distributions adl ,
2361: po_req_distributions_all req
2362: WHERE adl.distribution_id = p_record.document_distribution_id
2363: AND req.distribution_id = p_record.document_distribution_id
2364: AND req.award_id = adl.award_set_id
2518: select nvl(adl.burdenable_raw_cost,0) ,
2519: adl.award_set_id award_set_id,
2520: adl_line_num adl_line_num
2521: FROM po_distributions_all pod,
2522: gms_award_distributions adl
2523: where adl.award_set_id = pod.award_id
2524: AND adl.po_distribution_id = pod.po_distribution_id
2525: and adl.adl_status = 'A'
2526: and pod.po_distribution_id = X_po_dist_id ;
2661: select nvl(adl.burdenable_raw_cost,0) ,
2662: adl.award_set_id award_set_id,
2663: adl_line_num adl_line_num
2664: FROM gms_encumbrance_items_all enc,
2665: gms_award_distributions adl
2666: where adl.expenditure_item_id = ENC.encumbrance_item_id
2667: and adl.adl_status = 'A'
2668: and ENC.encumbrance_item_id = bc_pkt_rec.document_header_id
2669: and enc.enc_distributed_flag = 'Y' ;
2760: adl.resource_list_member_id,
2761: adl.bud_task_id,
2762: adl.ind_compiled_set_id
2763: FROM ap_invoice_distributions APD,
2764: gms_award_distributions ADL,
2765: gl_ledgers G
2766: where adl.invoice_distribution_id = APD.invoice_distribution_id
2767: and adl.adl_status = 'A'
2768: and adl.award_id = P_record.award_id
2793: adl.resource_list_member_id,
2794: adl.bud_task_id,
2795: ADL.IND_COMPILED_SET_ID
2796: FROM AP_SELF_ASSESSED_TAX_DIST APSAT,
2797: gms_award_distributions ADL,
2798: GL_LEDGERS G
2799: where adl.invoice_distribution_id = APSAT.invoice_distribution_id
2800: and adl.adl_status = 'A'
2801: and adl.award_id = P_record.award_id
2927: adl.bud_task_id,
2928: adl.ind_compiled_set_id
2929: from ap_invoice_distributions_all apd1,
2930: ap_invoice_distributions_all apd2,
2931: gms_award_distributions adl
2932: where apd1.invoice_distribution_id = bc_pkt_rec.document_distribution_id
2933: and apd2.invoice_distribution_id = apd1.prepay_distribution_id
2934: and apd2.award_id = adl.award_set_id
2935: and adl.invoice_distribution_id = apd2.invoice_distribution_id
2952: adl.bud_task_id,
2953: ADL.IND_COMPILED_SET_ID
2954: from AP_SELF_ASSESSED_TAX_DIST_ALL apsat1,
2955: AP_SELF_ASSESSED_TAX_DIST_ALL apsat2,
2956: GMS_AWARD_DISTRIBUTIONS ADL
2957: where APSAT1.INVOICE_DISTRIBUTION_ID = BC_PKT_REC.DOCUMENT_DISTRIBUTION_ID
2958: and apsat2.invoice_distribution_id = apsat1.prepay_distribution_id
2959: and apsat2.award_id = adl.award_set_id
2960: and adl.invoice_distribution_id = apsat2.invoice_distribution_id
2988:
2989: CURSOR C_GET_AWARD_SET_ID IS
2990: SELECT adl.award_set_id , adl.adl_line_num
2991: FROM ap_invoice_distributions AP,
2992: gms_award_distributions ADL
2993: where adl.invoice_distribution_id = AP.invoice_distribution_id
2994: and adl.adl_status = 'A'
2995: and adl.award_id = p_record.award_id
2996: and ap.expenditure_type = p_record.expenditure_type
2999: and ap.invoice_distribution_id = P_RECORD.document_distribution_id -- AP Lines change
3000: union /* BUG 14216205 : Added the union for SAT */
3001: select ADL.AWARD_SET_ID , ADL.ADL_LINE_NUM
3002: FROM AP_SELF_ASSESSED_TAX_DIST APSAT,
3003: GMS_AWARD_DISTRIBUTIONS ADL
3004: where adl.invoice_distribution_id = APSAT.invoice_distribution_id
3005: and adl.adl_status = 'A'
3006: and ADL.AWARD_ID = P_RECORD.AWARD_ID
3007: and APSAT.EXPENDITURE_TYPE = P_RECORD.EXPENDITURE_TYPE
3018: 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount ,
3019: sum(nvl(adl.burdenable_raw_cost,0)) sum_burden,
3020: count(*) sum_count
3021: FROM ap_invoice_distributions AP,
3022: gms_award_distributions ADL,
3023: gl_ledgers G
3024: where adl.invoice_distribution_id = AP.invoice_distribution_id
3025: and adl.adl_status = 'A'
3026: and adl.award_id = bc_pkt_rec.award_id
3044: 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount ,
3045: sum(nvl(adl.burdenable_raw_cost,0)) sum_burden,
3046: COUNT(*) SUM_COUNT
3047: FROM AP_SELF_ASSESSED_TAX_DIST APSAT,
3048: gms_award_distributions ADL,
3049: gl_ledgers G
3050: where adl.invoice_distribution_id = APSAT.invoice_distribution_id
3051: and adl.adl_status = 'A'
3052: and adl.award_id = bc_pkt_rec.award_id
3668: nvl(ap.base_amount,ap.amount),
3669: 'N',
3670: 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount
3671: FROM ap_invoice_distributions AP,
3672: gms_award_distributions ADL,
3673: GL_LEDGERS G
3674: where adl.invoice_distribution_id = AP.invoice_distribution_id
3675: and adl.adl_status = 'A'
3676: and adl.award_id = p_award_id
4598: BEGIN
4599: null; -- All code remove as this will be taken care by Interface FC
4600: END PPAY_LOG_POSTING ;
4601:
4602: PROCEDURE CALC_prepayment_burden( X_AP_REC ap_invoice_distributions_all%ROWTYPE , X_adl_rec gms_award_distributions%ROWTYPE ) is
4603: BEGIN
4604: null; -- All code remove as this will be taken care by Interface FC
4605: END CALC_prepayment_burden ;
4606:
4647: l_stage := 'Retrieve records';
4648: FOR bc_records in c_packet
4649: LOOP
4650: If bc_records.document_type = 'REQ' then
4651: update gms_award_distributions
4652: set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4653: ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4654: 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id),
4655: last_update_date=SYSDATE, --BUG14274932
4658: and adl_status = 'A'
4659: and document_type = 'REQ';
4660:
4661: Elsif bc_records. document_type = 'PO' then
4662: update gms_award_distributions
4663: set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4664: ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4665: 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id),
4666: last_update_date=SYSDATE, --BUG14274932
4669: and adl_status = 'A'
4670: and document_type = 'PO';
4671:
4672: Elsif bc_records.document_type = 'AP' then
4673: update gms_award_distributions
4674: set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4675: ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4676: 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id),
4677: last_update_date=SYSDATE, --BUG14274932
4681: and adl_status = 'A'
4682: and document_type = 'AP';
4683:
4684: Elsif bc_records.document_type = 'ENC' then
4685: update gms_award_distributions
4686: set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4687: ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4688: 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id),
4689: last_update_date=SYSDATE, --BUG14274932
4758: -- Resolution : Joins with the base tables were added.
4759: --
4760: select adl.ind_compiled_set_id
4761: into x_ind_compiled_set_id
4762: from gms_award_distributions adl,
4763: po_req_distributions_all req
4764: where req.distribution_id = x_distribution_id
4765: and req.award_id = adl.award_set_id
4766: and adl.adl_line_num = 1 ;
4771: -- Resolution : Joins with the base tables were added.
4772: --
4773: select adl.ind_compiled_set_id
4774: into x_ind_compiled_set_id
4775: from gms_award_distributions adl,
4776: po_distributions_all po
4777: where po.po_distribution_id = x_distribution_id
4778: and po.award_id = adl.award_set_id
4779: and adl.adl_line_num = 1 ;
4785: --
4786: /* BUG 14216205 : Commented the below code : Starts */
4787: /*select adl.ind_compiled_set_id
4788: into x_ind_compiled_set_id
4789: from gms_award_distributions adl,
4790: ap_invoice_distributions_all apd
4791: where apd.distribution_line_number = x_distribution_line_number
4792: and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
4793: and apd.award_id = adl.award_set_id
4797: /* BUG 14216205 : Added the following Code for SAT : Starts */
4798: BEGIN
4799: select adl.ind_compiled_set_id
4800: into x_ind_compiled_set_id
4801: from gms_award_distributions adl,
4802: ap_invoice_distributions_all apd
4803: where apd.distribution_line_number = x_distribution_line_number
4804: and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
4805: and apd.award_id = adl.award_set_id
4810: EXCEPTION
4811: when NO_DATA_FOUND then
4812: select adl.ind_compiled_set_id
4813: into x_ind_compiled_set_id
4814: from GMS_AWARD_DISTRIBUTIONS ADL,
4815: AP_SELF_ASSESSED_TAX_DIST_ALL apsat
4816: where apsat.distribution_line_number = x_distribution_line_number
4817: and apsat.invoice_distribution_id = x_distribution_id -- AP Lines change
4818: and apsat.AWARD_ID = ADL.AWARD_SET_ID
4825:
4826: elsif x_doc_type = 'ENC' then
4827: select ind_compiled_set_id
4828: into x_ind_compiled_set_id
4829: from gms_award_distributions
4830: where expenditure_item_id = x_distribution_id
4831: and adl_status = 'A'
4832: and fc_status = 'A'
4833: and nvl(reversed_flag, 'N') <> 'Y' --Bug 5726575
4858: -- Resolution : Joins with the base tables were added.
4859: --
4860: select adl.burdenable_raw_cost
4861: into x_burdenable_raw_cost
4862: from gms_award_distributions adl,
4863: po_req_distributions_all req
4864: where req.distribution_id = x_distribution_id
4865: and req.award_id = adl.award_set_id
4866: and adl.adl_line_num = 1 ;
4871: -- Resolution : Joins with the base tables were added.
4872: --
4873: select adl.burdenable_raw_cost
4874: into x_burdenable_raw_cost
4875: from gms_award_distributions adl,
4876: po_distributions_all po
4877: where po.po_distribution_id = x_distribution_id
4878: and po.award_id = adl.award_set_id
4879: and adl.adl_line_num = 1 ;
4885: --
4886: /* BUG 14216205 : Commented the below code : Starts */
4887: /*select adl.burdenable_raw_cost
4888: into x_burdenable_raw_cost
4889: from gms_award_distributions adl,
4890: ap_invoice_distributions_all apd
4891: where apd.distribution_line_number = x_distribution_line_number
4892: and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
4893: and apd.award_id = adl.award_set_id
4897: /* BUG 14216205 : Added the following Code for SAT : Starts */
4898: BEGIN
4899: select adl.burdenable_raw_cost
4900: into x_burdenable_raw_cost
4901: from gms_award_distributions adl,
4902: ap_invoice_distributions_all apd
4903: where apd.distribution_line_number = x_distribution_line_number
4904: and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
4905: and apd.award_id = adl.award_set_id
4910: EXCEPTION
4911: when no_data_found then
4912: select adl.burdenable_raw_cost
4913: into x_burdenable_raw_cost
4914: from GMS_AWARD_DISTRIBUTIONS ADL,
4915: AP_SELF_ASSESSED_TAX_DIST_ALL apsat
4916: where apsat.distribution_line_number = x_distribution_line_number
4917: and apsat.invoice_distribution_id = x_distribution_id -- AP Lines change
4918: and apsat.AWARD_ID = ADL.AWARD_SET_ID
4926:
4927: elsif x_doc_type = 'ENC' then
4928: select burdenable_raw_cost
4929: into x_burdenable_raw_cost
4930: from gms_award_distributions
4931: where expenditure_item_id = x_distribution_id
4932: and adl_status = 'A'
4933: and fc_status = 'A'
4934: and nvl(reversed_flag, 'N') <> 'Y' --Bug 5726575