[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* PROCEDURE delete_burden_pct ( ... ) *
* FUNCTION do_pct_exist ( ... ) *
* *
* HISTORY *
* 21-Nov-2000 Uday Moogala - Created *
* Bug# 1419482 Percentage Burden Enhancements. *
* 1. Copy to all periods option : *
* Copy Percentage burden from one costing period to all the subsequent *
* open/frozen costing periods in the same calendar or *
* to all the open/frozen periods if it is a different calendar. *
* For more details refer to DLD : pct_burden_dld.rtf *
* 30-OCT-2002 RajaSekhar Bug#2641405 Added NOCOPY hint. *
*****************************************************************************/
PROCEDURE end_copy
(
pi_errstat IN VARCHAR2,
pi_errmsg IN VARCHAR2
);
PROCEDURE delete_burden_pct
(
pi_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
pi_period_id IN gmf_burden_percentages.period_id%TYPE,
pi_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
pi_from_range IN gmf_burden_codes.burden_code%TYPE,
pi_to_range IN gmf_burden_codes.burden_code%TYPE
);
SELECT a.period_id
INTO l_period_id_from
FROM cm_cldr_mst_v a
WHERE a.legal_entity_id = pi_legal_entity_id_from
AND a.calendar_code = pi_calendar_code_from
AND a.period_code = pi_period_code_from
AND a.cost_type_id = pi_cost_type_id_from;
l_sql_stmt := ' SELECT ' ||
' pct.legal_entity_id, ' ||
' pct.period_id, ' ||
' pct.cost_type_id, ' ||
' bur.burden_id, ' ||
' bur.burden_code, ' ||
' pct.organization_id, ' ||
' pct.master_organization_id, ' ||
' pct.inventory_item_id, ' ||
' pct.gl_category_id, ' ||
' pct.cost_category_id, ' ||
' pct.gl_prod_line_category_id, ' ||
' pct.gl_business_category_id, ' ||
' pct.sspl_category_id, ' ||
' pct.percentage ' ||
' FROM ' ||
' gmf_burden_percentages pct, ' ||
' gmf_burden_codes bur ' ||
' WHERE ' ||
' pct.legal_entity_id = :b_legal_entity_id AND ' ||
' pct.period_id = :b_period_id AND ' ||
' pct.cost_type_id = :b_cost_type_id AND ' ||
' pct.delete_mark = 0 AND ' ||
' pct.burden_id = bur.burden_id AND ' ||
' bur.delete_mark = 0 AND ' ||
' bur.burden_code >= nvl(:b_from_brdn,bur.burden_code) AND ' ||
' bur.burden_code <= nvl(:b_to_brdn,bur.burden_code) '||
' ORDER BY ' ||
' pct.legal_entity_id, pct.period_id, pct.cost_type_id, pct.burden_id';
l_sql_periods := 'SELECT :pi_period_id_to FROM dual ' ;
l_sql_periods := 'SELECT DISTINCT ' ||
' c3.period_id ' ||
'FROM ' ||
'cm_cldr_mst_v c3, cm_cldr_mst_v c2, cm_cldr_mst_v c1 ' ||
'WHERE ' ||
'c3.legal_entity_id = :pi_legal_entity_id AND ' ||
'c1.calendar_code = :pi_calendar_code_to AND ' ||
'c1.period_code = :pi_all_periods_from AND ' ||
'c2.calendar_code = :pi_calendar_code_to AND ' ||
'c2.period_code = :pi_all_periods_to AND ' ||
'c3.calendar_code = :pi_calendar_code_to AND ' ||
'c3.cost_Type_id = :pi_cost_type_id_to AND ' ||
'c2.legal_entity_id = c3.legal_entity_id AND ' ||
'c1.legal_entity_id = c2.legal_entity_id AND ' ||
'c3.start_date >= c1.start_date AND ' ||
'c3.end_date <= c2.end_date AND ' ||
'c3.period_status <> ''C''';
SELECT a.period_id
INTO pi_period_id_from
FROM cm_cldr_mst_v a
WHERE a.legal_entity_id = pi_legal_entity_id_from
AND a.calendar_code = pi_calendar_code_from
AND a.period_code = pi_period_code_from
AND a.cost_type_id = pi_cost_type_id_from;
SELECT a.period_id
INTO pi_period_id_to
FROM cm_cldr_mst_v a
WHERE a.legal_entity_id = pi_legal_entity_id_to
AND a.calendar_code = pi_calendar_code_to
AND a.period_code = pi_period_code_to
AND a.cost_type_id = pi_cost_type_id_to;
delete_burden_pct
(
pi_legal_entity_id_to,
l_period_id_to,
pi_cost_type_id_to,
pi_from_range,
pi_to_range
);
* Try update first *
*******************/
<>
DECLARE
e_insert_row_b EXCEPTION;
RAISE e_insert_row_b;
UPDATE gmf_burden_percentages
SET burden_percentage_id = GMF_BURDEN_PERCENTAGE_ID_S.NEXTVAL,
percentage = r_brdn_pct.percentage,
delete_mark = 0,
last_updated_by = g_user_id,
last_update_login = g_login_id,
last_update_date = SYSDATE
WHERE
legal_entity_id = pi_legal_entity_id_to AND
period_id = l_period_id_to AND
cost_type_id = pi_cost_type_id_to AND
burden_id = r_brdn_pct.burden_id AND
nvl(organization_id,-1) = nvl(r_brdn_pct.organization_id,-1) AND
nvl(master_organization_id,-1) = nvl(r_brdn_pct.master_organization_id,-1) AND
nvl(inventory_item_id,-1) = nvl(r_brdn_pct.inventory_item_id,-1) AND
nvl(gl_category_id,-1) = nvl(r_brdn_pct.gl_category_id,-1) AND
nvl(cost_category_id,-1) = nvl(r_brdn_pct.cost_category_id,-1) AND
nvl(gl_prod_line_category_id,-1) = nvl(r_brdn_pct.gl_prod_line_category_id,-1) AND
nvl(gl_business_category_id,-1) = nvl(r_brdn_pct.gl_business_category_id,-1) AND
nvl(sspl_category_id,-1) = nvl(r_brdn_pct.sspl_category_id,-1);
* If update fails then try insert *
**********************************/
IF( SQL%ROWCOUNT <= 0 ) THEN
RAISE e_insert_row_b;
WHEN e_insert_row_b THEN
INSERT INTO gmf_burden_percentages
(
burden_percentage_id,
legal_entity_id,
period_id,
cost_type_id,
burden_id,
organization_id,
master_organization_id,
inventory_item_id,
gl_category_id,
cost_category_id,
gl_prod_line_category_id,
gl_business_category_id,
sspl_category_id,
percentage,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
GMF_BURDEN_PERCENTAGE_ID_S.NEXTVAL,
pi_legal_entity_id_to,
l_period_id_to,
pi_cost_type_id_to,
r_brdn_pct.burden_id,
r_brdn_pct.organization_id,
r_brdn_pct.master_organization_id,
r_brdn_pct.inventory_item_id,
r_brdn_pct.gl_category_id,
r_brdn_pct.cost_category_id,
r_brdn_pct.gl_prod_line_category_id,
r_brdn_pct.gl_business_category_id,
r_brdn_pct.sspl_category_id,
r_brdn_pct.percentage,
0, -- delete_mark
g_user_id, -- created_by
SYSDATE, -- creation_date
g_user_id, -- last_updated_by
SYSDATE, -- last_update_date
g_login_id -- last_update_login
);
END insert_or_update_bur;
gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_brdn_rows) );
* delete_burden_pct *
* *
* DESCRIPTION *
* Deletes the burden percentages for the parameters passed *
* *
* INPUT PARAMETERS *
* calendar, period, cost_mthd, burden_codes range *
* *
* HISTORY *
* 15-Feb-2001 Uday Moogala Seshadri *
**************************************************************/
PROCEDURE delete_burden_pct
(
pi_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
pi_period_id IN gmf_burden_percentages.period_id%TYPE,
pi_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
pi_from_range IN gmf_burden_codes.burden_code%TYPE,
pi_to_range IN gmf_burden_codes.burden_code%TYPE
)
IS
/******************
* Local Variables *
******************/
l_del_stmt VARCHAR2(1500);
l_del_stmt := ' DELETE FROM gmf_burden_percentages pct ' ||
' WHERE ' ||
' pct.legal_entity_id = :b_legal_entity_id AND ' ||
' pct.period_id = :b_period_id AND ' ||
' pct.cost_type_id = :b_cost_type_id AND ' ||
' pct.burden_id IN ( ';
l_sub_qry := ' SELECT ' ||
' bur.burden_id ' ||
' FROM ' ||
' gmf_burden_codes bur ' ||
' WHERE ' ||
' bur.delete_mark = 0 AND ' ||
' bur.burden_code >= nvl(:b_burden_code_from,bur.burden_code) AND ' ||
' bur.burden_code <= nvl(:b_burden_code_to,bur.burden_code) ' ;
gmf_util.trace( SQL%ROWCOUNT || ' Rows deleted', 1 );
END delete_burden_pct;
SELECT COUNT(1)
FROM gmf_burden_percentages pct,
gmf_burden_codes bur
WHERE pct.legal_entity_id = p_legal_entity_id
AND pct.period_id = p_period_id
AND pct.delete_mark = 0
AND pct.burden_id = bur.burden_id
AND bur.delete_mark = 0
AND bur.burden_code >= nvl(p_burden_code_from,bur.burden_code)
AND bur.burden_code <= nvl(p_burden_code_to,bur.burden_code);