[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;
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
268: /* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
269: from pa_cost_distribution_lines cdl,
270: /*2255068*/ pa_expenditure_items ei
271: where cdl.line_type = 'R'
272: and nvl(cdl.amount,0) <> 0
273: and cdl.burden_sum_source_run_id = init_cdl_run_id
275: and ei.expenditure_item_id = cdl.expenditure_item_id
276: and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date)
277: )
278: or exists (select 1
279: from pa_cost_distribution_lines cdl,
280: pa_expenditure_items ei
281: where cdl.line_type = 'R'
282: and nvl(cdl.amount,0) <> 0
283: and cdl.burden_sum_source_run_id >0
315: and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
316: and (( x_end_date is not null and exists (select 1
317: /* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
318: /* Removed rownum=1 condition for the bug 4527643 (Basebug#4391999) */
319: from pa_cost_distribution_lines cdl,
320: /*2255068*/ pa_expenditure_items ei
321: where cdl.line_type = 'R'
322: and nvl(cdl.amount,0) <> 0
323: and cdl.burden_sum_source_run_id = init_cdl_run_id
325: and ei.expenditure_item_id = cdl.expenditure_item_id
326: /*2255068*/ and ei.expenditure_item_date <= l_end_date
327: )
328: ) OR x_end_date is null and exists (select 1
329: from pa_cost_distribution_lines cdl
330: where cdl.line_type = 'R'
331: and nvl(cdl.amount,0) <> 0
332: and cdl.burden_sum_source_run_id = init_cdl_run_id
333: and cdl.project_id = p.project_id
459: select ei.expenditure_item_id , ei.task_id,nvl(ei.override_to_organization_id,e.incurred_by_organization_id) organization_id ,
460: ei.expenditure_item_date, ei.expenditure_type ,
461: e.person_type person_type,
462: e.incurred_by_person_id
463: from pa_cost_distribution_lines cdl,
464: pa_expenditure_items ei,
465: pa_expenditures e /*3040724*/
466: where cdl.line_type = 'R'
467: and nvl(cdl.amount,0) <> 0
703: * is NOT required.
704: */
705: IF ( X_end_date IS NOT NULL )
706: THEN
707: update pa_cost_distribution_lines cdl
708: set burden_sum_rejection_code = 'PROJECT_TASK_NULL'
709: where cdl.line_type = 'R'
710: and nvl(cdl.amount,0) <> 0
711: and (cdl.burden_sum_source_run_id = init_cdl_run_id
721: and ei.expenditure_item_date <= l_end_date
722: )
723: and rownum <= l_profile_set_size ;
724: ELSE
725: update pa_cost_distribution_lines cdl
726: set cdl.burden_sum_rejection_code = 'PROJECT_TASK_NULL'
727: where cdl.line_type = 'R'
728: and nvl(cdl.amount,0) <> 0
729: and (cdl.burden_sum_source_run_id = init_cdl_run_id
833: /*SOM Bug# 5743708*/
834: l_tbl_eiid.delete;
835: l_tbl_cdlln.delete;
836: /*EOM Bug# 5743708*/
837: UPDATE pa_cost_distribution_lines cdl
838: SET cdl.prev_ind_compiled_set_id = decode(cdl.burden_sum_source_run_id,init_cdl_run_id,NULL
839: ,cdl.ind_compiled_set_id), /*3071338*/
840: request_id = x_request_id,
841: burden_sum_rejection_code = 'IN_PROCESS' /*2933915:Stamping it for intermediate processing*/
974: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '607 STATUS :Update eis/cdls ');/*5980459*/
975: END IF;
976:
977: FORALL i in 1..l_eiid_tbl.count
978: update pa_cost_distribution_lines cdl
979: set cdl.burden_sum_rejection_code = reason(i),
980: cdl.prev_ind_compiled_set_id =NULL
981: where cdl.request_id = x_request_id
982: AND cdl.project_id = current_project_id
1000:
1001: /*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999*/
1002:
1003: FORALL i in 1..l_eiid_tbl.count
1004: UPDATE pa_cost_distribution_lines cdl
1005: set ind_compiled_set_id = l_compiled_set_id_tbl(i) ,
1006: burden_sum_source_run_id = -9999 ,
1007: burden_sum_rejection_code = NULL
1008: where cdl.request_id = x_request_id
1070: ,CDL.ACCT_EXCHANGE_RATE ,CDL.PROJECT_RATE_DATE ,CDL.PROJECT_RATE_TYPE
1071: ,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
1072: ,CDL.PROJFUNC_COST_EXCHANGE_RATE
1073: FROM
1074: PA_COST_DISTRIBUTION_LINES_ALL CDL,
1075: PA_EXPENDITURE_ITEMS EI
1076: WHERE ei.expenditure_item_id = l_eiid_tbl(i)
1077: AND cdl.expenditure_item_id = ei.expenditure_item_id
1078: AND cdl.request_id = x_request_id
1132: IF P_DEBUG_MODE THEN
1133: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '606:Update CDL with rejection reason ');
1134: END IF;
1135:
1136: update pa_cost_distribution_lines cdl
1137: set cdl.burden_sum_rejection_code = reason,
1138: cdl.prev_ind_compiled_set_id =NULL
1139: where cdl.request_id = x_request_id
1140: AND cdl.project_id = current_project_id
1171: AND trunc(ei.expenditure_item_date) =trunc(rec.expenditure_item_date)
1172: AND ei.request_id = x_request_id /*2933915
1173: AND exists (select 1
1174: from pa_expenditures e,
1175: pa_cost_distribution_lines cdl
1176: where e.expenditure_id =ei.expenditure_id
1177: AND cdl.expenditure_item_id =ei.expenditure_item_id
1178: AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1179: AND nvl(ei.override_to_organization_id,e.incurred_by_organization_id)=rec.organization_id);
1180:
1181:
1182: /*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999
1183:
1184: UPDATE pa_cost_distribution_lines cdl
1185: set ind_compiled_set_id = l_compiled_set_id ,
1186: burden_sum_source_run_id = -9999 ,
1187: burden_sum_rejection_code = NULL
1188: where cdl.request_id = x_request_id
1233: THEN
1234: /* Removed ei table and pa_tasks table and changed cdl_all table to cdl view for
1235: bug# 1668634
1236: */
1237: update pa_cost_distribution_lines cdl
1238: set burden_sum_rejection_code = NULL,
1239: request_id = x_request_id, /*2161261*/
1240: burden_sum_source_run_id = current_run_id
1241: where cdl.line_type = 'R'
1253: )
1254: and rownum <= l_profile_set_size;
1255: /*and cdl.PA_DATE <= nvl(l_end_date,cdl.PA_DATE); Bug# 2255068*/
1256: ELSE
1257: update pa_cost_distribution_lines cdl
1258: set burden_sum_rejection_code = NULL,
1259: request_id = x_request_id, /*2161261*/
1260: burden_sum_source_run_id = current_run_id
1261: where cdl.line_type = 'R'
1284: * Bug#2255068
1285: * The following update ensures that all CDLs of an EI gets processed
1286: * in the same batch.
1287: */
1288: update pa_cost_distribution_lines cdl
1289: set burden_sum_rejection_code = NULL
1290: ,request_id = x_request_id
1291: ,burden_sum_source_run_id = current_run_id
1292: where cdl.line_type = 'R'
1297: request_id <> x_request_id or
1298: burden_sum_rejection_code is null)
1299: and exists
1300: (select NULL
1301: from pa_cost_distribution_lines cdl1
1302: where cdl1.burden_sum_source_run_id+0 = current_run_id
1303: and cdl1.burden_sum_rejection_code is NULL /*3071338*/
1304: and cdl1.expenditure_item_id= cdl.expenditure_item_id) ;
1305:
2457: * ** Removed ei table and pa_tasks table and
2458: * changed cdl_all table to cdl view for
2459: * bug# 1668634
2460: *
2461: * update pa_cost_distribution_lines cdl
2462: * set burden_sum_source_run_id = current_run_id
2463: * where cdl.line_type = 'R'
2464: * and cdl.burden_sum_source_run_id = init_cdl_run_id
2465: * and cdl.project_id = current_project_id
2518: Tp_Rule_percentage = decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Rule_percentage),
2519: Tp_Rule_percentage) /*End of bug 4754024*/
2520: where adjustment_type ='BURDEN_RESUMMARIZE'
2521: and project_id = current_project_id
2522: and exists (select 1 from pa_cost_distribution_lines_all cdl
2523: where cdl.expenditure_item_id = ei.expenditure_item_id
2524: and cdl.request_id = x_request_id
2525: /* and cdl.prev_ind_compiled_set_id is NOT NULL :Commented for bug# 3040724*/
2526: and cdl.burden_sum_source_run_id =current_run_id )
2534: /* Modified this sql for bug 5406802*/
2535:
2536: FORALL I IN 1..l_tbl_eiid.count
2537:
2538: UPDATE pa_cost_distribution_lines_all
2539: set prev_ind_compiled_set_id = NULL
2540: where prev_ind_compiled_set_id IS NOT NULL
2541: and project_id = current_project_id
2542: and request_id = x_request_id
3189: Used the cached expenditure item pl/sql table for populating the audit table
3190: instead opening the cursor from CDL table.
3191: */
3192:
3193: PROCEDURE InsBurdenAudit( p_project_id IN pa_cost_distribution_lines_all.project_id%TYPE,
3194: p_request_id IN NUMBER ,
3195: p_user_id IN number,
3196: x_status IN OUT NOCOPY number )
3197: IS
3233: l_program_id,
3234: l_program_application_id,
3235: p_request_id
3236: FROM
3237: pa_cost_distribution_lines_all
3238: WHERE expenditure_item_id = l_tbl_eiid(i)
3239: AND line_num= l_tbl_cdlln(i)
3240: AND prev_ind_compiled_set_id IS NOT NULL
3241: AND request_id = p_request_id
3314: ,CDL.ACCT_EXCHANGE_RATE ,CDL.PROJECT_RATE_DATE ,CDL.PROJECT_RATE_TYPE
3315: ,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
3316: ,CDL.PROJFUNC_COST_EXCHANGE_RATE
3317: FROM
3318: PA_COST_DISTRIBUTION_LINES_ALL CDL,
3319: PA_EXPENDITURE_ITEMS EI,
3320: PA_EXPENDITURES_ALL E
3321: WHERE cdl.burden_sum_source_run_id = p_current_run_id
3322: AND cdl.project_id = p_project_id
3385:
3386: If l_eiid.count > 0 THEN
3387:
3388: FORALL I in 1..l_eiid.count
3389: UPDATE PA_COST_DISTRIBUTION_LINES
3390: set BURDEN_SUM_REJECTION_CODE = 'BCC_EXP_TYPE_NULL'
3391: where expenditure_item_id = l_eiid(i)
3392: and line_num = l_linenum(i); /*added for the bug#5949107*/
3393: