From: "Mahendra, Anil" 7/27/99 2:07 PM Subject: RE: Help billing extension To: "'rpc@ndf.vsnl.net.in'" Here is a copy of the PA Labor Billing Extension. Regards AJM --------------------------------------------------------------------------------- -- FILENAME: GPALBEXB.pls - -- - -- DESCRIPTION: Package body for labor billing extension - -- - -- - -- HISTORY: - -- - -- VERSION DATE NAME DESCRIPTION - -- -------- -------- ----------- -------------- - -- All information removed! - -- - -- WARNING: Do not change the procedure name of the labor billing - -- extension. In addition, do not change the parameter - -- names, parameter types, parameter order, or number of - -- parameters in the procedure. - -- - --------------------------------------------------------------------------------- -- SET VERIFY ON SET SERVEROUTPUT ON WHENEVER SQLERROR EXIT FAILURE ROLLBACK; -- CREATE OR REPLACE PACKAGE BODY PA_Client_Extn_Billing AS -- PROCEDURE Calc_Bill_Amount( x_expenditure_item_id in number, x_sys_linkage_function in varchar2, x_amount in out number, x_bill_rate_flag in out varchar2, x_status in out number) IS -- v_quantity PA_EXPENDITURE_ITEMS_ALL.QUANTITY%TYPE; v_bill_rate_override PA_EXPENDITURE_ITEMS_ALL.BILL_RATE%TYPE; v_bill_rate PA_EXPENDITURE_ITEMS_ALL.BILL_RATE%TYPE; v_trans_exp_id PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_ID%TYPE; v_agreed_variance NUMBER; v_variance_amount NUMBER; v_standard_amount NUMBER; v_rev_var_amt NUMBER; v_orig_var_amt NUMBER; v_person_id NUMBER; v_revenue_budget REAL; v_cost_budget REAL; v_top_task_id NUMBER; v_exp_task_id NUMBER; v_proj_id NUMBER; v_exp_item_date DATE; v_rowid ROWID; e_extension_failure EXCEPTION; -- -- -- Local Cursor declarations -- -- Cursor to get the Agreed Variance percentage held in attribute5 -- CURSOR cur_agreed_variance IS SELECT pt.attribute5 -- Task Agreed Variance % , peia.rowid , peia.quantity , peia.transferred_from_exp_item_id , pbra.rate , pea.incurred_by_person_id , peia.expenditure_item_date FROM pa_expenditure_items_all peia , pa_expenditures_all pea , pa_tasks pt , pa_bill_rates_all pbra WHERE pbra.std_bill_rate_schedule = pt.labor_std_bill_rate_schdl AND pbra.start_date_active <= peia.expenditure_item_date AND nvl(pbra.end_date_active,to_date('31-dec-9999','dd-mon-yyyy')) >= peia.expenditure_item_date AND pbra.bill_rate_organization_id = pt.labor_bill_rate_org_id AND ((pbra.person_id = pea.incurred_by_person_id and pbra.person_id is not null) OR (pbra.person_id is null)) AND ((pbra.job_id = peia.job_id and pbra.job_id is not null) OR (pbra.job_id is null)) AND pbra.expenditure_type is null AND pbra.non_labor_resource is null AND pea.expenditure_id = peia.expenditure_id AND pt.task_id = peia.task_id AND peia.expenditure_item_id = x_expenditure_item_id; -- -- Cursor to get the original expenditure item -- CURSOR cur_orig_item(p_trans_from_id NUMBER) IS SELECT attribute6 -- The Agreed Variance amount FROM pa_expenditure_items_all WHERE expenditure_item_id = p_trans_from_id; -- -- Cursor to get the reversal expenditure item -- CURSOR cur_reversed_item(p_trans_from_id NUMBER) IS SELECT attribute6 -- The Agreed Variance amount FROM pa_expenditure_items_all WHERE adjusted_expenditure_item_id = p_trans_from_id; -- -- Cursor to get Project_id and Task_id for expenditure item id -- CURSOR cur_proj_task_id IS SELECT PAT.PROJECT_ID, PAT.TASK_ID FROM PA_TASKS PAT, PA_EXPENDITURE_ITEMS_ALL PEIA WHERE PAT.TASK_ID = PEIA.TASK_ID AND PEIA.EXPENDITURE_ITEM_ID = x_expenditure_item_id; -- -- Cursor to get Bill rate override for Person and Task for expenditure item id -- CURSOR cur_task_bill_rate_override (p_person_id NUMBER, p_task_id NUMBER, p_exp_item_date DATE) IS SELECT NVL(PEBR.RATE,0) FROM PA_EMP_BILL_RATE_OVERRIDES PEBR WHERE PEBR.PERSON_ID = p_person_id AND PEBR.TASK_ID = p_task_id AND p_exp_item_date BETWEEN START_DATE_ACTIVE AND nvl(PEBR.end_date_active,to_date('31-dec-9999','dd-mon-yyyy')); -- -- Cursor to get Bill rate override for Person and Project for expenditure item id -- CURSOR cur_proj_bill_rate_override (p_person_id NUMBER, p_proj_id NUMBER, p_exp_item_date DATE) IS SELECT NVL(PEBR.RATE,0) FROM PA_EMP_BILL_RATE_OVERRIDES PEBR WHERE PEBR.PERSON_ID = p_person_id AND PEBR.PROJECT_ID = p_proj_id AND p_exp_item_date BETWEEN START_DATE_ACTIVE AND nvl(PEBR.end_date_active,to_date('31-dec-9999','dd-mon-yyyy')); -- -- BEGIN -- -- Reset the output parameters. x_status := 0; v_variance_amount := 0; v_bill_rate_override := 0; -- IF (x_sys_linkage_function = 'STRAIGHT_TIME') THEN -- Get Project and Task id for expenditure item OPEN cur_proj_task_id; FETCH cur_proj_task_id INTO v_proj_id,v_exp_task_id; -- IF cur_proj_task_id%NOTFOUND THEN RAISE e_extension_failure; END IF; -- CLOSE cur_proj_task_id; -- -- Use standard Oracle API to get the Top Task Id for the Task associated with the expenditure item v_top_task_id := pa_task_utils.get_top_task_id(v_exp_task_id); -- -- Get the Budget for Project and Task associated with Expenditure item -- by using standard Oracle api which returns the revenue budget and -- cost budget. Only interested in Revenue Budget PB_PUBLIC.GET_BUDGET_AMOUNT(v_proj_id,v_top_task_id,v_revenue_budget,v_cost_budget); -- -- Task must have a budget otherwise Generate Draft Rev will fail for this task -- Therefore, need to check funding for this task. Do not calculate Agreed Variance -- if there is no funding for this task IF v_revenue_budget > 0 THEN -- -- Get the Agreed Variance percentage held in attribute5 OPEN cur_agreed_variance; FETCH cur_agreed_variance INTO v_agreed_variance, v_rowid, v_quantity, v_trans_exp_id, v_bill_Rate, v_person_id, v_exp_item_date; -- IF cur_agreed_variance%NOTFOUND THEN RAISE e_extension_failure; END IF; -- CLOSE cur_agreed_variance; -- IF NVL(v_agreed_variance,0) != 0 THEN -- call cursor to get Task Level Employee Bill Rate Override OPEN cur_task_bill_rate_override ( v_person_id , v_exp_task_id , v_exp_item_date); FETCH cur_task_bill_rate_override INTO v_bill_rate_override; -- IF cur_task_bill_rate_override%NOTFOUND THEN -- If task level override does not exist then -- call cursor to get Project Level Employee Bill Rate Override CLOSE cur_task_bill_rate_override; OPEN cur_proj_bill_rate_override ( v_person_id , v_proj_id , v_exp_item_date); FETCH cur_proj_bill_rate_override INTO v_bill_rate_override; -- IF cur_proj_bill_rate_override%NOTFOUND THEN CLOSE cur_proj_bill_rate_override; END IF; END IF; -- -- If an Employee Bill Rate Override exists then use it -- to calculate the revenue, otherwise use the Standard -- Bill rate -- IF v_bill_rate_override != 0 THEN v_bill_rate := v_bill_rate_override; END IF; -- v_standard_amount := v_quantity * v_bill_rate; v_variance_amount := pa_currency.round_currency_amt((v_standard_amount * v_agreed_variance)/100); x_amount := v_standard_amount + v_variance_amount; END IF; -- -- This update statement needs to be here because if the users transfer an expenditure -- item from a project with an agreed var % to a project without an agreed var % -- then this program needs to reset the agreed var dff to zero -- UPDATE pa_expenditure_items_all SET attribute6 = v_variance_amount WHERE rowid = v_rowid; -- IF SQL%NOTFOUND THEN RAISE e_extension_failure; END IF; -- -- If this Expenditure item was transferred from another project/task -- then we need to reverse the Agreed Variance Amount from the original expenditure item -- this is because it seems that the labour billing extension is not called when reversing -- the original item -- IF v_trans_exp_id IS NOT NULL THEN -- -- Get original item -- OPEN cur_orig_item(v_trans_exp_id); FETCH cur_orig_item INTO v_orig_var_amt; -- IF cur_orig_item%NOTFOUND THEN RAISE e_extension_failure; END IF; -- CLOSE cur_orig_item; -- -- Get reversal item -- OPEN cur_reversed_item(v_trans_exp_id); FETCH cur_reversed_item INTO v_rev_var_amt; -- IF cur_reversed_item%NOTFOUND THEN RAISE e_extension_failure; END IF; -- CLOSE cur_reversed_item; -- -- IF v_rev_var_amt IS NULL THEN -- -- Need to reverse the agreed variance amount on the reversal item -- UPDATE pa_expenditure_items_all SET attribute6 = v_orig_var_amt * -1 WHERE adjusted_expenditure_item_id = v_trans_exp_id; -- IF SQL%NOTFOUND THEN RAISE e_extension_failure; END IF; -- END IF; -- END IF; -- END IF; -- END IF; EXCEPTION WHEN e_extension_failure THEN IF cur_agreed_variance%ISOPEN THEN CLOSE cur_agreed_variance; ELSIF cur_reversed_item%ISOPEN THEN CLOSE cur_reversed_item; ELSIF cur_reversed_item%ISOPEN THEN CLOSE cur_orig_item; ELSIF cur_proj_task_id%ISOPEN THEN CLOSE cur_proj_task_id; END IF; x_status := SQLCODE; RAISE; WHEN OTHERS THEN -- Raise an ORACLE error. SQLCODE is always negative. x_status := SQLCODE; RAISE; -- END Calc_Bill_Amount; -- END PA_Client_Extn_Billing; -- -- /