PACKAGE BODY: APPS.POS_OSP_JOB

Source

1 package body pos_osp_job as
2 /* $Header: POSASNWB.pls 115.1 99/09/01 10:57:34 porting sh $ */
3 
4 function r_count(po_line_location_id in number) return number is
5 x_count number;
6 begin
7 
8   select count(*)
9   into x_count
10   from po_distributions_all
11   where line_location_id = po_line_location_id and
12         wip_entity_id is not null;
13 
14   return x_count;
15 
16 end r_count;
17 
18 function get_osp_info(po_line_location_id in number) return varchar2 is
19 x_count number;
20 job_info varchar2(2000);
21 begin
22 
23   x_count := r_count(po_line_location_id);
24 
25   if x_count = 1 then
26 
27     select ltrim(wipe.wip_entity_name || '-' || wipl.line_code || '-' || pod.wip_operation_seq_num)
28     into job_info
29     from po_distributions_all pod,
30          wip_entities wipe,
31          wip_lines wipl
32     where pod.line_location_id = po_line_location_id and
33           pod.wip_entity_id is not null and
34           pod.wip_entity_id = wipe.wip_entity_id and
35           pod.wip_line_id = wipl.line_id(+);
36 
37     return job_info;
38 
39   elsif x_count > 1 then
40 
41    return ' ';
42 
43   else
44 
45     return ' ';
46 
47   end if;
48 
49 end get_osp_info;
50 
51 function get_wip_info(po_distributions_id in number) return varchar2 is
52 job_info varchar2(2000);
53 begin
54 
55     select ltrim(wipe.wip_entity_name || '-' || wipl.line_code || '-' || pod.wip_operation_seq_num)
56     into job_info
57     from po_distributions_all pod,
58          wip_entities wipe,
59          wip_lines wipl
60     where pod.po_distribution_id = po_distributions_id and
61           pod.wip_entity_id is not null and
62           pod.wip_entity_id = wipe.wip_entity_id and
63           pod.wip_line_id = wipl.line_id(+);
64 
65    return job_info;
66 
67 end get_wip_info;
68 
69 function get_po_distribution_id(po_line_location_id in number) return number is
70 x_id number;
71 begin
72 
73   if r_count(po_line_location_id) = 1 then
74 
75     select po_distribution_id
76     into x_id
77     from po_distributions_all
78     where line_location_id = po_line_location_id and
79          wip_entity_id is not null;
80 
81     return x_id;
82   else
83     return null;
84   end if;
85 
86 end get_po_distribution_id;
87 
88 function get_wip_entity_id(po_line_location_id in number) return number is
89 x_id number;
90 begin
91 
92   if r_count(po_line_location_id) = 1 then
93 
94     select wip_entity_id
95     into x_id
96     from po_distributions_all
97     where line_location_id = po_line_location_id and
98          wip_entity_id is not null;
99 
100     return x_id;
101   else
102     return null;
103   end if;
104 
105 end get_wip_entity_id;
106 
107 function get_wip_seq_num(po_line_location_id in number) return number is
108 x_id number;
109 begin
110 
111   if r_count(po_line_location_id) = 1 then
112 
113     select wip_operation_seq_num
114     into x_id
115     from po_distributions_all
116     where line_location_id = po_line_location_id and
117          wip_entity_id is not null;
118 
119     return x_id;
120   else
121     return null;
122   end if;
123 
124 end get_wip_seq_num;
125 
126 function get_wip_line_id(po_line_location_id in number) return number is
127 x_id number;
128 begin
129 
130   if r_count(po_line_location_id) = 1 then
131 
132     select wip_line_id
133     into x_id
134     from po_distributions_all
135     where line_location_id = po_line_location_id and
136          wip_entity_id is not null;
137 
138     return x_id;
139   else
140     return null;
141   end if;
142 
143 end get_wip_line_id;
144 
145 end pos_osp_job;