MSC_ATP_CUSTOMERS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
PARTY_ID PARTY_SITE_ID LOCATION_ID GEOGRAPHY_ID SITE_CALENDAR_CODE CUST_CALENDAR_CODE

Name

PARTY_ID

PARTY_SITE_ID

LOCATION_ID

GEOGRAPHY_ID

SITE_CALENDAR_CODE

CUST_CALENDAR_CODE

Query

SQL_Statement
SELECT party_id, party_site_id, location_id, (SELECT mrl.geography_id FROM HZ_GEO_NAME_REFERENCES mrl WHERE mrl.location_id = party_view.location_id AND mrl.location_table_name = 'HZ_LOCATIONS' AND mrl.level_number = (SELECT MAX(level_number) FROM HZ_GEO_NAME_REFERENCES mrl1 WHERE mrl1.location_table_name = 'HZ_LOCATIONS' AND mrl1.location_id =mrl.location_id ) AND ROWNUM =1 ) geography_id, DECODE(site_cal.calendar_code,NULL,NULL,site_cal.calendar_code) AS site_calendar_code, DECODE(cust_cal.calendar_code,NULL,NULL,cust_cal.calendar_code) AS cust_calendar_code FROM (SELECT party_id, party_site_id, location_id FROM hz_party_sites PARTY_SITE WHERE exists (select 1 from hz_cust_accounts where hz_cust_accounts.party_id = PARTY_SITE.party_id) ) party_view, msc_calendar_assignments site_cal, msc_calendar_assignments cust_cal WHERE party_view.party_id = site_cal.partner_id(+) AND party_view.party_site_id = site_cal.partner_site_id(+) AND site_cal.partner_type(+) = 2 AND site_cal.calendar_type(+) = 'RECEIVING' AND site_cal.association_type(+) = 6 AND party_view.party_id = cust_cal.partner_id(+) AND cust_cal.partner_type(+) = 2 AND cust_cal.calendar_type(+) = 'RECEIVING' AND cust_cal.association_type(+) = 2

SQL_Statement

SELECT party_id,

party_site_id,

location_id,

(SELECT mrl.geography_id

FROM HZ_GEO_NAME_REFERENCES mrl

WHERE mrl.location_id = party_view.location_id

AND mrl.location_table_name = 'HZ_LOCATIONS'

AND mrl.level_number =

(SELECT MAX(level_number)

FROM HZ_GEO_NAME_REFERENCES mrl1

WHERE mrl1.location_table_name = 'HZ_LOCATIONS'

AND mrl1.location_id =mrl.location_id

)

AND ROWNUM =1

) geography_id,

DECODE(site_cal.calendar_code,NULL,NULL,site_cal.calendar_code) AS site_calendar_code,

DECODE(cust_cal.calendar_code,NULL,NULL,cust_cal.calendar_code) AS cust_calendar_code

FROM

(SELECT

party_id,

party_site_id,

location_id

FROM hz_party_sites PARTY_SITE

WHERE exists

(select 1 from hz_cust_accounts where hz_cust_accounts.party_id = PARTY_SITE.party_id)

) party_view,

msc_calendar_assignments site_cal,

msc_calendar_assignments cust_cal

WHERE party_view.party_id = site_cal.partner_id(+)

AND party_view.party_site_id = site_cal.partner_site_id(+)

AND site_cal.partner_type(+) = 2

AND site_cal.calendar_type(+) = 'RECEIVING'

AND site_cal.association_type(+) = 6

AND party_view.party_id = cust_cal.partner_id(+)

AND cust_cal.partner_type(+) = 2

AND cust_cal.calendar_type(+) = 'RECEIVING'

AND cust_cal.association_type(+) = 2