[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select all1.org_id
from pa_implementations_all all1
where all1.set_of_books_id = ( select s1.set_of_books_id
from pa_implementations s1);
SELECT
gl1.end_date,
sob1.chart_of_accounts_id,
imp1.set_of_books_id
INTO
G_p_gl_end_date ,
G_coa_id ,
G_sob
FROM gl_period_statuses gl1,
pa_implementations_all imp1,
gl_sets_of_books sob1
WHERE
imp1.org_id = p_org_id
AND imp1.set_of_books_id = gl1.set_of_books_id
AND gl1.application_id = 101
and imp1.set_of_books_id = sob1.set_of_books_id
and gl1.adjustment_period_flag = 'N'
AND ( ( G_p_gl_period_name is not null
and G_p_gl_period_name = gl1.period_name )
OR( G_p_gl_period_name is null
and trunc(sysdate) between gl1.start_date and gl1.end_date ));
select
ubr_uer_summary_id,
project_id,
cost_center_segment,
Account_segment,
gl_period_start_date,
process_flag,
delta_ubr,
delta_uer
from pa_ubr_uer_summ_acct
where request_id = G_p_request_id
and process_flag in ('I','U');
select sel1.project_id,
sel1.gl_period_start_date,
decode( sum( decode(UBR_UER_CODE,
'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
(UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
(UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
0, decode(zero_balance_flag,'N','Y','X'),
decode(zero_balance_flag,'Y','N','X')) zero_bal_flag
from pa_ubr_uer_summ_acct sel1
where sel1.project_id in
( select distinct temp1.project_id
from pa_draft_rev_inv_temp temp1 )
group by
sel1.project_id,
sel1.gl_period_start_date ,
sel1.zero_balance_flag
having
decode( sum( decode(UBR_UER_CODE,
'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
(UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
(UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
0, decode(zero_balance_flag,'N','Y','X'),
decode(zero_balance_flag,'Y','N','X')) <> 'X' ;
select dr.project_id, dr.draft_revenue_num,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
UNBILLED_CODE_COMBINATION_ID ) ubr_acct_seg,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'COST_CENTER',
UNBILLED_CODE_COMBINATION_ID ) ubr_cost_seg,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
UNEARNED_CODE_COMBINATION_ID ) uer_acct_seg,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'COST_CENTER',
UNEARNED_CODE_COMBINATION_ID ) uer_cost_seg,
get_gl_start_date(
101,
G_sob,
get_gl_period_name(
101,
G_sob,
dr.gl_date)) gl_period_start_date,
get_gl_period_name(
101,
G_sob,
dr.gl_date) gl_period_name ,
dr.unbilled_receivable_dr ubr_amount,
dr.unearned_revenue_cr uer_amount,
'U' ins_upd_flag
from pa_draft_revenues_all dr, pa_projects_all pa
where pa.org_id = G_org_id
and dr.project_id = pa.project_id
and dr.transfer_status_code = 'A'
and dr.gl_date <= G_p_gl_end_date
and dr.ubr_uer_process_flag = 'N'
and get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
dr.unbilled_code_combination_id ) is not null
and get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
dr.unearned_code_combination_id ) is not null
and (
( ( G_p_from_project_number is not null
and G_p_to_project_number is not null )
and pa.segment1 between G_p_from_project_number
and G_p_to_project_number)
OR
( ( G_p_from_project_number is not null
and G_p_to_project_number is null )
and pa.segment1 >= G_p_from_project_number )
OR
( ( G_p_from_project_number is null
and G_p_to_project_number is not null )
and pa.segment1 <= G_p_from_project_number )
OR
( G_p_from_project_number is null
and G_p_to_project_number is null )
);
l_project_id_arr.delete;
l_draft_rev_num_arr.delete;
l_ubr_acct_seg_arr.delete;
l_ubr_cost_seg_arr.delete;
l_uer_acct_seg_arr.delete;
l_uer_cost_seg_arr.delete;
l_gl_period_st_dt_arr.delete;
l_gl_period_name_arr.delete;
UPDATE pa_draft_revenues
SET ubr_uer_process_flag = 'S',
request_id = G_p_request_id
WHERE project_id = l_project_id_arr(j)
and draft_revenue_num = l_draft_rev_num_arr(j);
INSERT INTO pa_draft_rev_inv_temp
(
project_id ,
draft_rev_inv_num,
ubr_account_segment,
ubr_cost_center_segment,
uer_account_segment,
uer_cost_center_segment,
gl_period_start_date ,
gl_period_name ,
insert_update_flag ,
unbilled_receivable_dr ,
unearned_revenue_cr
)
VALUES
(
l_project_id_arr(j),
l_draft_rev_num_arr(j),
l_ubr_acct_seg_arr(j),
l_ubr_cost_seg_arr(j),
l_uer_acct_seg_arr(j),
l_uer_cost_seg_arr(j),
l_gl_period_st_dt_arr(j),
l_gl_period_name_arr(j),
l_ins_upd_flag_arr(j),
l_ubr_amount_arr(j),
l_uer_amount_arr(j)
);
l_sum_summary_id_arr.delete;
l_sum_project_id_arr.delete;
l_sum_cost_seg_arr.delete;
l_sum_acct_seg_arr.delete;
l_sum_gl_st_dt_arr.delete;
l_sum_proc_flag_arr.delete;
l_sum_ubr_arr.delete;
l_sum_uer_arr.delete;
UPDATE pa_ubr_uer_summ_acct
SET
UBR_BAL_PREV_PERIOD_DR =
nvl(UBR_BAL_PREV_PERIOD_DR,0) + l_sum_ubr_arr(J),
UER_BAL_PREV_PERIOD_CR =
nvl(UER_BAL_PREV_PERIOD_CR,0) + l_sum_uer_arr(J),
request_id = G_p_request_id
WHERE project_id = l_sum_project_id_arr(J)
AND cost_center_segment = l_sum_cost_seg_arr(J)
AND Account_segment = l_sum_acct_seg_arr(J)
AND gl_period_start_date > l_sum_gl_st_dt_arr(J);
UPDATE pa_ubr_uer_summ_acct upd1
SET ( upd1.UBR_BAL_PREV_PERIOD_DR, upd1.UER_BAL_PREV_PERIOD_CR ) =
( select nvl(sum(sel1.UNBILLED_RECEIVABLE_DR),0),
nvl(sum(sel1.UNEARNED_REVENUE_CR),0)
from pa_ubr_uer_summ_acct sel1
where sel1.project_id = upd1.project_id
and sel1.account_segment = upd1.account_segment
and sel1.cost_center_segment = upd1.cost_center_segment
and sel1.gl_period_start_date < upd1.gl_period_start_date )
WHERE ubr_uer_summary_id = l_sum_summary_id_arr(J)
AND l_sum_proc_flag_arr(J) = 'I' ;
UPDATE pa_ubr_uer_summ_acct upd1
SET process_flag = 'P',
delta_ubr = 0,
delta_uer = 0
WHERE project_id = l_sum_project_id_arr(J)
AND cost_center_segment = l_sum_cost_seg_arr(J)
AND Account_segment = l_sum_acct_seg_arr(J)
AND gl_period_start_date = l_sum_gl_st_dt_arr(J);
UPDATE pa_draft_revenues_all dr1
SET ( dr1.request_id,dr1.ubr_uer_process_flag , dr1.ubr_summary_id ,dr1.uer_summary_id )
= ( select G_p_request_id,'Y',temp1.ubr_summary_id, temp1.uer_summary_id
from pa_draft_rev_inv_temp temp1
where temp1.project_id = dr1.project_id
and temp1.draft_rev_inv_num = dr1.draft_revenue_num )
WHERE dr1.project_id = l_project_id_arr(j)
and dr1.draft_revenue_num = l_draft_rev_num_arr(j);
l_zer_project_id_arr.delete;
l_zer_gl_st_dt_arr.delete;
l_zer_flag_arr.delete;
UPDATE pa_ubr_uer_summ_acct
SET zero_balance_flag = l_zer_flag_arr(J)
WHERE project_id = l_zer_project_id_arr(J)
and gl_period_start_date = l_zer_gl_st_dt_arr(J)
and l_zer_flag_arr(J) <> 'X';
select sel1.project_id,
sel1.gl_period_start_date,
decode( sum( decode(UBR_UER_CODE,
'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
(UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
(UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
0, decode(zero_balance_flag,'N','Y','X'),
decode(zero_balance_flag,'Y','N','X')) zero_bal_flag
from pa_ubr_uer_summ_acct sel1
where sel1.project_id in
( select distinct temp1.project_id
from pa_draft_rev_inv_temp temp1 )
group by
sel1.project_id,
sel1.gl_period_start_date ,
sel1.zero_balance_flag
having
decode( sum( decode(UBR_UER_CODE,
'UBR',UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR,
'UER',UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR,
(UBR_BAL_PREV_PERIOD_DR+UNBILLED_RECEIVABLE_DR) -
(UER_BAL_PREV_PERIOD_CR + UNEARNED_REVENUE_CR) )),
0, decode(zero_balance_flag,'N','Y','X'),
decode(zero_balance_flag,'Y','N','X')) <> 'X' ;
select
ubr_uer_summary_id,
project_id,
cost_center_segment,
Account_segment,
gl_period_start_date,
process_flag,
delta_ubr,
delta_uer
from pa_ubr_uer_summ_acct
where request_id = G_p_request_id
and process_flag in ('I','U');
select di.project_id, di.draft_invoice_num,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
UNBILLED_CODE_COMBINATION_ID ) ubr_acct_seg,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'COST_CENTER',
UNBILLED_CODE_COMBINATION_ID ) ubr_cost_seg,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
UNEARNED_CODE_COMBINATION_ID ) uer_acct_seg,
get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'COST_CENTER',
UNEARNED_CODE_COMBINATION_ID ) uer_cost_seg,
get_gl_start_date(
101,
G_sob,
get_gl_period_name(
101,
G_sob,
di.gl_date)) gl_period_start_date,
get_gl_period_name(
101,
G_sob,
di.gl_date) gl_period_name ,
di.unbilled_receivable_dr ubr_amount,
di.unearned_revenue_cr uer_amount,
'U' ins_upd_flag
from pa_draft_invoices_all di, pa_projects_all pa
where pa.org_id = G_org_id
and di.project_id = pa.project_id
and di.transfer_status_code = 'A'
and di.gl_date <= G_p_gl_end_date
and get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
di.unbilled_code_combination_id ) is not null
and get_seg_val(
G_acct_appcol_name,
G_cost_appcol_name,
'ACCOUNT',
di.unearned_code_combination_id ) is not null
and di.ubr_uer_process_flag = 'N'
and (
( ( G_p_from_project_number is not null
and G_p_to_project_number is not null )
and pa.segment1 between G_p_from_project_number
and G_p_to_project_number)
OR
( ( G_p_from_project_number is not null
and G_p_to_project_number is null )
and pa.segment1 >= G_p_from_project_number )
OR
( ( G_p_from_project_number is null
and G_p_to_project_number is not null )
and pa.segment1 <= G_p_from_project_number )
OR
( G_p_from_project_number is null
and G_p_to_project_number is null )
);
l_project_id_arr.delete;
l_draft_inv_num_arr.delete;
l_ubr_acct_seg_arr.delete;
l_ubr_cost_seg_arr.delete;
l_uer_acct_seg_arr.delete;
l_uer_cost_seg_arr.delete;
l_gl_period_st_dt_arr.delete;
l_gl_period_name_arr.delete;
INSERT INTO pa_draft_rev_inv_temp
(
project_id ,
draft_rev_inv_num,
ubr_account_segment,
ubr_cost_center_segment,
uer_account_segment,
uer_cost_center_segment,
gl_period_start_date ,
gl_period_name ,
insert_update_flag ,
unbilled_receivable_dr ,
unearned_revenue_cr
)
VALUES
(
l_project_id_arr(j),
l_draft_inv_num_arr(j),
l_ubr_acct_seg_arr(j),
l_ubr_cost_seg_arr(j),
l_uer_acct_seg_arr(j),
l_uer_cost_seg_arr(j),
l_gl_period_st_dt_arr(j),
l_gl_period_name_arr(j),
l_ins_upd_flag_arr(j),
l_ubr_amount_arr(j),
l_uer_amount_arr(j)
);
l_sum_project_id_arr.delete;
l_sum_cost_seg_arr.delete;
l_sum_acct_seg_arr.delete;
l_sum_gl_st_dt_arr.delete;
l_sum_proc_flag_arr.delete;
l_sum_ubr_arr.delete;
l_sum_uer_arr.delete;
UPDATE pa_ubr_uer_summ_acct
SET
UBR_BAL_PREV_PERIOD_DR =
nvl(UBR_BAL_PREV_PERIOD_DR,0) + l_sum_ubr_arr(J),
UER_BAL_PREV_PERIOD_CR =
nvl(UER_BAL_PREV_PERIOD_CR,0) + l_sum_uer_arr(J)
WHERE project_id = l_sum_project_id_arr(J)
AND cost_center_segment = l_sum_cost_seg_arr(J)
AND Account_segment = l_sum_acct_seg_arr(J)
AND gl_period_start_date > l_sum_gl_st_dt_arr(J);
UPDATE pa_ubr_uer_summ_acct upd1
SET ( upd1.UBR_BAL_PREV_PERIOD_DR, upd1.UER_BAL_PREV_PERIOD_CR ) =
( select nvl(sum(sel1.UNBILLED_RECEIVABLE_DR),0),nvl(sum(sel1.UNEARNED_REVENUE_CR),0)
from pa_ubr_uer_summ_acct sel1
where sel1.project_id = upd1.project_id
and sel1.account_segment = upd1.account_segment
and sel1.cost_center_segment = upd1.cost_center_segment
and sel1.gl_period_start_date < upd1.gl_period_start_date )
WHERE ubr_uer_summary_id = l_sum_summary_id_arr(J)
AND l_sum_proc_flag_arr(J) = 'I' ;
UPDATE pa_ubr_uer_summ_acct upd1
SET process_flag = 'P',
delta_ubr = 0,
delta_uer = 0
WHERE project_id = l_sum_project_id_arr(J)
AND cost_center_segment = l_sum_cost_seg_arr(J)
AND Account_segment = l_sum_acct_seg_arr(J)
AND gl_period_start_date = l_sum_gl_st_dt_arr(J);
UPDATE pa_draft_invoices_all di1
SET ( di1.request_id,di1.ubr_uer_process_flag , di1.ubr_summary_id ,di1.uer_summary_id )
= ( select G_p_request_id,'Y',temp1.ubr_summary_id, temp1.uer_summary_id
from pa_draft_rev_inv_temp temp1
where temp1.project_id = di1.project_id
and temp1.draft_rev_inv_num = di1.draft_invoice_num )
WHERE di1.project_id = l_project_id_arr(j)
and di1.draft_invoice_num = l_draft_inv_num_arr(j);
l_zer_project_id_arr.delete;
l_zer_gl_st_dt_arr.delete;
l_zer_flag_arr.delete;
UPDATE pa_ubr_uer_summ_acct
SET zero_balance_flag = l_zer_flag_arr(J)
WHERE project_id = l_zer_project_id_arr(J)
and gl_period_start_date = l_zer_gl_st_dt_arr(J)
and l_zer_flag_arr(J) <> 'X';
select
decode(p_process_ubr_uer,
'UBR',ubr_account_segment,
'UER',uer_account_segment,
'-1') acct_seg,
decode(p_process_ubr_uer,
'UBR',ubr_cost_center_segment,
'UER',uer_cost_center_segment,
'-1') cost_seg,
gl_period_name ,
gl_period_start_date,
p_process_ubr_uer,
project_id,
decode(p_process_ubr_uer,
'UBR',sum(unbilled_receivable_dr),
'UER',sum(unearned_revenue_cr),-1)
from pa_draft_rev_inv_temp
group by
decode(p_process_ubr_uer,
'UBR',ubr_account_segment,
'UER',uer_account_segment,
'-1') ,
decode(p_process_ubr_uer,
'UBR',ubr_cost_center_segment,
'UER',uer_cost_center_segment,
'-1') ,
gl_period_name ,
gl_period_start_date,
project_id;
l_acct_seg_arr.delete;
l_cost_seg_arr.delete;
l_gl_period_arr.delete;
l_sum_project_id_arr.delete;
l_sum_amt_arr.delete;
l_process_ubr_uer_arr.delete;
UPDATE pa_ubr_uer_summ_acct
SET
unbilled_receivable_dr =
decode(l_process_ubr_uer_arr(j),
'UBR',unbilled_receivable_dr + l_sum_amt_arr(j),
'UER',unbilled_receivable_dr ,
-1 ),
unearned_revenue_cr =
decode(l_process_ubr_uer_arr(j),
'UBR',unearned_revenue_cr ,
'UER',unearned_revenue_cr + l_sum_amt_arr(j),
-1 ),
delta_ubr =
decode(l_process_ubr_uer_arr(j),
'UBR',delta_ubr + l_sum_amt_arr(j),
'UER',delta_ubr ,
-1 ),
delta_uer =
decode(l_process_ubr_uer_arr(j),
'UBR',delta_uer ,
'UER',delta_uer + l_sum_amt_arr(j),
-1 ),
ubr_uer_code =
decode(l_process_ubr_uer_arr(j),
'UBR', decode(nvl(ubr_uer_code,'-1'),
'UBR','UBR',
'UER','UBR_UER',
'UBR_UER','UBR_UER',
'UBR'),
'UER', decode(nvl(ubr_uer_code,'-1'),
'UER','UER',
'UBR','UBR_UER',
'UBR_UER','UBR_UER',
'UER'),
'-1' ) ,
process_flag = decode(process_flag,'I','I','U'),
last_update_date = sysdate ,
last_updated_by = -1 ,
request_id = G_p_request_id
WHERE project_id = l_sum_project_id_arr(j)
AND Account_segment = l_acct_seg_arr(j)
AND cost_center_segment = l_cost_seg_arr(j)
AND gl_period_start_date = l_gl_period_start_date_arr(j)
RETURNING
project_id,
ubr_uer_summary_id,
Account_segment,
cost_center_segment,
gl_period_start_date
BULK COLLECT INTO
l_upd_project_id_arr ,
l_upd_summary_id_arr ,
l_upd_acct_seg_arr ,
l_upd_cost_seg_arr ,
l_upd_gl_per_stdt_arr ;
select pa_ubr_uer_summ_acct_s.nextval
into l_ins_summary_id_arr(ins_j)
from dual;
l_acct_seg_arr.delete;
l_cost_seg_arr.delete;
l_gl_period_arr.delete;
l_sum_project_id_arr.delete;
l_sum_amt_arr.delete;
l_process_ubr_uer_arr.delete;
INSERT INTO pa_ubr_uer_summ_acct
( ubr_uer_summary_id ,
Account_segment ,
cost_center_segment ,
project_id ,
gl_period_name ,
gl_period_start_date ,
ubr_uer_code ,
process_flag ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
request_id ,
zero_balance_flag ,
multi_cost_center_flag ,
ubr_bal_prev_period_dr ,
uer_bal_prev_period_cr ,
delta_ubr,
delta_uer ,
UNBILLED_RECEIVABLE_DR ,
UNEARNED_REVENUE_CR )
VALUES
( l_ins_summary_id_arr(j),
l_ins_acct_seg_arr(j),
l_ins_cost_seg_arr(j),
l_ins_sum_project_id_arr(j),
l_ins_gl_period_arr(j),
l_ins_gl_per_stdt_arr(j),
l_ins_process_ubr_uer_arr(j) ,
'I',
sysdate,
-1,
sysdate,
-1,
G_p_request_id,
'N',
'N',
0 ,
0 ,
decode(l_ins_process_ubr_uer_arr(j),
'UBR',l_ins_sum_amt_arr(J),
'UER', 0, 0 ),
decode(l_ins_process_ubr_uer_arr(j),
'UER',l_ins_sum_amt_arr(J),
'UBR', 0, 0 ),
decode(l_ins_process_ubr_uer_arr(j),
'UBR',l_ins_sum_amt_arr(J),
'UER', 0, 0 ),
decode(l_ins_process_ubr_uer_arr(j),
'UER',l_ins_sum_amt_arr(J),
'UBR', 0, 0 )
);
UPDATE pa_draft_rev_inv_temp
SET ubr_summary_id = l_upd_summary_id_arr(J)
where project_id = l_upd_project_id_arr(J)
AND ubr_cost_center_segment = l_upd_cost_seg_arr(J)
AND ubr_account_segment = l_upd_acct_seg_arr(J)
AND gl_period_start_date = l_upd_gl_per_stdt_arr(J);
UPDATE pa_draft_rev_inv_temp
SET uer_summary_id = l_upd_summary_id_arr(J)
where project_id = l_upd_project_id_arr(J)
AND uer_cost_center_segment = l_upd_cost_seg_arr(J)
AND uer_account_segment = l_upd_acct_seg_arr(J)
AND gl_period_start_date = l_upd_gl_per_stdt_arr(J);
UPDATE pa_draft_rev_inv_temp
SET insert_update_flag = l_ins_ins_upd_flag_arr(J),
ubr_summary_id = l_ins_summary_id_arr(J)
where project_id = l_ins_sum_project_id_arr(J)
AND ubr_cost_center_segment = l_ins_cost_seg_arr(J)
AND ubr_account_segment = l_ins_acct_seg_arr(J)
AND gl_period_start_date = l_ins_gl_per_stdt_arr(J);
UPDATE pa_draft_rev_inv_temp
SET insert_update_flag = l_ins_ins_upd_flag_arr(J),
uer_summary_id = l_ins_summary_id_arr(J)
where project_id = l_ins_sum_project_id_arr(J)
AND uer_cost_center_segment = l_ins_cost_seg_arr(J)
AND uer_account_segment = l_ins_acct_seg_arr(J)
AND gl_period_start_date = l_ins_gl_per_stdt_arr(J);
UPDATE pa_ubr_uer_summ_acct sum1
set multi_cost_center_flag = 'Y'
where project_id = l_ins_sum_project_id_arr(J)
and gl_period_name = l_ins_gl_period_arr(J)
and multi_cost_center_flag = 'N'
and EXISTS ( select 'x'
from pa_ubr_uer_summ_acct sum2
where sum2.project_id = sum1.project_id
and sum2.gl_period_name = sum1.gl_period_name
and sum2.cost_center_segment <> l_ins_cost_seg_arr(J) );
select decode(p_acct_appcol_name,
'SEGMENT1',segment1,
'SEGMENT2',segment2,
'SEGMENT3',segment3,
'SEGMENT4',segment4,
'SEGMENT5',segment5,
'SEGMENT6',segment6,
'SEGMENT7',segment7,
'SEGMENT8',segment8,
'SEGMENT9',segment9,
'SEGMENT10',segment10,
'SEGMENT11',segment11,
'SEGMENT12',segment12,
'SEGMENT13',segment13,
'SEGMENT14',segment14,
'SEGMENT15',segment15,
'SEGMENT16',segment16,
'SEGMENT17',segment17,
'SEGMENT18',segment18,
'SEGMENT19',segment19,
'SEGMENT20',segment20,
'SEGMENT21',segment21,
'SEGMENT22',segment22,
'SEGMENT23',segment23,
NULL),
decode(p_cost_appcol_name,
'SEGMENT1',segment1,
'SEGMENT2',segment2,
'SEGMENT3',segment3,
'SEGMENT4',segment4,
'SEGMENT5',segment5,
'SEGMENT6',segment6,
'SEGMENT7',segment7,
'SEGMENT8',segment8,
'SEGMENT9',segment9,
'SEGMENT10',segment10,
'SEGMENT11',segment11,
'SEGMENT12',segment12,
'SEGMENT13',segment13,
'SEGMENT14',segment14,
'SEGMENT15',segment15,
'SEGMENT16',segment16,
'SEGMENT17',segment17,
'SEGMENT18',segment18,
'SEGMENT19',segment19,
'SEGMENT20',segment20,
'SEGMENT21',segment21,
'SEGMENT22',segment22,
'SEGMENT23',segment23,
NULL),
code_combination_id
into
G_acct_seg_val,
G_cost_seg_val,
G_ccid
from gl_code_combinations
where code_combination_id = p_ccid ;
select period_name ,
start_date ,
end_date
into
G_gl_period_name,
G_gl_start_date,
G_gl_end_date
from gl_period_statuses
where p_gl_date between START_DATE and END_DATE
and adjustment_period_flag = 'N'
and application_id = p_application_id
and set_of_books_id = p_set_of_books_id;
select set_of_books_id
into G_set_of_books_id
from pa_implementations_all
where nvl(org_id,-1) = nvl(p_org_id,-1);
select period_name ,
start_date ,
end_date
into
G_gl_period_name,
G_gl_start_date,
G_gl_end_date
from gl_period_statuses
where p_gl_date between START_DATE and END_DATE
and adjustment_period_flag = 'N'
and application_id = 101 /* GL */
and set_of_books_id = G_set_of_books_id;
select period_name ,
start_date ,
end_date
into
G_gl_period_name,
G_gl_start_date,
G_gl_end_date
from gl_period_statuses
where period_name = p_gl_period_name
and application_id = p_application_id
and set_of_books_id = p_set_of_books_id;
select
to_char(gl1.start_date,'DD-MON-RR')
into
l_gl_start_date
from gl_period_statuses gl1 ,
pa_implementations imp1
where gl1.period_name = p_gl_period_name
and gl1.application_id = 101
and gl1.set_of_books_id = imp1.set_of_books_id;
select to_char(p_gl_start_date,'DD-MON-RR') into l_gl_start_date
from dual;
and current value then the select will fire else it will use the
old values */
IF ( ( G_p_invoice_num is null )
OR ( G_p_invoice_num <> p_ar_invoice_number )
OR ( G_p_ubr_code_combination_id is null )
OR ( G_p_ubr_code_combination_id <> p_ubr_code_combination_id )
OR (G_p_invoice_line_num is null )
OR ( G_p_invoice_line_num <> p_invoice_line_number )
OR (G_p_period_name is null )
OR ( G_p_period_name <> p_period_name )
) THEN
G_p_invoice_num := p_ar_invoice_number;
SELECT je.je_header_id,
je.je_line_num,
jh.name ,
jb.name
INTO
G_x_inv_gl_header_id,
G_x_inv_gl_line_num,
G_x_inv_gl_header_name,
G_x_inv_gl_batch_name
FROM gl_je_lines je,ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctlgda ,
gl_je_headers jh,
gl_je_batches jb
WHERE je.reference_2 = TO_CHAR(rctlgda.customer_trx_id)
AND je.reference_3 = TO_CHAR(rctlgda.cust_trx_line_gl_dist_id)
AND je.code_combination_id = rctlgda.code_combination_id
AND je.period_name = p_period_name
AND rctlgda.customer_trx_line_id = rctla.customer_trx_line_id
AND rctlgda.code_combination_id = p_ubr_code_combination_id
AND rctla.customer_trx_id = p_ar_invoice_number
AND rctla.interface_line_attribute6 = p_invoice_line_number
AND je.je_header_id = jh.je_header_id
AND jh.je_batch_id = jb.je_batch_id(+);
and current value then the select will fire else it will use the
old values */
IF ( ( G_batch_name is null )
OR ( G_batch_name <> p_batch_name )
OR ( G_code_combination_id is null )
OR ( G_code_combination_id <> p_code_combination_id )
OR (G_system_ref_3 is null )
OR ( G_system_ref_3 <> p_system_ref_3 )
OR (G_rev_period_name is null )
OR ( G_rev_period_name <> p_period_name )
) THEN
G_batch_name := p_batch_name;
SELECT je.je_header_id,
je.je_line_num,
jh.name,
jb.name
INTO
G_x_rev_gl_header_id,
G_x_rev_gl_line_num,
G_x_rev_gl_header_name,
G_x_rev_gl_batch_name
FROM gl_je_lines je,
gl_je_headers jh,
gl_je_batches jb
WHERE je.reference_1 = p_batch_name
AND je.reference_3 = p_system_ref_3
AND je.code_combination_id = p_code_combination_id
AND je.period_name = p_period_name
AND je.je_header_id = jh.je_header_id
AND jh.je_batch_id = jb.je_batch_id(+);