[Home] [Help]
PACKAGE: APPS.GMD_COA_DATA_OM_NEW
Source
1 PACKAGE GMD_COA_DATA_OM_NEW AUTHID CURRENT_USER AS
2 /* $Header: GMDGCOAS.pls 120.4.12020000.2 2012/07/17 10:18:27 mtou ship $ */
3 /* API name: GMD_COA_DATA_OM_NEW
4 * Type: Group
5 * Function: gather COA data
6 **/
7
8 -- global variables
9 g_gmdlog_location VARCHAR2(255) := NULL;
10
11 TYPE t_coa_hdr_rec IS RECORD
12 (gmd_coa_id BINARY_INTEGER,
13 order_id oe_order_headers.header_id%TYPE,
14 line_id oe_order_lines.line_id%TYPE,
15 delivery_detail_id wsh_delivery_details.delivery_detail_id%TYPE,
16 --orgn_code op_ordr_hdr.orgn_code%TYPE, --INVCONV
17 organization_id mtl_parameters.organization_id%TYPE, --INVCONV
18 organization_code mtl_parameters.organization_code%TYPE, --INVCONV
19 order_no oe_order_headers.order_number%TYPE,
20 custpo_no oe_order_headers.cust_po_number%TYPE,
21 shipdate oe_order_lines.schedule_ship_date%TYPE,
22 cust_id hz_cust_accounts.cust_account_id%TYPE,
23 cust_no hz_cust_accounts.account_number%TYPE,
24 cust_name hz_parties.party_name%TYPE,
25 bol_id wsh_new_deliveries.delivery_id%TYPE,
26 bol_no wsh_new_deliveries.name%TYPE,
27 --item_id ic_item_mst.item_id%TYPE, --INVCONV
28 --item_no ic_item_mst.item_no%TYPE, --INVCONV
29 --item_desc ic_item_mst.item_desc1%TYPE, --INVCONV
30 inventory_item_id mtl_system_items_b_kfv.inventory_item_id%TYPE, --INVCONV
31 item_number mtl_system_items_b_kfv.concatenated_segments%TYPE, --INVCONV
32 item_description mtl_system_items_b_kfv.description%TYPE, --INVCONV
33 revision mtl_item_revisions.revision%TYPE, --Bug# 4662469
34 --whse_code ic_whse_mst.whse_code%TYPE, --INVCONV
35 --whse_name ic_whse_mst.whse_name%TYPE, --INVCONV
36 subinventory mtl_secondary_inventories.secondary_inventory_name%TYPE, --INVCONV
37 --lot_id ic_lots_mst.lot_id%TYPE, --INVCONV
38 --lot_no ic_lots_mst.lot_no%TYPE, --INVCONV
39 --lot_desc ic_lots_mst.lot_desc%TYPE, --INVCONV
40 --sublot_no ic_lots_mst.sublot_no%TYPE, --INVCONV
41 lot_number mtl_lot_numbers.lot_number%TYPE, --INVCONV
42 lot_description mtl_lot_numbers.description%TYPE, --INVCONV
43 order_qty1 oe_order_lines.ordered_quantity%TYPE,
44 order_qty2 oe_order_lines.ordered_quantity2%TYPE,
45 order_uom1 oe_order_lines.order_quantity_uom%TYPE, --INVCONV
46 order_uom2 oe_order_lines.ordered_quantity_UOM2%TYPE, --INVCONV
47 ship_qty1 oe_order_lines.shipped_quantity%TYPE,
48 ship_qty2 oe_order_lines.shipped_quantity2%TYPE,
49 ship_qty_uom1 oe_order_lines.shipping_quantity_uom%TYPE, --Bug # 3710191 added these two lines for the fields ship_uom1 and ship_uom2
50 ship_qty_uom2 oe_order_lines.shipping_quantity_uom2%TYPE, --INVCONV
51 ship_from_org_id oe_order_lines.ship_from_org_id%TYPE,
52 ship_to_site_id oe_order_lines.ship_to_org_id%TYPE, --Bug 4166529 added.
53 org_id oe_order_lines.org_id%TYPE,
54 report_title VARCHAR2(4),
55 spec_hdr_text_code NUMBER(10) -- Bug # 4260445
56 );
57
58 TYPE t_coa_dtl_rec IS RECORD
59 (gmd_coa_id BINARY_INTEGER,
60 result_id gmd_results.result_id%TYPE,
61 result_date gmd_results.result_date%TYPE,
62 spec_id gmd_specifications_b.spec_id%TYPE,
63 test_id gmd_qc_tests_b.test_id%TYPE,
64 test_code gmd_qc_tests_b.test_code%TYPE,
65 test_method varchar2(80),
66 test_type gmd_qc_tests_b.test_type%TYPE,
67 test_display gmd_qc_tests.test_desc%TYPE,
68 result gmd_results.result_value_char%TYPE,
69 specification varchar2(200),
70 min_spec varchar2(240),
71 max_spec varchar2(240),
72 mean gmd_composite_results.mean%TYPE,
73 mode_char gmd_composite_results.mode_char%TYPE,
74 test_unit gmd_qc_tests_b.test_unit%TYPE,
75 spec_text_code gmd_specifications_b.text_code%TYPE,
76 rslt_text_code gmd_results.text_code%TYPE,
77 result_value_char gmd_results.result_value_char%TYPE,
78 result_value_num gmd_results.result_value_num%TYPE,
79 report_precision NUMBER,
80 coa_sort_order NUMBER
81 );
82
83 TYPE t_coa_parameters IS RECORD
84 (order_id oe_order_headers_all.header_id%TYPE,
85 --orgn_code sy_orgn_mst.orgn_code%TYPE, --INVCONV
86 organization_id mtl_parameters.organization_id%TYPE, --INVCONV
87 cust_id hz_cust_accounts.cust_account_id%TYPE,
88 delivery_id wsh_new_deliveries.delivery_id%TYPE,
89 inventory_item_id mtl_system_items_b_kfv.inventory_item_id%TYPE, --INVCONV
90 revision mtl_item_revisions.revision%TYPE, --Bug# 4662469
91 --whse_code ic_whse_mst.whse_code%TYPE, --INVCONV
92 subinventory mtl_secondary_inventories.secondary_inventory_name%TYPE, --INVCONV
93 --location ic_loct_mst.whse_code%TYPE, --INVCONV
94 locator_id NUMBER, --INVCONV
95 --lot_id ic_lots_mst.lot_id%TYPE, --INVCONV
96 --lot_no ic_lots_mst.lot_no%TYPE, --INVCONV
97 lot_number mtl_lot_numbers.lot_number%TYPE, --INVCONV
98 org_id oe_order_headers_all.org_id%TYPE,
99 sampling_event_id gmd_sampling_events.sampling_event_id%TYPE,
100 spec_id gmd_specifications_b.spec_id%TYPE,
101 ship_to_site_id hz_cust_site_uses_all.site_use_id%TYPE -- Bug# 5399406
102 );
103
104 TYPE MATCH_RESULT_LOT_REC_TYPE IS RECORD
105 ( inventory_item_id NUMBER -- IN
106 ,revision NUMBER --Bug# 4662469
107 --,lot_id NUMBER -- IN --INVCONV
108 --,lot_no VARCHAR2(32) -- IN --INVCONV
109 ,parent_lot_number mtl_lot_numbers.parent_lot_number%TYPE -- 9646524 added
110 ,lot_number mtl_lot_numbers.lot_number%TYPE --IN --INVCONV
111 --,whse_code VARCHAR2(4) -- IN --INVCONV
112 ,subinventory mtl_secondary_inventories.secondary_inventory_name%TYPE --IN --INVCONV
113 --,location VARCHAR2(16) -- IN --INVCONV
114 ,locator_id NUMBER --IN --INVCONV
115 ,cust_id NUMBER -- IN
116 ,called_from VARCHAR2(4) -- IN
117 ,sample_id NUMBER -- OUT
118 ,spec_match_type VARCHAR2(1) -- OUT
119 ,result_type VARCHAR2(1) -- OUT
120 ,event_spec_disp_id NUMBER -- OUT
121 );
122
123 TYPE result_lot_match_tbl IS TABLE OF MATCH_RESULT_LOT_REC_TYPE INDEX BY BINARY_INTEGER;
124
125
126
127 PROCEDURE populate_coa_data(
128 p_api_version IN NUMBER
129 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
130 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
131 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
132 , x_return_status OUT NOCOPY VARCHAR2
133 , x_msg_count OUT NOCOPY NUMBER
134 , x_msg_data OUT NOCOPY VARCHAR2
135 , param_rec IN t_coa_parameters);
136
137 FUNCTION get_text_for_range(p_test_id NUMBER,
138 p_value NUMBER) RETURN VARCHAR2;
139
140 FUNCTION getprecision (p_value IN NUMBER,
141 p_report_precision IN NUMBER) return VARCHAR2;
142
143 PROCEDURE Log_Initialize;
144
145 PROCEDURE PrintLn(p_msg IN VARCHAR2);
146
147 PROCEDURE populate_hdr_text(tbl_hdr IN t_coa_hdr_rec,
148 x_return_status OUT NOCOPY VARCHAR2); -- Bug # 4260445
149
150 END gmd_coa_data_om_new;