[Home] [Help]
PACKAGE: APPS.GMD_COA_DATA_NEW
Source
1 PACKAGE gmd_coa_data_new AUTHID CURRENT_USER AS
2 /* $Header: GMDCOA2S.pls 115.2 2002/12/03 22:31:42 magupta noship $ */
3 /* API name: GMD_COA_DATA_NEW
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 TYPE t_coa_hdr_rec IS RECORD
28 (gmd_coa_id BINARY_INTEGER,
29 order_id op_ordr_hdr.order_id%TYPE,
30 line_id op_ordr_dtl.line_id%TYPE,
31 orgn_code op_ordr_hdr.orgn_code%TYPE,
32 order_no op_ordr_hdr.order_no%TYPE,
33 custpo_no op_ordr_dtl.custpo_no%TYPE,
34 shipdate op_ordr_dtl.sched_shipdate%TYPE,
35 cust_id op_cust_mst.cust_id%TYPE,
36 cust_no op_cust_mst.custsort_no%TYPE,
37 cust_name op_cust_mst.cust_name%TYPE,
38 bol_id op_bill_lad.bol_id%TYPE,
39 bol_no op_bill_lad.bol_no%TYPE,
40 item_id ic_item_mst.item_id%TYPE,
41 item_no ic_item_mst.item_no%TYPE,
42 item_desc ic_item_mst.item_desc1%TYPE,
43 whse_code ic_whse_mst.whse_code%TYPE,
44 whse_name ic_whse_mst.whse_name%TYPE,
45 lot_id ic_lots_mst.lot_id%TYPE,
46 lot_no ic_lots_mst.lot_no%TYPE,
47 lot_desc ic_lots_mst.lot_desc%TYPE,
48 sublot_no ic_lots_mst.sublot_no%TYPE,
49 order_qty1 op_ordr_dtl.order_qty1%TYPE,
50 order_qty2 op_ordr_dtl.order_qty2%TYPE,
51 order_um1 op_ordr_dtl.order_um1%TYPE,
52 order_um2 op_ordr_dtl.order_um2%TYPE,
53 ship_qty1 op_ordr_dtl.ship_qty1%TYPE,
54 ship_qty2 op_ordr_dtl.ship_qty2%TYPE,
55 report_title VARCHAR2(4)
56 );
57
58 TYPE t_coa_dtl_rec IS RECORD
59 (gmd_coa_id BINARY_INTEGER,
60 qc_result_id gmd_results.result_id%TYPE,
61 result_date gmd_results.result_date%TYPE,
62 qc_spec_id gmd_specifications_b.spec_id%TYPE,
63 assay_code gmd_qc_tests_b.test_code%TYPE,
64 assay_desc gmd_qc_tests.test_desc%TYPE,
65 result gmd_results.result_value_char%TYPE,
66 specification gmd_spec_tests_b.target_value_char%TYPE,
67 min_spec gmd_spec_tests_b.min_value_num%TYPE,
68 max_spec gmd_spec_tests_b.max_value_num%TYPE,
69 uom gmd_spec_tests_b.test_uom%TYPE,
70 spec_text_code gmd_specifications_b.text_code%TYPE,
71 rslt_text_code gmd_results.text_code%TYPE,
72 coa_sort_order NUMBER
73 );
74
75 TYPE t_coa_text_rec IS RECORD
76 (gmd_coa_id BINARY_INTEGER,
77 text_code qc_text_tbl.text_code%TYPE,
78 paragraph_code qc_text_tbl.paragraph_code%TYPE,
79 line_no qc_text_tbl.line_no%TYPE,
80 text qc_text_tbl.text%TYPE
81 );
82
83 TYPE t_coa_parameters IS RECORD
84 (order_id op_ordr_hdr.order_id%TYPE,
85 orgn_code op_ordr_hdr.orgn_code%TYPE,
86 custpo_no op_ordr_dtl.custpo_no%TYPE,
87 from_shipdate op_ordr_dtl.sched_shipdate%TYPE,
88 to_shipdate op_ordr_dtl.sched_shipdate%TYPE,
89 cust_id op_cust_mst.cust_id%TYPE,
90 bol_id op_bill_lad.bol_id%TYPE,
91 item_id ic_item_mst.item_id%TYPE,
92 whse_code ic_whse_mst.whse_code%TYPE,
93 lot_id ic_lots_mst.lot_id%TYPE
94 );
95
96 TYPE t_coa_header_tbl IS TABLE OF t_coa_hdr_rec INDEX BY BINARY_INTEGER;
97
98 TYPE t_coa_detail_tbl IS TABLE OF t_coa_dtl_rec INDEX BY BINARY_INTEGER;
99
100 TYPE t_coa_text_tbl IS TABLE OF t_coa_text_rec INDEX BY BINARY_INTEGER;
101
102 /* CONTEXT: GLOBAL */
103 v_report_title VARCHAR2(4) := 'COA';
104 empty_header t_coa_header_tbl;
105 empty_detail t_coa_detail_tbl;
106 empty_text t_coa_text_tbl;
107
108 PROCEDURE Populate_CoA_Data (
109 p_api_version IN NUMBER,
110 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
111 p_commit IN VARCHAR2 := FND_API.G_FALSE,
112 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
113 rec_param IN t_coa_parameters,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_msg_count OUT NOCOPY NUMBER,
116 x_msg_data OUT NOCOPY VARCHAR2,
117 tbl_hdr OUT NOCOPY t_coa_header_tbl,
118 tbl_dtl OUT NOCOPY t_coa_detail_tbl,
119 tbl_spec_text OUT NOCOPY t_coa_text_tbl,
120 tbl_rslt_text OUT NOCOPY t_coa_text_tbl);
121
122 PROCEDURE Dump_To_Db_Tables (
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
125 p_commit IN VARCHAR2 := FND_API.G_FALSE,
126 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
127 tbl_hdr IN t_coa_header_tbl,
128 tbl_dtl IN t_coa_detail_tbl,
129 tbl_spec_text IN t_coa_text_tbl,
130 tbl_rslt_text IN t_coa_text_tbl,
131 x_return_status OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2);
134
135 END gmd_coa_data_new;