1 PACKAGE BODY OE_1019PC2_LINE_RTLREJD 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) :='ARPROVE_RETURN_LINE_NTF';
18 l_wf_activity_status_code varchar2(8) :='COMPLETE';
19 l_wf_activity_result_code varchar2(30) :='FAIL';
20 CURSOR C_VC IS
21 SELECT count(*)
22 FROM wf_item_activity_statuses w, wf_process_activities wpa
23 WHERE w.item_type = l_wf_item_type
24 AND w.process_activity = wpa.instance_id
25 AND wpa.activity_name = l_wf_activity_name
26 AND w.activity_status = l_wf_activity_status_code
27 AND w.activity_result_code = l_wf_activity_result_code
28 AND w.item_key IN
29 ( SELECT a.LINE_ID || ''
30 FROM OE_AK_ORDER_LINES_V a
31 WHERE a.BLANKET_NUMBER = OE_BLANKET_LINE_SECURITY.g_record.ORDER_NUMBER
32 AND a.BLANKET_LINE_NUMBER = OE_BLANKET_LINE_SECURITY.g_record.LINE_NUMBER
33 )
34 ;
35
36 CURSOR C_RSC IS
37 SELECT count(*)
38 FROM OE_AK_ORDER_LINES_V a
39 WHERE a.BLANKET_NUMBER = OE_BLANKET_LINE_SECURITY.g_record.ORDER_NUMBER
40 AND a.BLANKET_LINE_NUMBER = OE_BLANKET_LINE_SECURITY.g_record.LINE_NUMBER
41 ;
42
43 BEGIN
44 x_result := 0;
45 OPEN C_VC;
46 FETCH C_VC into l_valid_count;
47 CLOSE C_VC;
48 If (l_valid_count > 0) then
49 If (p_scope = 'ALL') then
50 OPEN C_RSC;
51 FETCH C_RSC into l_set_count;
52 CLOSE C_RSC;
53 If (l_valid_count = l_set_count) then
54 x_result := 1;
55 End If;
56 Else
57 x_result := 1;
58 End If;
59 End If;
60 Return;
61 END Is_Valid;
62 END OE_1019PC2_LINE_RTLREJD;
[Home] [Help]