QUERY (Total execution time 2.094 sec.):
delete cmpinv_job;
delete cmpinv_amount;
delete cmpinv_premium;
-- jobs
insert into cmpinv_job (invoice_id, job_id, offering_id, employerplanyear_id, offering_alt_id)
select distinct p2.invoice_id, policy.job_id, eo.offering_id, eo.employerplanyear_id, o.alternate_id
from cycowner.b_journal j
join cycowner.b_posting p1 on p1.journal_id = j.id
join cycowner.b_posting p2 on p2.journal_id = j.id
and p1.id<>p2.id
join cycowner.b_account acc_p1 on acc_p1.id = p1.account_id
join cycowner.b_account acc_p2 on acc_p2.id = p2.account_id
join cycowner.b_policyjournal pj on pj.journal_id = j.id
join cycowner.p_policy policy on policy.id = pj.policy_id
join cycowner.b_paysiteinvoice psi on psi.invoice_id = p2.invoice_id
join cycowner.m_employeroffering eo on eo.id = policy.employeroffering_id
join cycowner.m_offering o on o.id = eo.offering_id
where p2.invoice_id = 2181230
;
-- Payroll Contributions (amount type 1)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select p122.invoice_id, policy12.job_id, eo12.offering_id, sum(p12.amount), 1
from cycowner.b_journal j12
join cycowner.b_posting p12 on p12.journal_id = j12.id
join cycowner.b_posting p122 on p122.journal_id = j12.id
and p12.id<>p122.id
join cycowner.b_account acc_p12 on acc_p12.id = p12.account_id
join cycowner.b_account acc_p122 on acc_p122.id = p122.account_id
join cycowner.b_policyjournal pj12 on pj12.journal_id = j12.id
join cycowner.p_policy policy12 on policy12.id = pj12.policy_id
join cycowner.m_employeroffering eo12 on eo12.id = policy12.employeroffering_id
where p122.invoice_id = 2181230
and
(
acc_p12.accounttype_id in (1, 17)
or acc_p122.accounttype_id in (1, 17)
)
and j12.settle_status_id in (474, 476)
group by p122.invoice_id, policy12.job_id, eo12.offering_id
having sum(p12.amount) > 0
;
-- ERPayrollSubmitted (amount type 2)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select p122.invoice_id, policy12.job_id, eo12.offering_id, sum(cr.amount) as sum_amount, 2
from cycowner.b_journal j12
join cycowner.b_contributionjournal cj on cj.journal_id = j12.id
join cycowner.b_contributionrecord cr on cr.id = cj.contribution_id
join cycowner.b_posting p12 on p12.journal_id = j12.id
join cycowner.b_posting p122 on p122.journal_id = j12.id
and p12.id<>p122.id
join cycowner.b_account acc_p12 on acc_p12.id = p12.account_id
join cycowner.b_account acc_p122 on acc_p122.id = p122.account_id
join cycowner.b_policyjournal pj12 on pj12.journal_id = j12.id
join cycowner.p_policy policy12 on policy12.id = pj12.policy_id
join cycowner.m_employeroffering eo12 on eo12.id = policy12.employeroffering_id
where p122.invoice_id = 2181230
and
(
acc_p12.accounttype_id in (1,17)
or acc_p122.accounttype_id in (1,17)
)
and j12.settle_status_id in (474,476)
and eo12.billingsource_id = 122
group by p122.invoice_id, policy12.job_id, eo12.offering_id
;
-- EEPayrollSubmitted (amount type 3)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select p122.invoice_id, policy12.job_id, eo12.offering_id, sum(cr.amount) as sum_amount, 3
from cycowner.b_journal j12
join cycowner.b_contributionjournal cj on cj.journal_id = j12.id
join cycowner.b_contributionrecord cr on cr.id = cj.contribution_id
join cycowner.b_posting p12 on p12.journal_id = j12.id
join cycowner.b_posting p122 on p122.journal_id = j12.id
and p12.id<>p122.id
join cycowner.b_account acc_p12 on acc_p12.id = p12.account_id
join cycowner.b_account acc_p122 on acc_p122.id = p122.account_id
join cycowner.b_policyjournal pj12 on pj12.journal_id = j12.id
join cycowner.p_policy policy12 on policy12.id = pj12.policy_id
join cycowner.m_employeroffering eo12 on eo12.id = policy12.employeroffering_id
where p122.invoice_id = 2181230
and
(
acc_p12.accounttype_id in (1,17)
or acc_p122.accounttype_id in (1,17)
)
and j12.settle_status_id in (474,476)
and eo12.billingsource_id = 121
group by p122.invoice_id, policy12.job_id, eo12.offering_id
;
-- TotalPayrollProcessed1 (amount type 4)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select p122.invoice_id, policy12.job_id, eo12.offering_id, sum(p12.amount) as sum_amount, 4
from cycowner.b_journal j12
join cycowner.b_posting p12 on p12.journal_id = j12.id
join cycowner.b_posting p122 on p122.journal_id = j12.id
and p12.id<>p122.id
join cycowner.b_account acc_p12 on acc_p12.id = p12.account_id
join cycowner.b_account acc_p122 on acc_p122.id = p122.account_id
join cycowner.b_policyjournal pj12 on pj12.journal_id = j12.id
join cycowner.p_policy policy12 on policy12.id = pj12.policy_id
join cycowner.m_employeroffering eo12 on eo12.id = policy12.employeroffering_id
where p122.invoice_id = 2181230
and
(
acc_p12.accounttype_id in (1,17)
or acc_p122.accounttype_id in (1,17)
)
and j12.settle_status_id in (474,476)
group by p122.invoice_id, policy12.job_id, eo12.offering_id
;
-- TotalPayrollProcessed2 (amount type 5)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select inv2.id, policy2.job_id, eo2.offering_id, sum(p2.amount) as sum_amount, 5
from cycowner.b_posting p2
join cycowner.b_journal j2 on j2.id = p2.journal_id
join cycowner.b_savingsaccount sa2 on sa2.account_id = p2.account_id
join cycowner.b_invoice inv2 on inv2.id = j2.invoice_id
join cycowner.b_advancejournal aj2 on aj2.advance_journal_id = j2.id
join cycowner.b_journal parentj2 on parentj2.id = aj2.policy_journal_id
join cycowner.b_policyjournal pj2 on pj2.journal_id = parentj2.id
join cycowner.p_policy policy2 on policy2.id = pj2.policy_id
join cycowner.m_employeroffering eo2 on eo2.id = policy2.employeroffering_id
where inv2.id = 2181230
and j2.activity_id = 612
and
(
j2.hidden is null
or j2.hidden <> 1
)
and p2.amount <> 0
and j2.settle_status_id in(474,476)
group by inv2.id, policy2.job_id, eo2.offering_id
;
-- AppliedToHsaBalance (amount type 6)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select p122.invoice_id, policy12.job_id, eo12.offering_id, sum(p12.amount) as sum_amount, 6
from cycowner.b_journal j12
join cycowner.b_posting p12 on p12.journal_id = j12.id
join cycowner.b_posting p122 on p122.journal_id = j12.id and p12.id<>p122.id
join cycowner.b_account acc_p12 on acc_p12.id = p12.account_id
join cycowner.b_account acc_p122 on acc_p122.id = p122.account_id
join cycowner.b_policyjournal pj12 on pj12.journal_id = j12.id
join cycowner.p_policy policy12 on policy12.id = pj12.policy_id
join cycowner.m_employeroffering eo12 on eo12.id = policy12.employeroffering_id
where p122.invoice_id = 2181230
and
(
acc_p12.accounttype_id in (1,17)
or acc_p122.accounttype_id in (1,17)
)
and j12.settle_status_id in (474,476)
group by p122.invoice_id, policy12.job_id, eo12.offering_id
;
-- AppliedToHsaOdPayBack (amount type 7)
insert into cmpinv_amount (invoice_id, job_id, offering_id, sum_amount, amount_type)
select inv2.id, policy2.job_id, eo2.offering_id, sum(p2.amount) as sum_amount, 7
from cycowner.b_posting p2
join cycowner.b_journal j2 on j2.id = p2.journal_id
join cycowner.b_savingsaccount sa2 on sa2.account_id = p2.account_id
join cycowner.b_invoice inv2 on inv2.id = j2.invoice_id
join cycowner.b_advancejournal aj2 on aj2.advance_journal_id = j2.id
join cycowner.b_journal parentj2 on parentj2.id = aj2.policy_journal_id
join cycowner.b_policyjournal pj2 on pj2.journal_id = parentj2.id
join cycowner.p_policy policy2 on policy2.id = pj2.policy_id
join cycowner.m_employeroffering eo2 on eo2.id = policy2.employeroffering_id
where inv2.id = 2181230
and j2.activity_id = 612
and
(
j2.hidden is null
or j2.hidden <> 1
)
and p2.amount <> 0
and j2.settle_status_id in( 476,474)
group by inv2.id, policy2.job_id, eo2.offering_id
;
-- EmployerAnnualElection (prem type 1)
insert into cmpinv_premium (invoice_id, job_id, offering_id, employerplanyear_id, sum_prem, prem_type)
select 2181230
, pp.job_id, eo.offering_id, eo.employerplanyear_id, sum(premiums.premium) as sum_prem, 1
from cycowner.p_policy pp
join cycowner.m_employeroffering eo on eo.id = pp.employeroffering_id
join cmpinv_job j on j.job_id = pp.job_id
and j.offering_id = eo.offering_id
and j.employerplanyear_id = eo.employerplanyear_id
join cycowner.p_premium premiums on premiums.policy_id = pp.id
where 1=1
and eo.billingsource_id = 122
and premiums.tt_start <= current_timestamp
and premiums.tt_stop > current_timestamp
group by pp.job_id, eo.offering_id, eo.employerplanyear_id
;
-- EmployeeAnnualElection (prem type 2)
insert into cmpinv_premium (invoice_id, job_id, offering_id, employerplanyear_id, sum_prem, prem_type)
select 2181230
, pp.job_id, eo.offering_id, eo.employerplanyear_id, sum(premiums.premium) as sum_prem, 2
from cycowner.p_policy pp
join cycowner.m_employeroffering eo on eo.id = pp.employeroffering_id
join cmpinv_job j on j.job_id = pp.job_id
and j.offering_id = eo.offering_id
and j.employerplanyear_id = eo.employerplanyear_id
join cycowner.p_premium premiums on premiums.policy_id = pp.id
where 1=1
and eo.billingsource_id = 121
and premiums.tt_start <= current_timestamp
and premiums.tt_stop > current_timestamp
group by pp.job_id, eo.offering_id, eo.employerplanyear_id
;
select
er.name as EmployerName,
j.invoice_id as InvoiceNumber,
person.first_name firstname,
person.last_name lastname,
person.ssn,
('xxx-xx-' || person.last_four_ssn) as last_four_ssn,
jb.external_employee_id as EmployerEmployeeId,
jb.partner_employee_id as PartnerEmployeeId,
jb.division as Division1,
jb.division2 as Division2,
jb.division3 as Division3,
jb.division4 as Division4,
ps.name as PaysiteName,
j.offering_alt_id as AccountType,
case
when jb.coverage_level = 'F'
then 'Family'
when jb.coverage_level = 'S'
then 'Single'
else ''
end as CoverageLevel,
jst.name AS Status,
to_char(js.vt_begin,'MM/dd/yyyy') AS EmployeeEffectiveDate,
case
when js.status = 2
then to_char(js.vt_begin,'MM/dd/yyyy')
else ''
end as TermDate,
nvl(erp.sum_prem, 0) as EmployerAnnualElection,
nvl(eep.sum_prem, 0) as EmployeeAnnualElection,
nvl(erp.sum_prem, 0) + nvl(eep.sum_prem, 0) as TotalAnnualElection,
nvl(psr.sum_amount, 0) as ERPayrollSubmitted,
nvl(pse.sum_amount, 0) as EEPayrollSubmitted,
nvl(psr.sum_amount, 0) + nvl(pse.sum_amount, 0) as TotalPayrollSubmitted,
nvl(pp1.sum_amount, 0) + nvl(pp2.sum_amount, 0) as TotalPayrollProcessed,
case when j.offering_id in (5) then nvl(hb.sum_amount, 0) else 0 end as AppliedToHsaBalance,
case when j.offering_id in (5) then nvl(hop.sum_amount, 0) else 0 end as AppliedToHsaOdPayBack,
nvl(hb.sum_amount, 0) + nvl(hop.sum_amount, 0) - (nvl(pp1.sum_amount, 0) + nvl(pp2.sum_amount, 0)) as DespositsNotApplied,
to_char(psi.deduction_date,'mm/dd/yyyy') as ContributionDate,
invType.name AS InvoiceType
-- , 1 as f_u_oracle
from cmpinv_job j
-- Payroll Contributions (amount type 1)
join cmpinv_amount pc on pc.invoice_id = j.invoice_id
and pc.job_id = j.job_id
and pc.offering_id = j.offering_id
and pc.amount_type = 1
-- EmployerAnnualElection (prem type 1)
left join cmpinv_premium erp on erp.invoice_id = j.invoice_id
and erp.job_id = j.job_id
and erp.offering_id = j.offering_id
and erp.prem_type = 1
-- EmployeeAnnualElection (prem type 2)
left join cmpinv_premium eep on eep.invoice_id = j.invoice_id
and eep.job_id = j.job_id
and eep.offering_id = j.offering_id
and eep.prem_type = 2
-- ERPayrollSubmitted (amount type 2)
left join cmpinv_amount psr on psr.invoice_id = j.invoice_id
and psr.job_id = j.job_id
and psr.offering_id = j.offering_id
and psr.amount_type = 2
-- EEPayrollSubmitted (amount type 3)
left join cmpinv_amount pse on pse.invoice_id = j.invoice_id
and pse.job_id = j.job_id
and pse.offering_id = j.offering_id
and pse.amount_type = 3
-- TotalPayrollProcessed1 (amount type 4)
left join cmpinv_amount pp1 on pp1.invoice_id = j.invoice_id
and pp1.job_id = j.job_id
and pp1.offering_id = j.offering_id
and pp1.amount_type = 4
-- TotalPayrollProcessed2 (amount type 5)
left join cmpinv_amount pp2 on pp2.invoice_id = j.invoice_id
and pp2.job_id = j.job_id
and pp2.offering_id = j.offering_id
and pp2.amount_type = 5
-- AppliedToHsaBalance (amount type 6)
left join cmpinv_amount hb on hb.invoice_id = j.invoice_id
and hb.job_id = j.job_id
and hb.offering_id = j.offering_id
and hb.amount_type = 6
-- AppliedToHsaOdPayBack (amount type 7)
left join cmpinv_amount hop on hop.invoice_id = j.invoice_id
and hop.job_id = j.job_id
and hop.offering_id = j.offering_id
and hop.amount_type = 7
join cycowner.m_job jb on jb.id = j.job_id
join cycowner.m_person person on person.id = jb.employee_id
join cycowner.m_employer er on er.id = jb.employer_id
join cycowner.m_system_status ss on ss.id = 1
join cycowner.m_jobstatus js on js.job_id = j.job_id
and js.tt_start <= current_timestamp
and js.tt_stop > current_timestamp
and js.vt_begin <= trunc(ss.system_date)
and js.vt_end > trunc(ss.system_date)
join cycowner.m_jobstatustype jst on jst.id = js.status
join cycowner.b_paysiteinvoice psi on psi.invoice_id = j.invoice_id
join cycowner.b_invoice inv on inv.id = psi.invoice_id
join cycowner.m_paysite ps on ps.id = psi.paysite_id
join cycowner.m_simplelookup invtype on invtype.id = inv.invoice_type
where
1=1
and j.invoice_id = 2181230
-- added ssn and account type to the order by
order by lastname, firstname, ssn, accounttype
;
GLOBAL TEMP TABLE SCRIPT:create global temporary table cmpinv_job
(
invoice_id number(20, 0),
job_id number(20, 0),
offering_id number(20, 0),
employerplanyear_id number(20, 0),
offering_alt_id varchar2(255)
)
;
create global temporary table cmpinv_amount
(
invoice_id number(20, 0),
job_id number(20, 0),
offering_id number(20, 0),
amount_type number(2),
sum_amount number(10, 2)
)
;
create global temporary table cmpinv_premium
(
invoice_id number(20, 0),
job_id number(20, 0),
offering_id number(20, 0),
employerplanyear_id number(20, 0),
prem_type number(2),
sum_prem number(10, 2)
)
;
create index ix_cmpinvjob_in_jo_of_py on cmpinv_job(invoice_id, job_id, offering_id, employerplanyear_id);
create index ix_cmpinvamt_in_jo_of_ty on cmpinv_amount(invoice_id, job_id, offering_id, amount_type);
create index ix_cmpinvprem_in_jo_of_py_ty on cmpinv_premium(invoice_id, job_id, offering_id, employerplanyear_id, prem_type);