[Home] [Help]
395:
396: CURSOR events_to_rollback
397: IS
398: SELECT DISTINCT project_id, event_num, event_calling_process event_type
399: FROM gms_event_attribute gea
400: WHERE project_id = x_award_project_id
401: --AND event_calling_process = x_calling_process
402: and event_num > 0 /* Added for bug 4594090 */ /*Changed to >0 for bug 6969435 */
403: AND ( (x_event_num IS NULL) OR
429: SELECT DISTINCT gei.expenditure_item_id, gei.adl_line_num,
430: gea.actual_project_id, gea.actual_task_id,
431: gea.expenditure_org_id, gei.amount,
432: gei.revenue_accumulated, gei.creation_date,gea.request_id
433: FROM gms_event_intersect gei, gms_event_attribute gea
434: WHERE gea.project_id = f_project_id
435: AND gea.event_num = f_event_num
436: AND gea.event_calling_process = f_event_calling_process
437: AND gei.award_project_id = gea.project_id
439: AND gei.event_type = UPPER(f_event_calling_process);
440:
441: f_raw_exp_item_id gms_award_distributions.expenditure_item_id%TYPE;
442: f_raw_adl_line_num gms_award_distributions.adl_line_num%TYPE;
443: f_raw_act_project_id gms_event_attribute.actual_project_id%TYPE;
444: f_raw_act_task_id gms_event_attribute.actual_task_id%TYPE;
445: f_raw_org_id gms_event_attribute.expenditure_org_id%TYPE;
446: f_raw_rev_accumulated gms_event_intersect.revenue_accumulated%TYPE;
447: f_raw_revenue_amount gms_event_intersect.amount%TYPE;
440:
441: f_raw_exp_item_id gms_award_distributions.expenditure_item_id%TYPE;
442: f_raw_adl_line_num gms_award_distributions.adl_line_num%TYPE;
443: f_raw_act_project_id gms_event_attribute.actual_project_id%TYPE;
444: f_raw_act_task_id gms_event_attribute.actual_task_id%TYPE;
445: f_raw_org_id gms_event_attribute.expenditure_org_id%TYPE;
446: f_raw_rev_accumulated gms_event_intersect.revenue_accumulated%TYPE;
447: f_raw_revenue_amount gms_event_intersect.amount%TYPE;
448: f_raw_creation_date gms_event_intersect.creation_date%TYPE;
441: f_raw_exp_item_id gms_award_distributions.expenditure_item_id%TYPE;
442: f_raw_adl_line_num gms_award_distributions.adl_line_num%TYPE;
443: f_raw_act_project_id gms_event_attribute.actual_project_id%TYPE;
444: f_raw_act_task_id gms_event_attribute.actual_task_id%TYPE;
445: f_raw_org_id gms_event_attribute.expenditure_org_id%TYPE;
446: f_raw_rev_accumulated gms_event_intersect.revenue_accumulated%TYPE;
447: f_raw_revenue_amount gms_event_intersect.amount%TYPE;
448: f_raw_creation_date gms_event_intersect.creation_date%TYPE;
449:
476: IS
477: SELECT actual_project_id, actual_task_id, installment_id,
478: revenue_amount, bill_amount
479: , rowid -- Bug 2715312
480: FROM gms_event_attribute
481: WHERE project_id = f_project_id
482: AND event_num = f_event_num
483: AND event_calling_process = f_event_calling_process;
484:
481: WHERE project_id = f_project_id
482: AND event_num = f_event_num
483: AND event_calling_process = f_event_calling_process;
484:
485: f_act_project_id gms_event_attribute.actual_project_id%TYPE;
486: f_act_task_id gms_event_attribute.actual_task_id%TYPE;
487: f_installment_id gms_event_attribute.installment_id%TYPE;
488: f_rev_amount gms_event_attribute.revenue_amount%TYPE;
489: f_bill_amount gms_event_attribute.bill_amount%TYPE;
482: AND event_num = f_event_num
483: AND event_calling_process = f_event_calling_process;
484:
485: f_act_project_id gms_event_attribute.actual_project_id%TYPE;
486: f_act_task_id gms_event_attribute.actual_task_id%TYPE;
487: f_installment_id gms_event_attribute.installment_id%TYPE;
488: f_rev_amount gms_event_attribute.revenue_amount%TYPE;
489: f_bill_amount gms_event_attribute.bill_amount%TYPE;
490: f_row_id rowid ; -- Bug 2715312
483: AND event_calling_process = f_event_calling_process;
484:
485: f_act_project_id gms_event_attribute.actual_project_id%TYPE;
486: f_act_task_id gms_event_attribute.actual_task_id%TYPE;
487: f_installment_id gms_event_attribute.installment_id%TYPE;
488: f_rev_amount gms_event_attribute.revenue_amount%TYPE;
489: f_bill_amount gms_event_attribute.bill_amount%TYPE;
490: f_row_id rowid ; -- Bug 2715312
491: x_stage NUMBER (2);
484:
485: f_act_project_id gms_event_attribute.actual_project_id%TYPE;
486: f_act_task_id gms_event_attribute.actual_task_id%TYPE;
487: f_installment_id gms_event_attribute.installment_id%TYPE;
488: f_rev_amount gms_event_attribute.revenue_amount%TYPE;
489: f_bill_amount gms_event_attribute.bill_amount%TYPE;
490: f_row_id rowid ; -- Bug 2715312
491: x_stage NUMBER (2);
492: BEGIN
485: f_act_project_id gms_event_attribute.actual_project_id%TYPE;
486: f_act_task_id gms_event_attribute.actual_task_id%TYPE;
487: f_installment_id gms_event_attribute.installment_id%TYPE;
488: f_rev_amount gms_event_attribute.revenue_amount%TYPE;
489: f_bill_amount gms_event_attribute.bill_amount%TYPE;
490: f_row_id rowid ; -- Bug 2715312
491: x_stage NUMBER (2);
492: BEGIN
493: /* --------------------------------------------------------------------------
495: || 10. Updating gms_award_distributions (revenue_distributed_flag/billed_flag)
496: || 20. Deleting from gms_event_intersect
497: || 30. Deleting from gms_burden_components
498: || 40. Updating gms_summary_project_fundings
499: || 50. Deleting gms_event_attribute
500: -------------------------------------------------------------------------- */
501:
502: FOR evt IN events_to_rollback
503: LOOP
692: AND installment_id = f_installment_id;
693:
694: END IF;
695:
696: -- 50: Delete gms_event_attribute
697:
698: DELETE
699: FROM gms_event_attribute
700: WHERE rowid= f_row_id ; -- Bug 2715312, Replaced the below conditions with rowid
695:
696: -- 50: Delete gms_event_attribute
697:
698: DELETE
699: FROM gms_event_attribute
700: WHERE rowid= f_row_id ; -- Bug 2715312, Replaced the below conditions with rowid
701: -- WHERE project_id = evt.project_id
702: -- AND event_num = evt.event_num
703: -- AND actual_project_id = f_act_project_id
1723:
1724: End GET_INSTALLMENT_NUM;
1725:
1726:
1727: -- PROCEDURE: INSERT_EVENT, new procedure to insert records into GMS_EVENT_ATTRIBUTE table
1728:
1729: PROCEDURE INSERT_EVENT(X_AWARD_PROJECT_ID IN NUMBER DEFAULT NULL,
1730: X_EVENT_NUM IN NUMBER DEFAULT NULL,
1731: X_INSTALLMENT_ID IN NUMBER DEFAULT NULL,
1741: X_Err_Buff IN OUT NOCOPY VARCHAR2,
1742: X_Calling_Process IN VARCHAR2 DEFAULT NULL) IS
1743:
1744: Begin
1745: /* Insert into GMS_EVENT_ATTRIBUTE_TABLE */
1746:
1747: INSERT INTO GMS_EVENT_ATTRIBUTE(
1748: PROJECT_ID,
1749: EVENT_NUM,
1743:
1744: Begin
1745: /* Insert into GMS_EVENT_ATTRIBUTE_TABLE */
1746:
1747: INSERT INTO GMS_EVENT_ATTRIBUTE(
1748: PROJECT_ID,
1749: EVENT_NUM,
1750: INSTALLMENT_ID,
1751: ACTUAL_PROJECT_ID,
1804:
1805: End INSERT_EVENT;
1806:
1807:
1808: -- PROCEDURE: UPDATE_EVENT, new procedure to update GMS_EVENT_ATTRIBUTE table records
1809:
1810: PROCEDURE UPDATE_EVENT(X_AWARD_PROJECT_ID IN NUMBER DEFAULT NULL,
1811: X_EVENT_NUM IN NUMBER DEFAULT NULL,
1812: X_INSTALLMENT_ID IN NUMBER DEFAULT NULL,
1821: X_Err_Code IN OUT NOCOPY NUMBER,
1822: X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
1823:
1824: Begin
1825: /* Update GMS_EVENT_ATTRIBUTE record */
1826:
1827: UPDATE GMS_EVENT_ATTRIBUTE
1828: SET installment_id = X_INSTALLMENT_ID,
1829: actual_project_id = X_ACTUAL_PROJECT_ID,
1823:
1824: Begin
1825: /* Update GMS_EVENT_ATTRIBUTE record */
1826:
1827: UPDATE GMS_EVENT_ATTRIBUTE
1828: SET installment_id = X_INSTALLMENT_ID,
1829: actual_project_id = X_ACTUAL_PROJECT_ID,
1830: actual_task_id = X_ACTUAL_TASK_ID,
1831: burden_cost_code = X_BURDEN_COST_CODE,
1862: End If;
1863:
1864: END UPDATE_EVENT;
1865:
1866: -- PROCEDURE: DELETE_EVENT, new procedure to delete records from GMS_EVENT_ATTRIBUTE table
1867:
1868: PROCEDURE DELETE_EVENT (X_AWARD_PROJECT_ID IN NUMBER,
1869: X_EVENT_NUM IN NUMBER,
1870: X_INSTALLMENT_ID IN NUMBER,
1871: X_Err_Code IN OUT NOCOPY NUMBER,
1872: X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
1873:
1874: Begin
1875: /* Delete from GMS_EVENT_ATTRIBUTE_TABLE */
1876:
1877: DELETE
1878: FROM gms_event_attribute
1879: WHERE project_id = X_AWARD_PROJECT_ID
1874: Begin
1875: /* Delete from GMS_EVENT_ATTRIBUTE_TABLE */
1876:
1877: DELETE
1878: FROM gms_event_attribute
1879: WHERE project_id = X_AWARD_PROJECT_ID
1880: AND event_num = X_EVENT_NUM
1881: AND installment_id = X_INSTALLMENT_ID
1882: AND event_calling_process IS NULL; --Added for bug 2979125
3138: IF L_DEBUG = 'Y' THEN
3139: gms_error_pkg.gms_debug('IN EVENT ARRIBUTE UPDATION -- START','C');
3140: END IF;
3141:
3142: update GMS_EVENT_ATTRIBUTE
3143: set
3144: EVENT_NUM = X_Event_Num
3145: ,last_update_date = sysdate
3146: ,last_updated_by = fnd_global.user_id
3156: into x_count
3157: from dual
3158: where exists
3159: (select 1
3160: from gms_event_attribute
3161: where request_id = X_request_id
3162: and project_id = X_Award_Project_Id
3163: and EVENT_NUM IS NULL);
3164:
3616: --Procedure CREATE_PA_EVENTS
3617: -----------------------------------------------------------------------------------------
3618: -- Bug 3235390 : This newly introduced procedure does the following :
3619: -- a. Fetches consolidated records from the plsql table
3620: -- b. Inserts consolidated events into gms_event_attribute table
3621: -- b. If amount <> 0 then inserts events into Pa_events and
3622: -- updates GEI and GEA with newly generated event_num.
3623: -- c. If amount = 0 then updates -1 on GEA and GEI and sets the
3624: -- NULL events variables.
3818: -- Procedure DO_EVENT_PROCESSING: This procedure builds the Event description and creates the
3819: -- Billing or Revenue Event
3820: -----------------------------------------------------------------------------------------
3821: -- Bug 3235390 : Modified the logic as mentioned below
3822: -- a. Code to create events in pa_events and gms_event_attribute table is
3823: -- shifted to CREATE_PA_EVENTS procedure.
3824: -- b. Events will be inserted into GBC and GEI and will be marked as billed/accrued.
3825: -- c. Events will be assigned a negative seqeunce number which starts from -1001
3826: -- d. Distinct events and their format will be temporarly stored in plsql table
3825: -- c. Events will be assigned a negative seqeunce number which starts from -1001
3826: -- d. Distinct events and their format will be temporarly stored in plsql table
3827: -- e. Newly introduced procedure CREATE_PA_EVENTS will fetch the consolidated
3828: -- records from the plsql table and will insert events into Pa_events and
3829: -- gms_event_attribute tables and updates GEI with newly generated event_num.
3830: -- Even the NULL events logic is shifted to create_pa_events procedure.
3831: ----------------------------------------------------------------------------------------------------
3832:
3833:
4352: ----------------------------------------------------------------------------------------
4353: --Procedure DO_REV_EVENT_PROCESSING
4354: -----------------------------------------------------------------------------------------
4355: -- Bug 3235390 : Modified the logic as mentioned below
4356: -- a. Code to create events in pa_events and gms_event_attribute table is
4357: -- shifted to CREATE_PA_EVENTS procedure.
4358: -- b. Events will be inserted into GBC and GEI and will be marked as billed/accrued.
4359: -- c. Events will be assigned a negative seqeunce number which starts from -1001
4360: -- d. Distinct events and their format will be temporarly stored in plsql table
4359: -- c. Events will be assigned a negative seqeunce number which starts from -1001
4360: -- d. Distinct events and their format will be temporarly stored in plsql table
4361: -- e. Newly introduced procedure CREATE_PA_EVENTS will fetch the consolidated
4362: -- records from the plsql table and will insert events into Pa_events and
4363: -- gms_event_attribute tables and updates GEI with newly generated event_num.
4364: -- Even the NULL events logic is shifted to create_pa_events procedure.
4365: ----------------------------------------------------------------------------------------------------
4366:
4367: PROCEDURE DO_REV_EVENT_PROCESSING(X_Act_Project_Id IN NUMBER,
7210:
7211: f_dummy := f_dummy + 1;
7212:
7213: -- Bug 3235390 : Calling the CREATE_PA_EVENTS procedure to insert records in pa_events
7214: -- gms_event_attribute table from temporary table.
7215:
7216: CREATE_PA_EVENTS (X_project_id ,
7217: X_Calling_Process ,
7218: X_rev_or_bill_Date ,
7357:
7358: g_non_labor_neg_exp_processed := f_non_labor_neg_exp_processed ; /* Added for bug 5026657 */
7359:
7360: -- Bug 3235390 : Calling the CREATE_PA_EVENTS procedure to insert records in pa_events
7361: -- gms_event_attribute table from temporary table.
7362:
7363: /* Added for bug 5026657 */
7364: IF (g_labor_exp_to_process = 'Y' and g_non_labor_neg_exp_processed = 'Y') THEN
7365: g_process_again := 'Y';