1: PACKAGE BODY pon_open_interface_pvt AS
2: /* $Header: PON_OPEN_INTERFACE_PVT.plb 120.7.12020000.11 2013/05/22 11:21:03 rakearun noship $ */
3:
4: --TYPE allowed_values_table IS TABLE OF VARCHAR2(20);
5: neg_header_record_data neg_header_record;
6:
7: TYPE column_test_table IS TABLE OF allowed_value_column_object;
8: allowed_values_table_data allowed_values_table;
4: --TYPE allowed_values_table IS TABLE OF VARCHAR2(20);
5: neg_header_record_data neg_header_record;
6:
7: TYPE column_test_table IS TABLE OF allowed_value_column_object;
8: allowed_values_table_data allowed_values_table;
9: yn_data allowed_values_table;
10: object_data allowed_value_column_object;
11: column_test_table_data column_test_table := column_test_table();
12:
5: neg_header_record_data neg_header_record;
6:
7: TYPE column_test_table IS TABLE OF allowed_value_column_object;
8: allowed_values_table_data allowed_values_table;
9: yn_data allowed_values_table;
10: object_data allowed_value_column_object;
11: column_test_table_data column_test_table := column_test_table();
12:
13: TYPE validate_header_col_table IS TABLE OF VARCHAR2(50);
58:
59:
60: BEGIN
61: column_test_table_data.extend(31);
62: yn_data := allowed_values_table('Y','N');
63: allowed_values_table_data := allowed_values_table('CONTRACT','BLANKET','STANDARD');
64: object_data := allowed_value_column_object('CONTRACT_TYPE',allowed_values_table_data);
65: column_test_table_data(1) := object_data;
66: allowed_values_table_data := allowed_values_table('PUBLIC','PRIVATE','HIERARCHY');
59:
60: BEGIN
61: column_test_table_data.extend(31);
62: yn_data := allowed_values_table('Y','N');
63: allowed_values_table_data := allowed_values_table('CONTRACT','BLANKET','STANDARD');
64: object_data := allowed_value_column_object('CONTRACT_TYPE',allowed_values_table_data);
65: column_test_table_data(1) := object_data;
66: allowed_values_table_data := allowed_values_table('PUBLIC','PRIVATE','HIERARCHY');
67: object_data := allowed_value_column_object('SECURITY_LEVEL_CODE',allowed_values_table_data); --SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PON_SECURITY_LEVEL_CODE' AND LANGUAGE=UserEnv('LANG'))
60: BEGIN
61: column_test_table_data.extend(31);
62: yn_data := allowed_values_table('Y','N');
63: allowed_values_table_data := allowed_values_table('CONTRACT','BLANKET','STANDARD');
64: object_data := allowed_value_column_object('CONTRACT_TYPE',allowed_values_table_data);
65: column_test_table_data(1) := object_data;
66: allowed_values_table_data := allowed_values_table('PUBLIC','PRIVATE','HIERARCHY');
67: object_data := allowed_value_column_object('SECURITY_LEVEL_CODE',allowed_values_table_data); --SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PON_SECURITY_LEVEL_CODE' AND LANGUAGE=UserEnv('LANG'))
68: column_test_table_data(2) := object_data;
62: yn_data := allowed_values_table('Y','N');
63: allowed_values_table_data := allowed_values_table('CONTRACT','BLANKET','STANDARD');
64: object_data := allowed_value_column_object('CONTRACT_TYPE',allowed_values_table_data);
65: column_test_table_data(1) := object_data;
66: allowed_values_table_data := allowed_values_table('PUBLIC','PRIVATE','HIERARCHY');
67: object_data := allowed_value_column_object('SECURITY_LEVEL_CODE',allowed_values_table_data); --SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PON_SECURITY_LEVEL_CODE' AND LANGUAGE=UserEnv('LANG'))
68: column_test_table_data(2) := object_data;
69: IF(p_contract_type IN ('BLANKET','CONTRACT')) THEN
70: object_data := allowed_value_column_object('GLOBAL_AGREEMENT_FLAG',yn_data);
63: allowed_values_table_data := allowed_values_table('CONTRACT','BLANKET','STANDARD');
64: object_data := allowed_value_column_object('CONTRACT_TYPE',allowed_values_table_data);
65: column_test_table_data(1) := object_data;
66: allowed_values_table_data := allowed_values_table('PUBLIC','PRIVATE','HIERARCHY');
67: object_data := allowed_value_column_object('SECURITY_LEVEL_CODE',allowed_values_table_data); --SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PON_SECURITY_LEVEL_CODE' AND LANGUAGE=UserEnv('LANG'))
68: column_test_table_data(2) := object_data;
69: IF(p_contract_type IN ('BLANKET','CONTRACT')) THEN
70: object_data := allowed_value_column_object('GLOBAL_AGREEMENT_FLAG',yn_data);
71: column_test_table_data(3) := object_data;
75: object_data := allowed_value_column_object('OPEN_AUCTION_NOW_FLAG',yn_data);
76: column_test_table_data(5) := object_data;
77: object_data := allowed_value_column_object('PUBLISH_AUCTION_NOW_FLAG',yn_data);
78: column_test_table_data(6) := object_data;
79: allowed_values_table_data := allowed_values_table('OPEN_BIDDING', 'SEALED_BIDDING', 'SEALED_AUCTION');
80: object_data := allowed_value_column_object('BID_VISIBILITY_CODE',allowed_values_table_data);
81: column_test_table_data(7) := object_data;
82: allowed_values_table_data := allowed_values_table('LINE_LEVEL_BIDDING', 'MUST_BID_ALL_ITEMS');
83: object_data := allowed_value_column_object('BID_SCOPE_CODE',allowed_values_table_data);
76: column_test_table_data(5) := object_data;
77: object_data := allowed_value_column_object('PUBLISH_AUCTION_NOW_FLAG',yn_data);
78: column_test_table_data(6) := object_data;
79: allowed_values_table_data := allowed_values_table('OPEN_BIDDING', 'SEALED_BIDDING', 'SEALED_AUCTION');
80: object_data := allowed_value_column_object('BID_VISIBILITY_CODE',allowed_values_table_data);
81: column_test_table_data(7) := object_data;
82: allowed_values_table_data := allowed_values_table('LINE_LEVEL_BIDDING', 'MUST_BID_ALL_ITEMS');
83: object_data := allowed_value_column_object('BID_SCOPE_CODE',allowed_values_table_data);
84: column_test_table_data(8) := object_data;
78: column_test_table_data(6) := object_data;
79: allowed_values_table_data := allowed_values_table('OPEN_BIDDING', 'SEALED_BIDDING', 'SEALED_AUCTION');
80: object_data := allowed_value_column_object('BID_VISIBILITY_CODE',allowed_values_table_data);
81: column_test_table_data(7) := object_data;
82: allowed_values_table_data := allowed_values_table('LINE_LEVEL_BIDDING', 'MUST_BID_ALL_ITEMS');
83: object_data := allowed_value_column_object('BID_SCOPE_CODE',allowed_values_table_data);
84: column_test_table_data(8) := object_data;
85: allowed_values_table_data := allowed_values_table('PUBLIC_BID_LIST', 'PRIVATE_BID_LIST');
86: object_data := allowed_value_column_object('BID_LIST_TYPE',allowed_values_table_data);
79: allowed_values_table_data := allowed_values_table('OPEN_BIDDING', 'SEALED_BIDDING', 'SEALED_AUCTION');
80: object_data := allowed_value_column_object('BID_VISIBILITY_CODE',allowed_values_table_data);
81: column_test_table_data(7) := object_data;
82: allowed_values_table_data := allowed_values_table('LINE_LEVEL_BIDDING', 'MUST_BID_ALL_ITEMS');
83: object_data := allowed_value_column_object('BID_SCOPE_CODE',allowed_values_table_data);
84: column_test_table_data(8) := object_data;
85: allowed_values_table_data := allowed_values_table('PUBLIC_BID_LIST', 'PRIVATE_BID_LIST');
86: object_data := allowed_value_column_object('BID_LIST_TYPE',allowed_values_table_data);
87: column_test_table_data(9) := object_data;
81: column_test_table_data(7) := object_data;
82: allowed_values_table_data := allowed_values_table('LINE_LEVEL_BIDDING', 'MUST_BID_ALL_ITEMS');
83: object_data := allowed_value_column_object('BID_SCOPE_CODE',allowed_values_table_data);
84: column_test_table_data(8) := object_data;
85: allowed_values_table_data := allowed_values_table('PUBLIC_BID_LIST', 'PRIVATE_BID_LIST');
86: object_data := allowed_value_column_object('BID_LIST_TYPE',allowed_values_table_data);
87: column_test_table_data(9) := object_data;
88: allowed_values_table_data := allowed_values_table('SINGLE_BID_ONLY', 'MULTIPLE_BIDS_ALLOWED');
89: object_data := allowed_value_column_object('BID_FREQUENCY_CODE',allowed_values_table_data);
82: allowed_values_table_data := allowed_values_table('LINE_LEVEL_BIDDING', 'MUST_BID_ALL_ITEMS');
83: object_data := allowed_value_column_object('BID_SCOPE_CODE',allowed_values_table_data);
84: column_test_table_data(8) := object_data;
85: allowed_values_table_data := allowed_values_table('PUBLIC_BID_LIST', 'PRIVATE_BID_LIST');
86: object_data := allowed_value_column_object('BID_LIST_TYPE',allowed_values_table_data);
87: column_test_table_data(9) := object_data;
88: allowed_values_table_data := allowed_values_table('SINGLE_BID_ONLY', 'MULTIPLE_BIDS_ALLOWED');
89: object_data := allowed_value_column_object('BID_FREQUENCY_CODE',allowed_values_table_data);
90: column_test_table_data(10) := object_data;
84: column_test_table_data(8) := object_data;
85: allowed_values_table_data := allowed_values_table('PUBLIC_BID_LIST', 'PRIVATE_BID_LIST');
86: object_data := allowed_value_column_object('BID_LIST_TYPE',allowed_values_table_data);
87: column_test_table_data(9) := object_data;
88: allowed_values_table_data := allowed_values_table('SINGLE_BID_ONLY', 'MULTIPLE_BIDS_ALLOWED');
89: object_data := allowed_value_column_object('BID_FREQUENCY_CODE',allowed_values_table_data);
90: column_test_table_data(10) := object_data;
91: allowed_values_table_data := allowed_values_table('PRICE_ONLY','MULTI_ATTRIBUTE_SCORING');
92: object_data := allowed_value_column_object('BID_RANKING',allowed_values_table_data);
85: allowed_values_table_data := allowed_values_table('PUBLIC_BID_LIST', 'PRIVATE_BID_LIST');
86: object_data := allowed_value_column_object('BID_LIST_TYPE',allowed_values_table_data);
87: column_test_table_data(9) := object_data;
88: allowed_values_table_data := allowed_values_table('SINGLE_BID_ONLY', 'MULTIPLE_BIDS_ALLOWED');
89: object_data := allowed_value_column_object('BID_FREQUENCY_CODE',allowed_values_table_data);
90: column_test_table_data(10) := object_data;
91: allowed_values_table_data := allowed_values_table('PRICE_ONLY','MULTI_ATTRIBUTE_SCORING');
92: object_data := allowed_value_column_object('BID_RANKING',allowed_values_table_data);
93: column_test_table_data(11) := object_data;
87: column_test_table_data(9) := object_data;
88: allowed_values_table_data := allowed_values_table('SINGLE_BID_ONLY', 'MULTIPLE_BIDS_ALLOWED');
89: object_data := allowed_value_column_object('BID_FREQUENCY_CODE',allowed_values_table_data);
90: column_test_table_data(10) := object_data;
91: allowed_values_table_data := allowed_values_table('PRICE_ONLY','MULTI_ATTRIBUTE_SCORING');
92: object_data := allowed_value_column_object('BID_RANKING',allowed_values_table_data);
93: column_test_table_data(11) := object_data;
94: allowed_values_table_data := allowed_values_table('NONE','WIN_LOSE','NUMBERING');
95: object_data := allowed_value_column_object('RANKING_INDICATOR',allowed_values_table_data);
88: allowed_values_table_data := allowed_values_table('SINGLE_BID_ONLY', 'MULTIPLE_BIDS_ALLOWED');
89: object_data := allowed_value_column_object('BID_FREQUENCY_CODE',allowed_values_table_data);
90: column_test_table_data(10) := object_data;
91: allowed_values_table_data := allowed_values_table('PRICE_ONLY','MULTI_ATTRIBUTE_SCORING');
92: object_data := allowed_value_column_object('BID_RANKING',allowed_values_table_data);
93: column_test_table_data(11) := object_data;
94: allowed_values_table_data := allowed_values_table('NONE','WIN_LOSE','NUMBERING');
95: object_data := allowed_value_column_object('RANKING_INDICATOR',allowed_values_table_data);
96: column_test_table_data(12) := object_data;
90: column_test_table_data(10) := object_data;
91: allowed_values_table_data := allowed_values_table('PRICE_ONLY','MULTI_ATTRIBUTE_SCORING');
92: object_data := allowed_value_column_object('BID_RANKING',allowed_values_table_data);
93: column_test_table_data(11) := object_data;
94: allowed_values_table_data := allowed_values_table('NONE','WIN_LOSE','NUMBERING');
95: object_data := allowed_value_column_object('RANKING_INDICATOR',allowed_values_table_data);
96: column_test_table_data(12) := object_data;
97: allowed_values_table_data := allowed_values_table('PARTIAL_QTY_BIDS_ALLOWED', 'FULL_QTY_BIDS_REQD');
98: object_data := allowed_value_column_object('FULL_QUANTITY_BID_CODE',allowed_values_table_data);
91: allowed_values_table_data := allowed_values_table('PRICE_ONLY','MULTI_ATTRIBUTE_SCORING');
92: object_data := allowed_value_column_object('BID_RANKING',allowed_values_table_data);
93: column_test_table_data(11) := object_data;
94: allowed_values_table_data := allowed_values_table('NONE','WIN_LOSE','NUMBERING');
95: object_data := allowed_value_column_object('RANKING_INDICATOR',allowed_values_table_data);
96: column_test_table_data(12) := object_data;
97: allowed_values_table_data := allowed_values_table('PARTIAL_QTY_BIDS_ALLOWED', 'FULL_QTY_BIDS_REQD');
98: object_data := allowed_value_column_object('FULL_QUANTITY_BID_CODE',allowed_values_table_data);
99: column_test_table_data(13) := object_data;
93: column_test_table_data(11) := object_data;
94: allowed_values_table_data := allowed_values_table('NONE','WIN_LOSE','NUMBERING');
95: object_data := allowed_value_column_object('RANKING_INDICATOR',allowed_values_table_data);
96: column_test_table_data(12) := object_data;
97: allowed_values_table_data := allowed_values_table('PARTIAL_QTY_BIDS_ALLOWED', 'FULL_QTY_BIDS_REQD');
98: object_data := allowed_value_column_object('FULL_QUANTITY_BID_CODE',allowed_values_table_data);
99: column_test_table_data(13) := object_data;
100: IF(p_auction_type NOT IN ('REQUEST_FOR_QUOTE','SOLICITATION')) THEN
101: object_data := allowed_value_column_object('MULTIPLE_ROUNDS_FLAG',yn_data);
94: allowed_values_table_data := allowed_values_table('NONE','WIN_LOSE','NUMBERING');
95: object_data := allowed_value_column_object('RANKING_INDICATOR',allowed_values_table_data);
96: column_test_table_data(12) := object_data;
97: allowed_values_table_data := allowed_values_table('PARTIAL_QTY_BIDS_ALLOWED', 'FULL_QTY_BIDS_REQD');
98: object_data := allowed_value_column_object('FULL_QUANTITY_BID_CODE',allowed_values_table_data);
99: column_test_table_data(13) := object_data;
100: IF(p_auction_type NOT IN ('REQUEST_FOR_QUOTE','SOLICITATION')) THEN
101: object_data := allowed_value_column_object('MULTIPLE_ROUNDS_FLAG',yn_data);
102: column_test_table_data(14) := object_data;
108: column_test_table_data(16) := object_data;
109: END IF;
110: object_data := allowed_value_column_object('AWARD_APPROVAL_FLAG',yn_data);
111: column_test_table_data(17) := object_data;
112: allowed_values_table_data := allowed_values_table('REQUISITION', 'BLANKET');
113: object_data := allowed_value_column_object('AUCTION_ORIGINATION_CODE',allowed_values_table_data);
114: column_test_table_data(18) := object_data;
115: IF(p_is_complex = 'Y') THEN
116: object_data := allowed_value_column_object('ADVANCE_NEGOTIABLE_FLAG',yn_data);
109: END IF;
110: object_data := allowed_value_column_object('AWARD_APPROVAL_FLAG',yn_data);
111: column_test_table_data(17) := object_data;
112: allowed_values_table_data := allowed_values_table('REQUISITION', 'BLANKET');
113: object_data := allowed_value_column_object('AUCTION_ORIGINATION_CODE',allowed_values_table_data);
114: column_test_table_data(18) := object_data;
115: IF(p_is_complex = 'Y') THEN
116: object_data := allowed_value_column_object('ADVANCE_NEGOTIABLE_FLAG',yn_data);
117: column_test_table_data(19) := object_data;
129: IF(p_auction_type IN ('REQUEST_FOR_QUOTE','SOLICITATION')) THEN
130: object_data := allowed_value_column_object('TWO_PART_FLAG',yn_data);
131: column_test_table_data(25) := object_data;
132: END IF;
133: allowed_values_table_data := allowed_values_table('BUYER','SUPPLIER','BOTH','NONE');
134: object_data := allowed_value_column_object('PF_TYPE_ALLOWED',allowed_values_table_data);
135: column_test_table_data(26) := object_data;
136: IF(p_contract_type = 'BLANKET') THEN
137: allowed_values_table_data := allowed_values_table('REQUIRED','OPTIONAL','NONE');
130: object_data := allowed_value_column_object('TWO_PART_FLAG',yn_data);
131: column_test_table_data(25) := object_data;
132: END IF;
133: allowed_values_table_data := allowed_values_table('BUYER','SUPPLIER','BOTH','NONE');
134: object_data := allowed_value_column_object('PF_TYPE_ALLOWED',allowed_values_table_data);
135: column_test_table_data(26) := object_data;
136: IF(p_contract_type = 'BLANKET') THEN
137: allowed_values_table_data := allowed_values_table('REQUIRED','OPTIONAL','NONE');
138: object_data := allowed_value_column_object('PRICE_BREAK_RESPONSE',allowed_values_table_data);
133: allowed_values_table_data := allowed_values_table('BUYER','SUPPLIER','BOTH','NONE');
134: object_data := allowed_value_column_object('PF_TYPE_ALLOWED',allowed_values_table_data);
135: column_test_table_data(26) := object_data;
136: IF(p_contract_type = 'BLANKET') THEN
137: allowed_values_table_data := allowed_values_table('REQUIRED','OPTIONAL','NONE');
138: object_data := allowed_value_column_object('PRICE_BREAK_RESPONSE',allowed_values_table_data);
139: column_test_table_data(27) := object_data;
140: END IF;
141: allowed_values_table_data := allowed_values_table('PRICE_BREAKS','QUANTITY_BASED','NONE');
134: object_data := allowed_value_column_object('PF_TYPE_ALLOWED',allowed_values_table_data);
135: column_test_table_data(26) := object_data;
136: IF(p_contract_type = 'BLANKET') THEN
137: allowed_values_table_data := allowed_values_table('REQUIRED','OPTIONAL','NONE');
138: object_data := allowed_value_column_object('PRICE_BREAK_RESPONSE',allowed_values_table_data);
139: column_test_table_data(27) := object_data;
140: END IF;
141: allowed_values_table_data := allowed_values_table('PRICE_BREAKS','QUANTITY_BASED','NONE');
142: object_data := allowed_value_column_object('PRICE_TIERS_INDICATOR',allowed_values_table_data);
137: allowed_values_table_data := allowed_values_table('REQUIRED','OPTIONAL','NONE');
138: object_data := allowed_value_column_object('PRICE_BREAK_RESPONSE',allowed_values_table_data);
139: column_test_table_data(27) := object_data;
140: END IF;
141: allowed_values_table_data := allowed_values_table('PRICE_BREAKS','QUANTITY_BASED','NONE');
142: object_data := allowed_value_column_object('PRICE_TIERS_INDICATOR',allowed_values_table_data);
143: column_test_table_data(28) := object_data;
144: object_data := allowed_value_column_object('AMENDMENT_FLAG',yn_data);
145: column_test_table_data(29) := object_data;
138: object_data := allowed_value_column_object('PRICE_BREAK_RESPONSE',allowed_values_table_data);
139: column_test_table_data(27) := object_data;
140: END IF;
141: allowed_values_table_data := allowed_values_table('PRICE_BREAKS','QUANTITY_BASED','NONE');
142: object_data := allowed_value_column_object('PRICE_TIERS_INDICATOR',allowed_values_table_data);
143: column_test_table_data(28) := object_data;
144: object_data := allowed_value_column_object('AMENDMENT_FLAG',yn_data);
145: column_test_table_data(29) := object_data;
146: object_data := allowed_value_column_object('AUCTION_PUBLISHED_FLAG',yn_data);