1 PACKAGE BODY GMD_FORMULA_IBOM AS
2 /* $Header: GMDFORMB.pls 120.4 2011/01/10 18:28:30 rnalla ship $ */
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 DECODE(s.status_type,900,699,s.status_type)
27 FROM gmd_recipe_validity_rules fe, fm_form_mst fm, gmd_recipes_b r, fm_matl_dtl fdtl, gmd_status s
28 WHERE fe.inventory_item_id = V_item_id
29 AND fe.recipe_use = eff_type
30 AND fdtl.formula_id = fm.formula_id /* Added in Bug No.8639969 */
31 AND fdtl.line_type = 1 /* Added in Bug No.8639969 */
32 AND fdtl.inventory_item_id = fe.inventory_item_id /* Added in Bug No.8639969 */
33 AND fe.validity_rule_status = s.status_code
34 AND fe.validity_rule_status NOT IN (800,1000) /* Added in Bug No.8639969 */
35 AND fm.formula_status LIKE NVL(inact_status,'%')
36 AND TRUNC(fe.start_date) <= TRUNC(TO_DATE(eff_date,'DD-MM-RRRR'))
37 AND (TRUNC(fe.end_date) >= TRUNC(TO_DATE(eff_date,'DD-MM-RRRR'))
38 OR fe.end_date IS NULL)
39 AND fe.delete_mark = 0
40 AND r.formula_id = fm.formula_id
41 AND fe.recipe_id = r.recipe_id
42 AND fm.delete_mark = 0
43 AND (pvalidity_organization_type = 2
44 OR fe.organization_id IS NULL
45 OR (pvalidity_organization_type = 0 AND fe.organization_id = porganization_id )
46 OR (pvalidity_organization_type = 1 AND fe.organization_id IN ( SELECT organization_id
47 FROM org_access_view
48 WHERE responsibility_id = presp_id)))
49 -- Bug 4777885 KSHUKLA : Start
50
51 ORDER BY fe.orgn_code asc,10 desc, 8, fe.start_date desc;
52
53 -- Bug 4777885 : End
54 --END BUG#3616788
55 --END BUG#1252454
56 Cur_rec IBOM_CUR%ROWTYPE;
57 BEGIN
58 OPEN IBOM_CUR;
59 FETCH IBOM_CUR INTO Cur_rec;
60 IF (IBOM_CUR%NOTFOUND) THEN
61 RAISE NO_DATA_FOUND;
62 END IF;
63 CLOSE IBOM_CUR;
64 pformula_id := cur_rec.formula_id;
65 pformula_std_qty := cur_rec.std_qty;
66 pformula_std_qty_uom := cur_rec.detail_uom;
67 gmd_debug.put_line('formula id is'|| pformula_id ||'std qty is '|| pformula_std_qty||'qty _uom is '||pformula_std_qty_uom);
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 gmd_Debug.put_line('no data found');
71 pformula_id := -1;
72 END get_formula;
73 END GMD_FORMULA_IBOM;
[Home] [Help]