1 PACKAGE CHV_CUM_PERIODS_S2 AUTHID CURRENT_USER as
2 /*$Header: CHVCUMPS.pls 115.1 2002/11/23 04:10:47 sbull ship $ */
3
4 /*===========================================================================
5 PACKAGE NAME: CHV_CREATE_ITEMS
6 DESCRIPTION: This package contains the server side of Supplier Scheduling
7 APIs to select cum information.
8
9 CLIENT/SERVER: Server
10
11 OWNER: Shawna Liu
12
13 FUNCTION/
14 get_cum_info()
15
16 ============================================================================*/
17
18 /*===========================================================================
19 PROCEDURE NAME : get_cum_info
20
21 DESCRIPTION : GET_CUM_INFO is an API to retrieve cum information
22 such as cum_enable flag and the cum period to be used
23 for the organization and item.
24
25 PARAMETERS : p_organization_id IN NUMBER,
26 p_vendor_id IN NUMBER,
27 p_vendor_site_id IN NUMBER,
28 p_item_id IN NUMBER,
29 p_horizon_start_date IN DATE,
30 p_purch_unit_of_measure IN VARCHAR2,
31 p_last_receipt_transaction_id IN OUT NUMBER,
32 p_cum_quantity_received IN OUT NUMBER,
33 p_quantity_received_primary IN OUT NUMBER,
34 p_cum_period_end_date IN OUT DATE
35
36 DESIGN REFERENCES :
37
38 ALGORITHM : Select enable_cum_flag from CHV_ORG_OPTIONS for the
39 organization.
40 If x_enable_cum_flag = 'Y' then do the following:
41 Select cum_period_id, cum_period_end_date from
42 CHV_CUM_PERIODS based on the organization and
43 x_horizon_start_date which should be between
44 cum_period_start_date and cum_period_end_date.
45
46 Select cum_quantity_received, cum_qty_primary based
47 on the cum_period_id selected from the item.
48
49 If the record does not exist in CHV_CUM_PERIOD_ITEMS
50 for the supplier item then insert a new record.
51
52 NOTES : Two quantity parameters are in out rather than out,
53 because the select into statement requires that.
54
55 OPEN ISSUES : 1. Should we consider TOO_MANY_ROWS exception for
56 the selects in this procedure?
57 2. What is the exact name of the sequence number for
58 cum_period_item_id?
59 3. Can we get purchasing_unit_of_measure and
60 primary_unit_of_measure from po_approved_supplier_list
61 table with the combination of key of organization_id,
62 vendor_id, vendor_site_id, and item_id?
63 4. Where to get last_updated_by, created_by,
64 last_update_login, request_id, program_application_id,
65 program_id, and program_udpate_date?
66
67 CLOSED ISSUES :
68
69 CHANGE HISTORY : Created 1-APR-1995 SXLIU
70 ==========================================================================*/
71 PROCEDURE get_cum_info (x_organization_id IN NUMBER,
72 x_vendor_id IN NUMBER,
73 x_vendor_site_id IN NUMBER,
74 x_item_id IN NUMBER,
75 x_horizon_start_date IN DATE,
76 x_horizon_end_date IN DATE,
77 x_purchasing_unit_of_measure IN VARCHAR2,
78 x_primary_unit_of_measure IN VARCHAR2,
79 x_last_receipt_transaction_id IN OUT NOCOPY NUMBER,
80 x_cum_quantity_received IN OUT NOCOPY NUMBER,
81 x_cum_quantity_received_prim IN OUT NOCOPY NUMBER,
82 x_cum_period_end_date IN OUT NOCOPY DATE);
83
84 END CHV_CUM_PERIODS_S2;