eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CSP_STOCKING_SITE_DETAILS_V

Source

View Text - Preformatted


SELECT cpp.organization_id , mp.organization_code, haou.name , cpp.secondary_inventory , haou.location_id , hz_format_pub.format_address(cpp.hz_location_id), cpp.stocking_site_type, flvv.meaning stocking_site, hz_timezone_pub.convert_datetime( a.client_timezone_id, a.server_timezone_id, hz_timezone_pub.convert_datetime( a.server_timezone_id, NVL(cpp.timezone_id,a.server_timezone_id), sysdate)) local_time, DECODE(SIGN(hz_timezone_pub.convert_datetime( a.server_timezone_id, cpp.timezone_id, sysdate)- DECODE(cpp.calendar_id,NULL,TRUNC(sysdate-3),cocv.start_time))+ SIGN(DECODE(cpp.calendar_id,NULL,TRUNC(sysdate+3),cocv.end_time)- hz_timezone_pub.convert_datetime( a.server_timezone_id, cpp.timezone_id, sysdate)),2,b.wh_open,b.wh_closed) , DECODE(cpp.calendar_id,NULL,TRUNC(sysdate),cocv.start_time), DECODE(cpp.calendar_id,NULL,TRUNC(sysdate+1)-0.00001,cocv.end_time), DECODE(cpp.calendar_id,NULL,TRUNC(sysdate+1),cnov.start_time), DECODE(cpp.calendar_id,NULL,TRUNC(sysdate+2)-0.00001,cnov.end_time), htv.name , cpp.managed_by , cpp.contact_name , cpp.contact_phone , cpp.ah_contact_name , cpp.ah_contact_phone , cpp.special_instructions, cpp.hz_location_id FROM csp_planning_parameters cpp, mtl_parameters mp, fnd_lookup_values_vl flvv, csp_open_closed_v cocv, csp_next_open_v cnov, hr_all_organization_units haou, hz_timezones_vl htv, (SELECT FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') server_timezone_id, FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID') client_timezone_id FROM dual ) a, (SELECT flvv2.meaning wh_open, flvv3.meaning wh_closed FROM fnd_lookup_values_vl flvv2, fnd_lookup_values_vl flvv3 WHERE flvv2.lookup_type = 'OPEN_CLOSED' AND flvv2.lookup_code = 'OPEN' AND flvv3.lookup_type = 'OPEN_CLOSED' AND flvv3.lookup_code = 'CLOSED' ) b WHERE mp.organization_id = cpp.organization_id AND haou.organization_id = mp.organization_id AND cpp.stocking_site_type IN ('MANNED','UNMANNED','DEDICATED') AND flvv.lookup_type = 'CSP_STOCKING_SITE_TYPE' AND flvv.lookup_code = cpp.stocking_site_type AND cocv.calendar_id(+) = cpp.calendar_id AND cnov.calendar_id(+) = cpp.calendar_id AND htv.timezone_id(+) = cpp.timezone_id UNION ALL SELECT cpp.organization_id, mp.organization_code, haou.name, cpp.secondary_inventory, cpp.hz_location_id, hz_format_pub.format_address(cpp.hz_location_id), cpp.stocking_site_type, d.meaning, hz_timezone_pub.convert_datetime( a.client_timezone_id, a.server_timezone_id, hz_timezone_pub.convert_datetime( a.server_timezone_id, NVL(jrrev.time_zone,NVL(jrrev.time_zone,a.server_timezone_id)), sysdate)), DECODE(SIGN(hz_timezone_pub.convert_datetime( a.server_timezone_id, NVL(jrrev.time_zone,NVL(jrrev.time_zone,a.server_timezone_id)), sysdate)-csoc.start_date_time)+ SIGN(csoc.end_date_time- hz_timezone_pub.convert_datetime( a.server_timezone_id, NVL(jrrev.time_zone,NVL(jrrev.time_zone,a.server_timezone_id)), sysdate)),2,b.wh_open,b.wh_closed), csoc.start_date_time, csoc.end_date_time, c.start_date_time, c.end_date_time, htv.name, NULL, jrrev.source_name, jrrev.source_phone, NULL, NULL, NULL, NULL FROM csp_planning_parameters cpp, mtl_parameters mp, (SELECT meaning FROM fnd_lookup_values_vl WHERE lookup_type = 'CSP_STOCKING_SITE_TYPE' AND lookup_code = 'TECHNICIAN' ) d, jtf_rs_resource_extns jrrev, csp_sec_inventories csi, cac_sr_object_capacity csoc, hz_timezones_vl htv, hr_all_organization_units haou, (SELECT FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') server_timezone_id, FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID') client_timezone_id FROM dual ) a, (SELECT flvv2.meaning wh_open, flvv3.meaning wh_closed FROM fnd_lookup_values_vl flvv2, fnd_lookup_values_vl flvv3 WHERE flvv2.lookup_type = 'OPEN_CLOSED' AND flvv2.lookup_code = 'OPEN' AND flvv3.lookup_type = 'OPEN_CLOSED' AND flvv3.lookup_code = 'CLOSED' ) b, (SELECT csoc.object_type object_type, csoc.object_id object_id, MIN(csoc.start_date_time) start_date_time, MIN(csoc.end_date_time) end_date_time FROM cac_sr_object_capacity csoc WHERE csoc.start_date_time > TRUNC(sysdate+1) GROUP BY csoc.object_type, csoc.object_id ) c WHERE mp.organization_id = cpp.organization_id AND cpp.stocking_site_type IN ('TECHNICIAN') AND csi.organization_id = cpp.organization_id AND haou.organization_id = cpp.organization_id AND csi.secondary_inventory_name = cpp.secondary_inventory AND 'RS_' ||jrrev.category = csi.owner_resource_type AND jrrev.resource_id = csi.owner_resource_id AND csoc.object_type(+) = csi.owner_resource_type AND csoc.object_id(+) = csi.owner_resource_id AND c.object_type(+) = csi.owner_resource_type AND c.object_id(+) = csi.owner_resource_id AND TRUNC(sysdate) BETWEEN TRUNC(csoc.start_date_time(+)) AND TRUNC(csoc.end_date_time(+)) AND htv.timezone_id(+) = jrrev.time_zone

