[Home] [Help]
2806: SELECT a.run_id run_id
2807: , a.project_id project_id
2808: , a.task_id task_id
2809: , nvl(a.eligible_amount,0) eligible_amount
2810: FROM pa_alloc_run_source_det a,
2811: pa_alloc_runs ar
2812: WHERE a.run_id = ar.run_id
2813: AND a.rule_id = p_rule_id
2814: AND ar.fiscal_year = NVL(p_fscl_year, ar.fiscal_year)
2815: AND ar.quarter = NVL(p_qrtr_num, ar.quarter)
2816: AND ar.period_num = NVL(p_prd_num, ar.period_num )
2817: AND a.run_id < p_run_id
2818: AND NOT EXISTS ( SELECT 1
2819: FROM pa_alloc_run_source_det b
2820: WHERE a.project_id = b.project_id
2821: AND a.task_id = b.task_id
2822: AND b.rule_id = p_rule_id
2823: AND b.run_id = p_run_id )
2821: AND a.task_id = b.task_id
2822: AND b.rule_id = p_rule_id
2823: AND b.run_id = p_run_id )
2824: AND a.run_id =( SELECT max(c.run_id)
2825: FROM pa_alloc_run_source_det c,
2826: pa_alloc_runs c_ar -- added this table to exclude reversed runs.
2827: WHERE c.project_id = a.project_id
2828: AND c.task_id = a.task_id
2829: AND c.run_id = c_ar.run_id
2841: SELECT A.Run_Id Run_Id,
2842: A.Project_Id Project_Id,
2843: A.Task_Id Task_Id,
2844: NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
2845: FROM PA_ALLOC_RUN_SOURCE_DET A ,
2846: ( --
2847: -- The purpose of this in-line view is to return
2848: -- the PA_ALLOC_RUNS (single) record for the largest Run_Id
2849: -- less than the input p_Run_Id
2861: WHERE A.Run_Id = AR.Run_Id
2862: AND A.Rule_Id = p_Rule_Id
2863: AND NOT EXISTS
2864: ( SELECT 1
2865: FROM PA_ALLOC_RUN_SOURCE_DET B
2866: WHERE A.Project_Id = B.Project_Id
2867: AND A.Task_Id = B.Task_Id
2868: AND B.Rule_Id = p_Rule_Id
2869: AND B.Run_Id = p_Run_Id
3635: , p_pool_amount IN NUMBER
3636: , p_allocated_amount IN NUMBER ) IS
3637: CURSOR C_offset_det IS
3638: SELECT line_num, project_id, task_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3639: FROM pa_alloc_run_source_det
3640: WHERE run_id=p_run_id
3641: GROUP BY line_num, project_id, task_id;
3642: CURSOR c_offset_proj_sum IS
3643: SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3640: WHERE run_id=p_run_id
3641: GROUP BY line_num, project_id, task_id;
3642: CURSOR c_offset_proj_sum IS
3643: SELECT project_id, nvl(sum(nvl(eligible_amount,0)),0) eligible_amount
3644: FROM pa_alloc_run_source_det
3645: WHERE run_id = p_run_id
3646: GROUP BY project_id ;
3647: CURSOR c_pool_amount IS
3648: SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
3645: WHERE run_id = p_run_id
3646: GROUP BY project_id ;
3647: CURSOR c_pool_amount IS
3648: SELECT nvl(sum(nvl(eligible_amount,0)),0) pool_amount
3649: FROM pa_alloc_run_source_det
3650: WHERE run_id = p_run_id;
3651: v_project_id NUMBER ;
3652: v_task_id NUMBER ;
3653: v_proj_prev_offset_amt NUMBER ;
6118: RAISE ;
6119: END insert_alloc_basis_resource;
6120: -- ==========================================================================
6121: /* PROCEDURE : insert_alloc_source_resource
6122: Purpose : To insert data into pa_alloc_run_source_det table for each resource
6123: for each task which has some data available in summarization.
6124: Separate inserts are written for each type of amt_type
6125: (FYTD,qtd,itd and ptd).
6126: Created : 16-JAN-02 Manokuma
6199: IF P_DEBUG_MODE = 'Y' THEN
6200: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
6201: END IF;
6202: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------------{
6203: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6204: RUN_ID
6205: , RULE_ID
6206: , LINE_NUM
6207: , PROJECT_ID
6302: ,G_last_updated_by
6303: ,G_last_update_login);
6304: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6305: /* FP.M : Allocation Impact */
6306: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
6307: RUN_ID
6308: , RULE_ID
6309: , LINE_NUM
6310: , PROJECT_ID
6417: IF P_DEBUG_MODE = 'Y' THEN
6418: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6419: END IF;
6420: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ------------{
6421: INSERT INTO PA_ALLOC_RUN_source_DET (
6422: RUN_ID
6423: , RULE_ID
6424: , LINE_NUM
6425: , PROJECT_ID
6499: ,G_last_update_date
6500: ,G_last_updated_by
6501: ,G_last_update_login);
6502: ElsiF Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6503: INSERT INTO PA_ALLOC_RUN_source_DET (
6504: RUN_ID
6505: , RULE_ID
6506: , LINE_NUM
6507: , PROJECT_ID
6580: ,G_last_update_login);
6581: End If; -------------}
6582: ELSE /* p_run_period_type = 'GL' */
6583: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ----------- {
6584: INSERT INTO PA_ALLOC_RUN_source_DET (
6585: RUN_ID
6586: , RULE_ID
6587: , LINE_NUM
6588: , PROJECT_ID
6666: ,G_last_update_date
6667: ,G_last_updated_by
6668: ,G_last_update_login);
6669: ElsIF Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6670: INSERT INTO PA_ALLOC_RUN_source_DET (
6671: RUN_ID
6672: , RULE_ID
6673: , LINE_NUM
6674: , PROJECT_ID
6775: IF P_DEBUG_MODE = 'Y' THEN
6776: pa_debug.write_file('insert_alloc_source_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
6777: END IF;
6778: IF Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------{
6779: INSERT INTO PA_ALLOC_RUN_source_DET (
6780: RUN_ID
6781: , RULE_ID
6782: , LINE_NUM
6783: , PROJECT_ID
6866: ,G_last_update_date
6867: ,G_last_updated_by
6868: ,G_last_update_login);
6869: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6870: INSERT INTO PA_ALLOC_RUN_source_DET (
6871: RUN_ID
6872: , RULE_ID
6873: , LINE_NUM
6874: , PROJECT_ID
7304: , p_last_update_login IN NUMBER)
7305: IS
7306: BEGIN
7307: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7308: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7309: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7310: RUN_ID
7311: , RULE_ID
7312: , LINE_NUM
7305: IS
7306: BEGIN
7307: pa_debug.set_err_stack('insert_alloc_run_source_det') ;
7308: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_SOURCE_DET' ;
7309: INSERT INTO PA_ALLOC_RUN_SOURCE_DET (
7310: RUN_ID
7311: , RULE_ID
7312: , LINE_NUM
7313: , PROJECT_ID
7407: and run_id = p_run_id
7408: and exclude_flag <> 'Y' ;
7409: cursor C_get_pool_amount is
7410: select nvl(sum(nvl(eligible_amount,0)),0)
7411: from pa_alloc_run_source_det
7412: where run_id = p_run_id
7413: AND (pa_project_stus_utils.is_project_closed(project_id ))= 'N'; -- added as part of the bug 13949738
7414:
7415: -- Start: added as part of the bug 13949738
7413: AND (pa_project_stus_utils.is_project_closed(project_id ))= 'N'; -- added as part of the bug 13949738
7414:
7415: -- Start: added as part of the bug 13949738
7416: cursor C_get_missing_source_amount is
7417: Select * from pa_alloc_run_source_det
7418: where run_id = p_run_id
7419: AND (pa_project_stus_utils.is_project_closed(project_id ))= 'Y';
7420:
7421: CURSOR C_get_previous_run_amount
7425: , p_Project_Id IN NUMBER
7426: , p_Task_Id IN NUMBER
7427: ) IS
7428: SELECT NVL ( A.Eligible_Amount, 0 ) Eligible_Amount
7429: FROM PA_ALLOC_RUN_SOURCE_DET A ,
7430: ( SELECT MAX ( AR.Run_Id ) AS Run_Id
7431: FROM PA_ALLOC_RUNS AR
7432: WHERE AR.Fiscal_Year = NVL ( p_Fscl_Year , AR.Fiscal_Year )
7433: AND AR.Quarter = NVL ( p_Qrtr_Num , AR.Quarter )
7574: END IF;
7575: If p_run_amount_type in ( 'FYTD','QTD') then
7576: For l_get_proj in c_get_proj
7577: Loop
7578: Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id,
7579: task_id, creation_date, created_by,
7580: last_update_date,
7581: last_updated_by, last_update_login,
7582: amount, eligible_amount)
7644: elsif p_run_amount_type = 'PTD' then
7645: If p_run_period_type = 'GL' then
7646: For l_get_proj in c_get_proj
7647: Loop
7648: Insert into pa_alloc_run_source_det( rule_id, run_id, line_num, project_id, task_id,
7649: creation_date, created_by,
7650: last_update_date,
7651: last_updated_by, last_update_login,
7652: amount, eligible_amount)
7703: End Loop;
7704: elsif p_run_period_type = 'PA' then
7705: For l_get_proj in c_get_proj
7706: Loop
7707: Insert into pa_alloc_run_source_det(
7708: rule_id, run_id, line_num, project_id, task_id,
7709: creation_date, created_by, last_update_date,
7710: last_updated_by, last_update_login,
7711: amount, eligible_amount)
7759: end if ;
7760: else
7761: For l_get_proj in c_get_proj
7762: Loop
7763: Insert into pa_alloc_run_source_det(
7764: rule_id, run_id, line_num, project_id, task_id,
7765: creation_date, created_by, last_update_date,
7766: last_updated_by, last_update_login,
7767: amount, eligible_amount)
9914: /*Code Changes for Bug No.2984871 end */
9915: End Loop;
9916: --- Delete Transactions from source_details
9917: While 1=1 Loop /* Bug 2176096 */
9918: Delete from pa_alloc_run_source_det
9919: where run_id = p_run_id and rownum < 10001;
9920: /* Commented for Bug 2984871 Commit; */
9921: If Sql%Rowcount = 0 THen
9922: Exit;
10064: /*Code Changes for Bug No.2984871 end */
10065: End Loop;
10066: --- Delete Transactions from source_details
10067: While 1=1 Loop /* Bug 2176096 */
10068: Delete from pa_alloc_run_source_det
10069: where run_id = l_run_id and rownum < 10001;
10070: /* Commented for Bug 2984871 Commit; */
10071: If Sql%Rowcount = 0 THen
10072: Exit;