1 PACKAGE gme_post_migration AUTHID CURRENT_USER AS
2 /* $Header: GMEVRCBS.pls 120.6.12020000.2 2012/07/26 15:43:17 gmurator ship $ */
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 -- Bug 12966683 Increased the size from 16 to 80
12 TYPE locator_tab IS TABLE OF locator_rec INDEX BY VARCHAR2(80);
13 p_locator_tbl locator_tab;
14
15 TYPE mtl_dtl_mig_tab IS TABLE OF gme_material_details_mig%ROWTYPE INDEX BY BINARY_INTEGER;
16 TYPE steps_mig_tab IS TABLE OF gme_batch_steps_mig%ROWTYPE INDEX BY BINARY_INTEGER;
17 TYPE activ_mig_tab IS TABLE OF gme_batch_step_activ_mig%ROWTYPE INDEX BY BINARY_INTEGER;
18 TYPE rsrc_mig_tab IS TABLE OF gme_batch_step_resources_mig%ROWTYPE INDEX BY BINARY_INTEGER;
19 TYPE process_param_mig_tab IS TABLE OF gme_process_parameters_mig%ROWTYPE INDEX BY BINARY_INTEGER;
20 TYPE process_param_tab IS TABLE OF gme_process_parameters%ROWTYPE INDEX BY BINARY_INTEGER;
21 TYPE rsrc_txns_mig_tab IS TABLE OF gme_resource_txns_mig%ROWTYPE INDEX BY BINARY_INTEGER;
22 TYPE rsrc_txns_tab IS TABLE OF gme_resource_txns%ROWTYPE INDEX BY BINARY_INTEGER;
23
24 -- Bug 13706812 Add header table so that completed transactions are only.
25 -- processed if the batch was successfully released in the first place.
26 /* Bug 5620671 Added param completed ind */
27 CURSOR Cur_get_txns(v_completed_ind NUMBER) IS
28 SELECT p.*, m.new_batch_id, m.organization_id, m.new_batch_no, m.plant_code
29 FROM gme_batch_txns_mig t, gme_batch_mapping_mig m,
30 ic_tran_pnd p, gme_batch_header hdr -- Bug 13706812 Add header table.
31 WHERE t.batch_id = m.old_batch_id
32 AND p.trans_id = t.trans_id
33 AND NVL(t.migrated_ind,0) = 0
34 AND p.completed_ind = v_completed_ind
35 AND hdr.batch_id = m.new_batch_id -- Bug 13706812
36 AND (hdr.batch_status = 2 OR v_completed_ind = 0) -- Bug 13706812
37 ORDER BY m.organization_id, t.batch_id, p.line_id, p.trans_id;
38
39 PROCEDURE recreate_open_batches(err_buf OUT NOCOPY VARCHAR2,
40 ret_code OUT NOCOPY VARCHAR2);
41 PROCEDURE build_batch_hdr(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
42 x_batch_header OUT NOCOPY gme_batch_header%ROWTYPE);
43 PROCEDURE build_mtl_dtl(p_mtl_dtl_mig IN gme_post_migration.mtl_dtl_mig_tab,
44 x_mtl_dtl OUT NOCOPY gme_common_pvt.material_details_tab);
45 PROCEDURE build_steps(p_steps_mig IN gme_post_migration.steps_mig_tab,
46 x_steps OUT NOCOPY gme_common_pvt.steps_tab);
47 PROCEDURE build_activities(p_activities_mig IN gme_post_migration.activ_mig_tab,
48 x_activities IN OUT NOCOPY gme_common_pvt.activities_tab);
49 PROCEDURE build_resources(p_resources_mig IN gme_post_migration.rsrc_mig_tab,
50 x_resources IN OUT NOCOPY gme_common_pvt.resources_tab);
51 PROCEDURE build_parameters(p_parameters_mig IN gme_post_migration.process_param_mig_tab,
52 x_parameters IN OUT NOCOPY gme_post_migration.process_param_tab);
53 PROCEDURE build_rsrc_txns(p_rsrc_txns_mig IN gme_post_migration.rsrc_txns_mig_tab,
54 x_rsrc_txns IN OUT NOCOPY gme_post_migration.rsrc_txns_tab);
55 FUNCTION get_new_step_id(p_old_step_id IN NUMBER,
56 p_new_batch_id IN NUMBER) RETURN NUMBER;
57 FUNCTION get_new_mat_id(p_old_mat_id IN NUMBER,
58 p_new_batch_id IN NUMBER) RETURN NUMBER;
59 PROCEDURE create_step_dependencies(p_old_batch_id IN NUMBER,
60 p_new_batch_id IN NUMBER);
61 PROCEDURE create_item_step_assoc(p_old_batch_id IN NUMBER,
62 p_new_batch_id IN NUMBER);
63 PROCEDURE create_batch_step_charges(p_old_batch_id IN NUMBER,
64 p_new_batch_id IN NUMBER);
65 PROCEDURE create_batch_step_transfers(p_old_batch_id IN NUMBER,
66 p_new_batch_id IN NUMBER);
67 PROCEDURE create_batch_mapping(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
68 p_batch_header IN gme_batch_header%ROWTYPE);
69 PROCEDURE create_phantom_links;
70 PROCEDURE release_batches;
71 PROCEDURE check_date(p_organization_id IN NUMBER,
72 p_date IN DATE,
73 x_date OUT NOCOPY DATE,
74 x_return_status OUT NOCOPY VARCHAR2);
75 PROCEDURE get_subinventory(p_whse_code IN VARCHAR2,
76 x_subinventory OUT NOCOPY VARCHAR2,
77 x_organization_id OUT NOCOPY NUMBER);
78 PROCEDURE get_locator(p_location IN VARCHAR2,
79 p_whse_code IN VARCHAR2,
80 x_organization_id OUT NOCOPY NUMBER,
81 x_locator_id OUT NOCOPY NUMBER,
82 x_subinventory OUT NOCOPY VARCHAR2);
83 FUNCTION get_latest_revision(p_organization_id IN NUMBER,
84 p_inventory_item_id IN NUMBER) RETURN VARCHAR2;
85 PROCEDURE get_subinv_locator_type(p_subinventory IN VARCHAR2,
86 p_organization_id IN NUMBER,
87 x_locator_type OUT NOCOPY NUMBER);
88 FUNCTION get_reason(p_reason_code IN VARCHAR2) RETURN NUMBER;
89 PROCEDURE create_locator(p_location IN VARCHAR2,
90 p_organization_id IN NUMBER,
91 p_subinventory_code IN VARCHAR2,
92 x_location_id OUT NOCOPY NUMBER,
93 x_failure_count OUT NOCOPY NUMBER);
94 PROCEDURE get_distribution_account(p_subinventory IN VARCHAR2,
95 p_org_id IN NUMBER,
96 x_dist_acct_id OUT NOCOPY NUMBER);
97 /* Bug 5620671 Added param completed ind */
98 PROCEDURE create_txns_reservations(p_completed_ind IN NUMBER);
99 PROCEDURE create_issue_receipt(p_curr_org_id IN NUMBER,
100 p_inventory_item_id IN NUMBER,
101 p_txn_rec IN Cur_get_txns%ROWTYPE,
102 p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
103 p_item_no IN VARCHAR2,
104 p_subinventory IN VARCHAR2,
105 p_locator_id IN NUMBER,
106 p_batch_org_id IN NUMBER,
107 x_subinventory OUT NOCOPY VARCHAR2,
108 x_locator_id OUT NOCOPY NUMBER,
109 x_lot_number OUT NOCOPY VARCHAR2,
110 x_return_status OUT NOCOPY VARCHAR2);
111 PROCEDURE insert_interface_recs(p_mti_rec IN mtl_transactions_interface%ROWTYPE,
112 p_mtli_rec IN mtl_transaction_lots_interface%ROWTYPE,
113 x_return_status OUT NOCOPY VARCHAR2);
114 PROCEDURE close_steps;
115 PROCEDURE insert_lab_lots;
116 END gme_post_migration;