View Text - HTML Formatted

SELECT CPP.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HAOU.NAME
, CPP.SECONDARY_INVENTORY
, HAOU.LOCATION_ID
, HZ_FORMAT_PUB.FORMAT_ADDRESS(CPP.HZ_LOCATION_ID)
, CPP.STOCKING_SITE_TYPE
, FLVV.MEANING STOCKING_SITE
, HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.CLIENT_TIMEZONE_ID
, A.SERVER_TIMEZONE_ID
, HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.SERVER_TIMEZONE_ID
, NVL(CPP.TIMEZONE_ID
, A.SERVER_TIMEZONE_ID)
, SYSDATE)) LOCAL_TIME
, DECODE(SIGN(HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.SERVER_TIMEZONE_ID
, CPP.TIMEZONE_ID
, SYSDATE)- DECODE(CPP.CALENDAR_ID
, NULL
, TRUNC(SYSDATE-3)
, COCV.START_TIME))+ SIGN(DECODE(CPP.CALENDAR_ID
, NULL
, TRUNC(SYSDATE+3)
, COCV.END_TIME)- HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.SERVER_TIMEZONE_ID
, CPP.TIMEZONE_ID
, SYSDATE))
, 2
, B.WH_OPEN
, B.WH_CLOSED)
, DECODE(CPP.CALENDAR_ID
, NULL
, TRUNC(SYSDATE)
, COCV.START_TIME)
, DECODE(CPP.CALENDAR_ID
, NULL
, TRUNC(SYSDATE+1)-0.00001
, COCV.END_TIME)
, DECODE(CPP.CALENDAR_ID
, NULL
, TRUNC(SYSDATE+1)
, CNOV.START_TIME)
, DECODE(CPP.CALENDAR_ID
, NULL
, TRUNC(SYSDATE+2)-0.00001
, CNOV.END_TIME)
, HTV.NAME
, CPP.MANAGED_BY
, CPP.CONTACT_NAME
, CPP.CONTACT_PHONE
, CPP.AH_CONTACT_NAME
, CPP.AH_CONTACT_PHONE
, CPP.SPECIAL_INSTRUCTIONS
, CPP.HZ_LOCATION_ID
FROM CSP_PLANNING_PARAMETERS CPP
, MTL_PARAMETERS MP
, FND_LOOKUP_VALUES_VL FLVV
, CSP_OPEN_CLOSED_V COCV
, CSP_NEXT_OPEN_V CNOV
, HR_ALL_ORGANIZATION_UNITS HAOU
, HZ_TIMEZONES_VL HTV
, (SELECT FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') SERVER_TIMEZONE_ID
, FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID') CLIENT_TIMEZONE_ID
FROM DUAL ) A
, (SELECT FLVV2.MEANING WH_OPEN
, FLVV3.MEANING WH_CLOSED
FROM FND_LOOKUP_VALUES_VL FLVV2
, FND_LOOKUP_VALUES_VL FLVV3
WHERE FLVV2.LOOKUP_TYPE = 'OPEN_CLOSED'
AND FLVV2.LOOKUP_CODE = 'OPEN'
AND FLVV3.LOOKUP_TYPE = 'OPEN_CLOSED'
AND FLVV3.LOOKUP_CODE = 'CLOSED' ) B
WHERE MP.ORGANIZATION_ID = CPP.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND CPP.STOCKING_SITE_TYPE IN ('MANNED'
, 'UNMANNED'
, 'DEDICATED')
AND FLVV.LOOKUP_TYPE = 'CSP_STOCKING_SITE_TYPE'
AND FLVV.LOOKUP_CODE = CPP.STOCKING_SITE_TYPE
AND COCV.CALENDAR_ID(+) = CPP.CALENDAR_ID
AND CNOV.CALENDAR_ID(+) = CPP.CALENDAR_ID
AND HTV.TIMEZONE_ID(+) = CPP.TIMEZONE_ID UNION ALL SELECT CPP.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HAOU.NAME
, CPP.SECONDARY_INVENTORY
, CPP.HZ_LOCATION_ID
, HZ_FORMAT_PUB.FORMAT_ADDRESS(CPP.HZ_LOCATION_ID)
, CPP.STOCKING_SITE_TYPE
, D.MEANING
, HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.CLIENT_TIMEZONE_ID
, A.SERVER_TIMEZONE_ID
, HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.SERVER_TIMEZONE_ID
, NVL(JRREV.TIME_ZONE
, NVL(JRREV.TIME_ZONE
, A.SERVER_TIMEZONE_ID))
, SYSDATE))
, DECODE(SIGN(HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.SERVER_TIMEZONE_ID
, NVL(JRREV.TIME_ZONE
, NVL(JRREV.TIME_ZONE
, A.SERVER_TIMEZONE_ID))
, SYSDATE)-CSOC.START_DATE_TIME)+ SIGN(CSOC.END_DATE_TIME- HZ_TIMEZONE_PUB.CONVERT_DATETIME( A.SERVER_TIMEZONE_ID
, NVL(JRREV.TIME_ZONE
, NVL(JRREV.TIME_ZONE
, A.SERVER_TIMEZONE_ID))
, SYSDATE))
, 2
, B.WH_OPEN
, B.WH_CLOSED)
, CSOC.START_DATE_TIME
, CSOC.END_DATE_TIME
, C.START_DATE_TIME
, C.END_DATE_TIME
, HTV.NAME
, NULL
, JRREV.SOURCE_NAME
, JRREV.SOURCE_PHONE
, NULL
, NULL
, NULL
, NULL
FROM CSP_PLANNING_PARAMETERS CPP
, MTL_PARAMETERS MP
, (SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'CSP_STOCKING_SITE_TYPE'
AND LOOKUP_CODE = 'TECHNICIAN' ) D
, JTF_RS_RESOURCE_EXTNS JRREV
, CSP_SEC_INVENTORIES CSI
, CAC_SR_OBJECT_CAPACITY CSOC
, HZ_TIMEZONES_VL HTV
, HR_ALL_ORGANIZATION_UNITS HAOU
, (SELECT FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') SERVER_TIMEZONE_ID
, FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID') CLIENT_TIMEZONE_ID
FROM DUAL ) A
, (SELECT FLVV2.MEANING WH_OPEN
, FLVV3.MEANING WH_CLOSED
FROM FND_LOOKUP_VALUES_VL FLVV2
, FND_LOOKUP_VALUES_VL FLVV3
WHERE FLVV2.LOOKUP_TYPE = 'OPEN_CLOSED'
AND FLVV2.LOOKUP_CODE = 'OPEN'
AND FLVV3.LOOKUP_TYPE = 'OPEN_CLOSED'
AND FLVV3.LOOKUP_CODE = 'CLOSED' ) B
, (SELECT CSOC.OBJECT_TYPE OBJECT_TYPE
, CSOC.OBJECT_ID OBJECT_ID
, MIN(CSOC.START_DATE_TIME) START_DATE_TIME
, MIN(CSOC.END_DATE_TIME) END_DATE_TIME
FROM CAC_SR_OBJECT_CAPACITY CSOC
WHERE CSOC.START_DATE_TIME > TRUNC(SYSDATE+1) GROUP BY CSOC.OBJECT_TYPE
, CSOC.OBJECT_ID ) C
WHERE MP.ORGANIZATION_ID = CPP.ORGANIZATION_ID
AND CPP.STOCKING_SITE_TYPE IN ('TECHNICIAN')
AND CSI.ORGANIZATION_ID = CPP.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = CPP.ORGANIZATION_ID
AND CSI.SECONDARY_INVENTORY_NAME = CPP.SECONDARY_INVENTORY
AND 'RS_' ||JRREV.CATEGORY = CSI.OWNER_RESOURCE_TYPE
AND JRREV.RESOURCE_ID = CSI.OWNER_RESOURCE_ID
AND CSOC.OBJECT_TYPE(+) = CSI.OWNER_RESOURCE_TYPE
AND CSOC.OBJECT_ID(+) = CSI.OWNER_RESOURCE_ID
AND C.OBJECT_TYPE(+) = CSI.OWNER_RESOURCE_TYPE
AND C.OBJECT_ID(+) = CSI.OWNER_RESOURCE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(CSOC.START_DATE_TIME(+))
AND TRUNC(CSOC.END_DATE_TIME(+))
AND HTV.TIMEZONE_ID(+) = JRREV.TIME_ZONE