[Home] [Help]
3302: , p_line_num IN NUMBER
3303: , p_project_id IN NUMBER
3304: , p_task_id IN NUMBER ) IS
3305: SELECT nvl(sum( nvl(basis_percent, 0)*nvl(line_percent,100)/10000),0) basis
3306: FROM pa_alloc_run_basis_det
3307: WHERE run_id = p_run_id
3308: AND line_num = p_line_num
3309: AND project_id = p_project_id
3310: AND task_id = p_task_id ;
5394: RAISE ;
5395: END get_amttype_start_date ;
5396: -- ==========================================================================
5397: /* PROCEDURE : insert_alloc_basis_resource
5398: Purpose : To insert data into pa_alloc_run_basis_det table for each resource
5399: for each task which has some data available in summarization.
5400: Separate inserts are written for each type of amt_type
5401: (FYTD,qtd,itd and ptd).
5402: Created : 16-JAN-02 Manokuma
5460: IF P_DEBUG_MODE = 'Y' THEN
5461: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
5462: END IF;
5463: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------- {
5464: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5465: RUN_ID
5466: , RULE_ID
5467: , LINE_NUM
5468: , PROJECT_ID
5546: ,G_last_update_date
5547: ,G_last_updated_by
5548: ,G_last_update_login);
5549: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5550: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5551: RUN_ID
5552: , RULE_ID
5553: , LINE_NUM
5554: , PROJECT_ID
5646: IF P_DEBUG_MODE = 'Y' THEN
5647: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5648: END IF;
5649: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------{
5650: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5651: RUN_ID
5652: , RULE_ID
5653: , LINE_NUM
5654: , PROJECT_ID
5714: ,G_last_update_date
5715: ,G_last_updated_by
5716: ,G_last_update_login);
5717: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5718: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5719: RUN_ID
5720: , RULE_ID
5721: , LINE_NUM
5722: , PROJECT_ID
5781: ,G_last_update_login);
5782: End If; ---------------- }
5783: ELSE /* p_run_period_type = 'GL' */
5784: If Nvl(p_resource_struct_type,'RL') = 'RL' Then --------------{
5785: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5786: RUN_ID
5787: , RULE_ID
5788: , LINE_NUM
5789: , PROJECT_ID
5853: ,G_last_update_date
5854: ,G_last_updated_by
5855: ,G_last_update_login);
5856: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5857: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5858: RUN_ID
5859: , RULE_ID
5860: , LINE_NUM
5861: , PROJECT_ID
5947: IF P_DEBUG_MODE = 'Y' THEN
5948: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5949: END IF;
5950: If Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------------{
5951: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5952: RUN_ID
5953: , RULE_ID
5954: , LINE_NUM
5955: , PROJECT_ID
6023: ,G_last_update_date
6024: ,G_last_updated_by
6025: ,G_last_update_login);
6026: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6027: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
6028: RUN_ID
6029: , RULE_ID
6030: , LINE_NUM
6031: , PROJECT_ID
6975: RAISE ;
6976: END insert_alloc_source_resource;
6977: -- ==========================================================================
6978: /* PROCEDURE : insert_budget_basis_resource
6979: Purpose : inserts records into pa_alloc_run_basis_det when budgets are used
6980: for basis and resource lists are used to calculate basis amounts.
6981: Created : 02-feb-01 Manoj.
6982: Modified : 24-JAN-03 Tarun for bug 2757875
6983: 06-Apr-04 vthakkar FP.M : ALlocation Impact
7001: v_commit NUMBER := 0;
7002: BEGIN
7003: FOR c_projects_rec IN c_projects LOOP
7004: If p_run_period_type = 'PA' then
7005: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7006: RUN_ID
7007: , RULE_ID
7008: , LINE_NUM
7009: , PROJECT_ID
7066: ,G_last_update_date
7067: ,G_last_updated_by
7068: ,G_last_update_login);
7069: Elsif p_run_period_type = 'GL' then
7070: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7071: RUN_ID
7072: , RULE_ID
7073: , LINE_NUM
7074: , PROJECT_ID
7953: IF P_DEBUG_MODE = 'Y' THEN
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7959: RUN_ID
7960: , RULE_ID
7961: , LINE_NUM
7954: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7955: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7956: END IF;
7957: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7958: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7959: RUN_ID
7960: , RULE_ID
7961: , LINE_NUM
7962: , PROJECT_ID
8031: Else
8032: If basis_category is ACTUALS then
8033: For each project and task in pa_alloc_run_targets
8034: find the basis amount at project and task level from pa_txn_accum
8035: and insert that into pa_alloc_run_basis_det.
8036: Else
8037: For each project and task in pa_alloc_run_targets
8038: find the basis amount at project and task level from
8039: pa_base_budget_by_pa_period_v
8036: Else
8037: For each project and task in pa_alloc_run_targets
8038: find the basis amount at project and task level from
8039: pa_base_budget_by_pa_period_v
8040: and insert that into pa_alloc_run_basis_det.
8041: End if.
8042: End if;
8043: End if
8044: Determine the basis_percent ;
8082: and run_id = p_run_id
8083: and exclude_flag <> 'Y' ;
8084: cursor C_tot_basis_amt is
8085: select nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
8086: from pa_alloc_run_basis_det
8087: where run_id = p_run_id ;
8088: cursor C_line_basis_amt is
8089: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
8090: from pa_alloc_run_basis_det
8086: from pa_alloc_run_basis_det
8087: where run_id = p_run_id ;
8088: cursor C_line_basis_amt is
8089: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
8090: from pa_alloc_run_basis_det
8091: where run_id = p_run_id
8092: group by line_num, line_percent ;
8093: cursor C_tgt_line_pct is
8094: select sum((nvl(line_percent,0)))
8091: where run_id = p_run_id
8092: group by line_num, line_percent ;
8093: cursor C_tgt_line_pct is
8094: select sum((nvl(line_percent,0)))
8095: from pa_alloc_run_basis_det
8096: where run_id = p_run_id ;
8097: cursor c_get_proj is
8098: Select RUN_ID ,
8099: RULE_ID ,
8274: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8275: END IF;
8276: For l_get_proj in c_get_proj
8277: Loop
8278: Insert into pa_alloc_run_basis_det (
8279: rule_id, run_id, line_num, project_id, task_id,
8280: line_percent, creation_date, created_by,
8281: last_update_date, last_updated_by,
8282: last_update_login, amount)
8340: elsif p_basis_amt_type = 'PTD' then
8341: If p_run_period_type = 'GL' then
8342: For l_get_proj in c_get_proj
8343: Loop
8344: Insert into pa_alloc_run_basis_det (
8345: rule_id, run_id, line_num, project_id, task_id,
8346: line_percent, creation_date, created_by,
8347: last_update_date, last_updated_by,
8348: last_update_login, amount)
8397: End Loop;
8398: Elsif p_run_period_type = 'PA' then
8399: For l_get_proj in c_get_proj
8400: Loop
8401: Insert into pa_alloc_run_basis_det (
8402: rule_id, run_id, line_num, project_id, task_id,
8403: line_percent, creation_date, created_by,
8404: last_update_date, last_updated_by,
8405: last_update_login, amount)
8449: end if ;
8450: Else -- process for ITD amounts
8451: For l_get_proj in c_get_proj
8452: Loop
8453: Insert into pa_alloc_run_basis_det (
8454: rule_id, run_id, line_num, project_id, task_id,
8455: line_percent, creation_date, created_by,
8456: last_update_date, last_updated_by,
8457: last_update_login, amount)
8515: IF P_DEBUG_MODE = 'Y' THEN
8516: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
8517: END IF;
8518: if p_basis_amt_type = 'ITD' then
8519: Insert into pa_alloc_run_basis_det(
8520: rule_id, run_id, line_num, project_id, task_id,
8521: line_percent, creation_date, created_by, last_update_date,
8522: last_updated_by, last_update_login, amount)
8523: select part.rule_id, part.run_id, part.line_num,
8552: part.line_percent, G_creation_date, G_created_by,
8553: G_last_update_date,
8554: G_last_updated_by, G_last_update_login ;
8555: elsif p_basis_amt_type in ('FYTD','QTD') then /* Added bug 2757875 */
8556: Insert into pa_alloc_run_basis_det(
8557: rule_id, run_id, line_num, project_id, task_id,
8558: line_percent, creation_date, created_by, last_update_date,
8559: last_updated_by, last_update_login, amount)
8560: select part.rule_id, part.run_id, part.line_num,
8590: G_last_updated_by, G_last_update_login ;
8591: /* Added the following code for bug 2757875 */
8592: else -- process for PTD amounts
8593: If p_run_period_type = 'GL' then
8594: Insert into pa_alloc_run_basis_det(
8595: rule_id, run_id, line_num, project_id, task_id,
8596: line_percent, creation_date, created_by, last_update_date,
8597: last_updated_by, last_update_login, amount)
8598: select part.rule_id, part.run_id, part.line_num,
8621: part.line_percent, G_creation_date, G_created_by,
8622: G_last_update_date,
8623: G_last_updated_by, G_last_update_login ;
8624: elsif p_run_period_type = 'PA' then
8625: Insert into pa_alloc_run_basis_det(
8626: rule_id, run_id, line_num, project_id, task_id,
8627: line_percent, creation_date, created_by, last_update_date,
8628: last_updated_by, last_update_login, amount)
8629: select part.rule_id, part.run_id, part.line_num,
8655: end if; -- p_run_period_type
8656: /* Code changes end for bug 2757875 */
8657: End if ;
8658: --- Create Zero amount records for tasks that did not have budgets..
8659: Insert into pa_alloc_run_basis_det(
8660: rule_id, run_id, line_num, project_id, task_id,
8661: line_percent, creation_date, created_by, last_update_date,
8662: last_updated_by, last_update_login, amount)
8663: select part.rule_id, part.run_id, part.line_num,
8670: where part.rule_id = p_rule_id
8671: and part.run_id = p_run_id
8672: and part.exclude_flag = 'N'
8673: and NOT EXISTS ( Select 'Exists'
8674: from pa_alloc_run_basis_det parbd
8675: where parbd.run_id = part.run_id
8676: and parbd.project_id = part.project_id
8677: and parbd.task_id = part.task_id ) ;
8678: End if ; -- basis category
8679: End if ; -- For Resource list
8680: End if ; --- For basis_method
8681: /* added for bug 2182563. For FP insert one row per target with zero amount */
8682: IF p_basis_method = 'FP' then
8683: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8684: with amount = 0. so that the following insert does not insert any duplicate
8685: records into the table if there is a record already existing with amount = 0 */
8686: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8687: WHERE rule_id = p_rule_id
8682: IF p_basis_method = 'FP' then
8683: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8684: with amount = 0. so that the following insert does not insert any duplicate
8685: records into the table if there is a record already existing with amount = 0 */
8686: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8687: WHERE rule_id = p_rule_id
8688: AND run_id = p_run_id
8689: AND nvl(amount,0) = 0;
8690: /* we need to insert rows for a run_id/line_num only if there are no records in
8689: AND nvl(amount,0) = 0;
8690: /* we need to insert rows for a run_id/line_num only if there are no records in
8691: in basis table for that run_id and line number with amount <> 0. Because in this
8692: case the program has function like basis_method = 'FS' for that target line. */
8693: Insert into pa_alloc_run_basis_det (
8694: rule_id, run_id, line_num, project_id, task_id,
8695: line_percent, creation_date, created_by,
8696: last_update_date, last_updated_by,
8697: last_update_login, amount)
8704: and part.run_id = p_run_id
8705: and part.exclude_flag = 'N'
8706: and not exists
8707: (select null
8708: from pa_alloc_run_basis_det parb
8709: where parb.run_id = part.run_id
8710: and parb.line_num = part.line_num
8711: and parb.amount <> 0);
8712: END IF;
8739: END IF;
8740: End if;
8741: Close C_tot_basis_amt ;
8742: if nvl(v_tot_basis_amt,0) <> 0 then /* added for 1900331 */
8743: UPDATE pa_alloc_run_basis_det
8744: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_tot_basis_amt)
8745: WHERE run_id = p_run_id ;
8746: else
8747: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8747: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8748: p_basis_method := 'S';
8749: /* bug 1900331 prorate equally */
8750: /* commented for 2182563
8751: UPDATE pa_alloc_run_basis_det
8752: SET basis_percent = 100/v_tot_basis_rec
8753: WHERE run_id = p_run_id ;
8754: */
8755: end if;
8768: END IF;
8769: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_LINE_BASIS_AMT_IS_ZERO',TRUE) ;
8770: End if;
8771: if nvl(v_line_basis_amt,0) <> 0 then /* added for bug 1900331 */
8772: UPDATE pa_alloc_run_basis_det
8773: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_line_basis_amt)
8774: WHERE run_id = p_run_id
8775: AND line_num = v_line_num ;
8776: else
8774: WHERE run_id = p_run_id
8775: AND line_num = v_line_num ;
8776: else
8777: /* 1900331 - prorate equaly within the same target line */
8778: UPDATE pa_alloc_run_basis_det
8779: SET basis_percent = 100/v_line_count
8780: WHERE run_id = p_run_id
8781: AND line_num = v_line_num ;
8782: end if;
9926: /*Code Changes for Bug No.2984871 end */
9927: End Loop;
9928: --- Delete Transactions from basis_details
9929: While 1=1 Loop /* Bug 2176096 */
9930: Delete from pa_alloc_run_basis_det
9931: where run_id = p_run_id and rownum < 10001;
9932: /* Commented for Bug 2984871 Commit; */
9933: If Sql%Rowcount = 0 THen
9934: Exit;
10076: /*Code Changes for Bug No.2984871 end */
10077: End Loop;
10078: --- Delete Transactions from basis_details
10079: While 1=1 Loop /* Bug 2176096 */
10080: Delete from pa_alloc_run_basis_det
10081: where run_id = l_run_id and rownum < 10001;
10082: /* Commented for Bug 2984871 Commit; */
10083: If Sql%Rowcount = 0 THen
10084: Exit;