This forum is currently locked. You can't register or post questions at this time. (read more)

Oracle Ad-hoc report with multiple queries creating issues

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

buggleboy007
Member
Posts: 1
Joined: Thu Mar 08, 2012 11:38 pm

Oracle Ad-hoc report with multiple queries creating issues

Postby buggleboy007 » Sat Mar 10, 2012 4:51 pm

Hi Guys,

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle Ad-hoc report with multiple queries creating issu

Postby Tim... » Sat Mar 10, 2012 7:48 pm

Hi.

1) The scripts you sent do not run unmodified. There are extra newline characters in them, which make them fail. All I ask is you run what you post!

2) When I correct your scripts and run the query you have posted, I get the 6 rows you want, not the 7 rows you claim to get returned.

Code: Select all

        ID   ENTP_ABN    ACCT_ID PR PERIOD_START_DATE    PERIOD_END_DATE      A RETURN_STATU
---------- ---------- ---------- -- -------------------- -------------------- - ------------
STATUS_DATE           LOCTR_NBR SCH LIT AB_TAXABLE_INCOME AMT_TAXABLE_IN_AB CORP_INCOME
-------------------- ---------- --- --- ----------------- ----------------- -----------
   3715944  401377197     139048 01 01-FEB-2008 00:00:00 31-JAN-2009 00:00:00 N ASSESSED
30-APR-2009 00:00:00 6045574495 000 029                                               1

   3715944  401377197     139048 01 01-FEB-2008 00:00:00 31-JAN-2009 00:00:00 N ASSESSED
30-APR-2009 00:00:00 6045574495 000 062           1069336

   3715944  401377197     139048 01 01-FEB-2008 00:00:00 31-JAN-2009 00:00:00 N ASSESSED
30-APR-2009 00:00:00 6045574495 000 066                                1000


        ID   ENTP_ABN    ACCT_ID PR PERIOD_START_DATE    PERIOD_END_DATE      A RETURN_STATU
---------- ---------- ---------- -- -------------------- -------------------- - ------------
STATUS_DATE           LOCTR_NBR SCH LIT AB_TAXABLE_INCOME AMT_TAXABLE_IN_AB CORP_INCOME
-------------------- ---------- --- --- ----------------- ----------------- -----------
   4117092  400571410      57794 01 01-JAN-2009 00:00:00 31-DEC-2009 00:00:00 N ASSESSED
27-JUL-2010 00:00:00 6053117120 000 029                                               3

   4117092  400571410      57794 01 01-JAN-2009 00:00:00 31-DEC-2009 00:00:00 N ASSESSED
27-JUL-2010 00:00:00 6053117120 000 062            790068

   4117092  400571410      57794 01 01-JAN-2009 00:00:00 31-DEC-2009 00:00:00 N ASSESSED
27-JUL-2010 00:00:00 6053117120 000 066                                 790


6 rows selected.

test@db11g>


I don't know what you are doing different, but your test case (despite its mistakes) does not do what you say it does. This also seems to suggest you have not run your test case!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron