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.

APPS.MSC_WS_SNO_PUBLISH dependencies on MSC_INT_ASCP_EXCEPTION_DETAILS

Line 51: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,

47: LAST_UPDATED_BY,
48: CREATION_DATE,
49: CREATED_BY)
50: SELECT
51: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
52: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
53: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
54: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
55: DepartmentID,

Line 52: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,

48: CREATION_DATE,
49: CREATED_BY)
50: SELECT
51: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
52: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
53: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
54: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
55: DepartmentID,
56: -1,

Line 53: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,

49: CREATED_BY)
50: SELECT
51: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
52: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
53: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
54: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
55: DepartmentID,
56: -1,
57: MSC_EXCEPTION_DETAILS_S.NEXTVAL,

Line 54: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,

50: SELECT
51: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
52: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
53: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
54: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
55: DepartmentID,
56: -1,
57: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
58: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

Line 58: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

54: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
55: DepartmentID,
56: -1,
57: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
58: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
59: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
60: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
61: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,

Line 59: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,

55: DepartmentID,
56: -1,
57: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
58: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
59: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
60: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
61: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'

Line 60: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,

56: -1,
57: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
58: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
59: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
60: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
61: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

Line 63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'

59: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
60: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
61: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

Line 64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

60: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
61: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

Line 65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date

61: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
69: AND msc_demands.origination_type=81

Line 66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id

62: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
69: AND msc_demands.origination_type=81
70: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1

Line 67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

63: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
69: AND msc_demands.origination_type=81
70: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
71: then

Line 68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

64: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
65: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
69: AND msc_demands.origination_type=81
70: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
71: then
72: msc_demands.demand_id

Line 70: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1

66: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
67: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
68: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
69: AND msc_demands.origination_type=81
70: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
71: then
72: msc_demands.demand_id
73: else
74: null

Line 76: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )

72: msc_demands.demand_id
73: else
74: null
75: end,
76: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
77: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
78: ELSE
79: NULL
80: END,

Line 77: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)

73: else
74: null
75: end,
76: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
77: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
78: ELSE
79: NULL
80: END,
81: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

Line 81: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

77: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
78: ELSE
79: NULL
80: END,
81: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
82: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
83: ELSE
84: NULL
85: END,

Line 82: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID

78: ELSE
79: NULL
80: END,
81: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
82: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
83: ELSE
84: NULL
85: END,
86: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

Line 86: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

82: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
83: ELSE
84: NULL
85: END,
86: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
87: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
88: ELSE
89: NULL
90: END,

Line 87: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID

83: ELSE
84: NULL
85: END,
86: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
87: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
88: ELSE
89: NULL
90: END,
91: SYSDATE,

Line 95: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS

91: SYSDATE,
92: '-1',
93: SYSDATE,
94: '-1'
95: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id

Line 96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'

92: '-1',
93: SYSDATE,
94: '-1'
95: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

Line 97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

93: SYSDATE,
94: '-1'
95: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

Line 98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date

94: '-1'
95: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
102: AND msc_demands.origination_type=81

Line 99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id

95: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
102: AND msc_demands.origination_type=81
103: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;

Line 100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

96: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
102: AND msc_demands.origination_type=81
103: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
104: EXCEPTION WHEN others THEN

Line 101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

97: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
98: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
102: AND msc_demands.origination_type=81
103: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
104: EXCEPTION WHEN others THEN
105: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001';

Line 103: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;

99: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
100: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
101: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
102: AND msc_demands.origination_type=81
103: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
104: EXCEPTION WHEN others THEN
105: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001';
106: raise;
107: END;

Line 131: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,

127: LAST_UPDATED_BY,
128: CREATION_DATE,
129: CREATED_BY)
130: SELECT
131: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
132: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
133: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
134: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
135: DepartmentID,

Line 132: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,

128: CREATION_DATE,
129: CREATED_BY)
130: SELECT
131: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
132: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
133: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
134: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
135: DepartmentID,
136: -1,

