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, DECODE( wca.association_type,'CARRIER', wca.PARTY_ID ,NULL) PARTNER_ID, DECODE( wca.association_type,'CARRIER', carrierpartnerHZP.PARTNER_NAME ,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 MSC_AP_ORGANIZATIONS_V 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.SCHEDULE_USAGE,'CARRIER',8,2) ASSOCIATION_TYPE, wca.SCHEDULE_ID CALENDAR_CODE, wca.SCHEDULE_USAGE CALENDAR_TYPE, wca.party_id PARTNER_ID, CUSTOMER.PARTY_NUMBER PARTNER_NAME, NULL PARTNER_SITE_ID, NULL 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, MSC_AP_CARRIERS_V carrierpartnerHZP WHERE wca.ENABLED_FLAG ='Y' AND wca.ASSOCIATION_TYPE = 'CUSTOMER' AND CUSTOMER.PARTY_ID(+) = WCA.PARTY_ID AND carrierpartnerHZP.SR_TP_ID(+) = DECODE(WCA.SCHEDULE_USAGE,'CARRIER',WCA.CARRIER_USAGE_ID) UNION ALL SELECT DECODE(wca.SCHEDULE_USAGE,'CARRIER',11,6) ASSOCIATION_TYPE, wca.SCHEDULE_ID CALENDAR_CODE, wca.SCHEDULE_USAGE CALENDAR_TYPE, wca.party_id PARTNER_ID, custHZP.PARTY_NUMBER PARTNER_NAME, wca.party_site_id PARTNER_SITE_ID, custsitesHPS.PARTY_SITE_NUMBER PARTNER_SITE_CODE, NULL ORGANIZATION_ID, NULL ORGANIZATION_CODE, carrierpartnerHZP.SR_TP_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 custHZP, HZ_PARTY_SITES CUSTSITESHPS, MSC_AP_CARRIERS_V CARRIERPARTNERHZP WHERE wca.ENABLED_FLAG ='Y' AND wca.ASSOCIATION_TYPE = 'CUSTOMER_SITE' and custhzp.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)