[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:
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
276: and cdl.project_id = p.project_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
287: and nvl(cdl.reversed_flag,'N') = 'N'
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
325: and ( x_end_date is null OR exists
326: (select 1 from pa_expenditure_items ei
327: where ei.expenditure_item_id = cdl.expenditure_item_id
328: and ei.expenditure_item_date <= l_end_date )
329: )
330: );
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
464: and ((cdl.burden_sum_source_run_id >0
711: and cdl.line_num_reversed IS NULL )) /*2933915*/
712: and cdl.project_id = current_project_id
713: and nvl(cdl.burden_sum_rejection_code, 'ABC') <> 'PROJECT_TASK_NULL' /*2255068*/
714: and exists ( select NULL
715: from pa_expenditure_items_all ei
716: where ei.expenditure_item_id = cdl.expenditure_item_id
717: and ei.expenditure_item_date <= l_end_date
718: )
719: and rownum <= l_profile_set_size ;
844: /* and cdl.burden_sum_rejection_code IS NULL 3040274 -Commented to process rejected cdls of previous runs*/
845: and cdl.project_id = current_project_id
846: and cdl.request_id <>x_request_id /*2933915*/
847: and exists (select null
848: from pa_expenditure_items ei
849: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
850: and ei.project_id = current_project_id /*5406802*/
851: and ei.expenditure_item_id = cdl.expenditure_item_id
852: and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
866: and cdl.amount <> 0 --Bug 10174581
867: and cdl.project_id = current_project_id
868: and cdl.request_id <>x_request_id
869: and exists (select null
870: from pa_expenditure_items ei
871: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
872: and ei.project_id = current_project_id
873: and ei.expenditure_item_id = cdl.expenditure_item_id
874: and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
887: IF l_tbl_eiid.count > 0 THEN /* Bug# 5406802 */
888:
889: /* Changed this update for Bug# 5406802 */
890: FORALL I in 1..l_tbl_eiid.count
891: UPDATE pa_expenditure_items ei
892: set ei.request_id = x_request_id
893: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
894: and ei.project_id = current_project_id
895: and ei.request_id <> x_request_id /*2933915*/
1013:
1014: /*2933915 : Update affected ei with the newly derived compiled set_id */
1015:
1016: FORALL i in 1..l_eiid_tbl.count
1017: UPDATE pa_expenditure_items ei
1018: SET cost_ind_compiled_set_id = l_compiled_set_id_tbl(i)
1019: where ei.task_id =l_task_id_tbl(i)
1020: AND ei.expenditure_type =l_exp_type_tbl(i)
1021: AND trunc(ei.expenditure_item_date) =trunc(l_exp_item_date_tbl(i))
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
1109: AND cdl.project_id = current_project_id
1175: AND cdl.project_id = current_project_id
1176: /* AND cdl.prev_ind_compiled_set_id is NOT NULL :3040724
1177: AND cdl.burden_sum_rejection_code ='IN_PROCESS'
1178: AND cdl.expenditure_item_id in (select ei.expenditure_item_id
1179: from pa_expenditure_items_all ei,
1180: pa_expenditures_all e
1181: where e.expenditure_id =ei.expenditure_id
1182: AND ei.task_id =rec.task_id
1183: AND ei.expenditure_type =rec.expenditure_type
1196: END IF;
1197:
1198: IF ((l_status = 0) OR (l_status =100 AND l_stage =400)) THEN
1199:
1200: UPDATE pa_expenditure_items ei
1201: SET cost_ind_compiled_set_id = l_compiled_set_id
1202: where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
1203: AND ei.task_id =rec.task_id
1204: AND ei.expenditure_type =rec.expenditure_type
1224: AND cdl.project_id = current_project_id
1225: /* AND cdl.prev_ind_compiled_set_id is NOT NULL Commented for 3040724
1226: AND cdl.burden_sum_rejection_code ='IN_PROCESS' /*2993915
1227: AND cdl.expenditure_item_id in (select ei.expenditure_item_id
1228: from pa_expenditure_items_all ei,
1229: pa_expenditures_all e
1230: where e.expenditure_id =ei.expenditure_id
1231: AND ei.task_id =rec.task_id
1232: AND ei.expenditure_type =rec.expenditure_type
1285: burden_sum_rejection_code is null)
1286: and cdl.project_id = current_project_id
1287: and exists
1288: (select null
1289: from pa_expenditure_items_all ei
1290: where ei.expenditure_item_id = cdl.expenditure_item_id
1291: and ei.expenditure_item_date <= l_end_date
1292: )
1293: and rownum <= l_profile_set_size;
1307: burden_sum_rejection_code is null)
1308: and cdl.project_id = current_project_id
1309: and exists
1310: (select null
1311: from pa_expenditure_items_all ei
1312: where ei.expenditure_item_id = cdl.expenditure_item_id
1313: and ei.expenditure_item_date <= l_end_date
1314: )
1315: and exists
1781: begin
1782: IF P_DEBUG_MODE THEN
1783: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1400:Get Expenditure Item Id from Sequence');
1784: END IF;
1785: select pa_expenditure_items_s.nextval
1786: into exp_item_id from dual;
1787: i := i +1;
1788:
1789:
2336:
2337: IF P_DEBUG_MODE THEN
2338: pa_cc_utils.log_message('create_burden_expenditure_item: ' || '2100:Process Last set .. Get Exp item id from sequence');
2339: END IF;
2340: select pa_expenditure_items_s.nextval
2341: into exp_item_id from dual;
2342:
2343: /* Moved the derivation of work type id to here for bug 2607781 as it needs to be passed to pa_transactions_pub.validate_transaction */
2344: IF ( NVL(pa_utils4.is_exp_work_type_enabled, 'N') = 'Y' )
2629: /*2933915 :Resetting 'special eis' -Adjustment Type and commiting by batches */
2630:
2631: /* LOOP commented for Bug 4747865 */
2632:
2633: UPDATE pa_expenditure_items_all ei
2634: set adjustment_type =NULL, /*Start of bug 4754024*/
2635: cc_bl_distributed_code =decode(tp_ind_compiled_set_id,NULL,
2636: decode(cc_bl_distributed_code,'Y','N',cc_bl_distributed_code),cc_bl_distributed_code), /*4754024*/
2637: cc_ic_processed_code =decode(tp_ind_compiled_set_id,NULL,decode(cc_ic_processed_code,'Y','N',cc_ic_processed_code)
2759:
2760: prev_bcc_rec pa_cmt_burden_summary_v%rowtype;
2761: l_attribute1 pa_projects_all.attribute1%type;
2762: l_cmt_line_id pa_commitment_txns.cmt_line_id%type;
2763: l_burden_cost pa_expenditure_items_all.burden_cost%type;
2764: l_txn_ref1 pa_commitment_txns.original_txn_reference1%type;
2765: apps_id number(3) := 275; -- For PA
2766: sob_id number(15);
2767: l_gl_period varchar2(15);
2774:
2775: /* end bug 2324127 */
2776:
2777: /* 4057874 */
2778: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2779: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2780: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2781: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2782: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2775: /* end bug 2324127 */
2776:
2777: /* 4057874 */
2778: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2779: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2780: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2781: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2782: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2783:
2776:
2777: /* 4057874 */
2778: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2779: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2780: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2781: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2782: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2783:
2784: ------------ Decalrarion of Cursors ---------------
2777: /* 4057874 */
2778: l_job_id PA_EXPENDITURE_ITEMS_ALL.job_id%type default null;
2779: l_non_labor_resource PA_EXPENDITURE_ITEMS_ALL.non_labor_resource%type default null;
2780: l_non_labor_resource_orgn_id PA_EXPENDITURE_ITEMS_ALL.organization_id%type default null;
2781: l_wip_resource_id PA_EXPENDITURE_ITEMS_ALL.wip_resource_id%type default null;
2782: l_incurred_by_person_id PA_EXPENDITURES_ALL.incurred_by_person_id%type default null;
2783:
2784: ------------ Decalrarion of Cursors ---------------
2785:
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
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
3472: AND cdl.expenditure_item_id = ei.expenditure_item_id