Query TerpanjangPosted by gilang on July 17th, 2008
Ini nih query terpanjang yang gua temuin hari ini,.. ga tau dah apa maksudnya.. bener-bener gila tuh programmer-programmer NextG terdahulu kalau pada bikin query…
SELECT DISTINCT coalesce(result.product, 'other') as product,
result.journal_entry, result.reference, result.invoice_client_name,
result.invoice_date, result.period_first, result.cco_name,
result.transaction_currency, (CASE WHEN
(result.premi_full_amount-result.premi<result.stamp_line) AND
(result.instalment_id=1) THEN result.premi_full_amount-result.stamp_line
ELSE result.premi END ) AS premi, (CASE WHEN
(result.premi=result.premi_full_amount) AND (result.instalment_id=1) THEN
result.stamp_line WHEN
(result.premi_full_amount-result.premi<result.stamp_line) AND
(result.instalment_id=1) THEN result.premi_full_amount-result.premi ELSE 0
END ) AS stamp,
((result.commissi*result.proporsi*result.proporsii) -
(result.tax * result.proporsi * result.proporsii))
AS brokerage_netto,
((result.commissi*result.proporsi*result.proporsii)/1.1) AS
brokerage_gross, (result.commissi*result.proporsi*result.proporsii) AS
brokerage_incppn, (result.tax*result.proporsi*result.proporsii) AS pph_23,
result.coins AS coins, (CASE WHEN now()<=result.aging THEN 'Belum Jatuh
Tempo' WHEN (result.aging + interval '1 mons')> now() THEN '<1 Bulan'
WHEN (result.aging + interval '1 mons')<=now() AND now() <=(result.aging +
interval '2 mons')THEN '1-2 Bulan' WHEN (result.aging + interval '3
mons')<=now() AND now() <=(result.aging + interval '6 mons')THEN '3-6
Bulan' ELSE '>6 Bulan' END ) AS aging, (coalesce((CASE WHEN
(result.premi_full_amount-result.premi<result.stamp_line) AND
(result.instalment_id=1) THEN result.premi_full_amount-result.stamp_line
ELSE result.premi END ), 0)+coalesce((CASE WHEN
(result.premi=result.premi_full_amount) AND (result.instalment_id=1) THEN
result.stamp_line WHEN
(result.premi_full_amount-result.premi<result.stamp_line) AND
(result.instalment_id=1) THEN result.premi_full_amount-result.premi ELSE 0
END ),
0)-coalesce(((result.commissi*result.proporsi*result.proporsii) -
(result.tax*result.proporsi*result.proporsii)),
0)+coalesce((result.coins*result.proporsi*result.proporsii), 0)) AS
bill_netto, result.instalment_id FROM ( select *, ( select
transaction_full_amount - COALESCE((SELECT sum(trans_amount) FROM
tbl_fn_recording_instalments a WHERE a.apar_number =i.apar_number AND
instalment_id = i.instalment_id), 0) from tbl_fn_apar_instalments i,
tbl_fn_apar_details d where i.apar_number=d.apar_number and
d.reference=li.reference and i.instalment_id=li.instalment_id and
d.transaction_type='Insurance premium' and (case when
substr(transaction_full_amount, 1, 1) = '-' then '-' else '+'
end)::text=li.sign::text ) as premi, (SELECT transaction_amount FROM
tbl_fn_apar_lines WHERE apar_number=(select apar_number from
tbl_fn_apar_details where journal_entry=li.journal_entry) and
transaction_origin='Admin & stamp duty AR' ) AS stamp_line, ( select
transaction_full_amount from tbl_fn_apar_instalments i,
tbl_fn_apar_details d where i.apar_number=d.apar_number and
d.reference=li.reference and i.instalment_id=li.instalment_id and
d.transaction_type='Insurance premium' and (case when
substr(transaction_full_amount, 1, 1) = '-' then '-' else '+'
end)::text=li.sign::text ) as premi_full_amount, ( select
transaction_amount from tbl_fn_apar_lines i, tbl_fn_apar_details d where
i.apar_number=d.apar_number and d.reference=li.reference and
d.transaction_type = 'Insurance commission broker' and
i.transaction_origin='Commission expense' and (case when
substr(transaction_amount, 1, 1) = '-' then '-' else '+'
end)::text=li.sign::text ) as commissi, ( select
coalesce(transaction_amount, 0) from tbl_fn_apar_lines l,
tbl_fn_apar_details d where l.apar_number=d.apar_number and
d.reference=li.reference and d.transaction_type = 'Insurance commission
broker' and l.transaction_origin='Withholding income tax payable' and
(case when substr(transaction_amount, 1, 1) = '-' then '-' else '+'
end)::text=li.sign::text ) as tax, ( select transaction_outstanding from
tbl_fn_apar_instalments i, tbl_fn_apar_details d where
i.apar_number=d.apar_number and d.reference=li.reference and
i.instalment_id=li.instalment_id and d.transaction_type = 'Co-insurance
out premium' and (case when substr(transaction_full_amount, 1, 1) = '-'
then '+' else '-' end)::text=li.sign::text ) as coins , (SELECT
coalesce(sum(transaction_full_amount), 0) FROM tbl_fn_apar_instalments
fai, tbl_fn_apar_details fad_c WHERE fai.apar_number=fad_c.apar_number AND
fad_c.transaction_type in ('Insurance commission broker', 'Insurance
commission agent') AND fad_c.reference=li.reference AND
fad_c.invoice_date=li.invoice_date AND fai.instalment_id=li.instalment_id
and (case when substr(transaction_full_amount, 1, 1) = '-' then '-' else
'+' end)::text=li.sign::text ) / (SELECT coalesce(sum(transaction_amount),
1) FROM tbl_fn_apar_lines fal, tbl_fn_apar_details fad_c WHERE
fal.apar_number=fad_c.apar_number AND fad_c.transaction_type in
('Insurance commission broker', 'Insurance commission agent') AND
fad_c.reference=li.reference AND fad_c.invoice_date=li.invoice_date AND
fal.transaction_origin='Commission AP' AND (case when substr((select
sum(transaction_amount) from tbl_fn_apar_lines where
apar_number=fad_c.apar_number), 1, 1)='-' then '-' else '+' end)=li.sign )
AS proporsii, ( (SELECT coalesce(sum(transaction_outstanding), 0) FROM
tbl_fn_apar_instalments fai, tbl_fn_apar_details fad_c WHERE
fai.apar_number=fad_c.apar_number AND fad_c.transaction_type in
('Insurance commission broker', 'Insurance commission agent') AND
fad_c.reference=li.reference AND fad_c.invoice_date=li.invoice_date AND
fai.instalment_id=li.instalment_id and (case when
substr(transaction_full_amount, 1, 1) = '-' then '-' else '+'
end)::text=li.sign::text )::numeric / (SELECT
coalesce(sum(transaction_full_amount), 0) FROM tbl_fn_apar_instalments
fai, tbl_fn_apar_details fad_c WHERE fai.apar_number=fad_c.apar_number AND
fad_c.transaction_type in ('Insurance commission broker', 'Insurance
commission agent') AND fad_c.reference=li.reference AND
fad_c.invoice_date=li.invoice_date AND fai.instalment_id=li.instalment_id
and (case when substr(transaction_full_amount, 1, 1) = '-' then '-' else
'+' end)::text=li.sign::text )::numeric) AS proporsi from ( select (select
journal_entry from tbl_fn_apar_details app where
app.transaction_type='Insurance premium' and reference=lii.reference and (
case when substr(( select sum(transaction_full_amount) from
tbl_fn_apar_instalments i where i.apar_number=app.apar_number ), 1, 1)
='-' then '-' else '+' end )=lii.sign ) as journal_entry, lii.* from (
select distinct sd.insurance_period_from as period_first, sd.cco_name,
(select dname from tbl_gl_dimensions gd, tbl_gl_dimension_mapping dm where
dm.dim = gd.dcode and dm.value=substr(sd.sales_number, 4, 3)) as product,
ap.invoice_date, ap.reference, ap.transaction_currency,
ap.payment_client_name as invoice_client_name, instalment_id, i.due_date
as aging, case when substr(( select sum(transaction_full_amount) from
tbl_fn_apar_instalments i, tbl_fn_apar_details d where
i.apar_number=d.apar_number and d.apar_number=ap.apar_number and
d.transaction_type='Insurance premium' ), 1, 1) ='-' then '-' else '+' end
as sign from tbl_so_sales_details sd, tbl_fn_apar_details ap,
tbl_fn_apar_instalments i where
sd.sales_number||';'||sd.revision_number=ap.reference and ap.invoice_date
> ‘2008-01-01′ and ap.apar_number=i.apar_number and (select
sum(transaction_outstanding) from tbl_fn_apar_instalments where
apar_number=ap.apar_number)<>0 and payment_client_code=’3849′ and
ap.back_office_code=’11′ and ap.transaction_type in (’Insurance premium’,
‘Insurance commission broker’, ‘Co-insurance out premium’) ) as lii ) as
li ) result WHERE (coalesce(result.premi, 0)<>0 OR
coalesce(result.commissi, 0)<>0 OR coalesce(result.coins, 0)<>0) AND
result.invoice_date<=now()
Filed under: Daily | 2 Comments