[Home] [Help]
85: -- expenditure group id for each run of this program
86: exp_id pa_expenditures_all.expenditure_id%type;
87: -- expenditure id of expenditure items
88: x_exp_id pa_expenditures_all.expenditure_id%type;
89: exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
90: exp_org_id pa_expenditure_items_all.organization_id%type;
91: -- expenditure organization/override organization
92: over_project_id pa_projects_all.project_id%type;
93: -- project id of the source cdl or the project type level override
86: exp_id pa_expenditures_all.expenditure_id%type;
87: -- expenditure id of expenditure items
88: x_exp_id pa_expenditures_all.expenditure_id%type;
89: exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
90: exp_org_id pa_expenditure_items_all.organization_id%type;
91: -- expenditure organization/override organization
92: over_project_id pa_projects_all.project_id%type;
93: -- project id of the source cdl or the project type level override
94: over_task_id pa_tasks.task_id%type;
96: prev_bcc_rec pa_cdl_burden_summary_v%rowtype;
97: -- Record to hold previous record from bcc_cur Cursor
98: l_attribute1 pa_projects_all.attribute1%type;
99: -- local variable to hold attribute1 from project for locking
100: l_burden_cost pa_expenditure_items_all.burden_cost%type;
101: -- Running total of burden cost (project functional currency)for an summarization group
102: /*
103: Multi-Currency Related changes:
104: Added local variables to store the sum of burdened_cost in denom and acct currencies
103: Multi-Currency Related changes:
104: Added local variables to store the sum of burdened_cost in denom and acct currencies
105: (also initialised to 0)
106: */
107: l_denom_burdened_cost pa_expenditure_items_all.denom_burdened_cost%type := 0;
108: -- Running total of burden cost (denom currency)for an summarization group
109: l_acct_burdened_cost pa_expenditure_items_all.acct_burdened_cost%type := 0;
110: -- Running total of burden cost (acct currency)for an summarization group
111: l_project_burdened_cost pa_expenditure_items_all.project_burdened_cost%type := 0;
105: (also initialised to 0)
106: */
107: l_denom_burdened_cost pa_expenditure_items_all.denom_burdened_cost%type := 0;
108: -- Running total of burden cost (denom currency)for an summarization group
109: l_acct_burdened_cost pa_expenditure_items_all.acct_burdened_cost%type := 0;
110: -- Running total of burden cost (acct currency)for an summarization group
111: l_project_burdened_cost pa_expenditure_items_all.project_burdened_cost%type := 0;
112: -- Running total of burden cost (project currency)for a summarization group
113:
107: l_denom_burdened_cost pa_expenditure_items_all.denom_burdened_cost%type := 0;
108: -- Running total of burden cost (denom currency)for an summarization group
109: l_acct_burdened_cost pa_expenditure_items_all.acct_burdened_cost%type := 0;
110: -- Running total of burden cost (acct currency)for an summarization group
111: l_project_burdened_cost pa_expenditure_items_all.project_burdened_cost%type := 0;
112: -- Running total of burden cost (project currency)for a summarization group
113:
114: i number := 0; -- running sequence to load EI
115:
121: -- The current project id
122: c_expenditure_type varchar2(30);
123: -- The current expenditure type
124: c_billable_flag varchar2(1);
125: l_work_type_id pa_expenditure_items_all.work_type_id%TYPE := NULL;
126:
127: -- To maintain the current task id, project_id and expenditure_type
128: -- call to patc.get_status needs to be called only if task_id
129: -- or project_id or expenditure_type
149: l_stage NUMBER ; /*2933915*/
150: l_burden_profile VARCHAR2(2); /*2933915*/
151: ei_update_count NUMBER ; /*2933915*/
152: cdl_update_count NUMBER ; /*2933915*/
153: /*reason PA_Expenditure_Items.Ind_Cost_Dist_Rejection_Code%TYPE; /*2933915*/
154: l_proj_bc_enabled VARCHAR2(1);
155:
156:
157:
158: l_prev_expenditure_id NUMBER ; -- Bug 3551106
159: l_curr_expenditure_id NUMBER ; -- Bug 3551106
160:
161: /* Local variables added for 4057874 */
162: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type DEFAULT NULL;
163: l_nl_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type DEFAULT NULL;
164: l_nl_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type DEFAULT NULL;
165: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type DEFAULT NULL;
166: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type DEFAULT NULL;
159: l_curr_expenditure_id NUMBER ; -- Bug 3551106
160:
161: /* Local variables added for 4057874 */
162: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type DEFAULT NULL;
163: l_nl_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type DEFAULT NULL;
164: l_nl_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type DEFAULT NULL;
165: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type DEFAULT NULL;
166: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type DEFAULT NULL;
167: l_inventory_item_id PA_EXPENDITURE_ITEMS_ALL.inventory_item_id%type DEFAULT NULL;
160:
161: /* Local variables added for 4057874 */
162: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type DEFAULT NULL;
163: l_nl_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type DEFAULT NULL;
164: l_nl_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type DEFAULT NULL;
165: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type DEFAULT NULL;
166: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type DEFAULT NULL;
167: l_inventory_item_id PA_EXPENDITURE_ITEMS_ALL.inventory_item_id%type DEFAULT NULL;
168: l_vendor_id PA_COMMITMENT_TXNS.vendor_id%type default null;
161: /* Local variables added for 4057874 */
162: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type DEFAULT NULL;
163: l_nl_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type DEFAULT NULL;
164: l_nl_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type DEFAULT NULL;
165: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type DEFAULT NULL;
166: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type DEFAULT NULL;
167: l_inventory_item_id PA_EXPENDITURE_ITEMS_ALL.inventory_item_id%type DEFAULT NULL;
168: l_vendor_id PA_COMMITMENT_TXNS.vendor_id%type default null;
169: l_bom_labor_resource_id PA_COMMITMENT_TXNS.bom_equipment_resource_id%type default null;
163: l_nl_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type DEFAULT NULL;
164: l_nl_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type DEFAULT NULL;
165: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type DEFAULT NULL;
166: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type DEFAULT NULL;
167: l_inventory_item_id PA_EXPENDITURE_ITEMS_ALL.inventory_item_id%type DEFAULT NULL;
168: l_vendor_id PA_COMMITMENT_TXNS.vendor_id%type default null;
169: l_bom_labor_resource_id PA_COMMITMENT_TXNS.bom_equipment_resource_id%type default null;
170: l_bom_equipment_resource_id PA_COMMITMENT_TXNS.bom_labor_resource_id%type default null;
171:
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
274: and cdl.project_id = p.project_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
284: and nvl(cdl.reversed_flag,'N') = 'N'
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
324: and cdl.project_id = p.project_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
468: and ((cdl.burden_sum_source_run_id >0
715: and cdl.line_num_reversed IS NULL )) /*2933915*/
716: and cdl.project_id = current_project_id
717: and nvl(cdl.burden_sum_rejection_code, 'ABC') <> 'PROJECT_TASK_NULL' /*2255068*/
718: and exists ( select NULL
719: from pa_expenditure_items_all ei
720: where ei.expenditure_item_id = cdl.expenditure_item_id
721: and ei.expenditure_item_date <= l_end_date
722: )
723: and rownum <= l_profile_set_size ;
847: /* and cdl.burden_sum_rejection_code IS NULL 3040274 -Commented to process rejected cdls of previous runs*/
848: and cdl.project_id = current_project_id
849: and cdl.request_id <>x_request_id /*2933915*/
850: and exists (select null
851: from pa_expenditure_items ei
852: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
853: and ei.project_id = current_project_id /*5406802*/
854: and ei.expenditure_item_id = cdl.expenditure_item_id
855: and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
860: IF l_tbl_eiid.count > 0 THEN /* Bug# 5406802 */
861:
862: /* Changed this update for Bug# 5406802 */
863: FORALL I in 1..l_tbl_eiid.count
864: UPDATE pa_expenditure_items ei
865: set ei.request_id = x_request_id
866: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
867: and ei.project_id = current_project_id
868: and ei.request_id <> x_request_id /*2933915*/
986:
987: /*2933915 : Update affected ei with the newly derived compiled set_id */
988:
989: FORALL i in 1..l_eiid_tbl.count
990: UPDATE pa_expenditure_items ei
991: SET cost_ind_compiled_set_id = l_compiled_set_id_tbl(i)
992: where ei.task_id =l_task_id_tbl(i)
993: AND ei.expenditure_type =l_exp_type_tbl(i)
994: AND trunc(ei.expenditure_item_date) =trunc(l_exp_item_date_tbl(i))
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
1079: AND cdl.project_id = current_project_id
1140: AND cdl.project_id = current_project_id
1141: /* AND cdl.prev_ind_compiled_set_id is NOT NULL :3040724
1142: AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1143: AND cdl.expenditure_item_id in (select ei.expenditure_item_id
1144: from pa_expenditure_items_all ei,
1145: pa_expenditures_all e
1146: where e.expenditure_id =ei.expenditure_id
1147: AND ei.task_id =rec.task_id
1148: AND ei.expenditure_type =rec.expenditure_type
1161: END IF;
1162:
1163: IF ((l_status = 0) OR (l_status =100 AND l_stage =400)) THEN
1164:
1165: UPDATE pa_expenditure_items ei
1166: SET cost_ind_compiled_set_id = l_compiled_set_id
1167: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
1168: AND ei.task_id =rec.task_id
1169: AND ei.expenditure_type =rec.expenditure_type
1189: AND cdl.project_id = current_project_id
1190: /* AND cdl.prev_ind_compiled_set_id is NOT NULL Commented for 3040724
1191: AND cdl.burden_sum_rejection_code ='IN_PROCESS' /*2993915
1192: AND cdl.expenditure_item_id in (select ei.expenditure_item_id
1193: from pa_expenditure_items_all ei,
1194: pa_expenditures_all e
1195: where e.expenditure_id =ei.expenditure_id
1196: AND ei.task_id =rec.task_id
1197: AND ei.expenditure_type =rec.expenditure_type
1246: burden_sum_rejection_code is null)
1247: and cdl.project_id = current_project_id
1248: and exists
1249: (select null
1250: from pa_expenditure_items_all ei
1251: where ei.expenditure_item_id = cdl.expenditure_item_id
1252: and ei.expenditure_item_date <= l_end_date
1253: )
1254: and rownum <= l_profile_set_size;
1642: begin
1643: IF P_DEBUG_MODE THEN
1644: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1400:Get Expenditure Item Id from Sequence');
1645: END IF;
1646: select pa_expenditure_items_s.nextval
1647: into exp_item_id from dual;
1648: i := i +1;
1649:
1650:
2194:
2195: IF P_DEBUG_MODE THEN
2196: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2100:Process Last set .. Get Exp item id from sequence');
2197: END IF;
2198: select pa_expenditure_items_s.nextval
2199: into exp_item_id from dual;
2200:
2201: /* Moved the derivation of work type id to here for bug 2607781 as it needs to be passed to pa_transactions_pub.validate_transaction */
2202: IF ( NVL(pa_utils4.is_exp_work_type_enabled, 'N') = 'Y' )
2485: /*2933915 :Resetting 'special eis' -Adjustment Type and commiting by batches */
2486:
2487: /* LOOP commented for Bug 4747865 */
2488:
2489: UPDATE pa_expenditure_items_all ei
2490: set adjustment_type =NULL, /*Start of bug 4754024*/
2491: cc_bl_distributed_code =decode(tp_ind_compiled_set_id,NULL,
2492: decode(cc_bl_distributed_code,'Y','N',cc_bl_distributed_code),cc_bl_distributed_code), /*4754024*/
2493: cc_ic_processed_code =decode(tp_ind_compiled_set_id,NULL,decode(cc_ic_processed_code,'Y','N',cc_ic_processed_code)
2615:
2616: prev_bcc_rec pa_cmt_burden_summary_v%rowtype;
2617: l_attribute1 pa_projects_all.attribute1%type;
2618: l_cmt_line_id pa_commitment_txns.cmt_line_id%type;
2619: l_burden_cost pa_expenditure_items_all.burden_cost%type;
2620: l_txn_ref1 pa_commitment_txns.original_txn_reference1%type;
2621: apps_id number(3) := 275; -- For PA
2622: sob_id number(15);
2623: l_gl_period varchar2(15);
2630:
2631: /* end bug 2324127 */
2632:
2633: /* 4057874 */
2634: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2635: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2636: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2637: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2638: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2631: /* end bug 2324127 */
2632:
2633: /* 4057874 */
2634: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2635: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2636: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2637: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2638: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2639:
2632:
2633: /* 4057874 */
2634: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2635: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2636: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2637: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2638: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2639:
2640: ------------ Decalrarion of Cursors ---------------
2633: /* 4057874 */
2634: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2635: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2636: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2637: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2638: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2639:
2640: ------------ Decalrarion of Cursors ---------------
2641:
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
3323: AND cdl.expenditure_item_id = ei.expenditure_item_id