SELECT tax_type_no,
tax_centre_no,
tax_period_no,
tper_start_date,
tper_end_date,
tper_due_date,
tper_payment_date,
tper_month,
tper_year,
tper_week,
form_no,
version_no
FROM (SELECT DISTINCT tfc.tax_type_no,
nvl(tac.tax_centre_no, ta.tax_centre_no) tax_centre_no,
tfc.form_no,
tfc.form_ird_no,
tpc.version_no,
tpc.tax_period_no,
tpc.tper_year,
tpc.tper_month,
tpc.tper_week,
tpc.imposition_base_no,
pck_tax_period.txprd_date_fn(tpc.tax_period_no,
ta.tax_payer_no,
tt.enterprise_no,
2,
1) tper_start_date,
pck_tax_period.txprd_date_fn(tpc.tax_period_no,
ta.tax_payer_no,
tt.enterprise_no,
1,
1) tper_end_date,
pck_tax_period.duedt_file(tpc.tax_period_no,
ta.tax_payer_no,
ta.tax_account_no,
NULL,
NVL(NULL,
tt.estab_no),
NULL) tper_due_date,
CASE
WHEN tpc.tax_on_roll_fl = 'N' THEN
pck_tax_period.get_payment_date(tpc.tax_period_no,
ta.tax_payer_no,
ta.tax_account_no,
NVL(NULL,
tt.estab_no),
NULL,
NULL,
NULL)
ELSE
NULL
END AS tper_payment_date
FROM tax_account ta,
(SELECT ta1.tax_account_no,
te.estab_no,
est.enterprise_no,
te.regist_date,
te.end_date,
NULL lic_base_no,
NULL license_no,
'Y' chargeable_fl
FROM tax_account ta1,
tax_type tt1,
tax_estab te,
ENTERPRISE ep,
establishment est
WHERE ta1.tax_payer_no = 31554214
AND ta1.tax_type_no = tt1.tax_type_no
AND ta1.tax_type_no = te.tax_type_no
AND ta1.tax_account_no = te.tax_account_no
AND te.estab_no = est.estab_no
AND est.enterprise_no = ep.enterprise_no
AND tt1.tax_basis_no = 1 --ESTABLISHMENT
UNION
SELECT ta2.tax_account_no,
NULL estab_no,
NULL enterprise_no,
nvl(lic2.regist_date, ta2.reg_date) regist_date,
nvl(lic2.end_date, ta2.close_date) end_date,
lic2.lic_base_no,
lic2.license_no,
lic2.chargeable_fl
FROM tax_account ta2, tax_type tt2, license lic2
WHERE ta2.tax_payer_no = 31554214
AND ta2.tax_type_no = tt2.tax_type_no
AND tt2.tax_basis_no != 1 --ESTABLISHMENT
AND ta2.tax_account_no = lic2.tax_account_no(+)
UNION
SELECT ta1.tax_account_no,
lic.estab_no,
est.enterprise_no,
lic.regist_date,
lic.end_date,
lic.lic_base_no,
lic.license_no,
lic.chargeable_fl
FROM tax_account ta1,
tax_type tt1,
license lic,
ENTERPRISE ep,
establishment est
WHERE ta1.tax_payer_no = 31554214
AND ta1.tax_type_no = tt1.tax_type_no
AND ta1.tax_type_no = lic.tax_type_no
AND ta1.tax_account_no = lic.tax_account_no
AND lic.estab_no = est.estab_no
AND est.enterprise_no = ep.enterprise_no
AND tt1.tax_basis_no = 1) tt,
tax_form tf,
tax_period tp,
tax_period tpc,
attached_tax_form atf,
tax_form tfc,
tax_account tac,
tax_estab te,
license lic,
tax_acct_taxable_obj tato
WHERE atf.consol_form_no = 309
AND atf.consol_version_no = 1
AND atf.attached_form_no = tf.form_no
AND atf.consol_form_no = tfc.form_no
AND atf.consol_form_no = tpc.form_no
AND atf.consol_version_no = tpc.version_no
AND atf.attached_form_no = tp.form_no
AND tp.tper_year = tpc.tper_year
AND NVL(tp.tper_month, -1) = NVL(tpc.tper_month, -1)
AND NVL(tp.tper_week, -1) = NVL(tpc.tper_week, -1)
AND NVL(tp.imposition_base_no, -1) =
NVL(tpc.imposition_base_no, -1)
AND (tp.imposition_base_no = NULL OR
NULL IS NULL)
AND tp.tper_type_no = tpc.tper_type_no
AND ta.tax_type_no = tf.tax_type_no
AND ta.tax_payer_no = 31554214
AND ta.tax_account_no = tt.tax_account_no
AND tac.tax_payer_no(+) = 31554214
AND tac.tax_type_no(+) = tfc.tax_type_no
--
AND te.tax_account_no(+) = ta.tax_account_no
AND (tp.tper_start_date <= nvl(te.end_date,tp.tper_start_date+1) AND tp.tper_end_date >= nvl(te.regist_date,tp.tper_end_date-1))
--
AND lic.tax_account_no(+) = ta.tax_account_no
AND (tp.tper_start_date <= nvl(lic.end_date,tp.tper_start_date+1) AND tp.tper_end_date >= nvl(lic.regist_date,tp.tper_end_date-1))
--
AND tato.tax_account_no(+)= ta.tax_account_no
AND (tp.tper_start_date <= nvl(tato.end_date,tp.tper_start_date+1) AND tp.tper_end_date >= nvl(tato.start_date,tp.tper_end_date-1))
--
AND nvl(tt.chargeable_fl, 'Y') = 'Y'
AND (ta.tax_centre_no = NULL OR NULL IS NULL)
AND ((tp.tper_type_no = 2 AND
tp.tper_year >=
(SELECT MIN(fy.fiscal_year_from)
FROM fiscal_year fy
WHERE fy.tax_payer_no = 31554214)) OR
tp.tper_type_no != 2)
AND (ta.reg_date <
trunc(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
ta.tax_payer_no,
tt.enterprise_no,
1)))
AND ta.close_date IS NULL
AND NOT EXISTS
(SELECT 1
FROM assessment ass
WHERE tax_account_no = ta.tax_account_no
AND ass.tax_period_no = tp.tax_period_no
AND NVL(ass.estab_no, -1) =
NVL(NVL(NULL, tt.estab_no), -1)
AND NVL(ass.license_no, -1) =
NVL(NVL(NULL, tt.license_no), -1)
AND ass.taxable_object_no IS NULL)
/* AND ((tt.end_date IS NULL AND
tt.regist_date <=
trunc(pck_tax_period.txprd_date_fn(tp.tax_period_no,
ta.tax_payer_no,
tt.enterprise_no,
1,
1))) OR
(tt.estab_no IS NULL AND tt.license_no IS NULL))
*/
AND NOT EXISTS
(SELECT d.DOC_STATE_NO
FROM document d
WHERE d.doc_type_no = 1
AND d.tax_period_no = tpc.tax_period_no
AND d.tax_payer_no = 31554214
AND d.tax_type_no = 273
AND d.tax_centre_no = ta.tax_centre_no
AND doc_state_no <> 3
AND (d.received_date IS NOT NULL OR
d.received_incomplete_date IS NOT NULL OR
d.printed_date IS NOT NULL))
--
AND ((pck_tax_roll.check_inactivity_period(ta.tax_account_no,
tt.estab_no,
tt.license_no,
NULL,
PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
31554214,
tt.enterprise_no,
2),
PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
31554214,
tt.enterprise_no,
1),
2,
1) = 0) OR
NULL = 'Y')
AND (pck_tax_roll.check_inactivity_period(ta.tax_account_no,
tt.estab_no,
tt.license_no,
NULL,
PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
ta.tax_payer_no,
tt.enterprise_no,
2),
PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
ta.tax_payer_no,
tt.enterprise_no,
1),
5,
1) = 0)
AND (pck_tax_roll.check_inactivity_period(ta.tax_account_no,
tt.estab_no,
tt.license_no,
NULL,
PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
31554214,
tt.enterprise_no,
2),
PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
31554214,
tt.enterprise_no,
1),
4,
1) = 0)
--
) unfil
WHERE unfil.tper_start_date BETWEEN TO_DATE('11/01/2018', 'MM/DD/YYYY') AND TO_DATE('11/30/2018', 'MM/DD/YYYY')
AND unfil.tper_end_date BETWEEN TO_DATE('11/01/2018', 'MM/DD/YYYY') AND TO_DATE('11/30/2018', 'MM/DD/YYYY')
AND ((unfil.tax_period_no = 2836
AND
2836 = 2836)
OR
(2836 <> 2836));