[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* the code combinations and then select from this table. The queries
* were also rewritten to enhance their performance.
* Wrapped all calls to fnd_file inside a procedure and included
* trace messages throughout for easier debugging
* 30-Oct-2002 R.Sharath Kumar Bug# 2641405
* Added NOCOPY hint
* 24-SEP-2003 Venkat Chukkapalli Bug# 3150227
* Modified code in parse_account() to parse OPM/OF accounts based on
* OF segment_num and not based on OF application_column_name.
* 26-SEP-2003 Venkat Chukkapalli Bug# 3163804
* Removed 3150227 fix and added code in parse_account() to
* initialize PL/SQL table x_segment.
* 26-JUL-2007 Himadri Chakroborty Bug #6133153
*changed the size of the variables l_fiscal_year and l_period_type to 15
* 09-Oct-2008 Pramod B.H. Bug 7458002
* a)Modified procedure process_alloc_dtl() to merge records into gl_aloc_dtl
* instead of deleting and inserting rows in gl_aloc_dtl.
* b)Modified procedure delete_allocations() to delete only obsoleted rows
* in gl_aloc_dtl.
******************************************************************************/
/* Package body global variables */
g_calendar_code cm_cldr_hdr.calendar_code%type;
SELECT COUNT(1)
INTO l_prev_req_count
FROM fnd_concurrent_requests
WHERE concurrent_program_id IN (SELECT a.concurrent_program_id
FROM fnd_concurrent_programs a,
fnd_application b
WHERE a.application_id = b.application_id
AND b.application_short_name = 'GMF'
AND a.concurrent_program_name = 'COSTALOC')
AND status_code in ('I','Q')
AND argument1 = p_legal_entity_id
AND argument2 = p_calendar_code
AND argument3 = p_period_code
AND argument4 = p_cost_type_id;
SELECT period_id
INTO g_period_id
FROM gmf_period_statuses
WHERE legal_entity_id = p_legal_entity_id
AND calendar_code = p_calendar_code
AND period_code = p_period_code
AND cost_type_id = p_cost_type_id;
SELECT cost_mthd_code
INTO g_cost_mthd_code
FROM cm_mthd_mst
WHERE cost_type_id = p_cost_type_id;
/* UPDATE cm_alpr_ctl
SET running_ind = 0,
ended_on = sysdate
WHERE calendar_code = P_control_record.calendar_code
AND period_code = P_control_record.period_code;
* delete_allocations
* DESCRIPTION
* Deletes all allocations for the current calendar and period.
*
* INPUT PARAMETERS
* v_from_alloc_code
* v_to_alloc_code
* v_calendar_code
* v_period
*
* AUTHOR
* Sukarna Reddy 09/24/98
*
* OUTPUT PARAMETERS
* v_status = 0 No row found for deletion and can continue
* = -1 Fatal Error
*
*******************************************************************************/
PROCEDURE delete_allocations(
v_from_alloc_code VARCHAR2,
v_to_alloc_code VARCHAR2,
v_status OUT NOCOPY NUMBER
)
IS
l_local_module VARCHAR2(80) := '.delete_allocations';
DELETE FROM
gl_aloc_dtl
WHERE
period_id = g_period_id AND
cost_type_id = g_cost_type_id
AND alloc_id IN (
SELECT alloc_id
FROM gl_aloc_mst
WHERE legal_entity_id = g_legal_entity_id
AND alloc_code between nvl(v_from_alloc_code,alloc_code)
AND nvl(v_to_alloc_code,alloc_code)
); */
/* Bug 7458002 - To delete only obsoleted rows in gl_aloc_dtl */
DELETE FROM
gl_aloc_dtl
WHERE period_id = g_period_id
AND cost_type_id = g_cost_type_id
AND (alloc_id,line_no) NOT IN (
SELECT b.alloc_id, b.line_no
FROM gl_aloc_mst m,gl_aloc_bas b
WHERE m.alloc_id = b.alloc_id
AND m.legal_entity_id = g_legal_entity_id
AND b.delete_mark = 0
AND m.delete_mark = 0
AND m.alloc_code BETWEEN nvl(v_from_alloc_code,m.alloc_code) and
nvl(v_to_alloc_code ,m.alloc_code)
);
alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Allocations table gl_aloc_dtl' );
alloc_log_msg(C_LOG_FILE, '0 Rows deleted from gl_aloc_dtl ');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,c_module||l_local_module,'0 Rows deleted from gl_aloc_dtl');
END delete_allocations;
SELECT gps.legal_entity_id,
xep.name
FROM gmf_period_statuses gps,
xle_entity_profiles xep
WHERE gps.period_id = p_period_id
AND gps.legal_entity_id = xep.legal_entity_id
AND gps.delete_mark = 0;
SELECT gfp.*
FROM gmf_fiscal_policies gfp
WHERE gfp.legal_entity_id = p_le_id
AND gfp.delete_mark = 0;
SELECT
concatenated_segment_delimiter,fifstr.id_flex_num
FROM
gl_ledger_le_v gll,
fnd_id_flex_structures fifstr,
fnd_application fa
WHERE
gll.chart_of_accounts_id = fifstr.id_flex_num
AND gll.ledger_id = p_ledger_id
AND fifstr.id_flex_code = 'GL#'
AND fifstr.application_id = fa.application_id
AND fa.application_short_name = 'SQLGL';
* Delete_interface
*
* DESCRIPTION
* Deletes the interface row for a given criteria when refresh ind is set.
*
* AUTHOR
* Sukarna Reddy
*
* INPUT PARAMETERS
*
* v_calendar_code = Calendar code
* v_period_code = period code
*
* OUTPUT PARAMETERS
*
* v_status = 0 No rows to delete
* = -1 Fatal Error
* HISTORY
* Sukarna Reddy Modified code for convergence July 20
****************************************************************************/
PROCEDURE delete_interface(v_status OUT NOCOPY NUMBER) IS
l_local_module VARCHAR2(80) := '.delete_interface';
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.begin','Delete interface');
DELETE
FROM gl_aloc_inp
WHERE calendar_code = g_calendar_code
AND period_code = g_period_code;
alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Interface table gl_aloc_inp' );
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.end','Delete interface');
alloc_log_msg(C_LOG_FILE, '0 rows deleted from gl_aloc_inp');
END delete_interface;
* Deletes the existing allocations for current calendar and period specified
* if refresh indicator is 1 , deletes interface rows from gl_alloc_inp for
* calendar and period specified.Populates the interface table picking
* rows from expense table, allocation basis table. if refresh interface is
* not set , refreshes the interface table with fixed percentages in the
* allocation basis. Finally calculates the percentages and expense amount
* and populates allocation table.
*
* AUTHOR
* Sukarna Reddy Date : 09/18/98
*
* INPUT PARAMETERS
*
* v_from_alloc_code
* v_to_alloc_code
* v_refresh_interface
*
* OUTPUT PARAMETERS
* v_status = -1 Fatal error occured while deleting allocations
* = -2 Fatal error occured while deleting interface rows.
* = 0 Successfull
*
* HISTORY
* Chetan Nagar 19-Feb-2001 B1418787
* List out all the burden codes that have total fixed percentage
* more than 100. These allocation codes will be ignored from processing.
* Sukarna Reddy Modified code for convergence July 2005
***************************************************************************/
PROCEDURE cost_allocate(v_from_alloc_code VARCHAR2,
v_to_alloc_code VARCHAR2,
v_refresh_interface NUMBER,
v_status OUT NOCOPY NUMBER
)
IS
x_status NUMBER;
SELECT m.alloc_code alloc_code,
sum(b.fixed_percent) total_percentage
FROM gl_aloc_mst m, gl_aloc_bas b
WHERE m.alloc_id = b.alloc_id
AND m.legal_entity_id = g_legal_entity_id
AND b.alloc_method = 1
AND m.alloc_code BETWEEN NVL(v_from_alloc_code,m.alloc_code)
AND nvl(v_to_alloc_code,m.alloc_code)
AND m.delete_mark = 0
AND b.delete_mark = 0
GROUP BY m.alloc_code
HAVING sum(b.fixed_percent) <> 100
ORDER BY 1;
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.delete_allocations','deleting Allocations ...');
delete_allocations(v_from_alloc_code, v_to_alloc_code, x_status);
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.delete_interface','deleting Interface data...');
delete_interface(x_status);
SELECT
a.alloc_code as exp_alloc_code,
e.alloc_id as exp_alloc_id,
e.line_no as exp_line_no,
e.from_account_id as exp_from_account,
e.to_account_id as exp_to_account,
e.balance_type as exp_balance_type, e.exp_ytd_ptd
FROM
gl_aloc_exp e,
gl_aloc_mst a
WHERE
e.alloc_id = a.alloc_id
and a.legal_entity_id = g_legal_entity_id
and e.delete_mark = 0
and a.delete_mark = 0
and a.alloc_code between nvl(v_from_alloc_code,a.alloc_code) and
nvl(v_to_alloc_code,a.alloc_code)
and 100 =
(select decode(max(b.alloc_method), 0, 100, 1, sum(fixed_percent))
from gl_aloc_bas b
where b.alloc_id = a.alloc_id
and b.delete_mark = 0
)
;
SELECT decode(v_balance_type,'0','A','1','B','2','A')
FROM dual;
SELECT decode(v_balance_type,'0','STAT',NULL)
FROM dual;
SELECT gl.short_name,
gl.period_set_name,
gl.accounted_period_type,
gl.chart_of_accounts_id
FROM gl_ledgers gl
WHERE gl.ledger_id = P_ledger_id
AND rownum = 1;
insert_alloc_inp(v_alloc_id,
v_line_no,
v_account_type,
x_to_account_id,
x_amount
);
* insert_alloc_inp
*
* DESCRIPTION
* Inserts a row in to gl_aloc_inp.
*
* AUTHOR
* Sukarna Reddy 09/18/98
*
* INPUT PARAMETERS
* v_alloc_id
* v_line_no
* v_account_type
* v_amount
*
* OUTPUT PARAMETERS
*
*
* HISTORY
* Modified code for inventory convergence sschinch July 2005
*******************************************************************/
PROCEDURE insert_alloc_inp(
v_alloc_id NUMBER,
v_line_no VARCHAR2,
v_account_type NUMBER,
v_to_segment NUMBER,
v_amount NUMBER
)
IS
l_local_module VARCHAR2(80):= '.insert_alloc_inp';
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.begin','Inserting ...');
INSERT
INTO
gl_aloc_inp
(
gl_aloc_inp_id,
calendar_code,
period_code,
alloc_id,
line_no,
account_key_type,
account_id,
amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
trans_cnt,
delete_mark,
text_code
)
VALUES
(
gem5_gl_aloc_inp_id_s.nextval,
g_calendar_code,
g_period_code,
v_alloc_id,
v_line_no,
v_account_type,
v_to_segment,
nvl(v_amount,0),
sysdate,
P_created_by,
sysdate,
P_created_by,
NULL,
0,
0,
NULL
);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.end','Completed Inserting data into interface...');
END insert_alloc_inp;
* Retrieves the allocation basis information,Inserts basis
* Information with balances retrieved from financials
* AUTHOR
* Sukarna Reddy 09/18/98
*
* INPUT PARAMETERS
* v_co_code
* v_from_alloc_code
* v_to_alloc_code
*
* OUTPUT PARAMETERS
*
*
* HISTORY
* Ignore all the burden codes that have total
* fixed percentage not equal to 100.
************************************************************/
PROCEDURE get_alloc_basis(
v_from_alloc_code VARCHAR2,
v_to_alloc_code VARCHAR2
)
IS
CURSOR cur_alloc_basis IS
SELECT m.alloc_id,
m.alloc_code,
b.line_no,
b.alloc_method,
b.basis_account_id,
b.balance_type,
b.bas_ytd_ptd,
b.fixed_percent
FROM gl_aloc_mst m, gl_aloc_bas b
WHERE m.alloc_id = b.alloc_id
AND m.legal_entity_id = g_legal_entity_id
AND m.alloc_code BETWEEN NVL(v_from_alloc_code,m.alloc_code) AND nvl(v_to_alloc_code,m.alloc_code)
AND m.delete_mark = 0
AND b.delete_mark = 0
AND (b.alloc_method = 0 OR
(b.alloc_method = 1 AND 100 = ( SELECT sum(bb.fixed_percent)
FROM gl_aloc_bas bb
WHERE bb.alloc_id = b.alloc_id and
bb.delete_mark = 0
)
)
)
ORDER BY 1,2;
insert_alloc_inp(cur_alloc_basis_tmp.alloc_id,
cur_alloc_basis_tmp.line_no,
0,
NULL,
cur_alloc_basis_tmp.fixed_percent);
* procedure deletes all the fixed percent rows from interface table
* and inserts into the interface table with new fixed percent rows
* values from allocation basis table.
*
* AUTHOR
* Sukarna Reddy 09/17/98
*
INPUT PARAMETERS
* v_from_alloc_code
* v_to_alloc_code
*
* OUTPUT PARAMETERS
*
*
* HISTORY
* Ignore all the burden codes that have total fixed percentage
* not equal to 100.
***************************************************************/
PROCEDURE refresh_fixed(v_from_alloc_code VARCHAR2,v_to_alloc_code VARCHAR2) IS
CURSOR cur_alloc_fixed IS
SELECT m.alloc_id,
b.line_no,
b.alloc_method,
b.fixed_percent
FROM gl_aloc_mst m, gl_aloc_bas b
WHERE m.alloc_id = b.alloc_id
AND m.legal_entity_id = g_legal_entity_id
AND b.alloc_method = 1
AND m.alloc_code BETWEEN NVL(v_from_alloc_code,m.alloc_code) AND nvl(v_to_alloc_code,m.alloc_code)
AND m.delete_mark = 0
AND b.delete_mark = 0
AND 100 = ( SELECT sum(bb.fixed_percent)
FROM gl_aloc_bas bb
WHERE bb.alloc_id = b.alloc_id and
bb.delete_mark = 0
)
ORDER BY 1,2;
DELETE
FROM gl_aloc_inp a
WHERE a.account_key_type = 0
and a.calendar_code = g_calendar_code
AND a.period_code = g_period_code
and a.alloc_id in (
select m.alloc_id
from gl_aloc_mst m,gl_aloc_bas b
where m.legal_entity_id = g_legal_entity_id
and m.alloc_id = b.alloc_id
and b.alloc_method = 1
and m.alloc_code between nvl(v_from_alloc_code,m.alloc_code)
and nvl(v_to_alloc_code,m.alloc_code)
);
alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Interface table gl_aloc_inp' );
alloc_log_msg(C_LOG_FILE, '0 Rows deleted from gl_aloc_inp for fixed');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,g_calling_module,'0 Rows deleted from gl_aloc_inp for fixed');
insert_alloc_inp(cur_aloc_fixed_tmp.alloc_id,
cur_aloc_fixed_tmp.line_no,
0,
NULL,
cur_aloc_fixed_tmp.fixed_percent
);
SELECT *
FROM gl_aloc_inp i
WHERE i.calendar_code = g_calendar_code
AND i.period_code = g_period_code
AND i.delete_mark = 0
AND account_key_type = 0
AND i.alloc_id IN (SELECT b.alloc_id
FROM gl_aloc_mst m,gl_aloc_bas b
where m.alloc_id = b.alloc_id
AND m.legal_entity_id = g_legal_entity_id
AND b.delete_mark = 0
AND m.delete_mark = 0
AND m.alloc_code BETWEEN nvl(v_from_alloc_code,m.alloc_code) and
nvl(v_to_alloc_code ,m.alloc_code))
ORDER BY alloc_id;
SELECT alloc_code
FROM gl_aloc_mst
WHERE alloc_id = v_alloc_id;
/* Select total basis amount for calculating percentage.*/
SELECT sum(amount) INTO X_prev_basis_amount
FROM gl_aloc_inp
WHERE alloc_id = cur_alocinp_tmp.alloc_id
AND calendar_code = g_calendar_code
AND period_code = g_period_code
AND account_key_type = 0;
/* Select total expense amount for allocation.*/
SELECT sum(amount)
INTO x_expense_amount
FROM gl_aloc_inp
WHERE alloc_id = cur_alocinp_tmp.alloc_id
AND calendar_code = g_calendar_code
AND period_code = g_period_code
AND account_key_type = 1;
INSERT INTO gl_aloc_dtl
(
PERIOD_ID,
COST_TYPE_ID,
ALLOC_ID,
LINE_NO,
ALLOCDTL_ID,
PERCENT_ALLOCATION,
ALLOCATED_EXPENSE_AMT,
AC_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
TRANS_CNT,
DELETE_MARK,
TEXT_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
g_period_id,
g_cost_type_id,
cur_alocinp_tmp.alloc_id,
cur_alocinp_tmp.line_no,
gem5_allocdtl_id_s.nextval,
NVL(x_alloc_percent,0),
NVL(x_allocated_amount,0),
0,
SYSDATE,
P_created_by,
P_login_id,
SYSDATE,
P_created_by,
0,
0,
NULL,
P_request_id,
P_prog_application_id,
P_program_id,
SYSDATE
); */
/* Bug 7458002 - replaced Insert with Merge */
MERGE INTO gl_aloc_dtl gdtl
USING ( SELECT g_period_id period_id,
g_cost_type_id cost_type_id,
cur_alocinp_tmp.alloc_id alloc_id,
cur_alocinp_tmp.line_no line_no
FROM dual
) ginp
ON ( gdtl.period_id = ginp.period_id
AND gdtl.cost_type_id = ginp.cost_type_id
AND gdtl.alloc_id = ginp.alloc_id
AND gdtl.line_no = ginp.line_no
)
WHEN MATCHED THEN
UPDATE SET
gdtl.PERCENT_ALLOCATION = NVL(x_alloc_percent,0)
, gdtl.ALLOCATED_EXPENSE_AMT = NVL(x_allocated_amount,0)
, gdtl.AC_STATUS = 0
, gdtl.LAST_UPDATE_LOGIN = P_login_id
, gdtl.LAST_UPDATE_DATE = SYSDATE
, gdtl.LAST_UPDATED_BY = P_created_by
, gdtl.TRANS_CNT = 0
, gdtl.DELETE_MARK = 0
, gdtl.TEXT_CODE = NULL
, gdtl.REQUEST_ID = P_request_id
, gdtl.PROGRAM_APPLICATION_ID = P_prog_application_id
, gdtl.PROGRAM_ID = P_program_id
, gdtl.PROGRAM_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN
INSERT
(
PERIOD_ID,
COST_TYPE_ID,
ALLOC_ID,
LINE_NO,
ALLOCDTL_ID,
PERCENT_ALLOCATION,
ALLOCATED_EXPENSE_AMT,
AC_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
TRANS_CNT,
DELETE_MARK,
TEXT_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
g_period_id,
g_cost_type_id,
cur_alocinp_tmp.alloc_id,
cur_alocinp_tmp.line_no,
gem5_allocdtl_id_s.nextval,
NVL(x_alloc_percent,0),
NVL(x_allocated_amount,0),
0,
SYSDATE,
P_created_by,
P_login_id,
SYSDATE,
P_created_by,
0,
0,
NULL,
P_request_id,
P_prog_application_id,
P_program_id,
SYSDATE
)
;
/* Bug 7458002 - To delete obsoleted rows in gl_aloc_dtl (Start) */
IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.delete_allocations','deleting Allocations ...');
delete_allocations(v_from_alloc_code, v_to_alloc_code, x_status);