1 PACKAGE gme_post_migration AS
2 /* $Header: GMEVRCBS.pls 120.4 2006/10/27 17:20:39 creddy noship $ */
3
4 TYPE subinv_rec IS RECORD (subinventory VARCHAR2(10), organization_id NUMBER);
5 TYPE subinv_tab IS TABLE OF subinv_rec INDEX BY VARCHAR2(4);
6 p_subinv_tbl subinv_tab;
7 TYPE subinv_loctype_rec IS RECORD (locator_type NUMBER);
8 TYPE subinv_loctype_tab IS TABLE OF subinv_loctype_rec INDEX BY VARCHAR2(10);
9 p_subinv_loctype_tbl subinv_loctype_tab;
10 TYPE locator_rec IS RECORD (locator_id NUMBER, organization_id NUMBER, subinventory VARCHAR2(10));
11 TYPE locator_tab IS TABLE OF locator_rec INDEX BY VARCHAR2(16);
12 p_locator_tbl locator_tab;
13
14 TYPE mtl_dtl_mig_tab IS TABLE OF gme_material_details_mig%ROWTYPE INDEX BY BINARY_INTEGER;
15 TYPE steps_mig_tab IS TABLE OF gme_batch_steps_mig%ROWTYPE INDEX BY BINARY_INTEGER;
16 TYPE activ_mig_tab IS TABLE OF gme_batch_step_activ_mig%ROWTYPE INDEX BY BINARY_INTEGER;
17 TYPE rsrc_mig_tab IS TABLE OF gme_batch_step_resources_mig%ROWTYPE INDEX BY BINARY_INTEGER;
18 TYPE process_param_mig_tab IS TABLE OF gme_process_parameters_mig%ROWTYPE INDEX BY BINARY_INTEGER;
19 TYPE process_param_tab IS TABLE OF gme_process_parameters%ROWTYPE INDEX BY BINARY_INTEGER;
20 TYPE rsrc_txns_mig_tab IS TABLE OF gme_resource_txns_mig%ROWTYPE INDEX BY BINARY_INTEGER;
21 TYPE rsrc_txns_tab IS TABLE OF gme_resource_txns%ROWTYPE INDEX BY BINARY_INTEGER;
22 /* Bug 5620671 Added param completed ind */
23 CURSOR Cur_get_txns(v_completed_ind NUMBER) IS
24 SELECT p.*, m.new_batch_id, m.organization_id, m.new_batch_no, m.plant_code
25 FROM gme_batch_txns_mig t, gme_batch_mapping_mig m, ic_tran_pnd p
26 WHERE t.batch_id = m.old_batch_id
27 AND p.trans_id = t.trans_id
28 AND NVL(t.migrated_ind,0) = 0
29 AND p.completed_ind = v_completed_ind
30 ORDER BY m.organization_id, t.batch_id, p.line_id, p.trans_id;
31
32 PROCEDURE recreate_open_batches(err_buf OUT NOCOPY VARCHAR2,
33 ret_code OUT NOCOPY VARCHAR2);
34 PROCEDURE build_batch_hdr(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
35 x_batch_header OUT NOCOPY gme_batch_header%ROWTYPE);
36 PROCEDURE build_mtl_dtl(p_mtl_dtl_mig IN gme_post_migration.mtl_dtl_mig_tab,
37 x_mtl_dtl OUT NOCOPY gme_common_pvt.material_details_tab);
38 PROCEDURE build_steps(p_steps_mig IN gme_post_migration.steps_mig_tab,
39 x_steps OUT NOCOPY gme_common_pvt.steps_tab);
40 PROCEDURE build_activities(p_activities_mig IN gme_post_migration.activ_mig_tab,
41 x_activities IN OUT NOCOPY gme_common_pvt.activities_tab);
42 PROCEDURE build_resources(p_resources_mig IN gme_post_migration.rsrc_mig_tab,
43 x_resources IN OUT NOCOPY gme_common_pvt.resources_tab);
44 PROCEDURE build_parameters(p_parameters_mig IN gme_post_migration.process_param_mig_tab,
45 x_parameters IN OUT NOCOPY gme_post_migration.process_param_tab);
46 PROCEDURE build_rsrc_txns(p_rsrc_txns_mig IN gme_post_migration.rsrc_txns_mig_tab,
47 x_rsrc_txns IN OUT NOCOPY gme_post_migration.rsrc_txns_tab);
48 FUNCTION get_new_step_id(p_old_step_id IN NUMBER,
49 p_new_batch_id IN NUMBER) RETURN NUMBER;
50 FUNCTION get_new_mat_id(p_old_mat_id IN NUMBER,
51 p_new_batch_id IN NUMBER) RETURN NUMBER;
52 PROCEDURE create_step_dependencies(p_old_batch_id IN NUMBER,
53 p_new_batch_id IN NUMBER);
54 PROCEDURE create_item_step_assoc(p_old_batch_id IN NUMBER,
55 p_new_batch_id IN NUMBER);
56 PROCEDURE create_batch_step_charges(p_old_batch_id IN NUMBER,
57 p_new_batch_id IN NUMBER);
58 PROCEDURE create_batch_step_transfers(p_old_batch_id IN NUMBER,
59 p_new_batch_id IN NUMBER);
60 PROCEDURE create_batch_mapping(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
61 p_batch_header IN gme_batch_header%ROWTYPE);
62 PROCEDURE create_phantom_links;
63 PROCEDURE release_batches;
64 PROCEDURE check_date(p_organization_id IN NUMBER,
65 p_date IN DATE,
66 x_date OUT NOCOPY DATE,
67 x_return_status OUT NOCOPY VARCHAR2);
68 PROCEDURE get_subinventory(p_whse_code IN VARCHAR2,
69 x_subinventory OUT NOCOPY VARCHAR2,
70 x_organization_id OUT NOCOPY NUMBER);
71 PROCEDURE get_locator(p_location IN VARCHAR2,
72 p_whse_code IN VARCHAR2,
73 x_organization_id OUT NOCOPY NUMBER,
74 x_locator_id OUT NOCOPY NUMBER,
75 x_subinventory OUT NOCOPY VARCHAR2);
76 FUNCTION get_latest_revision(p_organization_id IN NUMBER,
77 p_inventory_item_id IN NUMBER) RETURN VARCHAR2;
78 PROCEDURE get_subinv_locator_type(p_subinventory IN VARCHAR2,
79 p_organization_id IN NUMBER,
80 x_locator_type OUT NOCOPY NUMBER);
81 FUNCTION get_reason(p_reason_code IN VARCHAR2) RETURN NUMBER;
82 PROCEDURE create_locator(p_location IN VARCHAR2,
83 p_organization_id IN NUMBER,
84 p_subinventory_code IN VARCHAR2,
85 x_location_id OUT NOCOPY NUMBER,
86 x_failure_count OUT NOCOPY NUMBER);
87 PROCEDURE get_distribution_account(p_subinventory IN VARCHAR2,
88 p_org_id IN NUMBER,
89 x_dist_acct_id OUT NOCOPY NUMBER);
90 /* Bug 5620671 Added param completed ind */
91 PROCEDURE create_txns_reservations(p_completed_ind IN NUMBER);
92 PROCEDURE create_issue_receipt(p_curr_org_id IN NUMBER,
93 p_inventory_item_id IN NUMBER,
94 p_txn_rec IN Cur_get_txns%ROWTYPE,
95 p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
96 p_item_no IN VARCHAR2,
97 p_subinventory IN VARCHAR2,
98 p_locator_id IN NUMBER,
99 p_batch_org_id IN NUMBER,
100 x_subinventory OUT NOCOPY VARCHAR2,
101 x_locator_id OUT NOCOPY NUMBER,
102 x_lot_number OUT NOCOPY VARCHAR2,
103 x_return_status OUT NOCOPY VARCHAR2);
104 PROCEDURE insert_interface_recs(p_mti_rec IN mtl_transactions_interface%ROWTYPE,
105 p_mtli_rec IN mtl_transaction_lots_interface%ROWTYPE,
106 x_return_status OUT NOCOPY VARCHAR2);
107 PROCEDURE close_steps;
108 PROCEDURE insert_lab_lots;
109 END gme_post_migration;