[Home] [Help]
PACKAGE BODY: APPS.GMD_SPEC_MATCH_API
Source
1 package body GMD_SPEC_MATCH_API as
2 /* $Header: GMDRLSMB.pls 120.1 2006/04/10 14:08:43 rakulkar noship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDRLSMB.pls |
11 --| Package Name : GMD_SPEC_MATCH_API |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Specification Match |
16 --| exclusivly for the picking rules |
17 --| |
18 --| HISTORY |
19 --| Liping Gao 6-Jan-2006 Created. |
20 --| |
21 --+==========================================================================+
22 -- End of comments
23
24 Function get_spec_match
25 ( p_source_line_id IN NUMBER
26 , p_lot_number IN VARCHAR2
27 , p_subinventory_code IN VARCHAR2
28 , p_locator_id IN NUMBER
29 )
30 RETURN VARCHAR2
31 IS
32 find_cust_spec_rec GMD_SPEC_MATCH_GRP.customer_spec_rec_type;
33 l_spec_vr_id NUMBER;
34 l_spec_return_status VARCHAR(1);
35 l_return_status VARCHAR(1);
36 l_message_data VARCHAR(2000);
37 l_spec_return BOOLEAN := FALSE;
38 l_spec_hdr_id NUMBER;
39 l_customer_id NUMBER;
40 l_ship_to_site_id NUMBER;
41 l_grade VARCHAR(150);
42 l_inventory_item_id NUMBER;
43 l_schedule_ship_date DATE;
44 l_header_id NUMBER;
45 l_organization_id NUMBER;
46
47 l_spec_match_type VARCHAR2(4);
48 result_lot_match_tbl GMD_SPEC_MATCH_GRP.result_lot_match_tbl;
49
50 Cursor get_order_line_info (p_source_line_id IN Number) Is
51 Select header_id
52 , ship_to_org_id ship_to_site_id
53 , sold_to_org_id customer_id
54 , ship_from_org_id organization_id
55 , inventory_item_id
56 , schedule_ship_date
57 , preferred_grade
58 From oe_order_lines_all
59 Where line_id = p_source_line_id
60 ;
61 /*CURSOR get_spec_match_type IS
62 SELECT meaning
63 FROM gem_lookups
64 WHERE lookup_type = 'GMD_QC_SPEC_MATCH_TYPES'
65 AND lookup_code = l_spec_match_type;*/
66
67 Begin
68
69 open get_order_line_info(p_source_line_id);
70 Fetch get_order_line_info
71 Into l_header_id
72 , l_ship_to_site_id
73 , l_customer_id
74 , l_organization_id
75 , l_inventory_item_id
76 , l_schedule_ship_date
77 , l_grade
78 ;
79 Close get_order_line_info;
80 find_cust_spec_rec.cust_id := l_customer_id;
81 find_cust_spec_rec.inventory_item_id := l_inventory_item_id;
82 find_cust_spec_rec.grade_code := l_grade;
83 find_cust_spec_rec.date_effective := l_schedule_ship_date;
84 --find_cust_spec_rec.org_id := l_organization_id;
85 find_cust_spec_rec.organization_id := l_organization_id;
86 find_cust_spec_rec.ship_to_site_id := l_ship_to_site_id;
87 find_cust_spec_rec.order_id := l_header_id;
88 find_cust_spec_rec.order_line_id := p_source_line_id;
89 find_cust_spec_rec.exact_match := 'N';
90 find_cust_spec_rec.look_in_other_orgn := 'Y';
91 l_spec_return := gmd_spec_match_grp.find_customer_spec
92 ( p_customer_spec_rec => find_cust_spec_rec
93 ,x_spec_id => l_spec_hdr_id
94 ,x_spec_vr_id => l_spec_vr_id
95 ,x_return_status => l_spec_return_status
96 ,x_message_data => l_message_data
97 );
98 IF (l_spec_hdr_id > 0) THEN
99 result_lot_match_tbl(1).inventory_item_id := l_inventory_item_id;
100 result_lot_match_tbl(1).lot_number := p_lot_number;
101 result_lot_match_tbl(1).subinventory := p_subinventory_code;
102 result_lot_match_tbl(1).locator_id := p_locator_id;
103 result_lot_match_tbl(1).organization_id := l_organization_id;
104 --Calling quality api to fetch the spec_match_type
105 GMD_SPEC_MATCH_GRP.get_result_match_for_spec
106 ( p_spec_id => l_spec_hdr_id
107 ,p_lots => result_lot_match_tbl
108 ,x_return_status => l_return_status
109 ,x_message_data => l_message_data
110 );
111 IF (l_return_status = 'S' AND result_lot_match_tbl.COUNT > 0 ) THEN
112 l_spec_match_type := result_lot_match_tbl(1).spec_match_type;
113 END IF;
114 IF (l_spec_match_type = 'A') THEN
115 return 'ACCEPTABLE';
116 Else
117 return 'UNACCEPTABLE';
118 END IF;
119 Else -- all else return unacceptable
120 return 'UNACCEPTABLE';
121 END IF;
122
123 END get_spec_match ;
124 end GMD_SPEC_MATCH_API;