[Home] [Help]
PACKAGE: APPS.GMD_COA_DATA_OM
Source
1 PACKAGE gmd_coa_data_om AUTHID CURRENT_USER AS
2 /* $Header: GMDCOA3S.pls 115.5 2003/05/23 15:43:34 magupta noship $ */
3 /* API name: GMD_COA_DATA_om
4 * Type: Group
5 * Function: gather COA data via business rules
6 * (customer/global/item-lot hierarchy)
7 * Pre-reqs: Package assumes validation is done in calling program/form
8 * Parameters for main procedure (populate_coa_data):
9 * IN: p_api_version IN NUMBER
10 * p_init_msg_list IN VARCHAR2 optional default = FND_API.G_FALSE
11 * p_commit IN VARCHAR2 optional default = FND_API.G_FALSE
12 * p_validation_level IN NUMBER optional
13 * default = FND_API.G_VALID_LEVEL_FULL
14 * rec_param IN t_coa_parameters record of parameters
15 * OUT: x_return_status OUT VARCHAR2(1)
16 * x_msg_count OUT NUMBER
17 * x_msg_data OUT VARCHAR2(2000)
18 * tbl_hdr OUT t_coa_header_tbl
19 * tbl_dtl OUT t_coa_detail_tbl
20 * tbl_spec_text OUT t_coa_text_tbl
21 * tbl_rslt_text OUT t_coa_text_tbl
22 *
23 * Version: 2.1
24 * Notes:
25 * End of comments ******************************************************* */
26
27
28
29
30 TYPE t_coa_hdr_rec IS RECORD
31 (gmd_coa_id BINARY_INTEGER,
32 order_id oe_order_headers.header_id%TYPE,
33 line_id oe_order_lines.line_id%TYPE,
34 delivery_detail_id wsh_delivery_details.delivery_detail_id%TYPE,
35 orgn_code op_ordr_hdr.orgn_code%TYPE,
36 order_no oe_order_headers.order_number%TYPE,
37 custpo_no oe_order_headers.cust_po_number%TYPE,
38 shipdate oe_order_lines.schedule_ship_date%TYPE,
39 cust_id hz_cust_accounts.cust_account_id%TYPE,
40 cust_no hz_cust_accounts.account_number%TYPE,
41 cust_name hz_parties.party_name%TYPE,
42 bol_id wsh_new_deliveries.delivery_id%TYPE,
43 bol_no wsh_new_deliveries.name%TYPE,
44 item_id ic_item_mst.item_id%TYPE,
45 item_no ic_item_mst.item_no%TYPE,
46 item_desc ic_item_mst.item_desc1%TYPE,
47 whse_code ic_whse_mst.whse_code%TYPE,
48 whse_name ic_whse_mst.whse_name%TYPE,
49 lot_id ic_lots_mst.lot_id%TYPE,
50 lot_no ic_lots_mst.lot_no%TYPE,
51 lot_desc ic_lots_mst.lot_desc%TYPE,
52 sublot_no ic_lots_mst.sublot_no%TYPE,
53 order_qty1 oe_order_lines.ordered_quantity%TYPE,
54 order_qty2 oe_order_lines.ordered_quantity2%TYPE,
55 order_um1 oe_order_lines.order_quantity_uom%TYPE,
56 order_um2 oe_order_lines.ordered_quantity_UOM2%TYPE,
57 ship_qty1 oe_order_lines.shipped_quantity%TYPE,
58 ship_qty2 oe_order_lines.shipped_quantity2%TYPE,
59 ship_from_org_id oe_order_lines.ship_to_org_id%TYPE,
60 org_id oe_order_lines.org_id%TYPE,
61 report_title VARCHAR2(4)
62 );
63
64 TYPE t_coa_dtl_rec IS RECORD
65 (gmd_coa_id BINARY_INTEGER,
66 qc_result_id gmd_results.result_id%TYPE,
67 result_date gmd_results.result_date%TYPE,
68 qc_spec_id gmd_specifications_b.spec_id%TYPE,
69 assay_code gmd_qc_tests_b.test_code%TYPE,
70 assay_desc gmd_qc_tests.test_desc%TYPE,
71 result gmd_results.result_value_char%TYPE,
72 specification gmd_spec_tests_b.target_value_char%TYPE,
73 min_spec gmd_spec_tests_b.min_value_num%TYPE,
74 max_spec gmd_spec_tests_b.max_value_num%TYPE,
75 uom gmd_spec_tests_b.test_uom%TYPE,
76 spec_text_code gmd_specifications_b.text_code%TYPE,
77 rslt_text_code gmd_results.text_code%TYPE,
78 coa_sort_order NUMBER
79 );
80
81 TYPE t_coa_text_rec IS RECORD
82 (gmd_coa_id BINARY_INTEGER,
83 text_code qc_text_tbl.text_code%TYPE,
84 paragraph_code qc_text_tbl.paragraph_code%TYPE,
85 line_no qc_text_tbl.line_no%TYPE,
86 text qc_text_tbl.text%TYPE
87 );
88
89 TYPE t_coa_parameters IS RECORD
90 (order_id op_ordr_hdr.order_id%TYPE,
91 orgn_code op_ordr_hdr.orgn_code%TYPE,
92 custpo_no op_ordr_dtl.custpo_no%TYPE,
93 from_shipdate op_ordr_dtl.sched_shipdate%TYPE,
94 to_shipdate op_ordr_dtl.sched_shipdate%TYPE,
95 cust_id hz_cust_accounts.cust_account_id%TYPE,
96 bol_id op_bill_lad.bol_id%TYPE,
97 item_id ic_item_mst.item_id%TYPE,
98 whse_code ic_whse_mst.whse_code%TYPE,
99 lot_id ic_lots_mst.lot_id%TYPE,
100 shipment_no wsh_new_deliveries.name%TYPE,
101 org_id oe_order_headers_all.org_id%TYPE
102 );
103
104 TYPE t_coa_header_tbl IS TABLE OF t_coa_hdr_rec INDEX BY BINARY_INTEGER;
105
106 TYPE t_coa_detail_tbl IS TABLE OF t_coa_dtl_rec INDEX BY BINARY_INTEGER;
107
108 TYPE t_coa_text_tbl IS TABLE OF t_coa_text_rec INDEX BY BINARY_INTEGER;
109
110 /* CONTEXT: GLOBAL */
111 v_report_title VARCHAR2(4) := 'COA';
112 empty_header t_coa_header_tbl;
113 empty_detail t_coa_detail_tbl;
114 empty_text t_coa_text_tbl;
115
116 PROCEDURE Populate_CoA_Data (
117 p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
119 p_commit IN VARCHAR2 := FND_API.G_FALSE,
120 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
121 rec_param IN t_coa_parameters,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 tbl_hdr OUT NOCOPY t_coa_header_tbl,
126 tbl_dtl OUT NOCOPY t_coa_detail_tbl,
127 tbl_spec_text OUT NOCOPY t_coa_text_tbl,
128 tbl_rslt_text OUT NOCOPY t_coa_text_tbl);
129
130 PROCEDURE Dump_To_Db_Tables (
131 p_api_version IN NUMBER,
132 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
134 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
135 tbl_hdr IN t_coa_header_tbl,
136 tbl_dtl IN t_coa_detail_tbl,
137 tbl_spec_text IN t_coa_text_tbl,
138 tbl_rslt_text IN t_coa_text_tbl,
139 x_return_status OUT NOCOPY VARCHAR2,
140 x_msg_count OUT NOCOPY NUMBER,
141 x_msg_data OUT NOCOPY VARCHAR2);
142
143 PROCEDURE run_coa_coc (
144 errbuf OUT NOCOPY VARCHAR2,
145 retcode OUT NOCOPY NUMBER,
146 p_delivery_id number);
147
148
149 END gmd_coa_data_om;