[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gps.Ledger_Id Ledger_Id,
gps.Period_Name Period_Name
FROM gl_Period_Statuses gps
WHERE gps.Ledger_Id = p_Ledger_Id
AND gps.cLosing_Status IN ('C','P')
AND gps.Adjustment_Period_Flag = 'N'
AND gps.Application_Id = '101'
AND gps.Start_Date >= (SELECT Start_Date
FROM gl_Period_Statuses
WHERE Ledger_Id = p_Ledger_Id
AND Application_Id = '101'
AND Period_Name = p_Start_Period)
AND (gps.End_Date <= (SELECT End_Date
FROM gl_Period_Statuses
WHERE Ledger_Id = p_Ledger_Id
AND Application_Id = '101'
AND Period_Name = p_End_Period)
OR p_End_Period is null)
AND EXISTS (SELECT 1
FROM Pay_All_Payrolls_f pp
WHERE pp.gl_Set_Of_Books_Id IS NOT NULL
AND gps.Ledger_Id = pp.gl_Set_Of_Books_Id)
ORDER BY gps.Start_Date;
SELECT 1
INTO lv_temp
FROM Dual
WHERE EXISTS (SELECT 1
FROM Pay_Patch_Status pps
WHERE Process_Type = gv_process_name
AND pps.Patch_Number = a.Ledger_Id
AND pps.Patch_Name = a.Period_Name
AND Status = 'U');
INSERT INTO Pay_Patch_Status
(Id,
Patch_Number,
Patch_Name,
Process_Type,
Status,
Phase,
update_date)
VALUES (Pay_Patch_Status_s.Nextval,
a.Ledger_Id,
a.Period_Name,
gv_process_name,
NULL,
gv_process_name||fnd_Global.Conc_Request_Id,
sysdate );
INSERT INTO Pay_Patch_Status
(Id,
Patch_Number,
Patch_Name,
Process_Type,
Status,
Phase,
Description,
update_date)
VALUES (Pay_Patch_Status_s.Nextval,
a.Ledger_Id,
a.Period_Name,
gv_process_name,
'U',
gv_process_name||fnd_Global.Conc_Request_Id,
'Period already Upgraded',
sysdate);
PROCEDURE Update_Proc_PAY_MGR (
X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
p_ledger_id in varchar2,
p_start_period in varchar2,
p_end_period in varchar2,
p_debug_flag in varchar2,
X_batch_size in number default 1,
X_Num_Workers in number default 5,
X_Argument4 in varchar2 default null,
X_Argument5 in varchar2 default null,
X_Argument6 in varchar2 default null,
X_Argument7 in varchar2 default null,
X_Argument8 in varchar2 default null,
X_Argument9 in varchar2 default null,
X_Argument10 in varchar2 default null)
IS
l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Proc_MGR';
l_api_name CONSTANT VARCHAR2(30) := 'Update_Proc_PAY_MGR';
select row_number() over(partition by phase order by id) Nu,patch_number Ledger_id,patch_name Period_name,decode(Status,'U','Upgraded','E','Errored',null) Status,Description description
from pay_patch_status
where PROCESS_TYPE='PAYSLAUPG'
and phase='PAYSLAUPG'||to_char(p_req_id)
and patch_number=p_ledger_id
order by id;
END Update_Proc_PAY_MGR;
PROCEDURE Update_Proc_PAY_WKR (
X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
X_batch_size in number,
X_Worker_Id in number,
X_Num_Workers in number,
X_Argument4 in varchar2 default null,
X_Argument5 in varchar2 default null,
X_Argument6 in varchar2 default null,
X_Argument7 in varchar2 default null,
X_Argument8 in varchar2 default null,
X_Argument9 in varchar2 default null,
X_Argument10 in varchar2 default null)
IS
l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Proc_WKR';
l_update_name varchar2(30);
update_subledger_exception exception;
l_update_name := 'PAYSLA' ||X_Argument7;
ad_parallel_updates_pkg.initialize_id_range(
X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
X_owner=>l_table_owner,
X_table=>l_table_name,
X_script=>l_update_name,
X_ID_column=>l_id_column,
X_worker_id=>X_Worker_Id,
X_num_workers=>X_num_workers,
X_batch_size=>X_batch_size,
X_debug_level=>0);
ad_parallel_updates_pkg.get_id_range(
l_start_id,
l_end_id,
l_any_rows_to_process,
X_batch_size,
TRUE);
fnd_file.put_line(fnd_file.log,'Before calling Update_Payroll_Subledger');
PAY_SLA_UPDATE_PKG.Update_Payroll_Subledger(
X_errbuf=>X_errbuf,
X_retcode=>X_retcode,
X_start_id=>l_start_id,
X_end_id=>l_end_id,
P_LEDGER_ID=>X_Argument4,
P_MGR_REQ_ID=>X_Argument7,
P_DEBUG_FLAG=>X_Argument8);
fnd_file.put_line(fnd_file.log,'After calling Update_Payroll_Subledger');
raise update_subledger_exception;
ad_parallel_updates_pkg.processed_id_range(
l_rows_processed,
l_end_id);
ad_parallel_updates_pkg.get_id_range(
l_start_id,
l_end_id,
l_any_rows_to_process,
X_batch_size,
FALSE);
WHEN update_subledger_exception THEN
ROLLBACK;
END Update_Proc_PAY_WKR;
select xla_transaction_entities_s.nextval,
xla_events_s.nextval,
xla_ae_headers_s.nextval
into g_xla_tran_s,g_xla_event_s,g_xla_headers_s
from dual;
select full_name
from per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions paa
where paa.assignment_id=paf.assignment_id
and paf.person_id=ppf.person_id
and paa.assignment_action_id=p_assnmnt_act_id
and p_eff_date between ppf.effective_start_date and ppf.effective_end_date;
PROCEDURE Update_Payroll_Subledger (
x_errbuf out nocopy varchar2,
x_retcode out nocopy varchar2,
x_start_id in number,
x_end_id in number,
p_ledger_id in varchar2,
p_mgr_req_id in varchar2,
p_debug_flag in varchar2
)
IS
l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Payroll_Subledger';
select patch_number ledger_id,patch_name period_name
from pay_patch_status
where id between p_start_id and p_end_id
and process_type=p_process_name
and phase = p_process_name||p_mgr_req_id
and patch_number=p_ledger_id
and status is null;
select je_header_id
from gl_je_headers gjh
where gjh.period_name=p_period_name
and gjh.je_category=p_app_name
and gjh.ledger_id=p_ledger_id
and gjh.je_source=p_app_name
order by je_header_id;
update gl_je_headers
set je_from_sla_flag='Y'
where je_header_id=l.je_header_id;
update gl_je_lines
set gl_sl_link_id=XLA_GL_SL_LINK_ID_S.nextval ,
gl_sl_link_table='XLAJEL'
where je_header_id =l.je_header_id;
update gl_import_references gir
set gir.gl_sl_link_id = (select gl_sl_link_id from gl_je_lines gjl1
where gir.je_header_id = gjl1.je_header_id
and gir.je_line_num=gjl1.je_line_num),
gir.gl_sl_link_table ='XLAJEL'
where gir.je_header_id = l.je_header_id
and gir.je_line_num = (select je_line_num from gl_je_lines gjl
where gir.je_header_id = gjl.je_header_id
and gir.je_line_num=gjl.je_line_num);
fnd_file.put_line(fnd_file.log,'Before Inserting into XLA Tables ');
INSERT ALL
WHEN (rank_id=1) then
INTO xla_transaction_entities (
upg_batch_id,
entity_id,
application_id,
ledger_id,
entity_code,
source_id_int_1,
source_id_char_1,
transaction_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
source_application_id,
upg_source_application_id)
VALUES (l_upg_batch_id,
xla_transaction_seq,
lv_application_id,
ledger_id,
'ASSIGNMENTS',
TGL_ASSIGNMENT_ACTION_ID,
To_char(EFFECTIVE_DATE,'YYYY/MM/DD'),
TGL_ASSIGNMENT_ACTION_ID,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
lv_application_id,
lv_application_id)
INTO xla_events (
upg_batch_id,
application_id,
entity_id,
event_id,
event_number,
event_type_code,
event_date,
event_status_code,
process_status_code,
on_hold_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
transaction_date,
upg_source_application_id)
VALUES (l_upg_batch_id,
lv_application_id,
xla_transaction_seq,
xla_events_seq,
1,
EVENT_TYPE_CODE,
EFFECTIVE_DATE,
'P',
'P',
'N',
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
EFFECTIVE_DATE,
lv_application_id)
INTO xla_ae_headers (
upg_batch_id,
application_id,
amb_context_code,
entity_id,
event_id,
event_type_code,
ae_header_id,
ledger_id,
je_category_name,
accounting_date,
period_name,
balance_type_code,
gl_transfer_status_code,
gl_transfer_date,
accounting_entry_status_code,
accounting_entry_type_code,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
zero_amount_flag,
accrual_reversal_flag,
upg_source_application_id)
VALUES (l_upg_batch_id,
lv_application_id,
'DEFAULT',
xla_transaction_seq,
xla_events_seq,
EVENT_TYPE_CODE,
xla_ae_headers_seq,
ledger_id,
lv_application_name,
EFFECTIVE_DATE,
period_name,
'A',
'Y',
effective_date,
'F',
'STANDARD',
header_desc,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
'N',
'N',
lv_application_id)
INTO pay_xla_events(
event_id,
assignment_action_id,
accounting_date,
event_status)
VALUES(xla_events_seq,
TGL_ASSIGNMENT_ACTION_ID,
EFFECTIVE_DATE,
'P')
WHEN (1=1) then
INTO xla_ae_lines (
upg_batch_id,
application_id,
ae_header_id,
ae_line_num,
code_combination_id,
gl_transfer_mode_code,
description,
accounted_dr,
accounted_cr,
currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
entered_dr,
entered_cr,
accounting_class_code,
gl_sl_link_id,
gl_sl_link_table,
gain_or_loss_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
accounting_date,
ledger_id,
mpa_accrual_entry_flag)
VALUES (l_upg_batch_id,
lv_application_id,
xla_ae_headers_seq,
rank_id,
code_combination_id,
'S',
line_desc,
accounted_dr,
accounted_cr,
currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
entered_dr,
entered_cr,
'COST',
link_id,
'XLAJEL',
'N',
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
EFFECTIVE_DATE,
ledger_id,
'N')
INTO xla_distribution_links (
upg_batch_id,
application_id,
event_id,
ae_header_id,
ae_line_num,
source_distribution_type,
source_distribution_id_num_1,
merge_duplicate_code,
ref_ae_header_id,
temp_line_num,
event_class_code,
event_type_code)
VALUES (l_upg_batch_id,
lv_application_id,
xla_events_seq,
xla_ae_headers_seq,
rank_id,
action_type,
TGL_ASSIGNMENT_ACTION_ID,
'N',
xla_ae_headers_seq,
rank_id,
EVENT_CLASS_CODE,
DIS_EVENT_TYPE_CODE)
SELECT row_number() over(partition by tgl_assignment_action_id order by debit_or_credit) RANK_ID,
get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_transaction_entities') xla_transaction_seq,
get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_events') xla_events_seq,
get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_ae_headers') xla_ae_headers_seq,
ledger_id,
period_name,
je_header_id,
currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
effective_date,
tgl_assignment_action_id,
code_combination_id,
costing_assignment_action_id,
link_id,
cost_allocation_keyflex_id,
element_name,
debit_or_credit,
entered_dr,
entered_cr,
(entered_dr*currency_conversion_rate) accounted_dr,
(entered_cr*currency_conversion_rate) accounted_cr,
decode(action_type,'C','COST','CP','PAYMENT_COST','S','RETRO_COST') event_type_code,
decode(action_type,'C','COSTS_ALL','CP','PAYMENT_COSTS_ALL','COSTS_ALL') dis_event_type_code,
decode(action_type,'C','COSTS','CP','PAYMENT_COSTS','COSTS') event_class_code,
decode(action_type,'CP','Payment Cost for '||get_full_name(tgl_assignment_action_id,effective_date)||' on '||effective_date) header_desc,
decode(action_type,'C',debit_or_credit ||' Cost for '||Element_name,'CP',debit_or_credit||' payment cost') line_desc,
action_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
From
(SELECT distinct gjh.ledger_id LEDGER_ID,
gjh.period_name PERIOD_NAME,
gjh.je_header_id JE_HEADER_ID,
gjh.currency_code,
gjh.currency_conversion_date,
gjh.currency_conversion_rate,
gjh.currency_conversion_type,
gjl.effective_date EFFECTIVE_DATE,
pa.assignment_action_id TGL_ASSIGNMENT_ACTION_ID,
gjl.code_combination_id CODE_COMBINATION_ID,
gjl.gl_sl_link_id LINK_ID,
gjl.creation_date,
gjl.created_by,
gjl.last_update_date,
gjl.last_updated_by,
gjl.last_update_login,
pcv.assignment_action_id COSTING_ASSIGNMENT_ACTION_ID,
pcv.cost_allocation_keyflex_id,
pcv.element_name,
pcv.debit_or_credit,
decode(pcv.debit_or_credit,'Debit',pcv.costed_value,null) entered_dr,
decode(pcv.debit_or_credit,'Credit',pcv.costed_value,null) entered_cr ,
ppa1.action_type
FROM pay_payroll_actions ppa1, -- Cost pay actions
pay_assignment_actions pa1, -- Cost asg actions.
pay_action_interlocks pi3, -- Cost - Run
pay_action_interlocks pi1, -- Cost - Trans GL
pay_all_payrolls_f pp,
pay_action_classifications pac,
pay_payroll_actions ppa2, -- Payroll run actions.
pay_assignment_actions pa2, -- Payroll run asg actions.
pay_action_interlocks pi2, -- Run - Trans GL
pay_assignment_actions pa, -- Trans GL asg actions
pay_payroll_actions ppa, -- Trans GL pay actions
pay_costs_v pcv,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ppa.payroll_action_id = to_number(gjl.reference_1)
AND pa.payroll_action_id = ppa.payroll_action_id
AND pa.action_status = 'C'
AND ppa2.payroll_action_id = to_number(gjl.reference_5)
AND pcv.cost_allocation_keyflex_id=to_number(gjl.reference_2)
AND pi2.locking_action_id = pa.assignment_action_id
AND pa2.assignment_action_id = pi2.locked_action_id
AND ppa2.payroll_action_id = pa2.payroll_action_id
AND ppa2.consolidation_set_id +0 = ppa.consolidation_set_id
AND pac.action_type = ppa2.action_type
AND pac.classification_name = 'COSTED'
AND pp.payroll_id = ppa2.payroll_id
AND pi1.locking_action_id = pa.assignment_action_id
AND pa1.assignment_action_id = pi1.locked_action_id
AND pa1.assignment_action_id <> pa2.assignment_action_id
AND pi3.locking_action_id = pa1.assignment_action_id
AND pa2.assignment_action_id = pi3.locked_action_id
AND ppa1.payroll_action_id = pa1.payroll_action_id
AND ppa1.action_type in ('C','S')
AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
AND pcv.assignment_action_id= pa1.assignment_action_id
AND gjl.je_header_id=gjh.je_header_id
AND decode(gjl.entered_cr,0,'Debit','Credit')=pcv.debit_or_credit
AND gjh.period_name=j.period_name
AND gjh.je_category=lv_application_name
AND gjh.ledger_id= j.ledger_id
AND gjh.je_source=lv_application_name
UNION
SELECT distinct gjh.ledger_id LEDGER_ID,
gjh.period_name PERIOD_NAME,
gjh.je_header_id JE_HEADER_ID,
gjh.currency_code,
gjh.currency_conversion_date,
gjh.currency_conversion_rate,
gjh.currency_conversion_type,
gjl.effective_date EFFECTIVE_DATE,
pa.assignment_action_id TGL_ASSIGNMENT_ACTION_ID,
gjl.code_combination_id CODE_COMBINATION_ID,
gjl.gl_sl_link_id LINK_ID,
gjl.creation_date,
gjl.created_by,
gjl.last_update_date,
gjl.last_updated_by,
gjl.last_update_login,
ppc.assignment_action_id COSTING_ASSIGNMENT_ACTION_ID,
ppc.gl_account_ccid,
ppc.payment_method_name,
ppc.debit_or_credit,
decode(ppc.debit_or_credit,'Debit',to_number(ppc.costed_value),null) entered_dr,
decode(ppc.debit_or_credit,'Credit',to_number(ppc.costed_value),null) entered_cr,
ppa1.action_type
FROM pay_payroll_actions ppa, -- Trans GL pay actions
pay_assignment_actions pa, -- Trans GL asg actions
pay_action_interlocks pi1, -- Cost - Trans GL
pay_assignment_actions pa1, -- Cost asg actions
pay_payroll_actions ppa1, -- Cost pay actions
per_all_assignments_f pera,
pay_all_payrolls_f pp,
pay_payment_costs_v ppc,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ppa.payroll_action_id = to_number(gjl.reference_1)
AND pa.payroll_action_id = ppa.payroll_action_id
AND pi1.locking_action_id = pa.assignment_action_id
AND pa1.assignment_action_id = pi1.locked_action_id
AND ppa1.payroll_action_id = pa1.payroll_action_id
AND ppa1.action_type = 'CP'
AND pera.assignment_id = pa.assignment_id
AND ppa1.effective_date BETWEEN pera.effective_start_date AND pera.effective_end_date
AND pp.payroll_id = pera.payroll_id
AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
AND ppc.gl_account_ccid =to_number(gjl.reference_2)
AND ppc.assignment_action_id= pa1.assignment_action_id
AND gjl.je_header_id=gjh.je_header_id
AND decode(gjl.entered_cr,0,'Debit','Credit')=ppc.debit_or_credit
AND gjh.period_name=j.period_name
AND gjh.je_category=lv_application_name
AND gjh.ledger_id= j.ledger_id
AND gjh.je_source=lv_application_name) A;
fnd_file.put_line(fnd_file.log,'After Inserting into XLA Tables ');
update pay_patch_status
set status='U',
description='Period Successfully Upgraded'
where process_type=gv_process_name
and phase =gv_process_name||P_MGR_REQ_ID
and patch_number=j.ledger_id
and patch_name =j.period_name;
update pay_patch_status
set status='E',
description='Error '||lv_Error_msg
where process_type=gv_process_name
and phase =gv_process_name||P_MGR_REQ_ID
and patch_number=j.ledger_id
and patch_name =j.period_name;
fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||j.period_name||' '||lv_Error_msg);
fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||SQLCODE||' '||SQLERRM);
end Update_Payroll_Subledger;