1 PACKAGE BODY GMD_FORMULA_IBOM AS
2 /* $Header: GMDFORMB.pls 120.2 2005/12/08 03:12:23 kshukla noship $ */
3
4 PROCEDURE get_formula(V_item_id NUMBER,
5 l_type VARCHAR2,
6 eff_type IN VARCHAR2,
7 eff_date IN VARCHAR2,
8 pformula_id OUT NOCOPY PLS_INTEGER,
9 pformula_std_qty OUT NOCOPY NUMBER,
10 pformula_std_qty_uom OUT NOCOPY VARCHAR2,
11 inact_status VARCHAR2,
12 pvalidity_organization_type NUMBER,
13 porganization_id NUMBER,
14 presp_id IN NUMBER) IS
15
16 -- BEGIN BUG#1252454 Sastry
17 -- Added where clause related to orgn to select formulas which have effectivities that are either
18 -- local to user orgn or global. Also added order by clause to sort them in ascending order.
19 -- Also modified the date format from DD-MON-RRRR to DD-MM-RRRR for GSCC complaint.
20 --BEGIN BUG#1856823 Rameshwar
21 --Modified the inact_ind to inact_status in the query.
22 -- BEGIN BUG#3616788
23 CURSOR IBOM_CUR IS
24 SELECT fm.formula_id, fm.formula_no, fm.formula_vers,fe.min_qty, fe.max_qty,
25 fe.std_qty, fe.detail_uom, fe.preference,fe.last_update_date
26 FROM gmd_recipe_validity_rules fe, fm_form_mst fm, gmd_recipes_b r
27 WHERE fe.inventory_item_id = V_item_id
28 AND fe.recipe_use = eff_type
29 AND fm.formula_status LIKE NVL(inact_status,'%')
30 AND TRUNC(fe.start_date) <= TRUNC(TO_DATE(eff_date,'DD-MM-RRRR'))
31 AND (
32 TRUNC(fe.end_date) >= TRUNC(TO_DATE(eff_date,'DD-MM-RRRR'))
33 OR fe.end_date IS NULL
34 )
35 AND fe.delete_mark = 0
36 AND r.formula_id = fm.formula_id
37 AND fe.recipe_id = r.recipe_id
38 AND fm.delete_mark = 0
39 AND (pvalidity_organization_type = 2
40 OR fe.organization_id IS NULL
41 OR (pvalidity_organization_type = 0 AND fe.organization_id = porganization_id )
42 OR (pvalidity_organization_type = 1 AND fe.organization_id IN ( SELECT organization_id
43 FROM org_access_view
44 WHERE responsibility_id = presp_id)))
45 -- Bug 4777885 KSHUKLA : Start
46
47 ORDER BY fe.orgn_code asc, 8, fe.start_date desc;
48
49 -- Bug 4777885 : End
50 --END BUG#3616788
51 --END BUG#1252454
52 Cur_rec IBOM_CUR%ROWTYPE;
53 BEGIN
54 OPEN IBOM_CUR;
55 FETCH IBOM_CUR INTO Cur_rec;
56 IF (IBOM_CUR%NOTFOUND) THEN
57 RAISE NO_DATA_FOUND;
58 END IF;
59 CLOSE IBOM_CUR;
60 pformula_id := cur_rec.formula_id;
61 pformula_std_qty := cur_rec.std_qty;
62 pformula_std_qty_uom := cur_rec.detail_uom;
63 gmd_debug.put_line('formula id is'|| pformula_id ||'std qty is '|| pformula_std_qty||'qty _uom is '||pformula_std_qty_uom);
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 gmd_Debug.put_line('no data found');
67 pformula_id := -1;
68 END get_formula;
69 END GMD_FORMULA_IBOM;
[Home] [Help]