[Home] [Help]
7: --End Of Mods(EOM) Bug # 5743708
8:
9: current_project_id pa_projects_all.project_id%type;
10: -- Project id of the project being processed.
11: current_run_id pa_cost_distribution_lines_all.burden_sum_source_run_id%TYPE; /*Bug# 2255068*/
12: -- Run id of the batch being processed.
13: -- 3699045
14: current_sponsored_flag gms_project_types.sponsored_flag%TYPE := 'N' ;
15:
51: BEGIN
52: current_run_id := x_run_id;
53: END set_current_run_id;
54:
55: FUNCTION get_current_run_id RETURN pa_cost_distribution_lines_all.burden_sum_source_run_id%TYPE
56: IS
57: BEGIN
58: return current_run_id;
59: END get_current_run_id;
264: /* Bug#3033030 Added the following to check if the project status allows creation of
265: burden trasanction */
266: and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
267: and (exists (select --1 /*Added for bug#14367928 */
268: /*+ no_unnest index(CDL PA_COST_DISTRIBUTION_LINES_N10 ) */ 1
269: /*Added for bug#14531616*/
270: /* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
271: from pa_cost_distribution_lines cdl,
272: /*2255068*/ pa_expenditure_items ei
267: and (exists (select --1 /*Added for bug#14367928 */
268: /*+ no_unnest index(CDL PA_COST_DISTRIBUTION_LINES_N10 ) */ 1
269: /*Added for bug#14531616*/
270: /* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
271: from pa_cost_distribution_lines cdl,
272: /*2255068*/ pa_expenditure_items ei
273: where cdl.line_type = 'R'
274: and cdl.amount <> 0 --Bug 10174581
275: and cdl.burden_sum_source_run_id = init_cdl_run_id
278: and ei.expenditure_item_id = cdl.expenditure_item_id
279: and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date)
280: )
281: or exists (select 1
282: from pa_cost_distribution_lines cdl,
283: pa_expenditure_items ei
284: where cdl.line_type = 'R'
285: and cdl.amount <> 0 --Bug 10174581
286: and cdl.burden_sum_source_run_id >0
315: pt.burden_account_flag in ('Y','y'))
316: and pt.org_id = p.org_id
317: and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
318: and exists (
319: select /*+ NO_UNNEST INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ 1
320: from pa_cost_distribution_lines cdl
321: where cdl.line_type||'' = 'R'
322: and cdl.amount <> 0 --Bug 10174581
323: and cdl.burden_sum_source_run_id = init_cdl_run_id
316: and pt.org_id = p.org_id
317: and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
318: and exists (
319: select /*+ NO_UNNEST INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ 1
320: from pa_cost_distribution_lines cdl
321: where cdl.line_type||'' = 'R'
322: and cdl.amount <> 0 --Bug 10174581
323: and cdl.burden_sum_source_run_id = init_cdl_run_id
324: and cdl.project_id = p.project_id
455: select ei.expenditure_item_id , ei.task_id,nvl(ei.override_to_organization_id,e.incurred_by_organization_id) organization_id ,
456: ei.expenditure_item_date, ei.expenditure_type ,
457: e.person_type person_type,
458: e.incurred_by_person_id
459: from pa_cost_distribution_lines cdl,
460: pa_expenditure_items ei,
461: pa_expenditures e /*3040724*/
462: where cdl.line_type = 'R'
463: and cdl.amount <> 0 --Bug 10174581
699: * is NOT required.
700: */
701: IF ( X_end_date IS NOT NULL )
702: THEN
703: update pa_cost_distribution_lines cdl
704: set burden_sum_rejection_code = 'PROJECT_TASK_NULL'
705: where cdl.line_type = 'R'
706: and cdl.amount <> 0 --Bug 10174581
707: and (cdl.burden_sum_source_run_id = init_cdl_run_id
717: and ei.expenditure_item_date <= l_end_date
718: )
719: and rownum <= l_profile_set_size ;
720: ELSE
721: update pa_cost_distribution_lines cdl
722: set cdl.burden_sum_rejection_code = 'PROJECT_TASK_NULL'
723: where cdl.line_type = 'R'
724: and cdl.amount <> 0 --Bug 10174581
725: and (cdl.burden_sum_source_run_id = init_cdl_run_id
830: l_tbl_eiid.delete;
831: l_tbl_cdlln.delete;
832: /*EOM Bug# 5743708*/
833: if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
834: UPDATE pa_cost_distribution_lines cdl
835: SET cdl.prev_ind_compiled_set_id = decode(cdl.burden_sum_source_run_id,init_cdl_run_id,NULL
836: ,cdl.ind_compiled_set_id), /*3071338*/
837: request_id = x_request_id,
838: burden_sum_rejection_code = 'IN_PROCESS' /*2933915:Stamping it for intermediate processing*/
854: returning expenditure_item_id, line_num bulk collect into l_tbl_eiid, l_tbl_cdlln; /* Bug# 5406802 */
855:
856: else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
857:
858: UPDATE pa_cost_distribution_lines cdl
859: SET cdl.prev_ind_compiled_set_id = decode(cdl.burden_sum_source_run_id,init_cdl_run_id,NULL
860: ,cdl.ind_compiled_set_id),
861: request_id = x_request_id,
862: burden_sum_rejection_code = 'IN_PROCESS'
1001: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '607 STATUS :Update eis/cdls ');/*5980459*/
1002: END IF;
1003:
1004: FORALL i in 1..l_eiid_tbl.count
1005: update pa_cost_distribution_lines cdl
1006: set cdl.burden_sum_rejection_code = reason(i),
1007: cdl.prev_ind_compiled_set_id =NULL
1008: where cdl.request_id = x_request_id
1009: AND cdl.project_id = current_project_id
1027:
1028: /*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999*/
1029:
1030: FORALL i in 1..l_eiid_tbl.count
1031: UPDATE pa_cost_distribution_lines cdl
1032: set ind_compiled_set_id = l_compiled_set_id_tbl(i) ,
1033: burden_sum_source_run_id = -9999 ,
1034: burden_sum_rejection_code = NULL
1035: where cdl.request_id = x_request_id
1100: ,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
1101: ,CDL.PROJFUNC_COST_EXCHANGE_RATE
1102: ,CDL.CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
1103: FROM
1104: PA_COST_DISTRIBUTION_LINES_ALL CDL,
1105: PA_EXPENDITURE_ITEMS EI
1106: WHERE ei.expenditure_item_id = l_eiid_tbl(i)
1107: AND cdl.expenditure_item_id = ei.expenditure_item_id
1108: AND cdl.request_id = x_request_id
1167: IF P_DEBUG_MODE THEN
1168: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '606:Update CDL with rejection reason ');
1169: END IF;
1170:
1171: update pa_cost_distribution_lines cdl
1172: set cdl.burden_sum_rejection_code = reason,
1173: cdl.prev_ind_compiled_set_id =NULL
1174: where cdl.request_id = x_request_id
1175: AND cdl.project_id = current_project_id
1206: AND trunc(ei.expenditure_item_date) =trunc(rec.expenditure_item_date)
1207: AND ei.request_id = x_request_id /*2933915
1208: AND exists (select 1
1209: from pa_expenditures e,
1210: pa_cost_distribution_lines cdl
1211: where e.expenditure_id =ei.expenditure_id
1212: AND cdl.expenditure_item_id =ei.expenditure_item_id
1213: AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1214: AND nvl(ei.override_to_organization_id,e.incurred_by_organization_id)=rec.organization_id);
1215:
1216:
1217: /*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999
1218:
1219: UPDATE pa_cost_distribution_lines cdl
1220: set ind_compiled_set_id = l_compiled_set_id ,
1221: burden_sum_source_run_id = -9999 ,
1222: burden_sum_rejection_code = NULL
1223: where cdl.request_id = x_request_id
1272: if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
1273:
1274: /* Bug# 9373031: Added hint for performane issue */
1275:
1276: update /*+ INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ pa_cost_distribution_lines cdl
1277: set burden_sum_rejection_code = NULL,
1278: request_id = x_request_id, /*2161261*/
1279: burden_sum_source_run_id = current_run_id
1280: where cdl.line_type = 'R'
1294: /*and cdl.PA_DATE <= nvl(l_end_date,cdl.PA_DATE); Bug# 2255068*/
1295:
1296: else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
1297:
1298: update pa_cost_distribution_lines cdl
1299: set burden_sum_rejection_code = NULL,
1300: request_id = x_request_id, /*2161261*/
1301: burden_sum_source_run_id = current_run_id
1302: where cdl.line_type = 'R'
1328:
1329: if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
1330:
1331: /* Bug# 9373031: Added hint for performane issue */
1332: update /*+ INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ pa_cost_distribution_lines cdl
1333: set burden_sum_rejection_code = NULL,
1334: request_id = x_request_id, /*2161261*/
1335: burden_sum_source_run_id = current_run_id
1336: where cdl.line_type = 'R'
1343: and rownum <= l_profile_set_size;
1344:
1345: else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
1346:
1347: update pa_cost_distribution_lines cdl
1348: set burden_sum_rejection_code = NULL,
1349: request_id = x_request_id, /*2161261*/
1350: burden_sum_source_run_id = current_run_id
1351: where cdl.line_type = 'R'
1388:
1389: if PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'N' then -- bug 9165385
1390:
1391: /* Bug# 9373031: Added hint for performane issue */
1392: update /*+ INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) */ pa_cost_distribution_lines cdl
1393: set burden_sum_rejection_code = NULL
1394: ,request_id = x_request_id
1395: ,burden_sum_source_run_id = current_run_id
1396: where cdl.line_type = 'R'
1401: request_id <> x_request_id or
1402: burden_sum_rejection_code is null)
1403: and exists
1404: (select NULL
1405: from pa_cost_distribution_lines cdl1
1406: where cdl1.burden_sum_source_run_id+0 = current_run_id
1407: and cdl1.burden_sum_rejection_code is NULL /*3071338*/
1408: and cdl1.expenditure_item_id= cdl.expenditure_item_id) ;
1409:
1408: and cdl1.expenditure_item_id= cdl.expenditure_item_id) ;
1409:
1410: else -- PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG = 'Y' -- bug 9165385
1411:
1412: update pa_cost_distribution_lines cdl
1413: set burden_sum_rejection_code = NULL
1414: ,request_id = x_request_id
1415: ,burden_sum_source_run_id = current_run_id
1416: where cdl.line_type = 'R'
1421: request_id <> x_request_id or
1422: burden_sum_rejection_code is null) /*Added for the bug#5683194*/
1423: and exists
1424: (select NULL
1425: from pa_cost_distribution_lines cdl1
1426: where cdl1.burden_sum_source_run_id+0 = current_run_id
1427: and cdl1.burden_sum_rejection_code is NULL /*3071338*/
1428: and cdl1.expenditure_item_id= cdl.expenditure_item_id)
1429: and exists
2601: * ** Removed ei table and pa_tasks table and
2602: * changed cdl_all table to cdl view for
2603: * bug# 1668634
2604: *
2605: * update pa_cost_distribution_lines cdl
2606: * set burden_sum_source_run_id = current_run_id
2607: * where cdl.line_type = 'R'
2608: * and cdl.burden_sum_source_run_id = init_cdl_run_id
2609: * and cdl.project_id = current_project_id
2662: Tp_Rule_percentage = decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Rule_percentage),
2663: Tp_Rule_percentage) /*End of bug 4754024*/
2664: where adjustment_type ='BURDEN_RESUMMARIZE'
2665: and project_id = current_project_id
2666: and exists (select 1 from pa_cost_distribution_lines_all cdl
2667: where cdl.expenditure_item_id = ei.expenditure_item_id
2668: and cdl.request_id = x_request_id
2669: /* and cdl.prev_ind_compiled_set_id is NOT NULL :Commented for bug# 3040724*/
2670: and cdl.burden_sum_source_run_id =current_run_id )
2678: /* Modified this sql for bug 5406802*/
2679:
2680: FORALL I IN 1..l_tbl_eiid.count
2681:
2682: UPDATE pa_cost_distribution_lines_all
2683: set prev_ind_compiled_set_id = NULL
2684: where prev_ind_compiled_set_id IS NOT NULL
2685: and project_id = current_project_id
2686: and request_id = x_request_id
3333: Used the cached expenditure item pl/sql table for populating the audit table
3334: instead opening the cursor from CDL table.
3335: */
3336:
3337: PROCEDURE InsBurdenAudit( p_project_id IN pa_cost_distribution_lines_all.project_id%TYPE,
3338: p_request_id IN NUMBER ,
3339: p_user_id IN number,
3340: x_status IN OUT NOCOPY number )
3341: IS
3377: l_program_id,
3378: l_program_application_id,
3379: p_request_id
3380: FROM
3381: pa_cost_distribution_lines_all
3382: WHERE expenditure_item_id = l_tbl_eiid(i)
3383: AND line_num= l_tbl_cdlln(i)
3384: AND prev_ind_compiled_set_id IS NOT NULL
3385: AND request_id = p_request_id
3431: ,PROJFUNC_COST_EXCHANGE_RATE
3432: ,CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
3433: )
3434: (
3435: select /*+ ordered INDEX (cdl, PA_COST_DISTRIBUTION_LINES_N10) INDEX(ei,PA_EXPENDITURE_ITEMS_U1) */
3436: cdl.project_id
3437: ,cdl.task_id
3438: ,nvl(ei.override_to_organization_id,e.incurred_by_organization_id)
3439: ,cdl.pa_date
3463: ,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
3464: ,CDL.PROJFUNC_COST_EXCHANGE_RATE
3465: ,CDL.CBS_ELEMENT_ID -- Added for CBS enahncement bug 16579913
3466: FROM
3467: PA_COST_DISTRIBUTION_LINES_ALL CDL,
3468: PA_EXPENDITURE_ITEMS EI,
3469: PA_EXPENDITURES_ALL E
3470: WHERE cdl.burden_sum_source_run_id = p_current_run_id
3471: AND cdl.project_id = p_project_id
3543:
3544: If l_eiid.count > 0 THEN
3545:
3546: FORALL I in 1..l_eiid.count
3547: UPDATE PA_COST_DISTRIBUTION_LINES
3548: set BURDEN_SUM_REJECTION_CODE = 'BCC_EXP_TYPE_NULL'
3549: where expenditure_item_id = l_eiid(i)
3550: and line_num = l_linenum(i); /*added for the bug#5949107*/
3551: