Here is what was caught by my co-worker recently.
One of the Developers, of our Preferred Vendor, actually wrote this stuff.
This is invoked by the one of the forms on the intranet Web application. I was told there are quite a few scripts like this one.
Performance issues? No one is complaining (yet) .... so who cares :)
Enjoy...
- Kirti
SELECT DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa, ' '),
NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,
NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '), NVL(LIST1.listed_tn_line_no, ' '),
NVL(ADDR1.house_no, ' '), NVL(ADDR1.house_no_suff, ' '), NVL(ADDR1.pre_directional, ' '),
NVL(ADDR1.street_name, ' '), NVL(ADDR1.post_directional, ' '), NVL(ADDR1.sub_location, ' '),
NVL(ADDR1.Community, ' '), NVL(ADDR1.zip_five, ' '), NVL(ADDR1.state_abbr_override, ' '),
LIST1.listing_status, NVL(CUST1.customer_type, ' '), NVL(CUST1.home_data_base, ' '),
NVL(CUST1.default_market_code, ' '),
NVL(TELCO.TELCO_CODE, ' '), NVL(TELCO.TELCO_NAME, ' '),
NVL(TO_CHAR(LIST1.establish_date,'YYYYMMDD'),' '), NVL((select 'Y' from query q, dir_curr_issue
where q.customer_id = cust1.customer_id
and q.query_status in (1,3,4)
and q.product_code = dir_curr_issue.directory_code
and q.product_issue_num >= dir_curr_issue.directory_issue_num
and rownum = 1), 'N' ) ,
NVL(CUST1.TELCO_CREDIT_STOP, ' '), NVL(CUST1.BARS_CREDIT_STOP, 0),
NVL(CUST1.EAST_CREDIT_STOP, ' '), NVL(CUST1.EARLY_CONT_CUST_IND, ' '),
NVL(LIST1.SOHO_LST_IND, ' '), NVL(CUST1.NI_AMOUNT, 0), NVL(CUST1.PI_AMOUNT, 0),
HEADING.short_heading_name,
NVL(CUST1.CUST_PROSPECT_CODE, 0),
NVL(CUST1.RETIREMENT_STATUS, ' '),
(SELECT nvl(sum(DIRECTORY_SUMMARY0.pi_amount),0)
FROM
DIRECTORY_ISSUE e, DIRECTORY_SUMMARY DIRECTORY_SUMMARY0,
ASSIGNMENT ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE DIR_CURR_ISSUE0
WHERE
DIRECTORY_SUMMARY0.customer_id = CUST1.customer_id
and DIRECTORY_SUMMARY0.product_code = DIR_CURR_ISSUE0.directory_code
and DIRECTORY_SUMMARY0.product_issue_num = DIR_CURR_ISSUE0.directory_issue_num
and DIRECTORY_SUMMARY0.PRODUCT_CODE = E.DIRECTORY_CODE
and DIRECTORY_SUMMARY0.PRODUCT_ISSUE_NUM = e.directory_issue_num
and e.directory_del_ind != :1
and DIRECTORY_SUMMARY0.canvass_code = ''
and DIRECTORY_SUMMARY0.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
and DIRECTORY_SUMMARY0.current_assign_id = ASSIGNMENT_C.assignment_id (+)
AND :2 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep))
,(CASE WHEN EXISTS( SELECT 1
FROM
DIR_CURR_ISSUE DC, DIRECTORY_SUMMARY DS
WHERE
CUST1.customer_id = DS.customer_id
AND DS.product_code = DC.directory_code
AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
AND DS.curr_special_flag = 20 and rownum = 1)
THEN 'Y'
ELSE 'N' END )
, (CASE WHEN EXISTS ( SELECT 1
FROM
ITEMS_IN_CLAIM IC, DIRECTORY_SUMMARY DIR1
WHERE
IC.customer_id = CUST1.customer_id
AND DIR1.customer_id = CUST1.customer_id
AND IC.PRODUCT = DIR1.product_code
AND IC.issue_number = DIR1.product_issue_num
AND IC.DELETE_INDICATOR ='N' )
THEN 'Y'
ELSE 'N' END ),
(CASE WHEN EXISTS ( SELECT 1
FROM
DIRECTORY_ISSUE DI, DIRECTORY_SUMMARY DIR1
WHERE
DIR1.customer_id = CUST1.customer_id
AND DIR1.product_code = DI.directory_code
AND DIR1.product_issue_num = DI.directory_issue_num
AND ADD_MONTHS(TO_DATE(:3,'YYYYMMDD'),1) > DI.TELCO_CLOSE_DATE
AND TO_DATE(:4,'YYYYMMDD') < DI.TELCO_CLOSE_DATE
AND DI.TELCO_CLOSE_DATE IS NOT NULL)
THEN 'Y'
ELSE 'N' END )
, NVL((select CONTACT.CONTACT_NAME from CONTACT
where CUST1.CONTACT_ID = CONTACT.CONTACT_ID(+)), ' ')
FROM LISTING LIST1, CUSTOMER CUST1, ADDRESS ADDR1, DIRECTORY_SUMMARY, TELCO, DIR_CURR_ISSUE, ASSIGNMENT, HEADING, ITEM_DIR
WHERE
CUST1.main_main_listing_id = LIST1.listing_id(+) AND
LIST1.last_version_ind(+) = :5 AND
LIST1.listed_address_id = ADDR1.address_id(+) AND
TELCO.TELCO_CODE = LIST1.TELCO_ID AND
DIRECTORY_SUMMARY.customer_id = CUST1.customer_id AND
DIRECTORY_SUMMARY.PRODUCT_CODE = DIR_CURR_ISSUE.DIRECTORY_CODE AND
DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM >= DIR_CURR_ISSUE.DIRECTORY_ISSUE_NUM AND
ASSIGNMENT.SALES_REP = :6 AND
CUST1.DOMINANT_HEADING = HEADING.HEADING_CODE (+)AND
NOT EXISTS
(SELECT 1 FROM DIRECTORY_SUMMARY DS, ASSIGNMENT ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE DC
WHERE DS.customer_id = CUST1.customer_id
AND DS.PRODUCT_CODE = DC.DIRECTORY_CODE
AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
AND DS.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
AND DS.current_assign_id = ASSIGNMENT_C.assignment_id (+)
AND :7 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep)
AND DS.RETIREMENT_STATUS!= :8 ) AND
DIRECTORY_SUMMARY.RETIREMENT_STATUS in (:9, :10) AND
CUST1.customer_id = ITEM_DIR.customer_id AND
DIRECTORY_SUMMARY.PRODUCT_CODE =ITEM_DIR.PRODUCT_CODE AND
DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM =ITEM_DIR.PRODUCT_ISSUE_NUM AND
ITEM_DIR.LAST_VERSION_IND = 'Y' AND
( ITEM_DIR.heading_code = :11) AND
rownum <= :12
AND DIRECTORY_SUMMARY.CURRENT_ASSIGN_ID = ASSIGNMENT.ASSIGNMENT_ID UNION SELECT
DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa, ' '),
NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,
NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '), NVL(LIST1.listed_tn_line_no, ' '),
NVL(ADDR1.house_no, ' '), NVL(ADDR1.house_no_suff, ' '), NVL(ADDR1.pre_directional, ' '),
NVL(ADDR1.street_name, ' '), NVL(ADDR1.post_directional, ' '), NVL(ADDR1.sub_location, ' '),
NVL(ADDR1.Community, ' '), NVL(ADDR1.zip_five, ' '), NVL(ADDR1.state_abbr_override, ' '),
LIST1.listing_status, NVL(CUST1.customer_type, ' '), NVL(CUST1.home_data_base, ' '),
NVL(CUST1.default_market_code, ' '),
NVL(TELCO.TELCO_CODE, ' '), NVL(TELCO.TELCO_NAME, ' '),
NVL(TO_CHAR(LIST1.establish_date,'YYYYMMDD'),' '), NVL((select 'Y' from query q, dir_curr_issue
where q.customer_id = cust1.customer_id
and q.query_status in (1,3,4)
and q.product_code = dir_curr_issue.directory_code
and q.product_issue_num >= dir_curr_issue.directory_issue_num
and rownum = 1), 'N' ) ,
NVL(CUST1.TELCO_CREDIT_STOP, ' '), NVL(CUST1.BARS_CREDIT_STOP, 0),
NVL(CUST1.EAST_CREDIT_STOP, ' '), NVL(CUST1.EARLY_CONT_CUST_IND, ' '),
NVL(LIST1.SOHO_LST_IND, ' '), NVL(CUST1.NI_AMOUNT, 0), NVL(CUST1.PI_AMOUNT, 0),
HEADING.short_heading_name,
NVL(CUST1.CUST_PROSPECT_CODE, 0),
NVL(CUST1.RETIREMENT_STATUS, ' '),
(SELECT nvl(sum(DIRECTORY_SUMMARY0.pi_amount),0)
FROM
DIRECTORY_ISSUE e, DIRECTORY_SUMMARY DIRECTORY_SUMMARY0,
ASSIGNMENT ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE DIR_CURR_ISSUE0
WHERE
DIRECTORY_SUMMARY0.customer_id = CUST1.customer_id
and DIRECTORY_SUMMARY0.product_code = DIR_CURR_ISSUE0.directory_code
and DIRECTORY_SUMMARY0.product_issue_num = DIR_CURR_ISSUE0.directory_issue_num
and DIRECTORY_SUMMARY0.PRODUCT_CODE = E.DIRECTORY_CODE
and DIRECTORY_SUMMARY0.PRODUCT_ISSUE_NUM = e.directory_issue_num
and e.directory_del_ind != :13
and DIRECTORY_SUMMARY0.canvass_code = ''
and DIRECTORY_SUMMARY0.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
and DIRECTORY_SUMMARY0.current_assign_id = ASSIGNMENT_C.assignment_id (+)
AND :14 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep))
,(CASE WHEN EXISTS( SELECT 1
FROM
DIR_CURR_ISSUE DC, DIRECTORY_SUMMARY DS
WHERE
CUST1.customer_id = DS.customer_id
AND DS.product_code = DC.directory_code
AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
AND DS.curr_special_flag = 20 and rownum = 1)
THEN 'Y'
ELSE 'N' END )
, (CASE WHEN EXISTS ( SELECT 1
FROM
ITEMS_IN_CLAIM IC, DIRECTORY_SUMMARY DIR1
WHERE
IC.customer_id = CUST1.customer_id
AND DIR1.customer_id = CUST1.customer_id
AND IC.PRODUCT = DIR1.product_code
AND IC.issue_number = DIR1.product_issue_num
AND IC.DELETE_INDICATOR ='N' )
THEN 'Y'
ELSE 'N' END ),
(CASE WHEN EXISTS ( SELECT 1
FROM
DIRECTORY_ISSUE DI, DIRECTORY_SUMMARY DIR1
WHERE
DIR1.customer_id = CUST1.customer_id
AND DIR1.product_code = DI.directory_code
AND DIR1.product_issue_num = DI.directory_issue_num
AND ADD_MONTHS(TO_DATE(:15,'YYYYMMDD'),1) > DI.TELCO_CLOSE_DATE
AND TO_DATE(:16,'YYYYMMDD') < DI.TELCO_CLOSE_DATE
AND DI.TELCO_CLOSE_DATE IS NOT NULL)
THEN 'Y'
ELSE 'N' END )
, NVL((select CONTACT.CONTACT_NAME from CONTACT
where CUST1.CONTACT_ID = CONTACT.CONTACT_ID(+)), ' ')
FROM LISTING LIST1, CUSTOMER CUST1, ADDRESS ADDR1, DIRECTORY_SUMMARY, TELCO, DIR_CURR_ISSUE, ASSIGNMENT, HEADING, ITEM_DIR
WHERE
CUST1.main_main_listing_id = LIST1.listing_id(+) AND
LIST1.last_version_ind(+) = :17 AND
LIST1.listed_address_id = ADDR1.address_id(+) AND
TELCO.TELCO_CODE = LIST1.TELCO_ID AND
DIRECTORY_SUMMARY.customer_id = CUST1.customer_id AND
DIRECTORY_SUMMARY.PRODUCT_CODE = DIR_CURR_ISSUE.DIRECTORY_CODE AND
DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM >= DIR_CURR_ISSUE.DIRECTORY_ISSUE_NUM AND
ASSIGNMENT.SALES_REP = :18 AND
CUST1.DOMINANT_HEADING = HEADING.HEADING_CODE (+)AND
NOT EXISTS
(SELECT 1 FROM DIRECTORY_SUMMARY DS, ASSIGNMENT ASSIGNMENT_C, assignment ASSIGNMENT_T,DIR_CURR_ISSUE DC
WHERE DS.customer_id = CUST1.customer_id
AND DS.PRODUCT_CODE = DC.DIRECTORY_CODE
AND DS.PRODUCT_ISSUE_NUM >= DC.DIRECTORY_ISSUE_NUM
AND DS.T_SPLIT_ASSIGN_ID = ASSIGNMENT_T.assignment_id (+)
AND DS.current_assign_id = ASSIGNMENT_C.assignment_id (+)
AND :19 in (ASSIGNMENT_C.sales_rep,ASSIGNMENT_T.sales_rep)
AND DS.RETIREMENT_STATUS!= :20 ) AND
DIRECTORY_SUMMARY.RETIREMENT_STATUS in (:21, :22) AND
CUST1.customer_id = ITEM_DIR.customer_id AND
DIRECTORY_SUMMARY.PRODUCT_CODE =ITEM_DIR.PRODUCT_CODE AND
DIRECTORY_SUMMARY.PRODUCT_ISSUE_NUM =ITEM_DIR.PRODUCT_ISSUE_NUM AND
ITEM_DIR.LAST_VERSION_IND = 'Y' AND
( ITEM_DIR.heading_code = :23) AND
rownum <= :24
AND DIRECTORY_SUMMARY.T_SPLIT_ASSIGN_ID is not NULL AND DIRECTORY_SUMMARY.T_SPLIT_ASSIGN_ID = ASSIGNMENT.ASSIGNMENT_ID
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri May 09 2003 - 11:21:44 CDT
