1 PACKAGE gme_convert_fpo_pvt AUTHID CURRENT_USER AS
2 /* $Header: GMEVCFPS.pls 120.3 2005/06/29 03:01:22 kxhunt noship $ */
3
4 --******************************************************************
5 TYPE pregen_fpo_row IS RECORD (
6 batch_id gme_batch_header.batch_id%TYPE
7 ,qty_per_batch NUMBER
8 ,batch_size_uom gme_material_details.dtl_um%TYPE
9 ,num_batches NUMBER
10 ,validity_rule_id gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE
11 ,leadtime NUMBER
12 ,offset_type NUMBER
13 ,batch_offset NUMBER
14 ,plan_start_date gme_batch_header.plan_start_date%TYPE
15 ,plan_cmplt_date gme_batch_header.plan_cmplt_date%TYPE
16 ,fpo_assigned_qty gme_material_details.plan_qty%TYPE
17 ,fpo_unassigned_qty gme_material_details.plan_qty%TYPE
18 ,schedule_method VARCHAR2 (10)
19 ,effective_qty gme_material_details.plan_qty%TYPE
20 ,sum_eff_qty gme_material_details.plan_qty%TYPE DEFAULT 0
21 ,std_qty NUMBER
22 ,fixed_leadtime NUMBER
23 ,variable_leadtime NUMBER
24 ,rules_found NUMBER
25 );
26
27 TYPE error_return_sts IS RECORD (
28 return_status VARCHAR2 (2)
29 );
30
31 TYPE validity_rule_row IS RECORD (
32 plant_code gme_batch_header.plant_code%TYPE
33 ,prim_prod_item_id gme_material_details.inventory_item_id%TYPE
34 ,prim_prod_item_no VARCHAR2(2000)
35 ,prim_prod_item_desc1 mtl_system_items.description%TYPE
36 ,prim_prod_item_um gme_material_details.dtl_um%TYPE
37 ,prim_prod_effective_qty gme_material_details.plan_qty%TYPE
38 ,organization_id NUMBER
39 );
40
41 TYPE fpo_material_details_tab IS TABLE OF gme_material_details%ROWTYPE
42 INDEX BY BINARY_INTEGER;
43
44 TYPE generated_pre_batch_tab IS TABLE OF gme_batch_header%ROWTYPE
45 INDEX BY BINARY_INTEGER;
46
47 TYPE recipe_validity_rule_tab IS TABLE OF gmd_recipe_validity_rules%ROWTYPE
48 INDEX BY BINARY_INTEGER;
49
50 TYPE return_array_sts IS TABLE OF error_return_sts
51 INDEX BY BINARY_INTEGER;
52
53 --******************************************************************
54
55 --can remove schedule_method as parameter as is now calculated
56 --in validation.
57 PROCEDURE VALIDATION (
58 p_batch_header IN gme_batch_header%ROWTYPE
59 ,p_batch_size IN NUMBER
60 ,p_batch_size_uom IN VARCHAR2
61 ,p_num_batches IN NUMBER
62 ,p_validity_rule_id IN NUMBER
63 ,p_leadtime IN NUMBER
64 ,p_batch_offset IN NUMBER
65 ,p_offset_type IN NUMBER
66 ,p_plan_start_date IN gme_batch_header.plan_start_date%TYPE
67 ,p_plan_cmplt_date IN gme_batch_header.plan_cmplt_date%TYPE
68 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
69 ,x_return_status OUT NOCOPY VARCHAR2);
70
71 PROCEDURE retrieve_fpo_data (
72 p_fpo_header_row IN gme_batch_header%ROWTYPE
73 ,x_fpo_header_row OUT NOCOPY gme_batch_header%ROWTYPE
74 ,p_pregen_fpo_row IN pregen_fpo_row
75 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
76 ,x_prim_prod_row OUT NOCOPY gme_material_details%ROWTYPE
77 ,x_validity_rule_row OUT NOCOPY validity_rule_row
78 ,x_fpo_material_details_tab OUT NOCOPY fpo_material_details_tab
79 ,x_return_status OUT NOCOPY VARCHAR2);
80
81 PROCEDURE calculate_leadtime (
82 p_pregen_fpo_row IN pregen_fpo_row
83 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
84 ,x_return_status OUT NOCOPY VARCHAR2);
85
86 PROCEDURE generate_pre_batch_header_recs (
87 p_fpo_header_row IN gme_batch_header%ROWTYPE
88 ,p_prim_prod_row IN gme_material_details%ROWTYPE
89 ,p_pregen_fpo_row IN pregen_fpo_row
90 ,x_generated_pre_batch_tab OUT NOCOPY generated_pre_batch_tab
91 ,x_return_status OUT NOCOPY VARCHAR2);
92
93 /* PROCEDURE get_validity_rule
94 ( p_validity_rule_row IN validity_rule_row
95 , x_recipe_validity_rule_tab OUT NOCOPY gme_common_pvt.recipe_validity_rule_tab
96 , x_return_status OUT NOCOPY VARCHAR2
97 );
98 */
99 /* Pawan Kumar 09-16-2003 Bug 823188
100 Modified the procedures call for shop calendar */
101 PROCEDURE convert_fpo_to_batch (
102 p_generated_pre_batch_tab IN generated_pre_batch_tab
103 ,p_recipe_validity_rule_tab IN gme_common_pvt.recipe_validity_rule_tab
104 ,p_pregen_fpo_row IN pregen_fpo_row
105 ,x_generated_pre_batch_tab OUT NOCOPY generated_pre_batch_tab
106 ,x_return_status OUT NOCOPY VARCHAR2
107 ,x_arr_rtn_sts OUT NOCOPY return_array_sts
108 ,p_process_row IN NUMBER DEFAULT 0
109 ,p_use_shop_cal IN VARCHAR2 := fnd_api.g_false
110 ,p_contiguity_override IN VARCHAR2 := fnd_api.g_true
111 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
112 ,p_fpo_validity_rule_id IN NUMBER -- Bug 3185748 Added
113 );
114
115 --can remove schedule_method as parameter as is now calculated
116 --in validation.
117 /* Pawan Kumar 09-16-2003 Bug 823188
118 Modified the procedures call for shop calendar */
119 PROCEDURE convert_fpo_main (
120 p_batch_header IN gme_batch_header%ROWTYPE
121 ,p_batch_size IN NUMBER
122 ,p_num_batches IN NUMBER
123 ,p_validity_rule_id IN NUMBER
124 ,p_validity_rule_tab IN gme_common_pvt.recipe_validity_rule_tab
125 ,p_enforce_vldt_check IN VARCHAR2 := fnd_api.g_true
126 ,p_leadtime IN NUMBER
127 ,p_batch_offset IN NUMBER
128 ,p_offset_type IN NUMBER
129 ,p_plan_start_date IN gme_batch_header.plan_start_date%TYPE
130 ,p_plan_cmplt_date IN gme_batch_header.plan_cmplt_date%TYPE
131 ,p_use_shop_cal IN VARCHAR2 := fnd_api.g_false
132 ,p_contiguity_override IN VARCHAR2 := fnd_api.g_true
133 ,x_return_status OUT NOCOPY VARCHAR2
134 ,x_batch_header OUT NOCOPY gme_batch_header%ROWTYPE
135 ,p_use_for_all IN VARCHAR2 := fnd_api.g_true);
136
137 PROCEDURE update_original_fpo (
138 p_fpo_header_row IN gme_batch_header%ROWTYPE
139 ,p_prim_prod_row IN gme_material_details%ROWTYPE
140 ,p_pregen_fpo_row IN pregen_fpo_row
141 ,p_fpo_material_details_tab IN fpo_material_details_tab
142 ,p_enforce_vldt_check IN VARCHAR2 := fnd_api.g_true
143 ,x_fpo_header_row OUT NOCOPY gme_batch_header%ROWTYPE
144 ,x_return_status OUT NOCOPY VARCHAR2);
145 END gme_convert_fpo_pvt;