Line 133: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,

129: CREATED_BY)
130: SELECT
131: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
132: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
133: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
134: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
135: DepartmentID,
136: -1,
137: MSC_EXCEPTION_DETAILS_S.NEXTVAL,

Line 134: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,

130: SELECT
131: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
132: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
133: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
134: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
135: DepartmentID,
136: -1,
137: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
138: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

Line 138: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

134: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
135: DepartmentID,
136: -1,
137: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
138: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
139: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
140: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
141: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,

Line 139: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,

135: DepartmentID,
136: -1,
137: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
138: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
139: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
140: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
141: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'

Line 140: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,

136: -1,
137: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
138: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
139: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
140: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
141: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

Line 143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'

139: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
140: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
141: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

Line 144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

140: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
141: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

Line 145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date

141: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
149: AND msc_demands.origination_type=81

Line 146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1

142: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
149: AND msc_demands.origination_type=81
150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1

Line 147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

143: case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
149: AND msc_demands.origination_type=81
150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
151: -- CUSTOMER_SITE_ID:

Line 148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

144: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
145: AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
149: AND msc_demands.origination_type=81
150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
151: -- CUSTOMER_SITE_ID:
152: AND (

Line 150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1

146: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
147: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
148: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
149: AND msc_demands.origination_type=81
150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
151: -- CUSTOMER_SITE_ID:
152: AND (
153: ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

Line 153: ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )

149: AND msc_demands.origination_type=81
150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
151: -- CUSTOMER_SITE_ID:
152: AND (
153: ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
155: AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
156: -- CUSTOMER_ID:
157: AND (

Line 154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

150: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
151: -- CUSTOMER_SITE_ID:
152: AND (
153: ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
155: AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
156: -- CUSTOMER_ID:
157: AND (
158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )

Line 155: AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )

151: -- CUSTOMER_SITE_ID:
152: AND (
153: ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
155: AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
156: -- CUSTOMER_ID:
157: AND (
158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
159: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

Line 158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )

154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
155: AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
156: -- CUSTOMER_ID:
157: AND (
158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
159: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
160: AND ( SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) = msc_demands.CUSTOMER_ID
161: )
162: then

Line 159: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

155: AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
156: -- CUSTOMER_ID:
157: AND (
158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
159: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
160: AND ( SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) = msc_demands.CUSTOMER_ID
161: )
162: then
163: msc_demands.demand_id

Line 160: AND ( SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) = msc_demands.CUSTOMER_ID

156: -- CUSTOMER_ID:
157: AND (
158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
159: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
160: AND ( SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) = msc_demands.CUSTOMER_ID
161: )
162: then
163: msc_demands.demand_id
164: else

Line 167: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )

163: msc_demands.demand_id
164: else
165: null
166: end,
167: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
168: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
169: ELSE
170: NULL
171: END,

Line 168: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)

164: else
165: null
166: end,
167: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
168: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
169: ELSE
170: NULL
171: END,
172: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

Line 172: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

168: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
169: ELSE
170: NULL
171: END,
172: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
173: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
174: ELSE
175: NULL
176: END,

Line 173: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID

169: ELSE
170: NULL
171: END,
172: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
173: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
174: ELSE
175: NULL
176: END,
177: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

Line 177: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )

173: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
174: ELSE
175: NULL
176: END,
177: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
178: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
179: ELSE
180: NULL
181: END,

Line 178: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID

174: ELSE
175: NULL
176: END,
177: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) is not null )
178: THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
179: ELSE
180: NULL
181: END,
182: SYSDATE,

Line 186: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS

182: SYSDATE,
183: '-1',
184: SYSDATE,
185: '-1'
186: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1

Line 187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'

183: '-1',
184: SYSDATE,
185: '-1'
186: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

Line 188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

184: SYSDATE,
185: '-1'
186: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

Line 189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date

185: '-1'
186: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
193: AND msc_demands.origination_type=81

