I have been having multiple issues (one after the other) in trying to generate a report for Business user. Let me start with my test cases and yes, this time I have made sure by testing and dirtying my hands that the test cases work properly and commit into the database (I will be more than happy to provide screen shots of the same).
Test Case:
Tables:RETURNS & RETURN_LINE_ITEMS
- Code: Select all
CREATE TABLE RETURNS
(
ID NUMBER(12),
PROG_PROGRAM_CD VARCHAR2(2 BYTE),
ACCT_ID NUMBER(12),
ACPE_ID NUMBER(12),
JENT_ID NUMBER(12),
PREV_RTRN_ID NUMBER(12),
ENTP_ABN NUMBER(9),
ACCT_OCCURNC_NBR NUMBER(4),
SOURCE_TYPE VARCHAR2(30 BYTE) DEFAULT 'BLANK',
RECEIVE_DATE DATE,
AMEND_IND VARCHAR2(1 BYTE) DEFAULT 'N',
CMPLT_IND VARCHAR2(1 BYTE) DEFAULT 'N',
PENALTY_OR_IND VARCHAR2(1 BYTE) DEFAULT 'N',
RETURN_STATUS VARCHAR2(12 BYTE),
STATUS_DATE DATE,
STATUS_USERID VARCHAR2(8 BYTE),
PERIOD_START_DATE DATE,
PERIOD_END_DATE DATE,
NOTICE_STATUS VARCHAR2(12 BYTE),
NOTICE_STATUS_DATE DATE,
NOTE_TEXT VARCHAR2(2000 BYTE),
PENALTY_OR_BY VARCHAR2(8 BYTE),
PENALTY_OR_TMST DATE,
FILING_ID NUMBER(12),
CASE_ID VARCHAR2(49 BYTE),
DOC_CONTRL_NBR NUMBER(29),
LOCTR_NBR NUMBER(10),
STATUTE_BARRED_DATE DATE,
MEDIA_TYPE VARCHAR2(30 BYTE),
DISPSTN_TYPE VARCHAR2(30 BYTE),
AMEND_TYPE VARCHAR2(30 BYTE),
CALC_MODE VARCHAR2(30 BYTE),
PROCESS_PASS_CNT NUMBER(1),
CONVRTD_IND VARCHAR2(1 BYTE) DEFAULT 'N',
LOSS_PERIOD_END_DATE DATE,
MF_SYNC_CD VARCHAR2(1 BYTE)
);
CREATE TABLE RETURN_LINE_ITEMS
(
ID NUMBER(12),
RTSC_ID NUMBER(12),
RTRN_ID NUMBER(12),
SCLI_ID NUMBER(12),
LITM_ID NUMBER(12),
ENTP_ABN NUMBER(9),
PROG_PROGRAM_CD VARCHAR2(2 BYTE),
ACCT_OCCURNC_NBR NUMBER(4),
ACPE_END_DATE DATE,
SCHED_NBR VARCHAR2(3 BYTE),
SCHD_VERSION_YR NUMBER(4) DEFAULT 0,
SCHD_VERSION_NBR NUMBER(3),
RTSC_OCCUR_NBR NUMBER(3),
LITM_LINE_ITEM_NBR VARCHAR2(3 BYTE),
SLIN_LINE_ITEM_ID NUMBER(12),
OCCUR_NBR NUMBER(3) DEFAULT 1,
PREV_VAL_MOD_IND VARCHAR2(1 BYTE) DEFAULT 'N',
VIABLE_IND VARCHAR2(1 BYTE) DEFAULT 'N',
ACTIVE_IND VARCHAR2(1 BYTE) DEFAULT 'Y',
ACTION_CD VARCHAR2(1 BYTE),
PREV_VAL_AMT NUMBER(15,2),
REVISE_VAL_AMT NUMBER(15,2),
PREV_VAL_TEXT VARCHAR2(100 BYTE),
REVISE_VAL_TEXT VARCHAR2(100 BYTE),
DISPLAY_SEQ_NBR NUMBER(3),
SYS_VAL_AMT NUMBER(15,2),
LITM_INNER_PASS_SEQ_NBR NUMBER(3)
);
Inserting data into RETURNS table
- Code: Select all
INSERT INTO RETURNS (
ID ,
PROG_PROGRAM_CD ,
ACCT_ID ,
ACPE_ID ,
JENT_ID ,
PREV_RTRN_ID ,
ENTP_ABN ,
ACCT_OCCURNC_NBR ,
SOURCE_TYPE ,
RECEIVE_DATE ,
AMEND_IND ,
CMPLT_IND ,
PENALTY_OR_IND ,
RETURN_STATUS ,
STATUS_DATE ,
STATUS_USERID ,
PERIOD_START_DATE ,
PERIOD_END_DATE ,
NOTICE_STATUS ,
NOTICE_STATUS_DATE ,
NOTE_TEXT ,
PENALTY_OR_BY ,
PENALTY_OR_TMST ,
FILING_ID ,
CASE_ID ,
DOC_CONTRL_NBR ,
LOCTR_NBR ,
STATUTE_BARRED_DATE ,
MEDIA_TYPE ,
DISPSTN_TYPE ,
AMEND_TYPE ,
CALC_MODE ,
PROCESS_PASS_CNT ,
CONVRTD_IND ,
LOSS_PERIOD_END_DATE ,
MF_SYNC_CD)
VALUES(
3715944,
'01',
139048,
3587419,
9518324,
NULL,
401377197,
1,
'TAXPAYER',
TO_DATE('4/7/2009','mm/dd/yyyy'),
'N',
'Y',
'N',
'ASSESSED',
TO_DATE('4/30/2009','mm/dd/yyyy'),
'TRAPROD2',
TO_DATE('2/1/2008','mm/dd/yyyy'),
TO_DATE('1/31/2009', 'mm/dd/yyyy'),
'PRINTED',
TO_DATE('4/30/2009' , 'mm/dd/yyyy'),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
6045574495,
NULL,
'RSI',
'NOTICE',
NULL,
NULL,
NULL,
'N',
NULL,
NULL);
NSERT INTO RETURNS (
ID ,
PROG_PROGRAM_CD ,
ACCT_ID ,
ACPE_ID ,
JENT_ID ,
PREV_RTRN_ID ,
ENTP_ABN ,
ACCT_OCCURNC_NBR ,
SOURCE_TYPE ,
RECEIVE_DATE ,
AMEND_IND ,
CMPLT_IND ,
PENALTY_OR_IND ,
RETURN_STATUS ,
STATUS_DATE ,
STATUS_USERID ,
PERIOD_START_DATE ,
PERIOD_END_DATE ,
NOTICE_STATUS ,
NOTICE_STATUS_DATE ,
NOTE_TEXT ,
PENALTY_OR_BY ,
PENALTY_OR_TMST ,
FILING_ID ,
CASE_ID ,
DOC_CONTRL_NBR ,
LOCTR_NBR ,
STATUTE_BARRED_DATE ,
MEDIA_TYPE ,
DISPSTN_TYPE ,
AMEND_TYPE ,
CALC_MODE ,
PROCESS_PASS_CNT ,
CONVRTD_IND ,
LOSS_PERIOD_END_DATE ,
MF_SYNC_CD)
VALUES(
4117092,
'01',
57794,
3864551,
10566221,
NULL,
400571410,
1,
'TAXPAYER',
TO_DATE('6/30/2010', 'mm/dd/yyyy'),
'N',
'Y',
'N',
'ASSESSED',
TO_DATE('7/27/2010','mm/dd/yyyy'),
'TRAPROD2',
TO_DATE('1/1/2009','mm/dd/yyyy'),
TO_DATE('12/31/2009','mm/dd/yyyy'),
'PRINTED',
TO_DATE('7/27/2010','mm/dd/yyyy'),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
6053117120,
NULL,
'RSI',
'NOTICE',
NULL,
NULL,
NULL,
'N',
NULL,
NULL);
INSERT INTO RETURNS (
ID ,
PROG_PROGRAM_CD ,
ACCT_ID ,
ACPE_ID ,
JENT_ID ,
PREV_RTRN_ID ,
ENTP_ABN ,
ACCT_OCCURNC_NBR ,
SOURCE_TYPE ,
RECEIVE_DATE ,
AMEND_IND ,
CMPLT_IND ,
PENALTY_OR_IND ,
RETURN_STATUS ,
STATUS_DATE ,
STATUS_USERID ,
PERIOD_START_DATE ,
PERIOD_END_DATE ,
NOTICE_STATUS ,
NOTICE_STATUS_DATE ,
NOTE_TEXT ,
PENALTY_OR_BY ,
PENALTY_OR_TMST ,
FILING_ID ,
CASE_ID ,
DOC_CONTRL_NBR ,
LOCTR_NBR ,
STATUTE_BARRED_DATE ,
MEDIA_TYPE ,
DISPSTN_TYPE ,
AMEND_TYPE ,
CALC_MODE ,
PROCESS_PASS_CNT ,
CONVRTD_IND ,
LOSS_PERIOD_END_DATE ,
MF_SYNC_CD)
VALUES(
4382179,
'01',
498210,
3957251,
11264174,
3727534,
405079963,
1,
'TAXPAYER',
TO_DATE('4/26/2011', 'mm/dd/yyyy'),
'Y',
'Y',
'N',
'ASSESSED',
TO_DATE('5/12/2011','mm/dd/yyyy'),
'LOPATS',
TO_DATE('2/1/2008', 'mm/dd/yyyy'),
TO_DATE('1/31/2009','mm/dd/yyyy'),
'PRINTED',
TO_DATE('5/12/2011','mm/dd/yyyy'),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
6058767341,
TO_DATE('4/30/2015','mm/dd/yyyy'),
'RSI',
'NOTICE',
'LOSS CARRY BACK (SIMPLE)',
'LOSS CARRY BACK',
NULL,
'N',
TO_DATE('1/31/2011', 'mm/dd/yyyy'),
NULL
);
Now inserting data into 2nd table i.e. RETURN_LINE_ITEMS
RTRN_ID means RETURN ID
This is for RTRN_ID =3715944
- Code: Select all
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES(
277707088,
8022477,
3715944,
NULL,
1585,
401377197,
'01',
1,
TO_DATE('1/31/2009', 'mm/dd/yyyy'),
'000',
1998,
1,
1,
'029',
23,
1,
'N',
'Y',
'Y',
NULL,
1,
1,
NULL,
NULL,
115,
1 ,
1);
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES
(
277707106,
8022477,
3715944,
NULL,
1603,
401377197,
'01',
1,
TO_DATE('1/31/2009', 'mm/dd/yyyy'),
'000',
1998,
1,
1,
'062',
40,
1,
'N',
'Y',
'Y',
NULL,
1069336,
1069336,
NULL,
NULL,
200,
1069336,
130
);
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES
(277707109,
8022477,
3715944,
NULL,
1539,
401377197,
'01',
1,
TO_DATE('1/31/2009','mm/dd/yyyy'),
'000',
1998,
1,
1,
'066',
43,
1,
'N',
'Y',
'Y',
NULL,
NULL,
1000,
'NULL',
'NULL',
215,
1069336,
200);
Now for RTRN_ID = 4117092
- Code: Select all
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES(
319820214,
9028477,
4117092,
NULL,
1585,
400571410,
'01',
1,
TO_DATE('12/31/2009','mm/dd/yyyy'),
'000',
1998,
1,
1,
'029',
23,
1,
'N',
'Y',
'Y',
NULL,
3,
3,
NULL,
NULL,
115,
3,
1
);
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES(
319820233,
9028477,
4117092,
NULL,
1603,
400571410,
'01',
1,
TO_DATE('12/31/2009','mm/dd/yyyy'),
'000',
1998,
1,
1,
'062',
40,
1,
'N',
'Y',
'Y',
NULL,
790068,
790068,
NULL,
NULL,
200,
790068,
130
);
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES
(
319820236,
9028477,
4117092,
NULL,
1539,
400571410,
'01',
1,
TO_DATE('12/31/2009','mm/dd/yyyy'),
'000',
1998,
1,
1,
'066',
43,
1,
'N',
'Y',
'Y',
NULL,
NULL,
790,
NULL,
NULL,
215,
790068,
200
);
Now finally for RTRN_ID = 4382179
- Code: Select all
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES
(
348462584,
9694297,
4382179,
NULL,
1585,
405079963,
'01',
1,
TO_DATE(1/31/2009, 'mm/dd/yyyy'),
'000'
1998,
1,
1,
'029',
23,
1,
'N',
'Y',
'Y',
NULL,
1,
1,
NULL,
NULL,
115,
NULL,
1
);
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES
(
348462602,
9694297,
4382179,
NULL,
1603,
405079963,
'01',
1,
TO_DATE('1/31/2009','mm/dd/yyyy'),
'000',
1998,
1,
1,
'062',
40,
1,
'N',
'Y',
'Y',
NULL,
672738,
643304,
NULL,
NULL,
200,
NULL,
130
);
INSERT INTO RETURN_LINE_ITEMS (
ID ,
RTSC_ID ,
RTRN_ID ,
SCLI_ID ,
LITM_ID ,
ENTP_ABN ,
PROG_PROGRAM_CD ,
ACCT_OCCURNC_NBR ,
ACPE_END_DATE ,
SCHED_NBR ,
SCHD_VERSION_YR ,
SCHD_VERSION_NBR ,
RTSC_OCCUR_NBR ,
LITM_LINE_ITEM_NBR ,
SLIN_LINE_ITEM_ID ,
OCCUR_NBR ,
PREV_VAL_MOD_IND ,
VIABLE_IND ,
ACTIVE_IND ,
ACTION_CD ,
PREV_VAL_AMT ,
REVISE_VAL_AMT ,
PREV_VAL_TEXT ,
REVISE_VAL_TEXT ,
DISPLAY_SEQ_NBR ,
SYS_VAL_AMT ,
LITM_INNER_PASS_SEQ_NBR
)
VALUES
(
348462605,
9694297,
4382179,
NULL,
1539,
405079963,
01,
1,
TO_DATE('1/31/2009','mm/dd/yyyy'),
'000',
1998,
1,
1,
'066',
43,
1,
'N',
'Y',
'Y',
NULL,
672738,
643304,
NULL,
NULL,
215,
643304,
200
);
OK. Now once this data is all inserted, comes the real problem and that is:
a) based on the following requirements
Requirements: Adhoc Report for Audit on CIT Income Allocation
1. Select from the RETURNS table:
• Id > 3600000 and
• Prog_program_cd = '01' and
• Return_status in ('ASSESSED', 'DU") and
• Tax year ending in 2009 (i.e. year portion of the Period_end_date is in 2009)
2. Retrieve the following fields from the RETURNS table :
• Id
• Entp_abn
• Acct_id
• Prog_program_cd
• Period_start_date
• Period_end_date
• Amend_ind
• Return_status
• Status_date
• Loctr_nbr
3. If there are multiple entries from the same account and tax year, only retain the latest record: From the records selected in step 2, if there are multiple records with the same Acct_Id and Period_end_date, only retain the record with the most recent Status_date (i.e. MAX value on the date).
4. Using the results from step 3, link to the applicable RETURN_LINE_ITEMS table where:
• RETURNS.Id = RETURN_LINE_ITEMS.Rtrn_Id
5. From the selected return on RETURN_LINE_ITEMS table, retrieve records where (value on Sch 000 Line 062 > 500,000) and (value on Sch 000 Line 066 < value on Sch 000 Line 062) :
• Sched_nbr = '000', and Litm_line_item_nbr = '062', and Active_ind = 'Y', get Revise_val_amt as 'ab_taxable_income'
• Sched_nbr = '000', and Litm_line_item_nbr = '066', and Active_ind = 'Y', get Revise_val_amt as 'amt_taxable_in_ab'
• Retain the return only if (ab_taxable_income > 500,000) and (amt_taxable_in_ab < ab_taxable_income)
[/b]
I utilized the following query and I am getting 4 rows (line item numbers 062 and 066 where in ab_taxable_income > 500,000) and (amt_taxable_in_ab < ab_taxable_income) and this is correct
- Code: Select all
SELECT r1.id, r1.entp_abn, r1.acct_id, r1.prog_program_cd, r1.period_start_date, r1.period_end_Date, r1.amend_ind, r1.return_status, r1.status_date, r1.loctr_nbr,
rtlnms.sched_nbr, rtlnms.litm_line_item_nbr,
decode(rtlnms.litm_line_item_nbr, '062', RTLNMS.REVISE_VAL_AMT)AB_TAXABLE_INCOME,
decode(rtlnms.litm_line_item_nbr, '066', RTLNMS.REVISE_VAL_AMT)AMT_TAXABLE_IN_AB
from returns r1, return_line_items rtlnms
where
r1.id = rtlnms.rtrn_id and
r1.prog_program_cd = rtlnms.prog_program_cd and
r1.entp_abn = rtlnms.entp_abn and
r1.id > 3600000 AND r1.prog_program_cd = '01' AND r1.return_status in ('ASSESSED', 'DU')
and r1.period_end_Date between to_date('01-01-2009','DD-mm-YYYY') and to_date ('31-12-2009', 'DD-mm-YYYY')
and r1.status_date = (select max (status_date) from returns r2 where r2. acct_id= r1.acct_id and r2.period_end_date = r1.period_end_date)
and rtlnms.sched_nbr = '000'
and rtlnms.active_ind = 'Y'
and r1.id in (4117092,3715944,4382179,3691435)
and RTLNMS.LITM_LINE_ITEM_NBR IN('062')
AND nvl(decode(litm_line_item_nbr, '062', REVISE_VAL_AMT),0) > 500000
and NVL(RTLNMS.REVISE_VAL_AMT,0) >( select NVL(RTLNMS2.REVISE_VAL_AMT,0)
FROM RETURN_LINE_ITEMS RTLNMS2
WHERE RTLNMS2.RTRN_ID=RTLNMS.RTRN_ID
AND RTLNMS2.LITM_LINE_ITEM_NBR = '066'
AND SCHED_NBR='000'
)
UNION
SELECT r1.id, r1.entp_abn, r1.acct_id, r1.prog_program_cd, r1.period_start_date, r1.period_end_Date, r1.amend_ind, r1.return_status, r1.status_date, r1.loctr_nbr,
rtlnms.sched_nbr, rtlnms.litm_line_item_nbr,
decode(rtlnms.litm_line_item_nbr, '062', RTLNMS.REVISE_VAL_AMT)AB_TAXABLE_INCOME,
decode(rtlnms.litm_line_item_nbr, '066', RTLNMS.REVISE_VAL_AMT)AMT_TAXABLE_IN_AB
from returns r1, return_line_items rtlnms
where r1.id = rtlnms.rtrn_id
AND r1.prog_program_cd = rtlnms.prog_program_cd
AND r1.entp_abn = rtlnms.entp_abn
AND r1.id > 3600000 AND r1.prog_program_cd = '01'
AND r1.return_status in ('ASSESSED', 'DU')
and r1.period_end_Date between to_date('01-01-2009','DD-mm-YYYY') and to_date ('31-12-2009', 'DD-mm-YYYY')
and r1.status_date = (select max (status_date) from returns r2 where r2. acct_id= r1.acct_id and r2.period_end_date = r1.period_end_date)
and rtlnms.sched_nbr = '000'
and rtlnms.active_ind = 'Y'
and r1.id in (4117092,3715944,4382179,3691435)
and RTLNMS.LITM_LINE_ITEM_NBR IN('066')
and NVL(RTLNMS.REVISE_VAL_AMT,0) <( select NVL(RTLNMS2.REVISE_VAL_AMT,0)
FROM RETURN_LINE_ITEMS RTLNMS2
WHERE RTLNMS2.RTRN_ID=RTLNMS.RTRN_ID
AND RTLNMS2.LITM_LINE_ITEM_NBR = '062'
and NVL(RTLNMS2.REVISE_VAL_AMT,0)>500000
AND SCHED_NBR='000'
)
order by ID
However there is another condition after "5c" i.e. after Retain the return only if (ab_taxable_income > 500,000) and (amt_taxable_in_ab < ab_taxable_income)
and that is:
For those returns meeting the criteria in step 5, also retrieve the following from RETURN_LINE_ITEMS table and that is:
sched_nbr = '000' and Litm_line_item_nbr = '029' and ACTIVE_IND ='Y', get Revise_val_amt as "corp_type"
and in order to go about this, I utilised the following query:
- Code: Select all
SELECT r1.id, r1.entp_abn, r1.acct_id, r1.prog_program_cd, r1.period_start_date, r1.period_end_Date, r1.amend_ind, r1.return_status, r1.status_date, r1.loctr_nbr,
rtlnms.sched_nbr, rtlnms.litm_line_item_nbr,
decode(rtlnms.litm_line_item_nbr, '062', RTLNMS.REVISE_VAL_AMT)AB_TAXABLE_INCOME,
decode(rtlnms.litm_line_item_nbr, '066', RTLNMS.REVISE_VAL_AMT)AMT_TAXABLE_IN_AB,
decode(rtlnms.litm_line_item_nbr, '029', RTLNMS.REVISE_VAL_AMT)CORP_INCOME
from returns r1, return_line_items rtlnms
where
r1.id = rtlnms.rtrn_id and
r1.prog_program_cd = rtlnms.prog_program_cd and
r1.entp_abn = rtlnms.entp_abn and
r1.id > 3600000 AND r1.prog_program_cd = '01' AND r1.return_status in ('ASSESSED', 'DU')
and r1.period_end_Date between to_date('01-01-2009','DD-mm-YYYY') and to_date ('31-12-2009', 'DD-mm-YYYY')
and r1.status_date = (select max (status_date) from returns r2 where r2. acct_id= r1.acct_id and r2.period_end_date = r1.period_end_date)
and rtlnms.sched_nbr = '000'
and rtlnms.active_ind = 'Y'
and r1.id in (4117092,3715944,4382179,3691435)
and RTLNMS.LITM_LINE_ITEM_NBR IN('062')
AND nvl(decode(litm_line_item_nbr, '062', REVISE_VAL_AMT),0) > 500000
and NVL(RTLNMS.REVISE_VAL_AMT,0) >( select NVL(RTLNMS2.REVISE_VAL_AMT,0)
FROM RETURN_LINE_ITEMS RTLNMS2
WHERE RTLNMS2.RTRN_ID=RTLNMS.RTRN_ID
AND RTLNMS2.LITM_LINE_ITEM_NBR IN( '066')
AND SCHED_NBR='000'
)
UNION
SELECT r1.id, r1.entp_abn, r1.acct_id, r1.prog_program_cd, r1.period_start_date, r1.period_end_Date, r1.amend_ind, r1.return_status, r1.status_date, r1.loctr_nbr,
rtlnms.sched_nbr, rtlnms.litm_line_item_nbr,
decode(rtlnms.litm_line_item_nbr, '062', RTLNMS.REVISE_VAL_AMT)AB_TAXABLE_INCOME,
decode(rtlnms.litm_line_item_nbr, '066', RTLNMS.REVISE_VAL_AMT)AMT_TAXABLE_IN_AB,
decode(rtlnms.litm_line_item_nbr, '029', RTLNMS.REVISE_VAL_AMT)CORP_INCOME
from returns r1, return_line_items rtlnms
where r1.id = rtlnms.rtrn_id
AND r1.prog_program_cd = rtlnms.prog_program_cd
AND r1.entp_abn = rtlnms.entp_abn
AND r1.id > 3600000 AND r1.prog_program_cd = '01'
AND r1.return_status in ('ASSESSED', 'DU')
and r1.period_end_Date between to_date('01-01-2009','DD-mm-YYYY') and to_date ('31-12-2009', 'DD-mm-YYYY')
and r1.status_date = (select max (status_date) from returns r2 where r2. acct_id= r1.acct_id and r2.period_end_date = r1.period_end_date)
and rtlnms.sched_nbr = '000'
and rtlnms.active_ind = 'Y'
and r1.id in (4117092,3715944,4382179,3691435)
and RTLNMS.LITM_LINE_ITEM_NBR IN('066','029')
and NVL(RTLNMS.REVISE_VAL_AMT,0) < ( select NVL(RTLNMS2.REVISE_VAL_AMT,0)
FROM RETURN_LINE_ITEMS RTLNMS2
WHERE RTLNMS2.RTRN_ID=RTLNMS.RTRN_ID
AND RTLNMS2.LITM_LINE_ITEM_NBR='062'
and NVL(RTLNMS2.REVISE_VAL_AMT,0)>500000
AND SCHED_NBR='000'
)
order by ID
My output is: 7 rows i.e.
3 rows of return id = 3715944,
3 rows of return id = 4117092
and 1 row of return id = 4382179
It should only retrieve rows for 3715944 and 4117092; not for row =4382179 and this is because based on question in 5 "c", rows retrieved were for return ids' = 3715944 and 4117092
I know where the problem is:
As soon as it retrieves rows for returns 3715944 and 4117092 (because REVISE_VAL_AMT is greater than 500,000), it also finds another record with return id = 4382179 with greater than 500,000 and it blindly brings that as well.
Is there a way around to ensure that I my results are compared only against the return id's retrieved from 5 "c" = 3715944 and 4117092
Believe me I have tried every option that I could think of to remove return id = 4382179 but somehow could not.
Can someone please help me out. I am SOL as such.
Thanks,
Sandeep