1 PACKAGE BODY OE_2PC2_SHIPSET_SHIPA AS
2 PROCEDURE Is_Valid
3 (
4 p_application_id in number,
5 p_entity_short_name in varchar2,
6 p_validation_entity_short_name in varchar2,
7 p_validation_tmplt_short_name in varchar2,
8 p_record_set_short_name in varchar2,
9 p_scope in varchar2,
10 x_result out nocopy number
11 )
12 IS
13 l_valid_count NUMBER := 0;
14 l_set_count NUMBER := 0;
15
16 l_wf_item_type varchar2(8) :='OEOL';
17 l_wf_activity_name varchar2(30) :='SHIP_LINE';
18 l_wf_activity_status_code varchar2(8) :='NOTIFIED';
19 CURSOR C_VC IS
20 SELECT count(*)
21 FROM wf_item_activity_statuses w, wf_process_activities wpa
22 WHERE w.item_type = l_wf_item_type
23 AND w.process_activity = wpa.instance_id
24 AND wpa.activity_name = l_wf_activity_name
25 AND w.activity_status = l_wf_activity_status_code
26 AND w.item_key IN
27 ( SELECT b.LINE_ID || ''
28 FROM OE_AK_ORDER_LINES_V b
29 WHERE b.HEADER_ID = OE_LINE_SECURITY.g_record.HEADER_ID
30 AND b.SHIP_SET_ID = OE_LINE_SECURITY.g_record.SHIP_SET_ID
31 );
32
33 CURSOR C_RSC IS
34 SELECT count(*)
35 FROM OE_AK_ORDER_LINES_V b
36 WHERE b.HEADER_ID = OE_LINE_SECURITY.g_record.HEADER_ID
37 AND b.SHIP_SET_ID = OE_LINE_SECURITY.g_record.SHIP_SET_ID
38 ;
39
40 BEGIN
41 x_result := 0;
42 OPEN C_VC;
43 FETCH C_VC into l_valid_count;
44 CLOSE C_VC;
45 If (l_valid_count > 0) then
46 If (p_scope = 'ALL') then
47 OPEN C_RSC;
48 FETCH C_RSC into l_set_count;
49 CLOSE C_RSC;
50 If (l_valid_count = l_set_count) then
51 x_result := 1;
52 End If;
53 Else
54 x_result := 1;
55 End If;
56 End If;
57 Return;
58 END Is_Valid;
59 END OE_2PC2_SHIPSET_SHIPA;
[Home] [Help]