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.

[Home] [Help]

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) <> 0 )

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) <> 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) <> 0 )
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) <> 0 )
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) <> 0 )

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) <> 0 )

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) <> 0 )
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) <> 0 )
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' || ' : ' || SQLERRM;

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' || ' : ' || SQLERRM;
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) <> 0 )

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) <> 0 )
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) <> 0 )

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) <> 0 )
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) <> 0 )
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) <> 0 )

Line 158: MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )

154: AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
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) <> 0 )
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) <> 0 )

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) <> 0 )
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) <> 0 )
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) <> 0 )

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) <> 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) <> 0 )
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) <> 0 )
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) <> 0 )

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) <> 0 )

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) <> 0 )
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) <> 0 )
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' || ' : ' || SQLERRM;

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' || ' : ' || SQLERRM;
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' || ' : ' || SQLERRM;
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 (

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 (
317: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )

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

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

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

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

Line 319: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end

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

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

319: else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
320: )
321: end,
322: -- department_id
323: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

Line 326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

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

Line 327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

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

Line 328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

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

Line 329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

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

Line 331: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

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

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

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

Line 338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

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

Line 339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

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

Line 340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

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

Line 341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

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

Line 343: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID

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

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

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

Line 348: MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,

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

Line 349: MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,

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

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

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

Line 354: THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)

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

Line 364: FROM MSC_INT_ASCP_EXCEPTION_DETAILS

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

Line 365: WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar

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

Line 366: AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1

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

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

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

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

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