Line 190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1

186: FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
193: AND msc_demands.origination_type=81
194: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;

Line 191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id

187: where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
193: AND msc_demands.origination_type=81
194: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;
195: EXCEPTION WHEN others THEN

Line 192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id

188: AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
189: AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
193: AND msc_demands.origination_type=81
194: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;
195: EXCEPTION WHEN others THEN
196: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001';

Line 194: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;

190: AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
191: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
192: AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
193: AND msc_demands.origination_type=81
194: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;
195: EXCEPTION WHEN others THEN
196: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001';
197: raise;
198: END;

Line 223: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,

219: LAST_UPDATED_BY,
220: CREATION_DATE,
221: CREATED_BY)
222: SELECT
223: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
224: MSC_PLANS.sr_instance_id,
225: MSC_PLANS.organization_id,
226: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
227: -1,

Line 226: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,

222: SELECT
223: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
224: MSC_PLANS.sr_instance_id,
225: MSC_PLANS.organization_id,
226: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
227: -1,
228: -1,
229: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
230: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

Line 230: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

226: MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
227: -1,
228: -1,
229: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
230: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
231: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,

Line 231: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,

227: -1,
228: -1,
229: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
230: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
231: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'

Line 232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,

228: -1,
229: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
230: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
231: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

Line 235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'

231: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID

232: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
233: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id

Line 238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id

234: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400

Line 239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

235: case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1

Line 240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id

236: AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1

Line 241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID

237: AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then

Line 242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400

238: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
239: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then
246: msc_supplies.transaction_id

Line 244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1

240: AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
241: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
242: AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
243: AND msc_supplies.order_type=1
244: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
245: then
246: msc_supplies.transaction_id
247: else
248: null

Line 250: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )

246: msc_supplies.transaction_id
247: else
248: null
249: end,
250: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
251: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
252: ELSE
253: NULL
254: END,

Line 251: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)

247: else
248: null
249: end,
250: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
251: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
252: ELSE
253: NULL
254: END,
255: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id, --SupplierSiteId

Line 255: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id, --SupplierSiteId

251: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
252: ELSE
253: NULL
254: END,
255: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id, --SupplierSiteId
256: MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId, --SupplierId
257: SYSDATE,
258: '-1',
259: SYSDATE,

Line 256: MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId, --SupplierId

252: ELSE
253: NULL
254: END,
255: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id, --SupplierSiteId
256: MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId, --SupplierId
257: SYSDATE,
258: '-1',
259: SYSDATE,
260: '-1'

Line 261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS

257: SYSDATE,
258: '-1',
259: SYSDATE,
260: '-1'
261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID

Line 262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'

258: '-1',
259: SYSDATE,
260: '-1'
261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records

Line 263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar

259: SYSDATE,
260: '-1'
261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id

Line 264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID

260: '-1'
261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID

261: FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
262: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id

Line 267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id

263: AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400

Line 268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

264: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)

Line 269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id

265: AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;

Line 270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID

266: AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274:

Line 271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400

267: AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
268: AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274:
275: EXCEPTION WHEN others THEN

Line 273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;

269: AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
270: AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
271: AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
272: AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
273: AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
274:
275: EXCEPTION WHEN others THEN
276: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001002';
277: raise;

Line 303: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,

299: LAST_UPDATED_BY,
300: CREATION_DATE,
301: CREATED_BY)
302: SELECT
303: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
304: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306: -- inventory_item_id
307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND

Line 304: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,

300: CREATION_DATE,
301: CREATED_BY)
302: SELECT
303: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
304: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306: -- inventory_item_id
307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE

Line 305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,

301: CREATED_BY)
302: SELECT
303: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
304: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306: -- inventory_item_id
307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

Line 307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND

303: MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
304: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306: -- inventory_item_id
307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

Line 310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

306: -- inventory_item_id
307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 )

Line 311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

307: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 )
315: then -1

