[Home] [Help]
PACKAGE BODY: APPS.WIP_SF_CUSTOM_ATTRIBUTES
Source
1 PACKAGE BODY wip_sf_custom_attributes AS
2 /* $Header: wipsfatb.pls 115.7 2002/11/28 13:02:04 jyeung ship $ */
3
4 PROCEDURE get_schedule_attr (
5 orgID IN NUMBER,
6 lineID IN NUMBER,
7 wipEntityID IN NUMBER,
8 opSeqID IN NUMBER,
9 p_num_attr OUT NOCOPY NUMBER,
10 p_labels OUT NOCOPY system.wip_attr_labels,
11 p_values OUT NOCOPY system.wip_attr_values,
12 p_colors OUT NOCOPY system.wip_attr_colors) IS
13 sched_num VARCHAR2(30);
14 org_code VARCHAR2(3);
15 line_code VARCHAR2(10);
16 sales_order VARCHAR2(122);
17 assembly VARCHAR2(40);
18 sched_group VARCHAR2(30);
19 build_seq NUMBER;
20 comp_date DATE;
21 proj_name VARCHAR2(30);
22 task_name VARCHAR2(20);
23 BEGIN
24 SELECT wfs.schedule_number,
25 mp.organization_code,
26 wl.line_code,
27 mso.concatenated_segments,
28 msik.concatenated_segments,
29 wsg.schedule_group_name,
30 wfs.build_sequence,
31 wfs.scheduled_completion_date,
32 pjm_project.all_proj_idtoname(wfs.project_id),
33 pjm_project.all_task_idtoname(wfs.task_id)
34 INTO
35 sched_num,
36 org_code,
37 line_code,
38 sales_order,
39 assembly,
40 sched_group,
41 build_seq,
42 comp_date,
43 proj_name,
44 task_name
45 FROM
46 wip_flow_schedules wfs,
47 mtl_system_items_kfv msik,
48 mtl_sales_orders_kfv mso,
49 mtl_parameters mp,
50 wip_lines wl,
51 wip_schedule_groups wsg
52 WHERE
53 wfs.organization_id = orgID
54 AND wfs.line_id = lineID
55 AND wfs.wip_entity_id = wipEntityID
56 AND wsg.organization_id (+) = wfs.organization_id
57 AND wsg.schedule_group_id (+) = wfs.schedule_group_id
58 AND wl.line_id = wfs.line_id
59 AND wl.organization_id = wfs.organization_id
60 AND msik.inventory_item_id = wfs.primary_item_id
61 AND msik.organization_id = wfs.organization_id
62 AND mso.sales_order_id (+) = wfs.demand_source_header_id
63 AND wfs.organization_id = mp.organization_id;
64
65 wip_sf_custom_api.schedule_custom_api (
66 scheduleNumber => sched_num,
67 orgCode => org_code,
68 lineCode => line_code,
69 opSeqID => opSeqID,
70 salesOrderNumber => sales_order,
71 assemblyName => assembly,
72 scheduleGroup => sched_group,
73 buildSequence => build_seq,
74 completionDate => comp_date,
75 projectName => proj_name,
76 taskName => task_name,
77 x_num_attr => p_num_attr,
78 x_labels => p_labels,
79 x_values => p_values,
80 x_colors => p_colors);
81 END get_schedule_attr;
82
83 PROCEDURE get_event_attr (
84 orgID IN NUMBER,
85 lineID IN NUMBER,
86 wipEntityID IN NUMBER,
87 lineopSeqID IN NUMBER, -- equals schedule's opSeqID
88 opSeqNum IN NUMBER,
89 p_num_attr OUT NOCOPY NUMBER,
90 p_labels OUT NOCOPY system.wip_attr_labels,
91 p_values OUT NOCOPY system.wip_attr_values,
92 p_colors OUT NOCOPY system.wip_attr_colors) IS
93 sched_num VARCHAR2(30);
94 org_code VARCHAR2(3);
95 line_code VARCHAR2(10);
96 op_code VARCHAR2(4);
97 dept_code VARCHAR2(10);
98 BEGIN
99 SELECT wfs.schedule_number,
100 mp.organization_code,
101 wl.line_code,
102 bso.operation_code,
103 bd.department_code
104 INTO
105 sched_num,
106 org_code,
107 line_code,
108 op_code,
109 dept_code
110 FROM wip_flow_schedules wfs,
111 wip_lines wl,
112 bom_operation_sequences bos,
113 bom_operation_sequences bos2,
114 bom_operational_routings bor,
115 bom_departments bd,
116 bom_standard_operations bso,
117 mtl_parameters mp
118 WHERE bor.organization_id = wfs.organization_id
119 and bor.assembly_item_id = wfs.primary_item_id
120 and bor.line_id = wfs.line_id
121 and bor.cfm_routing_flag = 1
122 and decode(bor.alternate_routing_designator, null,'@@@@@@@',bor.alternate_routing_designator) = decode(wfs.alternate_routing_designator, null, '@@@@@@@', wfs.alternate_routing_designator)
123 and bos.operation_type = 3
124 and bos.routing_sequence_id = bor.common_routing_sequence_id
125 and bos2.line_op_seq_id = bos.operation_sequence_id
126 and wfs.organization_id = orgID
127 and wfs.line_id = lineID
128 and wfs.wip_entity_id = wipEntityID
129 and bos.operation_sequence_ID = lineopSeqID
130 and bos2.operation_seq_num = opSeqNum
131 and wfs.organization_id = mp.organization_id
132 and trunc(BOS2.effectivity_date) <= trunc(nvl(WFS.routing_revision_date,sysdate))
133 and (BOS2.disable_date is null or trunc(BOS2.disable_date) > trunc(WFS.routing_revision_date))
134 and bd.department_id = bos2.department_id
135 and bso.standard_operation_id (+) = bos2.standard_operation_id
136 and wl.line_id = wfs.line_id
137 and wl.organization_id = wfs.organization_id;
138
139 wip_sf_custom_api.event_custom_api (
140 scheduleNumber => sched_num,
141 orgCode => org_code,
142 lineCode => line_code,
143 lineopSeqID => lineopSeqID,
144 opSeqNum => opSeqNum,
145 opCode => op_code,
146 deptCode => dept_code,
147 x_num_attr => p_num_attr,
148 x_labels => p_labels,
149 x_values => p_values,
150 x_colors => p_colors);
151 END get_event_attr;
152
153 END wip_sf_custom_attributes;