MSC_AP_CAL_ASSIGNMENTS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
ASSOCIATION_TYPE CALENDAR_CODE CALENDAR_TYPE PARTNER_ID PARTNER_NAME PARTNER_SITE_ID PARTNER_SITE_CODE ORGANIZATION_ID ORGANIZATION_CODE CARRIER_PARTNER_ID CARRIER_PARTNER_CODE MODE_OF_TRANSPORT SERVICE_LEVEL SHIP_METHOD_CODE PARTNER_TYPE WCA_ROWID

Query

SQL_Statement

SELECT DECODE(wca.association_type,'ORGANIZATION', DECODE(wca.SCHEDULE_USAGE,'CARRIER',9,3), 'CARRIER',4) ASSOCIATION_TYPE, wca.SCHEDULE_ID CALENDAR_CODE, wca.SCHEDULE_USAGE CALENDAR_TYPE, NULL PARTNER_ID, NULL PARTNER_NAME, null PARTNER_SITE_ID, null PARTNER_SITE_CODE, DECODE(wca.association_type,'ORGANIZATION', wca.ORGANIZATION_ID,NULL) ORGANIZATION_ID, ( SELECT ORGS.ORGANIZATION_CODE FROM INV_ORG_PARAMETERS ORGS WHERE orgs.organization_id(+) = wca.ORGANIZATION_ID AND rownum =1) AS ORGANIZATION_CODE, DECODE( wca.association_type,'CARRIER', wca.party_id,decode(wca.schedule_usage,'CARRIER',wca.carrier_usage_id)) carrier_partner_id, carrierpartnerHZP.PARTNER_NAME CARRIER_PARTNER_CODE, DECODE( wca.association_type,'CARRIER', wocs.MODE_OF_TRANSPORT,DECODE(wca.SCHEDULE_USAGE,'CARRIER',wocs.MODE_OF_TRANSPORT,NULL)) MODE_OF_TRANSPORT, DECODE( wca.association_type,'CARRIER', wocs. SERVICE_LEVEL,DECODE(wca.SCHEDULE_USAGE,'CARRIER',wocs. SERVICE_LEVEL,NULL)) SERVICE_LEVEL, NULL SHIP_METHOD_CODE, DECODE( wca.association_type,'ORGANIZATION',3, 'CARRIER',4, 'CARRIER_SITE',4) PARTNER_TYPE, wca.rowid wca_rowid FROM WSH_CALENDAR_ASSIGNMENTS wca, (SELECT * FROM WSH_ORG_CARRIER_SERVICES WHERE ORGANIZATION_ID IS NULL AND ENABLED_FLAG = 'Y' ) wocs, MSC_AP_CARRIERS_V carrierpartnerHZP WHERE wca.party_id = wocs.carrier_id(+) AND wca.ENABLED_FLAG ='Y' AND wca.ASSOCIATION_TYPE IN ('ORGANIZATION','CARRIER') AND carrierpartnerHZP.SR_TP_ID(+) = DECODE( wca.association_type,'CARRIER', wca.PARTY_ID,DECODE(wca.SCHEDULE_USAGE,'CARRIER',wca.carrier_usage_id, null)) UNION ALL SELECT DECODE(wca.association_type,'SUPPLIER', DECODE(wca.SCHEDULE_USAGE,'CARRIER',7,1), 'SUPPLIER_SITE', DECODE(wca.SCHEDULE_USAGE,'CARRIER',10,5) ) ASSOCIATION_TYPE, wca.SCHEDULE_ID CALENDAR_CODE, wca.SCHEDULE_USAGE CALENDAR_TYPE, ps.VENDOR_ID PARTNER_ID, supHZP.PARTNER_NAME PARTNER_NAME, DECODE(wca.ASSOCIATION_TYPE,'SUPPLIER_SITE',pss.VENDOR_SITE_ID) PARTNER_SITE_ID, pss.vendor_site_code PARTNER_SITE_CODE, NULL ORGANIZATION_ID, NULL ORGANIZATION_CODE, DECODE(wca.ASSOCIATION_TYPE,'SUPPLIER',DECODE(wca.SCHEDULE_USAGE,'CARRIER',wca.carrier_usage_id, NULL), 'SUPPLIER_SITE', (CARRIERPARTNERHZP1.SR_TP_ID),NULL ) CARRIER_PARTNER_ID, DECODE(WCA.ASSOCIATION_TYPE,'SUPPLIER',DECODE(WCA.SCHEDULE_USAGE,'CARRIER',CARRIERPARTNERHZP1.PARTNER_NAME, NULL), 'SUPPLIER_SITE', carrierpartnerHZP1.PARTNER_NAME,NULL ) CARRIER_PARTNER_CODE, NULL MODE_OF_TRANSPORT, NULL SERVICE_LEVEL, NULL SHIP_METHOD_CODE, 1 PARTNER_TYPE, wca.rowid wca_rowid FROM WSH_CALENDAR_ASSIGNMENTS wca, poz_suppliers ps, POZ_SUPPLIER_SITES_ALL_M pss, MSC_AP_SUPPLIERS_V suphzp, MSC_AP_CARRIERS_V CARRIERPARTNERHZP1 WHERE wca.party_id = ps.party_id AND wca.party_site_id = pss.party_site_id(+) AND wca.ENABLED_FLAG ='Y' AND wca.ASSOCIATION_TYPE IN ('SUPPLIER_SITE','SUPPLIER') AND supHZP.PARTY_ID(+) = wca.PARTY_ID AND CARRIERPARTNERHZP1.SR_TP_ID(+) = DECODE(WCA.SCHEDULE_USAGE,'CARRIER',WCA.CARRIER_USAGE_ID) UNION ALL SELECT DECODE(wca.ASSOCIATION_TYPE, 'CUSTOMER', DECODE(wca.SCHEDULE_USAGE,'CARRIER',8,2), 'CUSTOMER_SITE', DECODE(wca.SCHEDULE_USAGE,'CARRIER',11,6)) ASSOCIATION_TYPE, wca.SCHEDULE_ID CALENDAR_CODE, wca.SCHEDULE_USAGE CALENDAR_TYPE, wca.party_id PARTNER_ID, CUSTOMER.PARTY_NUMBER PARTNER_NAME, DECODE(wca.ASSOCIATION_TYPE, 'CUSTOMER_SITE', wca.party_site_id) PARTNER_SITE_ID, DECODE(wca.ASSOCIATION_TYPE, 'CUSTOMER_SITE', custsitesHPS.PARTY_SITE_NUMBER) PARTNER_SITE_CODE, NULL ORGANIZATION_ID, NULL ORGANIZATION_CODE, DECODE(WCA.SCHEDULE_USAGE,'CARRIER',WCA.CARRIER_USAGE_ID) CARRIER_PARTNER_ID, carrierpartnerHZP.PARTNER_NAME CARRIER_PARTNER_CODE, NULL MODE_OF_TRANSPORT, NULL SERVICE_LEVEL, NULL SHIP_METHOD_CODE, 2 PARTNER_TYPE, wca.rowid wca_rowid FROM WSH_CALENDAR_ASSIGNMENTS wca, HZ_PARTIES CUSTOMER, HZ_PARTY_SITES CUSTSITESHPS, MSC_AP_CARRIERS_V carrierpartnerHZP WHERE wca.ENABLED_FLAG = 'Y' AND wca.ASSOCIATION_TYPE IN ('CUSTOMER', 'CUSTOMER_SITE') AND CUSTOMER.PARTY_ID(+) = WCA.PARTY_ID AND CUSTSITESHPS.PARTY_SITE_ID(+) = WCA.PARTY_SITE_ID AND carrierpartnerHZP.SR_TP_ID(+) = DECODE(WCA.SCHEDULE_USAGE,'CARRIER',WCA.CARRIER_USAGE_ID)