Line 312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 )
315: then -1
316: else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )

Line 313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 )
315: then -1
316: else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
317: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others

Line 316: else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )

312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 )
315: then -1
316: else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
317: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
318: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
319: end,
320: -- department_id

Line 317: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others

313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 )
315: then -1
316: else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
317: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
318: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
319: end,
320: -- department_id
321: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND

Line 318: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end

314: ) > 0 )
315: then -1
316: else case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
317: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
318: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
319: end,
320: -- department_id
321: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
322: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE

Line 321: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND

317: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
318: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
319: end,
320: -- department_id
321: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
322: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
323: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
324: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

Line 324: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

320: -- department_id
321: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
322: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
323: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
324: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
326: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
327: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
328: ) > 0 )

Line 325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

321: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
322: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
323: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
324: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
326: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
327: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
328: ) > 0 )
329: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 326: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

322: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
323: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
324: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
326: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
327: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
328: ) > 0 )
329: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: else -1

Line 327: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

323: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
324: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
326: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
327: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
328: ) > 0 )
329: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: else -1
331: end,

Line 329: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

325: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
326: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
327: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
328: ) > 0 )
329: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: else -1
331: end,
332: -- resource_id
333: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND

Line 333: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND

329: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: else -1
331: end,
332: -- resource_id
333: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
334: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
335: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
336: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

Line 336: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

332: -- resource_id
333: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
334: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
335: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
336: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
338: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
339: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
340: ) > 0 )

Line 337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

333: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
334: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
335: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
336: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
338: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
339: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
340: ) > 0 )
341: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID

Line 338: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

334: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
335: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
336: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
338: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
339: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
340: ) > 0 )
341: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
342: else -1

Line 339: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

335: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
336: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
338: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
339: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
340: ) > 0 )
341: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
342: else -1
343: end,

Line 341: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID

337: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
338: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
339: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
340: ) > 0 )
341: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
342: else -1
343: end,
344: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
345: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

Line 345: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,

341: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
342: else -1
343: end,
344: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
345: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
346: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
347: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
348: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
349: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,

Line 346: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,

342: else -1
343: end,
344: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
345: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
346: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
347: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
348: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
349: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
350: NULL,

Line 347: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,

343: end,
344: MSC_EXCEPTION_DETAILS_S.NEXTVAL,
345: MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
346: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
347: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
348: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
349: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
350: NULL,
351: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )

Line 351: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )

347: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
348: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
349: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
350: NULL,
351: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
352: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
353: ELSE
354: NULL
355: END,

Line 352: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)

348: TO_DATE(PeriodStart, 'YYYY-MM-DD'),
349: TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
350: NULL,
351: CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
352: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
353: ELSE
354: NULL
355: END,
356: null, --customerSiteId

Line 362: FROM MSC_INT_ASCP_EXCEPTION_DETAILS

358: SYSDATE,
359: '-1',
360: SYSDATE,
361: '-1'
362: FROM MSC_INT_ASCP_EXCEPTION_DETAILS
363: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
364: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
365: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';

Line 363: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar

359: '-1',
360: SYSDATE,
361: '-1'
362: FROM MSC_INT_ASCP_EXCEPTION_DETAILS
363: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
364: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
365: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
367:

Line 364: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1

360: SYSDATE,
361: '-1'
362: FROM MSC_INT_ASCP_EXCEPTION_DETAILS
363: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
364: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
365: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
367:
368: EXCEPTION WHEN others THEN

Line 365: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'

361: '-1'
362: FROM MSC_INT_ASCP_EXCEPTION_DETAILS
363: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
364: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
365: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
367:
368: EXCEPTION WHEN others THEN
369: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001003';

Line 366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';

362: FROM MSC_INT_ASCP_EXCEPTION_DETAILS
363: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
364: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
365: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
367:
368: EXCEPTION WHEN others THEN
369: g_ErrorCode := 'ERROR_UPDATE_ALERTS_001003';
370: raise;