PACKAGE BODY: APPS.PV_SEED_DERIVED_PKG

Source

1 PACKAGE BODY PV_SEED_DERIVED_PKG as
2 /* $Header: pvdervdb.pls 115.0 2003/11/06 02:17:02 dhii noship $ */
3 
4 procedure last_order_date(partner_id number, x_last_order_date out nocopy jtf_varchar2_table_4000)
5 is
6 
7    cursor lc_get_date (pc_partner_id number) is
8    select to_char(aa.ordered_date,'yyyymmddhh24miss')
9    from oe_order_headers_all aa, hz_cust_accounts b, pv_partner_profiles c
10    where c.partner_id = pc_partner_id
11    and c.partner_party_id = b.party_id
12    and b.cust_account_id = aa.sold_to_org_id
13    order by aa.ordered_date desc;
14 
15    l_date varchar2(30);
16    lc_date_tbl jtf_varchar2_table_4000 := jtf_varchar2_table_4000();
17 
18 begin
19     open  lc_get_date(pc_partner_id => partner_id);
20     fetch lc_get_date into l_date;
21     close lc_get_date;
22     if l_date is not null then
23        lc_date_tbl.extend;
24        lc_date_tbl(1) := l_date;
25        x_last_order_date := lc_date_tbl;
26     end if;
27 end;
28 
29 procedure prod_bought_last_yr(partner_id number, x_inventory_item out nocopy jtf_varchar2_table_4000)
30 is
31 
32    cursor lc_prod_bought (pc_partner_id number) is
33    select distinct a.inventory_item_id
34    from oe_order_lines_all a, oe_order_headers_all aa, hz_cust_accounts b, pv_partner_profiles c
35    where c.partner_id = pc_partner_id
36    and c.partner_party_id = b.party_id and b.cust_account_id = aa.invoice_to_org_id
37    and aa.ordered_date > add_months(sysdate, -12)
38    and aa.header_id = a.header_id
39    and a.flow_status_code = 'CLOSED'
40    and a.actual_shipment_date is not null and a.line_category_code = 'ORDER'
41    and a.cancelled_flag = 'N';
42 
43    l_items_tbl jtf_varchar2_table_4000 := jtf_varchar2_table_4000();
44 
45 begin
46     for lc_rec in lc_prod_bought(pc_partner_id => partner_id)
47     loop
48        l_items_tbl.extend;
49        l_items_tbl(1) := lc_rec.inventory_item_id;
50     end loop;
51     x_inventory_item := l_items_tbl;
52 end;
53 
54 
55 procedure prod_sold_last_yr(partner_id number, x_inventory_item out nocopy jtf_varchar2_table_4000)
56 is
57 
58    cursor lc_prod_sold (pc_partner_id number) is
59    select distinct a.inventory_item_id
60    from ozf_sales_transactions_all a, hz_cust_accounts b, pv_partner_profiles c
61    where c.partner_id = pc_partner_id
62    and c.partner_party_id = b.party_id
63    and b.cust_account_id = a.SOLD_FROM_CUST_ACCOUNT_ID
64    and a.transfer_type = 'S'
65    and a.transaction_date > add_months(sysdate, -12);
66 
67    l_items_tbl jtf_varchar2_table_4000 := jtf_varchar2_table_4000();
68 
69 begin
70     for lc_rec in lc_prod_sold(pc_partner_id => partner_id)
71     loop
72        l_items_tbl.extend;
73        l_items_tbl(1) := lc_rec.inventory_item_id;
74     end loop;
75     x_inventory_item := l_items_tbl;
76 end;
77 
78 
79 end;