1 package body cs_war as
2 /* $Header: csxcuwab.pls 120.0 2005/08/01 12:36:16 smisra noship $ */
3 --
4 --
5 /*******************************************************************************
6 ********************************************************************************
7 --
8 -- Public Functions/procedures
9 --
10 ********************************************************************************
11 *******************************************************************************/
12 --
13 --
14 --Get the comma-separated item_ids of the attached warranties on an item,
15 --as on p_war_date. The Item-Validation-Organization of the attached
16 --warranty is the same as the item's.
17 function get_war_item_ids
18 (
19 p_organization_id number,
20 p_inventory_item_id number,
21 p_war_date date default sysdate
22 ) return varchar2 is
23 begin
24 return(cs_std.get_war_item_ids(p_organization_id, p_inventory_item_id,
25 p_war_date));
26 end get_war_item_ids;
27 --
28 --
29 -- This function returns Y or N for warranty attached to a customer
30 -- product_id
31 function warranty_exists
32 (
33 cp_id NUMBER
34 ) return VARCHAR2 is
35 begin
36 return(cs_std.warranty_exists(cp_id));
37 end warranty_exists;
38 --
39 --
40 --Get the duration, period and coverage of a warranty on a product, as on
41 --p_war_date.
42 --The Item-Validation-Organization of the attached
43 --warranty is the same as the product's.
44 --It is upto the caller to ensure that the warranty is a valid warranty
45 --on the product, else an exception NO_DATA_FOUND is raised.
46 procedure get_war_dur_per
47 (
48 p_organization_id number,
49 p_prod_inv_item_id number,
50 p_war_inv_item_id number,
51 p_war_date date default sysdate,
52 p_duration in out nocopy number,
53 p_uom_code in out nocopy varchar2,
54 p_cov_sch_id in out nocopy number
55 ) is
56 l_war_date date;
57 l_com_bill_seq_id number;
58 begin
59 if p_organization_id is null or
60 p_prod_inv_item_id is null or
61 p_war_inv_item_id is null then
62 raise NO_DATA_FOUND;
63 end if;
64 --
65 l_war_date := nvl(p_war_date, sysdate);
66 --
67 select common_bill_sequence_id
68 into l_com_bill_seq_id
69 from bom_bill_of_materials
70 where organization_id = p_organization_id
71 and assembly_item_id = p_prod_inv_item_id
72 and alternate_bom_designator is null;
73 --
74 -- BOM allows you to define the same component in the same bill
75 -- at the same level twice as long as they differ either in the
76 -- operation sequence (which doesnt make sense for warranties) or
77 -- effectivity date range. Normally this shouldn't occur, but just
78 -- in case it does, we pick any one.
79 -- If for some reason the duration and period is not defined in the
80 -- BOM, we look up the item master once ascertained that the warnty
81 -- is indeed present on the product. This shouldnt really be
82 -- necessary bcoz the BOM forms enforces the relevant data to be
83 -- entered, but just in case.
84 select bic.component_quantity, mtl.primary_uom_code,
85 mtl.coverage_schedule_id
86 into p_duration, p_uom_code, p_cov_sch_id
87 from mtl_system_items mtl, bom_inventory_components bic
88 where bic.component_item_id = mtl.inventory_item_id
89 and mtl.organization_id = p_organization_id
90 and bic.bill_sequence_id = l_com_bill_seq_id
91 and bic.component_item_id = p_war_inv_item_id
92 and l_war_date >= bic.effectivity_date
93 and l_war_date < nvl(bic.disable_date,l_war_date+1)
94 and mtl.vendor_warranty_flag = 'Y'
95 and rownum < 2;
96 --
97 if p_duration is null or
98 p_uom_code is null then
99 select service_duration, service_duration_period_code,
100 coverage_schedule_id
101 into p_duration, p_uom_code, p_cov_sch_id
102 from mtl_system_items
103 where inventory_item_id = p_war_inv_item_id
104 and organization_id = p_organization_id;
105 end if;
106 --
107 --
108 end get_war_dur_per;
109 --
110 --
111 end cs_war;
[Home] [Help]