[Home] [Help]
3290: , p_line_num IN NUMBER
3291: , p_project_id IN NUMBER
3292: , p_task_id IN NUMBER ) IS
3293: SELECT nvl(sum( nvl(basis_percent, 0)*nvl(line_percent,100)/10000),0) basis
3294: FROM pa_alloc_run_basis_det
3295: WHERE run_id = p_run_id
3296: AND line_num = p_line_num
3297: AND project_id = p_project_id
3298: AND task_id = p_task_id ;
5371: RAISE ;
5372: END get_amttype_start_date ;
5373: -- ==========================================================================
5374: /* PROCEDURE : insert_alloc_basis_resource
5375: Purpose : To insert data into pa_alloc_run_basis_det table for each resource
5376: for each task which has some data available in summarization.
5377: Separate inserts are written for each type of amt_type
5378: (FYTD,qtd,itd and ptd).
5379: Created : 16-JAN-02 Manokuma
5437: IF P_DEBUG_MODE = 'Y' THEN
5438: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id );
5439: END IF;
5440: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------- {
5441: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5442: RUN_ID
5443: , RULE_ID
5444: , LINE_NUM
5445: , PROJECT_ID
5523: ,G_last_update_date
5524: ,G_last_updated_by
5525: ,G_last_update_login);
5526: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5527: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5528: RUN_ID
5529: , RULE_ID
5530: , LINE_NUM
5531: , PROJECT_ID
5623: IF P_DEBUG_MODE = 'Y' THEN
5624: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5625: END IF;
5626: If Nvl(p_resource_struct_type,'RL') = 'RL' Then -------------{
5627: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5628: RUN_ID
5629: , RULE_ID
5630: , LINE_NUM
5631: , PROJECT_ID
5691: ,G_last_update_date
5692: ,G_last_updated_by
5693: ,G_last_update_login);
5694: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5695: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5696: RUN_ID
5697: , RULE_ID
5698: , LINE_NUM
5699: , PROJECT_ID
5758: ,G_last_update_login);
5759: End If; ---------------- }
5760: ELSE /* p_run_period_type = 'GL' */
5761: If Nvl(p_resource_struct_type,'RL') = 'RL' Then --------------{
5762: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5763: RUN_ID
5764: , RULE_ID
5765: , LINE_NUM
5766: , PROJECT_ID
5830: ,G_last_update_date
5831: ,G_last_updated_by
5832: ,G_last_update_login);
5833: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
5834: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5835: RUN_ID
5836: , RULE_ID
5837: , LINE_NUM
5838: , PROJECT_ID
5924: IF P_DEBUG_MODE = 'Y' THEN
5925: pa_debug.write_file('insert_alloc_basis_resource: ' || 'LOG', 'inserting data for project ' || c_projects_rec.project_id);
5926: END IF;
5927: If Nvl(p_resource_struct_type,'RL') = 'RL' Then ---------------{
5928: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
5929: RUN_ID
5930: , RULE_ID
5931: , LINE_NUM
5932: , PROJECT_ID
6000: ,G_last_update_date
6001: ,G_last_updated_by
6002: ,G_last_update_login);
6003: ElsIf Nvl(p_resource_struct_type,'RL') = 'RBS' Then
6004: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
6005: RUN_ID
6006: , RULE_ID
6007: , LINE_NUM
6008: , PROJECT_ID
6952: RAISE ;
6953: END insert_alloc_source_resource;
6954: -- ==========================================================================
6955: /* PROCEDURE : insert_budget_basis_resource
6956: Purpose : inserts records into pa_alloc_run_basis_det when budgets are used
6957: for basis and resource lists are used to calculate basis amounts.
6958: Created : 02-feb-01 Manoj.
6959: Modified : 24-JAN-03 Tarun for bug 2757875
6960: 06-Apr-04 vthakkar FP.M : ALlocation Impact
6978: v_commit NUMBER := 0;
6979: BEGIN
6980: FOR c_projects_rec IN c_projects LOOP
6981: If p_run_period_type = 'PA' then
6982: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
6983: RUN_ID
6984: , RULE_ID
6985: , LINE_NUM
6986: , PROJECT_ID
7043: ,G_last_update_date
7044: ,G_last_updated_by
7045: ,G_last_update_login);
7046: Elsif p_run_period_type = 'GL' then
7047: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7048: RUN_ID
7049: , RULE_ID
7050: , LINE_NUM
7051: , PROJECT_ID
7848: IF P_DEBUG_MODE = 'Y' THEN
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7854: RUN_ID
7855: , RULE_ID
7856: , LINE_NUM
7849: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'insert Basis record for task '||to_char(p_task_id)||':'||to_char(p_amount));
7850: pa_debug.write_file('insert_alloc_run_basis_det: ' || 'LOG', pa_debug.G_Err_Stage);
7851: END IF;
7852: pa_debug.G_err_stage := 'INSERTING PA_ALLOC_RUN_BASIS_DET' ;
7853: INSERT INTO PA_ALLOC_RUN_BASIS_DET (
7854: RUN_ID
7855: , RULE_ID
7856: , LINE_NUM
7857: , PROJECT_ID
7926: Else
7927: If basis_category is ACTUALS then
7928: For each project and task in pa_alloc_run_targets
7929: find the basis amount at project and task level from pa_txn_accum
7930: and insert that into pa_alloc_run_basis_det.
7931: Else
7932: For each project and task in pa_alloc_run_targets
7933: find the basis amount at project and task level from
7934: pa_base_budget_by_pa_period_v
7931: Else
7932: For each project and task in pa_alloc_run_targets
7933: find the basis amount at project and task level from
7934: pa_base_budget_by_pa_period_v
7935: and insert that into pa_alloc_run_basis_det.
7936: End if.
7937: End if;
7938: End if
7939: Determine the basis_percent ;
7977: and run_id = p_run_id
7978: and exclude_flag <> 'Y' ;
7979: cursor C_tot_basis_amt is
7980: select nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
7981: from pa_alloc_run_basis_det
7982: where run_id = p_run_id ;
7983: cursor C_line_basis_amt is
7984: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
7985: from pa_alloc_run_basis_det
7981: from pa_alloc_run_basis_det
7982: where run_id = p_run_id ;
7983: cursor C_line_basis_amt is
7984: select line_num, line_percent, nvl(sum(nvl(amount,0)),0), nvl(max(nvl(amount,0)),0), count(1) /* 1900331 */
7985: from pa_alloc_run_basis_det
7986: where run_id = p_run_id
7987: group by line_num, line_percent ;
7988: cursor C_tgt_line_pct is
7989: select sum((nvl(line_percent,0)))
7986: where run_id = p_run_id
7987: group by line_num, line_percent ;
7988: cursor C_tgt_line_pct is
7989: select sum((nvl(line_percent,0)))
7990: from pa_alloc_run_basis_det
7991: where run_id = p_run_id ;
7992: cursor c_get_proj is
7993: Select RUN_ID ,
7994: RULE_ID ,
8169: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','p_basis_amt_type' ||p_basis_amt_type);
8170: END IF;
8171: For l_get_proj in c_get_proj
8172: Loop
8173: Insert into pa_alloc_run_basis_det (
8174: rule_id, run_id, line_num, project_id, task_id,
8175: line_percent, creation_date, created_by,
8176: last_update_date, last_updated_by,
8177: last_update_login, amount)
8235: elsif p_basis_amt_type = 'PTD' then
8236: If p_run_period_type = 'GL' then
8237: For l_get_proj in c_get_proj
8238: Loop
8239: Insert into pa_alloc_run_basis_det (
8240: rule_id, run_id, line_num, project_id, task_id,
8241: line_percent, creation_date, created_by,
8242: last_update_date, last_updated_by,
8243: last_update_login, amount)
8292: End Loop;
8293: Elsif p_run_period_type = 'PA' then
8294: For l_get_proj in c_get_proj
8295: Loop
8296: Insert into pa_alloc_run_basis_det (
8297: rule_id, run_id, line_num, project_id, task_id,
8298: line_percent, creation_date, created_by,
8299: last_update_date, last_updated_by,
8300: last_update_login, amount)
8344: end if ;
8345: Else -- process for ITD amounts
8346: For l_get_proj in c_get_proj
8347: Loop
8348: Insert into pa_alloc_run_basis_det (
8349: rule_id, run_id, line_num, project_id, task_id,
8350: line_percent, creation_date, created_by,
8351: last_update_date, last_updated_by,
8352: last_update_login, amount)
8410: IF P_DEBUG_MODE = 'Y' THEN
8411: pa_debug.write_file('cal_proj_basis_amounts: ' || 'LOG','Inserting in basis_det from budgets');
8412: END IF;
8413: if p_basis_amt_type = 'ITD' then
8414: Insert into pa_alloc_run_basis_det(
8415: rule_id, run_id, line_num, project_id, task_id,
8416: line_percent, creation_date, created_by, last_update_date,
8417: last_updated_by, last_update_login, amount)
8418: select part.rule_id, part.run_id, part.line_num,
8447: part.line_percent, G_creation_date, G_created_by,
8448: G_last_update_date,
8449: G_last_updated_by, G_last_update_login ;
8450: elsif p_basis_amt_type in ('FYTD','QTD') then /* Added bug 2757875 */
8451: Insert into pa_alloc_run_basis_det(
8452: rule_id, run_id, line_num, project_id, task_id,
8453: line_percent, creation_date, created_by, last_update_date,
8454: last_updated_by, last_update_login, amount)
8455: select part.rule_id, part.run_id, part.line_num,
8485: G_last_updated_by, G_last_update_login ;
8486: /* Added the following code for bug 2757875 */
8487: else -- process for PTD amounts
8488: If p_run_period_type = 'GL' then
8489: Insert into pa_alloc_run_basis_det(
8490: rule_id, run_id, line_num, project_id, task_id,
8491: line_percent, creation_date, created_by, last_update_date,
8492: last_updated_by, last_update_login, amount)
8493: select part.rule_id, part.run_id, part.line_num,
8516: part.line_percent, G_creation_date, G_created_by,
8517: G_last_update_date,
8518: G_last_updated_by, G_last_update_login ;
8519: elsif p_run_period_type = 'PA' then
8520: Insert into pa_alloc_run_basis_det(
8521: rule_id, run_id, line_num, project_id, task_id,
8522: line_percent, creation_date, created_by, last_update_date,
8523: last_updated_by, last_update_login, amount)
8524: select part.rule_id, part.run_id, part.line_num,
8550: end if; -- p_run_period_type
8551: /* Code changes end for bug 2757875 */
8552: End if ;
8553: --- Create Zero amount records for tasks that did not have budgets..
8554: Insert into pa_alloc_run_basis_det(
8555: rule_id, run_id, line_num, project_id, task_id,
8556: line_percent, creation_date, created_by, last_update_date,
8557: last_updated_by, last_update_login, amount)
8558: select part.rule_id, part.run_id, part.line_num,
8565: where part.rule_id = p_rule_id
8566: and part.run_id = p_run_id
8567: and part.exclude_flag = 'N'
8568: and NOT EXISTS ( Select 'Exists'
8569: from pa_alloc_run_basis_det parbd
8570: where parbd.run_id = part.run_id
8571: and parbd.project_id = part.project_id
8572: and parbd.task_id = part.task_id ) ;
8573: End if ; -- basis category
8574: End if ; -- For Resource list
8575: End if ; --- For basis_method
8576: /* added for bug 2182563. For FP insert one row per target with zero amount */
8577: IF p_basis_method = 'FP' then
8578: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8579: with amount = 0. so that the following insert does not insert any duplicate
8580: records into the table if there is a record already existing with amount = 0 */
8581: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8582: WHERE rule_id = p_rule_id
8577: IF p_basis_method = 'FP' then
8578: /* First delete any records that are existing in the table 'pa_alloc_run_basis_det'
8579: with amount = 0. so that the following insert does not insert any duplicate
8580: records into the table if there is a record already existing with amount = 0 */
8581: DELETE FROM PA_ALLOC_RUN_BASIS_DET
8582: WHERE rule_id = p_rule_id
8583: AND run_id = p_run_id
8584: AND nvl(amount,0) = 0;
8585: /* we need to insert rows for a run_id/line_num only if there are no records in
8584: AND nvl(amount,0) = 0;
8585: /* we need to insert rows for a run_id/line_num only if there are no records in
8586: in basis table for that run_id and line number with amount <> 0. Because in this
8587: case the program has function like basis_method = 'FS' for that target line. */
8588: Insert into pa_alloc_run_basis_det (
8589: rule_id, run_id, line_num, project_id, task_id,
8590: line_percent, creation_date, created_by,
8591: last_update_date, last_updated_by,
8592: last_update_login, amount)
8599: and part.run_id = p_run_id
8600: and part.exclude_flag = 'N'
8601: and not exists
8602: (select null
8603: from pa_alloc_run_basis_det parb
8604: where parb.run_id = part.run_id
8605: and parb.line_num = part.line_num
8606: and parb.amount <> 0);
8607: END IF;
8634: END IF;
8635: End if;
8636: Close C_tot_basis_amt ;
8637: if nvl(v_tot_basis_amt,0) <> 0 then /* added for 1900331 */
8638: UPDATE pa_alloc_run_basis_det
8639: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_tot_basis_amt)
8640: WHERE run_id = p_run_id ;
8641: else
8642: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8642: /* for bug 2182563. Change the basis method to 'S' and return to main program */
8643: p_basis_method := 'S';
8644: /* bug 1900331 prorate equally */
8645: /* commented for 2182563
8646: UPDATE pa_alloc_run_basis_det
8647: SET basis_percent = 100/v_tot_basis_rec
8648: WHERE run_id = p_run_id ;
8649: */
8650: end if;
8663: END IF;
8664: alloc_errors(p_rule_id, p_run_id, 'B', 'E','PA_AL_LINE_BASIS_AMT_IS_ZERO',TRUE) ;
8665: End if;
8666: if nvl(v_line_basis_amt,0) <> 0 then /* added for bug 1900331 */
8667: UPDATE pa_alloc_run_basis_det
8668: SET basis_percent = decode(nvl(amount,0), 0, 0, amount*100/v_line_basis_amt)
8669: WHERE run_id = p_run_id
8670: AND line_num = v_line_num ;
8671: else
8669: WHERE run_id = p_run_id
8670: AND line_num = v_line_num ;
8671: else
8672: /* 1900331 - prorate equaly within the same target line */
8673: UPDATE pa_alloc_run_basis_det
8674: SET basis_percent = 100/v_line_count
8675: WHERE run_id = p_run_id
8676: AND line_num = v_line_num ;
8677: end if;
9821: /*Code Changes for Bug No.2984871 end */
9822: End Loop;
9823: --- Delete Transactions from basis_details
9824: While 1=1 Loop /* Bug 2176096 */
9825: Delete from pa_alloc_run_basis_det
9826: where run_id = p_run_id and rownum < 10001;
9827: /* Commented for Bug 2984871 Commit; */
9828: If Sql%Rowcount = 0 THen
9829: Exit;
9971: /*Code Changes for Bug No.2984871 end */
9972: End Loop;
9973: --- Delete Transactions from basis_details
9974: While 1=1 Loop /* Bug 2176096 */
9975: Delete from pa_alloc_run_basis_det
9976: where run_id = l_run_id and rownum < 10001;
9977: /* Commented for Bug 2984871 Commit; */
9978: If Sql%Rowcount = 0 THen
9979: Exit;