[Home] [Help]
13:
14:
15: PROCEDURE derive_item_id
16: (
17: p_key IN po_session_gt.key%TYPE,
18: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
19: p_vendor_id_tbl IN PO_TBL_NUMBER,
20: p_intf_header_id_tbl IN PO_TBL_NUMBER,
21: p_intf_line_id_tbl IN PO_TBL_NUMBER,
27: );
28:
29: PROCEDURE derive_item_revision
30: (
31: p_key IN po_session_gt.key%TYPE,
32: p_item_id_tbl IN PO_TBL_NUMBER,
33: x_item_revision_tbl IN OUT NOCOPY PO_TBL_VARCHAR5
34: );
35:
34: );
35:
36: PROCEDURE derive_job_business_group_id
37: (
38: p_key IN po_session_gt.key%TYPE,
39: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
40: p_job_business_group_name_tbl IN PO_TBL_VARCHAR2000,
41: x_job_business_group_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
42: );
42: );
43:
44: PROCEDURE derive_job_id
45: (
46: p_key IN po_session_gt.key%TYPE,
47: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
48: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
49: p_job_business_group_name_tbl IN PO_TBL_VARCHAR2000,
50: p_job_name_tbl IN PO_TBL_VARCHAR2000,
53: );
54:
55: PROCEDURE derive_category_id
56: (
57: p_key IN po_session_gt.key%TYPE,
58: p_category_tbl IN PO_TBL_VARCHAR2000,
59: x_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
60: );
61:
60: );
61:
62: PROCEDURE derive_ip_category_id
63: (
64: p_key IN po_session_gt.key%TYPE,
65: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
66: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
67: p_ip_category_tbl IN PO_TBL_VARCHAR2000,
68: x_ip_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
69: );
70:
71: PROCEDURE derive_unit_of_measure
72: (
73: p_key IN po_session_gt.key%TYPE,
74: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
75: p_uom_code_tbl IN PO_TBL_VARCHAR5,
76: x_unit_of_measure_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
77: );
77: );
78:
79: PROCEDURE derive_line_type_id
80: (
81: p_key IN po_session_gt.key%TYPE,
82: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
83: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
84: p_line_type_tbl IN PO_TBL_VARCHAR30,
85: x_line_type_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
86: );
87:
88: PROCEDURE derive_un_number_id
89: (
90: p_key IN po_session_gt.key%TYPE,
91: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
92: p_un_number_tbl IN PO_TBL_VARCHAR30,
93: x_un_number_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
94: );
94: );
95:
96: PROCEDURE derive_hazard_class_id
97: (
98: p_key IN po_session_gt.key%TYPE,
99: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
100: p_hazard_class_tbl IN PO_TBL_VARCHAR100,
101: x_hazard_class_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
102: );
102: );
103:
104: PROCEDURE derive_template_id
105: (
106: p_key IN po_session_gt.key%TYPE,
107: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
108: p_template_name_tbl IN PO_TBL_VARCHAR30,
109: x_template_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
110: );
110: );
111:
112: PROCEDURE default_info_from_line_type
113: (
114: p_key IN po_session_gt.key%TYPE,
115: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
116: p_line_type_id_tbl IN PO_TBL_NUMBER,
117: x_order_type_lookup_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30,
118: x_purchase_basis_tbl IN OUT NOCOPY PO_TBL_VARCHAR30,
123: );
124:
125: PROCEDURE default_info_from_item
126: (
127: p_key IN po_session_gt.key%TYPE,
128: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
129: p_item_id_tbl IN PO_TBL_NUMBER,
130: x_item_desc_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
131: x_unit_of_measure_tbl OUT NOCOPY PO_TBL_VARCHAR30,
138: );
139:
140: PROCEDURE default_info_from_job
141: (
142: p_key IN po_session_gt.key%TYPE,
143: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
144: p_job_id_tbl IN PO_TBL_NUMBER,
145: x_item_desc_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
146: x_category_id_tbl OUT NOCOPY PO_TBL_NUMBER
147: );
148:
149: PROCEDURE default_po_cat_id_from_ip
150: (
151: p_key IN po_session_gt.key%TYPE,
152: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
153: p_ip_category_id_tbl IN PO_TBL_NUMBER,
154: x_po_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
155: );
155: );
156:
157: PROCEDURE default_ip_cat_id_from_po
158: (
159: p_key IN po_session_gt.key%TYPE,
160: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
161: p_po_category_id_tbl IN PO_TBL_NUMBER,
162: x_ip_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
163: );
163: );
164:
165: PROCEDURE default_hc_id_from_un_number
166: (
167: p_key IN po_session_gt.key%TYPE,
168: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
169: p_un_number_tbl IN PO_TBL_VARCHAR30,
170: x_hazard_class_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
171: );
189: );
190:
191: PROCEDURE uniqueness_check_on_desc
192: (
193: p_key IN po_session_gt.key%TYPE,
194: p_group_num IN NUMBER,
195: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
196: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
197: );
197: );
198:
199: PROCEDURE uniqueness_check_on_item
200: (
201: p_key IN po_session_gt.key%TYPE,
202: p_group_num IN NUMBER,
203: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
204: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
205: );
205: );
206:
207: PROCEDURE uniqueness_check_on_vpn
208: (
209: p_key IN po_session_gt.key%TYPE,
210: p_group_num IN NUMBER,
211: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
212: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
213: );
213: );
214:
215: PROCEDURE uniqueness_check_on_job
216: (
217: p_key IN po_session_gt.key%TYPE,
218: p_group_num IN NUMBER,
219: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
220: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
221: );
221: );
222:
223: PROCEDURE uniqueness_check_on_line_num
224: (
225: p_key IN po_session_gt.key%TYPE,
226: p_group_num IN NUMBER,
227: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
228: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
229: );
230:
231:
232: PROCEDURE set_action_add
233: (
234: p_key IN po_session_gt.key%TYPE,
235: p_group_num IN NUMBER,
236: p_target_lines_index_tbl IN PO_TBL_NUMBER,
237: p_check_line_num_assign IN VARCHAR2,
238: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
258:
259: --CLM PDOI Integration Starts
260: PROCEDURE derive_contract_type
261: (
262: p_key IN po_session_gt.key%TYPE,
263: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
264: p_contract_type_display_tbl IN PO_TBL_VARCHAR240,
265: x_contract_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR240
266: );
266: );
267:
268: PROCEDURE derive_cost_constraint
269: (
270: p_key IN po_session_gt.key%TYPE,
271: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
272: p_cost_constraint_display_tbl IN PO_TBL_VARCHAR240,
273: x_cost_constraint_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
274: );
274: );
275:
276: PROCEDURE derive_clm_idc_type
277: (
278: p_key IN po_session_gt.key%TYPE,
279: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
280: p_clm_idc_type_display_tbl IN PO_TBL_VARCHAR240,
281: x_clm_idc_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR240
282: );
282: );
283:
284: PROCEDURE derive_clm_base_line_num
285: (
286: p_key IN po_session_gt.key%TYPE,
287: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
288: p_clm_base_line_num_disp_tbl IN PO_TBL_VARCHAR240,
289: p_intf_line_id_tbl IN PO_TBL_NUMBER,
290: p_intf_header_id_tbl IN PO_TBL_NUMBER,
294: );
295:
296: PROCEDURE derive_from_line_id
297: (
298: p_key IN po_session_gt.key%TYPE,
299: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
300: p_from_line_disp_tbl IN PO_TBL_VARCHAR240,
301: p_from_header_id_tbl IN PO_TBL_NUMBER,
302: x_from_line_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
303: );
304:
305: PROCEDURE derive_from_header_id
306: (
307: p_key IN po_session_gt.key%TYPE,
308: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
309: p_from_header_disp_tbl IN PO_TBL_VARCHAR240,
310: x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
311: );
311: );
312:
313: PROCEDURE default_clm_option_indicator
314: (
315: p_key IN po_session_gt.key%TYPE,
316: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
317: p_intf_line_id_tbl IN PO_TBL_NUMBER,
318: p_clm_base_line_num_tbl IN PO_TBL_NUMBER,
319: x_clm_option_indicator_tbl IN OUT NOCOPY PO_TBL_VARCHAR1
320: );
321:
322: PROCEDURE default_clm_option_num
323: (
324: p_key IN po_session_gt.key%TYPE,
325: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
326: p_intf_header_id_tbl IN PO_TBL_NUMBER,
327: p_intf_line_id_tbl IN PO_TBL_NUMBER,
328: p_clm_base_line_num_tbl IN PO_TBL_NUMBER,
331: );
332:
333: PROCEDURE default_from_header_id
334: (
335: p_key IN po_session_gt.key%TYPE,
336: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
337: p_intf_header_id_tbl IN PO_TBL_NUMBER,
338: p_intf_line_id_tbl IN PO_TBL_NUMBER,
339: x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2100: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2101: d_position NUMBER;
2102:
2103: -- key of temp table used to identify the derived result
2104: l_key po_session_gt.key%TYPE;
2105:
2106: -- table used to save the index of the each row
2107: l_index_tbl DBMS_SQL.NUMBER_TABLE;
2108: BEGIN
2818: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2819: d_position NUMBER;
2820:
2821: -- key of temp table used to identify the derived result
2822: l_key po_session_gt.key%TYPE;
2823:
2824: -- table used to save the index of the each row
2825: l_index_tbl DBMS_SQL.NUMBER_TABLE;
2826: BEGIN
3055: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3056: d_position NUMBER;
3057:
3058: -- key of temp table used to identify the derived result
3059: l_key po_session_gt.key%TYPE;
3060:
3061: -- table used to save the index of the each row
3062: l_index_tbl DBMS_SQL.NUMBER_TABLE;
3063:
3098: END IF;
3099:
3100: PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_DEFAULT);
3101:
3102: -- get key value to identify rows in po_session_gt table
3103: l_key := PO_CORE_S.get_session_gt_nextval;
3104:
3105: -- default line_type_id that will be used in other defaulting logic;
3106: -- l_index_tbl table is initialized as well
3539: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3540: d_position NUMBER;
3541:
3542: -- key of temp table used to identify the derived result
3543: l_key po_session_gt.key%TYPE;
3544:
3545: -- table used to save the index of the each row
3546: l_num_list DBMS_SQL.NUMBER_TABLE;
3547:
3579: d_position := 10;
3580:
3581: -- get values from draft tables
3582: FORALL i IN 1..l_num_list.COUNT
3583: INSERT INTO po_session_gt(key, num1, char1, num2, num3, num4) --num4 is added by vegajula for bugno:9531925
3584: SELECT l_key,
3585: l_num_list(i),
3586: order_type_lookup_code,
3587: item_id,
3594: d_position := 20;
3595:
3596: -- get values from txn table if no draft line exist
3597: FORALL i IN 1..l_num_list.COUNT
3598: INSERT INTO po_session_gt(key, num1, char1, num2, num3, num4) --num4 is added by vegajula for bugno:9531925
3599: SELECT l_key,
3600: l_num_list(i),
3601: order_type_lookup_code,
3602: item_id,
3610: AND draft_id = x_lines.draft_id_tbl(i));
3611:
3612: d_position := 30;
3613:
3614: DELETE FROM po_session_gt
3615: WHERE key = l_key
3616: RETURNING num1, char1, num2, num3, num4 BULK COLLECT INTO ---num4 added by vegajula for bugno:9531925
3617: l_index_tbl, l_order_type_tbl, l_item_id_tbl, l_job_id_tbl, l_po_header_id_tbl ; --l_po_header_id_tbl added by vegajula for bugno:9531925
3618:
4107: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4108: d_position NUMBER;
4109:
4110: -- key of temp table used to identify the records
4111: l_key po_session_gt.key%TYPE;
4112:
4113: -- table storing the index of records within the batch
4114: l_index_tbl DBMS_SQL.NUMBER_TABLE;
4115: l_index NUMBER;
4135: END IF;
4136:
4137: PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_UNIQUENESS_CHECK);
4138:
4139: -- get key value for po_session_gt table
4140: l_key := PO_CORE_S.get_session_gt_nextval;
4141:
4142: -- call individual uniqueness check procedure for each unique criteria
4143: IF (p_type = PO_PDOI_CONSTANTS.g_LINE_CSR_SYNC_ON_DESC) THEN
4259: d_position := 90;
4260:
4261: -- check whether uom changed for a line that has a release shipment
4262: FORALL i IN 1..x_lines.rec_count
4263: INSERT INTO po_session_gt(key, num1)
4264: SELECT l_key,
4265: l_index_tbl(i)
4266: FROM po_lines txn_lines
4267: WHERE txn_lines.po_line_id = x_lines.po_line_id_tbl(i)
4278: FROM po_lines_all
4279: WHERE from_line_id = txn_lines.po_line_id)
4280: );
4281:
4282: DELETE FROM po_session_gt
4283: WHERE key = l_key
4284: RETURNING num1 BULK COLLECT INTO l_expire_line_index_tbl;
4285:
4286: FOR i IN 1..l_expire_line_index_tbl.COUNT
4677: --Function: derive item_id from item or vendor_product_num
4678: --Parameters:
4679: --IN:
4680: -- p_key
4681: -- key used to identify rows in po_session_gt
4682: -- p_index_tbl
4683: -- table containging the indexes of all rows
4684: -- p_vendor_id_tbl
4685: -- list of vendor_ids read from the header
4703: --End of Comments
4704: ------------------------------------------------------------------------
4705: PROCEDURE derive_item_id
4706: (
4707: p_key IN po_session_gt.key%TYPE,
4708: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4709: p_vendor_id_tbl IN PO_TBL_NUMBER,
4710: p_intf_header_id_tbl IN PO_TBL_NUMBER,
4711: p_intf_line_id_tbl IN PO_TBL_NUMBER,
4744: END IF;
4745:
4746: -- derive based on item_num
4747: FORALL i IN 1..p_index_tbl.COUNT
4748: INSERT INTO po_session_gt(key, num1, num2)
4749: SELECT p_key,
4750: p_index_tbl(i),
4751: inventory_item_id
4752: FROM mtl_system_items_kfv
4762: -- sql so that matched category for the vendor product number
4763: -- item id will be derived.
4764:
4765: FORALL i IN 1.. p_index_tbl.COUNT
4766: INSERT INTO po_session_gt(key, num1, num2)
4767: SELECT DISTINCT
4768: p_key,
4769: p_index_tbl(i),
4770: txn_lines.item_id
4783:
4784: d_position := 20;
4785:
4786: -- read result from temp table, and delete the records from temp table
4787: DELETE FROM po_session_gt
4788: WHERE key = p_key
4789: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4790:
4791: d_position := 30;
4845: -- and create_item parameter value
4846: --Parameters:
4847: --IN:
4848: -- p_key
4849: -- key used to identify rows in po_session_gt
4850: -- p_item_id_tbl
4851: -- list of item_ids read within the batch
4852: --IN OUT:
4853: -- x_item_revision_tbl
4857: --End of Comments
4858: ------------------------------------------------------------------------
4859: PROCEDURE derive_item_revision
4860: (
4861: p_key IN po_session_gt.key%TYPE,
4862: p_item_id_tbl IN PO_TBL_NUMBER,
4863: x_item_revision_tbl IN OUT NOCOPY PO_TBL_VARCHAR5
4864: ) IS
4865:
4910: -- job_business_group_name
4911: --Parameters:
4912: --IN:
4913: -- p_key
4914: -- key used to identify rows in po_session_gt
4915: -- p_index_tbl
4916: -- table containging the indexes of all rows
4917: -- p_job_business_group_name_tbl
4918: -- list of job_business_group_names read within the batch
4926: --End of Comments
4927: ------------------------------------------------------------------------
4928: PROCEDURE derive_job_business_group_id
4929: (
4930: p_key IN po_session_gt.key%TYPE,
4931: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4932: p_job_business_group_name_tbl IN PO_TBL_VARCHAR2000,
4933: x_job_business_group_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4934: ) IS
4951: END IF;
4952:
4953: -- derive id from name
4954: FORALL i IN 1.. p_index_tbl.COUNT
4955: INSERT INTO po_session_gt(key, num1, num2)
4956: SELECT p_key,
4957: p_index_tbl(i),
4958: business_group_id
4959: FROM per_business_groups_perf
4965:
4966: d_position := 10;
4967:
4968: -- read result from temp table, and delete the records from temp table
4969: DELETE FROM po_session_gt
4970: WHERE key = p_key
4971: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4972:
4973: d_position := 20;
5003: --Function: derive job_id from job_business_group_name and job_name
5004: --Parameters:
5005: --IN:
5006: -- p_key
5007: -- key used to identify rows in po_session_gt
5008: -- p_index_tbl
5009: -- table containging the indexes of all rows
5010: -- p_file_line_language_tbl
5011: -- list of line level languages
5026: --End of Comments
5027: ------------------------------------------------------------------------
5028: PROCEDURE derive_job_id
5029: (
5030: p_key IN po_session_gt.key%TYPE,
5031: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5032: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
5033: p_job_business_group_name_tbl IN PO_TBL_VARCHAR2000,
5034: p_job_name_tbl IN PO_TBL_VARCHAR2000,
5079: d_position := 10;
5080:
5081: -- derive job_id from job_name
5082: FORALL i IN 1..p_index_tbl.COUNT
5083: INSERT INTO po_session_gt(key, num1, num2)
5084: SELECT p_key,
5085: p_index_tbl(i),
5086: jobs_b.job_id
5087: FROM per_jobs jobs_b,
5124: -- END OF info added for debugging purpose */
5125:
5126: -- derive job_id for lines with job_business_group_id = null
5127: FORALL i IN 1..p_index_tbl.COUNT
5128: INSERT INTO po_session_gt(key, num1, num2)
5129: SELECT p_key,
5130: p_index_tbl(i),
5131: jobs_b.job_id
5132: FROM per_jobs jobs_b,
5148: d_position := 30;
5149:
5150: -- derive job_id for lines with job_business_group_id <> null
5151: FORALL i IN 1..p_index_tbl.COUNT
5152: INSERT INTO po_session_gt(key, num1, num2)
5153: SELECT p_key,
5154: p_index_tbl(i),
5155: jobs_b.job_id
5156: FROM per_jobs jobs_b,
5172:
5173: d_position := 40;
5174:
5175: -- retrive result from temp table and delete the records in temp table
5176: DELETE FROM po_session_gt
5177: WHERE key = p_key
5178: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5179:
5180: d_position := 50;
5232: --Function: derive po category_id from po category name
5233: --Parameters:
5234: --IN:
5235: -- p_key
5236: -- key used to identify rows in po_session_gt
5237: -- p_category_tbl
5238: -- list of categories read within the batch
5239: --IN OUT:
5240: -- x_category_id_tbl
5246: --End of Comments
5247: ------------------------------------------------------------------------
5248: PROCEDURE derive_category_id
5249: (
5250: p_key IN po_session_gt.key%TYPE,
5251: p_category_tbl IN PO_TBL_VARCHAR2000,
5252: x_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5253: ) IS
5254:
5310: --Function: derive po category_id from po category name
5311: --Parameters:
5312: --IN:
5313: -- p_key
5314: -- key used to identify rows in po_session_gt
5315: -- p_index_tbl
5316: -- table containging the indexes of all rows
5317: -- p_file_line_language_tbl
5318: -- list of line level languages
5328: --End of Comments
5329: ------------------------------------------------------------------------
5330: PROCEDURE derive_ip_category_id
5331: (
5332: p_key IN po_session_gt.key%TYPE,
5333: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5334: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
5335: p_ip_category_tbl IN PO_TBL_VARCHAR2000,
5336: x_ip_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5353: END IF;
5354:
5355: -- 1. derive ip_category_id based on category key
5356: FORALL i IN 1.. p_index_tbl.COUNT
5357: INSERT INTO po_session_gt(key, num1, char1)
5358: SELECT p_key,
5359: p_index_tbl(i),
5360: cat.rt_category_id
5361: FROM icx_cat_categories_v cat
5365:
5366: d_position := 10;
5367:
5368: -- read result from temp table, and delete the records from temp table
5369: DELETE FROM po_session_gt
5370: WHERE key = p_key
5371: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5372:
5373: d_position := 20;
5389: -- bug 5489942: derivation is based on line level language
5390: -- 2. derive ip_category_id based on category name,
5391: -- name is language specific
5392: FORALL i IN 1.. p_index_tbl.COUNT
5393: INSERT INTO po_session_gt(key, num1, char1)
5394: SELECT p_key,
5395: p_index_tbl(i),
5396: rt_category_id
5397: FROM icx_cat_categories_v
5403:
5404: d_position := 40;
5405:
5406: -- read result from temp table, and delete the records from temp table
5407: DELETE FROM po_session_gt
5408: WHERE key = p_key
5409: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5410:
5411: d_position := 50;
5442: --Function: derive unit_of_measure from uom_code
5443: --Parameters:
5444: --IN:
5445: -- p_key
5446: -- key used to identify rows in po_session_gt
5447: -- p_index_tbl
5448: -- table containging the indexes of all rows
5449: -- p_uom_code_tbl
5450: -- list of uom_codes read within the batch
5458: --End of Comments
5459: ------------------------------------------------------------------------
5460: PROCEDURE derive_unit_of_measure
5461: (
5462: p_key IN po_session_gt.key%TYPE,
5463: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5464: p_uom_code_tbl IN PO_TBL_VARCHAR5,
5465: x_unit_of_measure_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
5466: ) IS
5481: END IF;
5482:
5483: -- derive unit_of_measure from uom_code
5484: FORALL i IN 1.. p_index_tbl.COUNT
5485: INSERT INTO po_session_gt(key, num1, char1)
5486: SELECT p_key,
5487: p_index_tbl(i),
5488: unit_of_measure
5489: FROM po_units_of_measure_val_v
5493:
5494: d_position := 10;
5495:
5496: -- read result from temp table, and delete the records from temp table
5497: DELETE FROM po_session_gt
5498: WHERE key = p_key
5499: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5500:
5501: d_position := 20;
5532: --Function: derive line_type_id from line_type
5533: --Parameters:
5534: --IN:
5535: -- p_key
5536: -- key used to identify rows in po_session_gt
5537: -- p_index_tbl
5538: -- table containging the indexes of all rows
5539: -- p_file_line_language_tbl
5540: -- list of line level languages
5550: --End of Comments
5551: ------------------------------------------------------------------------
5552: PROCEDURE derive_line_type_id
5553: (
5554: p_key IN po_session_gt.key%TYPE,
5555: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5556: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
5557: p_line_type_tbl IN PO_TBL_VARCHAR30,
5558: x_line_type_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5576:
5577: -- bug 5489942: derivation is based on line level language
5578: -- derive line_type_id from line_type
5579: FORALL i IN 1..p_index_tbl.COUNT
5580: INSERT INTO po_session_gt(key, num1, num2)
5581: SELECT p_key,
5582: p_index_tbl(i),
5583: b.line_type_id
5584: FROM po_line_types_b b,
5593:
5594: d_position := 10;
5595:
5596: -- read result from temp table, and delete the records from temp table
5597: DELETE FROM po_session_gt
5598: WHERE key = p_key
5599: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5600:
5601: d_position := 20;
5631: --Function: derive un_number_id from un_number
5632: --Parameters:
5633: --IN:
5634: -- p_key
5635: -- key used to identify rows in po_session_gt
5636: -- p_index_tbl
5637: -- table containging the indexes of all rows
5638: -- p_un_number_tbl
5639: -- list of un_numbers read within the batch
5647: --End of Comments
5648: ------------------------------------------------------------------------
5649: PROCEDURE derive_un_number_id
5650: (
5651: p_key IN po_session_gt.key%TYPE,
5652: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5653: p_un_number_tbl IN PO_TBL_VARCHAR30,
5654: x_un_number_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5655: ) IS
5670: END IF;
5671:
5672: -- derive un_number_id from un_number
5673: FORALL i IN 1.. p_index_tbl.COUNT
5674: INSERT INTO po_session_gt(key, num1, num2)
5675: SELECT p_key,
5676: p_index_tbl(i),
5677: un_number_id
5678: FROM po_un_numbers_vl
5683:
5684: d_position := 10;
5685:
5686: -- read result from temp table, and delete the records from temp table
5687: DELETE FROM po_session_gt
5688: WHERE key = p_key
5689: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5690:
5691: d_position := 20;
5721: --Function: derive hazard_class_id from hazard_class
5722: --Parameters:
5723: --IN:
5724: -- p_key
5725: -- key used to identify rows in po_session_gt
5726: -- p_index_tbl
5727: -- table containging the indexes of all rows
5728: -- p_hazard_class_tbl
5729: -- list of hazard_classes read within the batch
5737: --End of Comments
5738: ------------------------------------------------------------------------
5739: PROCEDURE derive_hazard_class_id
5740: (
5741: p_key IN po_session_gt.key%TYPE,
5742: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5743: p_hazard_class_tbl IN PO_TBL_VARCHAR100,
5744: x_hazard_class_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5745: ) IS
5760: END IF;
5761:
5762: -- derive hazard_class_id from hazard_class
5763: FORALL i IN 1..p_index_tbl.COUNT
5764: INSERT INTO po_session_gt(key, num1, num2)
5765: SELECT p_key,
5766: p_index_tbl(i),
5767: hazard_class_id
5768: FROM po_hazard_classes_val_v
5772:
5773: d_position := 10;
5774:
5775: -- read result from temp table, and delete the records from temp table
5776: DELETE FROM po_session_gt
5777: WHERE key = p_key
5778: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5779:
5780: d_position := 20;
5810: --Function: derive template_id from template_name
5811: --Parameters:
5812: --IN:
5813: -- p_key
5814: -- key used to identify rows in po_session_gt
5815: -- p_index_tbl
5816: -- table containging the indexes of all rows
5817: -- p_template_name_tbl
5818: -- list of template_names read within the batch
5826: --End of Comments
5827: ------------------------------------------------------------------------
5828: PROCEDURE derive_template_id
5829: (
5830: p_key IN po_session_gt.key%TYPE,
5831: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5832: p_template_name_tbl IN PO_TBL_VARCHAR30,
5833: x_template_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5834: ) IS
5849: END IF;
5850:
5851: -- derive template_id from template_name
5852: FORALL i IN 1..p_index_tbl.COUNT
5853: INSERT INTO po_session_gt(key, num1, num2)
5854: SELECT p_key,
5855: p_index_tbl(i),
5856: template_id
5857: FROM mtl_item_templates
5864:
5865: d_position := 10;
5866:
5867: -- read result from temp table, and delete the records from temp table
5868: DELETE FROM po_session_gt
5869: WHERE key = p_key
5870: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
5871:
5872: d_position := 20;
5909: -- 6. unit_price
5910: --Parameters:
5911: --IN:
5912: -- p_key
5913: -- key used to identify rows in po_session_gt
5914: -- p_index_tbl
5915: -- table containging the indexes of all rows
5916: -- p_line_type_id_tbl
5917: -- list of line_type_ids read within the batch
5932: --End of Comments
5933: ------------------------------------------------------------------------
5934: PROCEDURE default_info_from_line_type
5935: (
5936: p_key IN po_session_gt.key%TYPE,
5937: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5938: p_line_type_id_tbl IN PO_TBL_NUMBER,
5939: x_order_type_lookup_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30,
5940: x_purchase_basis_tbl IN OUT NOCOPY PO_TBL_VARCHAR30,
5975: x_unit_price_tbl.EXTEND(p_index_tbl.COUNT);
5976:
5977: -- retrieve the values based on line type id
5978: FORALL i IN 1..p_index_tbl.COUNT
5979: INSERT INTO po_session_gt(key, num1, char1, char2, char3, num2, char4, num3)
5980: SELECT p_key,
5981: p_index_tbl(i),
5982: order_type_lookup_code,
5983: purchase_basis,
5990:
5991: d_position := 10;
5992:
5993: -- read result from temp table and delete the records at the same time
5994: DELETE FROM po_session_gt
5995: WHERE key = p_key
5996: RETURNING num1, char1, char2, char3, num2, char4, num3
5997: BULK COLLECT INTO
5998: l_index_tbl,
6064: -- 8. secondary_unit_of_measure
6065: --Parameters:
6066: --IN:
6067: -- p_key
6068: -- key used to identify rows in po_session_gt
6069: -- p_index_tbl
6070: -- table containging the indexes of all rows
6071: -- p_item_id_tbl
6072: -- list of item_ids read within the batch
6091: --End of Comments
6092: ------------------------------------------------------------------------
6093: PROCEDURE default_info_from_item
6094: (
6095: p_key IN po_session_gt.key%TYPE,
6096: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
6097: p_item_id_tbl IN PO_TBL_NUMBER,
6098: x_item_desc_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
6099: x_unit_of_measure_tbl OUT NOCOPY PO_TBL_VARCHAR30,
6150: -- retrieve the values based on item id and default inv org id
6151: -- bug 4723323: get secondary_unit_of_measure value for dual-um
6152: -- control item, used in default logic
6153: FORALL i IN 1..p_index_tbl.COUNT
6154: INSERT INTO po_session_gt(key, num1, char1, char2, num2, num3,
6155: num4, num5, num6, char3)
6156: SELECT p_key,
6157: p_index_tbl(i),
6158: item_tl.description,
6179:
6180: d_position := 10;
6181:
6182: -- read result from temp table and delete the records at the same time
6183: DELETE FROM po_session_gt
6184: WHERE key = p_key
6185: RETURNING num1, char1, char2, num2, num3, num4, num5, num6, char3
6186: BULK COLLECT INTO
6187: l_index_tbl,
6255: -- 2. category_id
6256: --Parameters:
6257: --IN:
6258: -- p_key
6259: -- key used to identify rows in po_session_gt
6260: -- p_index_tbl
6261: -- table containging the indexes of all rows
6262: -- p_job_id_tbl
6263: -- list of job_ids read within the batch
6270: --End of Comments
6271: ------------------------------------------------------------------------
6272: PROCEDURE default_info_from_job
6273: (
6274: p_key IN po_session_gt.key%TYPE,
6275: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
6276: p_job_id_tbl IN PO_TBL_NUMBER,
6277: x_item_desc_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
6278: x_category_id_tbl OUT NOCOPY PO_TBL_NUMBER
6302: x_category_id_tbl.EXTEND(p_index_tbl.COUNT);
6303:
6304: -- retrieve the values based on line type id
6305: FORALL i IN 1..p_index_tbl.COUNT
6306: INSERT INTO po_session_gt(key, num1, char1, num2)
6307: SELECT p_key,
6308: p_index_tbl(i),
6309: association.job_description,
6310: association.category_id
6318:
6319: d_position := 10;
6320:
6321: -- read result from temp table and delete the records at the same time
6322: DELETE FROM po_session_gt
6323: WHERE key = p_key
6324: RETURNING num1, char1, num2
6325: BULK COLLECT INTO
6326: l_index_tbl,
6364: --Function: default po category_id from ip_category_id
6365: --Parameters:
6366: --IN:
6367: -- p_key
6368: -- key used to identify rows in po_session_gt
6369: -- p_index_tbl
6370: -- table containging the indexes of all rows
6371: -- p_ip_category_id_tbl
6372: -- list of ip_category_ids read within the batch
6377: --End of Comments
6378: ------------------------------------------------------------------------
6379: PROCEDURE default_po_cat_id_from_ip
6380: (
6381: p_key IN po_session_gt.key%TYPE,
6382: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
6383: p_ip_category_id_tbl IN PO_TBL_NUMBER,
6384: x_po_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
6385: ) IS
6401: END IF;
6402:
6403: -- retrieve the values based on ip category id
6404: FORALL i IN 1..p_index_tbl.COUNT
6405: INSERT INTO po_session_gt(key, num1, num2)
6406: SELECT p_key,
6407: p_index_tbl(i),
6408: po_category_id
6409: FROM icx_cat_shopping_cat_map_v
6413:
6414: d_position := 10;
6415:
6416: -- read result from temp table and delete the records at the same time
6417: DELETE FROM po_session_gt
6418: WHERE key = p_key
6419: RETURNING num1, num2
6420: BULK COLLECT INTO l_index_tbl, l_result_tbl;
6421:
6452: --Function: default ip_category_id from po category_id
6453: --Parameters:
6454: --IN:
6455: -- p_key
6456: -- key used to identify rows in po_session_gt
6457: -- p_index_tbl
6458: -- table containging the indexes of all rows
6459: -- p_po_category_id_tbl
6460: -- list of po_category_ids read within the batch
6465: --End of Comments
6466: ------------------------------------------------------------------------
6467: PROCEDURE default_ip_cat_id_from_po
6468: (
6469: p_key IN po_session_gt.key%TYPE,
6470: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
6471: p_po_category_id_tbl IN PO_TBL_NUMBER,
6472: x_ip_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
6473: ) IS
6489: END IF;
6490:
6491: -- retrieve the values based on po category id
6492: FORALL i IN 1..p_index_tbl.COUNT
6493: INSERT INTO po_session_gt(key, num1, num2)
6494: SELECT p_key,
6495: p_index_tbl(i),
6496: shopping_category_id
6497: FROM icx_cat_purchasing_cat_map_v
6501:
6502: d_position := 10;
6503:
6504: -- read result from temp table and delete the records at the same time
6505: DELETE FROM po_session_gt
6506: WHERE key = p_key
6507: RETURNING num1, num2
6508: BULK COLLECT INTO l_index_tbl, l_result_tbl;
6509:
6540: --Function: default hazard_class_id from un_number
6541: --Parameters:
6542: --IN:
6543: -- p_key
6544: -- key used to identify rows in po_session_gt
6545: -- p_index_tbl
6546: -- table containging the indexes of all rows
6547: -- p_un_number_tbl
6548: -- list of un_numbers read within the batch
6553: --End of Comments
6554: ------------------------------------------------------------------------
6555: PROCEDURE default_hc_id_from_un_number
6556: (
6557: p_key IN po_session_gt.key%TYPE,
6558: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
6559: p_un_number_tbl IN PO_TBL_VARCHAR30,
6560: x_hazard_class_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
6561: ) IS
6577: END IF;
6578:
6579: -- retrieve the values based on line type id
6580: FORALL i IN 1..p_index_tbl.COUNT
6581: INSERT INTO po_session_gt(key, num1, num2)
6582: SELECT p_key,
6583: p_index_tbl(i),
6584: hazard_class_id
6585: FROM po_un_numbers_vl
6590:
6591: d_position := 10;
6592:
6593: -- read result from temp table and delete the records at the same time
6594: DELETE FROM po_session_gt
6595: WHERE key = p_key
6596: RETURNING num1, num2
6597: BULK COLLECT INTO l_index_tbl, l_result_tbl;
6598:
6651: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
6652: d_position NUMBER;
6653:
6654: -- hold the key value which is used to identify rows in temp table
6655: l_key po_session_gt.key%TYPE;
6656:
6657: -- po line that has been found as a match for current line
6658: l_index_tbl PO_TBL_NUMBER;
6659: l_result_tbl PO_TBL_NUMBER;
6676: IF (PO_LOG.d_proc) THEN
6677: PO_LOG.proc_begin(d_module);
6678: END IF;
6679:
6680: -- get new key value for po_session_gt table
6681: l_key := PO_CORE_S.get_session_gt_nextval;
6682:
6683: -- match lines whose line_num is not null
6684: -- first, try to find matching line in draft table
6682:
6683: -- match lines whose line_num is not null
6684: -- first, try to find matching line in draft table
6685: FORALL i IN 1..x_lines.rec_count
6686: INSERT INTO po_session_gt(key, num1, num2)
6687: SELECT l_key,
6688: p_index_tbl(i),
6689: po_line_id
6690: FROM po_lines_draft_all
6694: AND NVL(delete_flag, 'N') <> 'Y';
6695:
6696: d_position := 10;
6697:
6698: DELETE FROM po_session_gt
6699: WHERE key = l_key
6700: RETURNING num1, num2
6701: BULK COLLECT INTO l_index_tbl, l_result_tbl;
6702:
6801: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
6802: d_position NUMBER;
6803:
6804: -- hold the key value which is used to identify rows in temp table
6805: l_key po_session_gt.key%TYPE;
6806: l_data_key po_session_gt.key%TYPE;
6807:
6808: -- identify lines that are going to be processed
6809: l_processing_line_tbl DBMS_SQL.NUMBER_TABLE;
6802: d_position NUMBER;
6803:
6804: -- hold the key value which is used to identify rows in temp table
6805: l_key po_session_gt.key%TYPE;
6806: l_data_key po_session_gt.key%TYPE;
6807:
6808: -- identify lines that are going to be processed
6809: l_processing_line_tbl DBMS_SQL.NUMBER_TABLE;
6810: l_num_list DBMS_SQL.NUMBER_TABLE;
6824: IF (PO_LOG.d_proc) THEN
6825: PO_LOG.proc_begin(d_module);
6826: END IF;
6827:
6828: -- get key value for po_session_gt table
6829: l_key := PO_CORE_S.get_session_gt_nextval;
6830:
6831: -- First, get lines qualified. The qualified lines must satisfy the following conditions:
6832: -- 1. x_lines.line_num_tbl(i) IS NULL
6866: -- perform matching logic for qualified lines
6867: -- a. match on draft table
6868: -- a.1 match on draft table when item_id is not null
6869: FORALL i IN INDICES OF l_processing_line_tbl
6870: INSERT INTO po_session_gt(key, num1, num2, num3)
6871: SELECT l_key,
6872: l_processing_line_tbl(i),
6873: po_line_id,
6874: line_num
6888: d_position := 20;
6889:
6890: -- a.2 match on draft table when item_id is null
6891: FORALL i IN INDICES OF l_processing_line_tbl
6892: INSERT INTO po_session_gt(key, num1, num2, num3)
6893: SELECT l_key,
6894: l_processing_line_tbl(i),
6895: po_line_id,
6896: line_num
6906:
6907: d_position := 30;
6908:
6909: -- get result from temp table
6910: DELETE FROM po_session_gt
6911: WHERE key = l_key
6912: RETURNING num1, num2, num3
6913: BULK COLLECT INTO l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
6914:
6942: x_num_list => l_num_list
6943: );
6944:
6945: FORALL i IN 1..x_lines.rec_count
6946: INSERT INTO po_session_gt(key, num1, num2, num3, char1, char2,
6947: num4, num5, char3, char4, num6)
6948: VALUES (l_data_key,
6949: x_lines.intf_line_id_tbl(i), -- num1
6950: x_lines.po_header_id_tbl(i), -- num2
6962:
6963: -- find lines that cannot find a match in the batch
6964: -- a. check lines where item_id is not null
6965: FORALL i IN INDICES OF l_processing_line_tbl
6966: INSERT INTO po_session_gt(key, num1)
6967: SELECT l_key,
6968: l_processing_line_tbl(i)
6969: FROM DUAL
6970: WHERE x_lines.item_id_tbl(i) IS NOT NULL
6969: FROM DUAL
6970: WHERE x_lines.item_id_tbl(i) IS NOT NULL
6971: AND NOT EXISTS(
6972: SELECT 1
6973: FROM po_session_gt gt
6974: WHERE gt.num2 = x_lines.po_header_id_tbl(i)
6975: AND gt.num1 < x_lines.intf_line_id_tbl(i)
6976: AND gt.num3 = x_lines.item_id_tbl(i)
6977: AND (gt.char1 = x_lines.item_revision_tbl(i) OR
6985: d_position := 60;
6986:
6987: -- b. check lines when item_id = null
6988: FORALL i IN INDICES OF l_processing_line_tbl
6989: INSERT INTO po_session_gt(key, num1)
6990: SELECT l_key,
6991: l_processing_line_tbl(i)
6992: FROM DUAL
6993: WHERE x_lines.item_id_tbl(i) IS NULL
6992: FROM DUAL
6993: WHERE x_lines.item_id_tbl(i) IS NULL
6994: AND NOT EXISTS(
6995: SELECT 1
6996: FROM po_session_gt gt
6997: WHERE gt.num2 = x_lines.po_header_id_tbl(i)
6998: AND gt.num1 < x_lines.intf_line_id_tbl(i)
6999: AND gt.char3 = x_lines.item_desc_tbl(i)
7000: AND gt.char2 = x_lines.unit_of_measure_tbl(i)
7004:
7005: d_position := 70;
7006:
7007: -- get result from temp table
7008: DELETE FROM po_session_gt
7009: WHERE key = l_key
7010: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7011:
7012: -- assign a new po_line_id and line_num to these lines
7035:
7036: -- Last, for lines that can find at least one match in current batch,
7037: -- return one matching line that has the smallest interface_line_id
7038: FORALL i IN INDICES OF l_processing_line_tbl
7039: INSERT INTO po_session_gt(key, num1, num2)
7040: SELECT l_key,
7041: l_processing_line_tbl(i),
7042: v.min_index
7043: FROM (SELECT min(num6) AS min_index
7040: SELECT l_key,
7041: l_processing_line_tbl(i),
7042: v.min_index
7043: FROM (SELECT min(num6) AS min_index
7044: FROM po_session_gt
7045: WHERE x_lines.item_id_tbl(i) IS NOT NULL
7046: AND num2 = x_lines.po_header_id_tbl(i)
7047: AND num1 < x_lines.intf_line_id_tbl(i)
7048: AND num3 = x_lines.item_id_tbl(i)
7056:
7057: d_position := 90;
7058:
7059: FORALL i IN INDICES OF l_processing_line_tbl
7060: INSERT INTO po_session_gt(key, num1, num2)
7061: SELECT l_key,
7062: l_processing_line_tbl(i),
7063: v.min_index
7064: FROM (SELECT min(num6) AS min_index
7061: SELECT l_key,
7062: l_processing_line_tbl(i),
7063: v.min_index
7064: FROM (SELECT min(num6) AS min_index
7065: FROM po_session_gt
7066: WHERE x_lines.item_id_tbl(i) IS NULL
7067: AND num2 = x_lines.po_header_id_tbl(i)
7068: AND num1 < x_lines.intf_line_id_tbl(i)
7069: AND char3 = x_lines.item_desc_tbl(i)
7073: WHERE v.min_index IS NOT NULL;
7074:
7075: d_position := 100;
7076:
7077: DELETE FROM po_session_gt
7078: WHERE key = l_key
7079: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_match_index_tbl;
7080:
7081: IF (PO_LOG.d_stmt) THEN
7102: END LOOP;
7103:
7104: d_position := 110;
7105:
7106: -- clean up po_session_gt
7107: PO_PDOI_UTL.remove_session_gt_records
7108: ( p_key => l_data_key
7109: );
7110:
7393: --End of Comments
7394: ------------------------------------------------------------------------
7395: PROCEDURE uniqueness_check_on_desc
7396: (
7397: p_key IN po_session_gt.key%TYPE,
7398: p_group_num IN NUMBER,
7399: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
7400: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
7401: ) IS
7403: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_desc';
7404: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
7405: d_position NUMBER;
7406:
7407: -- variables to hold matching result read from po_session_gt table
7408: l_index_tbl PO_TBL_NUMBER;
7409: l_po_line_id_tbl PO_TBL_NUMBER;
7410: l_line_num_tbl PO_TBL_NUMBER;
7411:
7409: l_po_line_id_tbl PO_TBL_NUMBER;
7410: l_line_num_tbl PO_TBL_NUMBER;
7411:
7412: l_index NUMBER;
7413: l_data_key po_session_gt.key%TYPE;
7414:
7415: BEGIN
7416: d_position := 0;
7417:
7420: END IF;
7421:
7422: -- check matchings on draft table
7423: FORALL i IN INDICES OF x_processing_row_tbl
7424: INSERT INTO po_session_gt
7425: (
7426: key,
7427: num1,
7428: num2,
7447: d_position := 10;
7448:
7449: -- check matching on txn table
7450: FORALL i IN INDICES OF x_processing_row_tbl
7451: INSERT INTO po_session_gt
7452: (
7453: key,
7454: num1,
7455: num2,
7477: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
7478:
7479: d_position := 20;
7480:
7481: DELETE FROM po_session_gt
7482: WHERE key = p_key
7483: RETURNING num1, num2, num3 BULK COLLECT INTO
7484: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
7485:
7549: -- find out all lines that cannot find a match
7550: -- and set their action to ADD
7551: l_data_key := PO_CORE_S.get_session_gt_nextval;
7552: FORALL i IN INDICES OF x_processing_row_tbl
7553: INSERT INTO po_session_gt
7554: (
7555: key,
7556: num1,
7557: num2,
7568:
7569: d_position := 40;
7570:
7571: FORALL i IN INDICES OF x_processing_row_tbl
7572: INSERT INTO po_session_gt
7573: (
7574: key,
7575: num1
7576: )
7578: x_processing_row_tbl(i)
7579: FROM DUAL
7580: WHERE NOT EXISTS(
7581: SELECT 1
7582: FROM po_session_gt gt
7583: WHERE key = l_data_key
7584: AND gt.num1 < x_lines.intf_line_id_tbl(i)
7585: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
7586: AND gt.char1 = x_lines.item_desc_tbl(i)
7588: NVL(gt.char2, -99));
7589:
7590: d_position := 50;
7591:
7592: DELETE FROM po_session_gt
7593: WHERE key = p_key
7594: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7595:
7596: -- bug5093465
7605: );
7606:
7607: d_position := 60;
7608:
7609: -- clean up po_session_gt
7610: PO_PDOI_UTL.remove_session_gt_records
7611: ( p_key => l_data_key
7612: );
7613:
7647: --End of Comments
7648: ------------------------------------------------------------------------
7649: PROCEDURE uniqueness_check_on_item
7650: (
7651: p_key IN po_session_gt.key%TYPE,
7652: p_group_num IN NUMBER,
7653: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
7654: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
7655: ) IS
7659: d_position NUMBER;
7660:
7661: l_def_master_org_id NUMBER;
7662:
7663: -- variables to hold matching result read from po_session_gt table
7664: l_index_tbl PO_TBL_NUMBER;
7665: l_po_line_id_tbl PO_TBL_NUMBER;
7666: l_line_num_tbl PO_TBL_NUMBER;
7667:
7665: l_po_line_id_tbl PO_TBL_NUMBER;
7666: l_line_num_tbl PO_TBL_NUMBER;
7667:
7668: l_index NUMBER;
7669: l_data_key po_session_gt.key%TYPE;
7670:
7671: BEGIN
7672: d_position := 0;
7673:
7694: END IF;
7695:
7696: -- check matching on draft table
7697: FORALL i IN INDICES OF x_processing_row_tbl
7698: INSERT INTO po_session_gt
7699: (
7700: key,
7701: num1,
7702: num2,
7729: d_position := 10;
7730:
7731: -- check matching on txn table
7732: FORALL i IN INDICES OF x_processing_row_tbl
7733: INSERT INTO po_session_gt
7734: (
7735: key,
7736: num1,
7737: num2,
7767: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
7768:
7769: d_position := 20;
7770:
7771: DELETE FROM po_session_gt
7772: WHERE key = p_key
7773: RETURNING num1, num2, num3 BULK COLLECT INTO
7774: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
7775:
7839: -- find out all lines that cannot find a match
7840: -- and set their action to ADD
7841: l_data_key := PO_CORE_S.get_session_gt_nextval;
7842: FORALL i IN INDICES OF x_processing_row_tbl
7843: INSERT INTO po_session_gt
7844: (
7845: key,
7846: num1,
7847: num2,
7865: -- bug4930510
7866: -- Take away the table mtl_system_items from the query since it's
7867: -- never used.
7868: FORALL i IN INDICES OF x_processing_row_tbl
7869: INSERT INTO po_session_gt
7870: (
7871: key,
7872: num1
7873: )
7876: FROM DUAL
7877: WHERE x_lines.item_tbl(i) IS NOT NULL
7878: AND NOT EXISTS(
7879: SELECT 1
7880: FROM po_session_gt gt
7881: WHERE key = l_data_key
7882: AND gt.num1 < x_lines.intf_line_id_tbl(i)
7883: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
7884: AND gt.char1 = x_lines.item_tbl(i)
7891: NVL(gt.char4, FND_API.g_NULL_CHAR));
7892:
7893: d_position := 50;
7894:
7895: DELETE FROM po_session_gt
7896: WHERE key = p_key
7897: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7898:
7899: -- bug5093465
7908: );
7909:
7910: d_position := 60;
7911:
7912: -- clean up po_session_gt
7913: PO_PDOI_UTL.remove_session_gt_records
7914: ( p_key => l_data_key
7915: );
7916:
7949: --End of Comments
7950: ------------------------------------------------------------------------
7951: PROCEDURE uniqueness_check_on_vpn
7952: (
7953: p_key IN po_session_gt.key%TYPE,
7954: p_group_num IN NUMBER,
7955: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
7956: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
7957: ) IS
7959: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_vpn';
7960: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
7961: d_position NUMBER;
7962:
7963: -- variables to hold matching result read from po_session_gt table
7964: l_index_tbl PO_TBL_NUMBER;
7965: l_po_line_id_tbl PO_TBL_NUMBER;
7966: l_line_num_tbl PO_TBL_NUMBER;
7967:
7965: l_po_line_id_tbl PO_TBL_NUMBER;
7966: l_line_num_tbl PO_TBL_NUMBER;
7967:
7968: l_index NUMBER;
7969: l_data_key po_session_gt.key%TYPE;
7970:
7971: BEGIN
7972: d_position := 0;
7973:
7976: END IF;
7977:
7978: -- check matchings on draft table
7979: FORALL i IN INDICES OF x_processing_row_tbl
7980: INSERT INTO po_session_gt
7981: (
7982: key,
7983: num1,
7984: num2,
8003: d_position := 10;
8004:
8005: -- check matching on txn table
8006: FORALL i IN INDICES OF x_processing_row_tbl
8007: INSERT INTO po_session_gt
8008: (
8009: key,
8010: num1,
8011: num2,
8033: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
8034:
8035: d_position := 20;
8036:
8037: DELETE FROM po_session_gt
8038: WHERE key = p_key
8039: RETURNING num1, num2, num3 BULK COLLECT INTO
8040: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
8041:
8105: -- find out all lines that cannot find a match
8106: -- and set their action to ADD
8107: l_data_key := PO_CORE_S.get_session_gt_nextval;
8108: FORALL i IN INDICES OF x_processing_row_tbl
8109: INSERT INTO po_session_gt
8110: (
8111: key,
8112: num1,
8113: num2,
8124:
8125: d_position := 40;
8126:
8127: FORALL i IN INDICES OF x_processing_row_tbl
8128: INSERT INTO po_session_gt
8129: (
8130: key,
8131: num1
8132: )
8136: WHERE x_lines.item_tbl(i) IS NULL
8137: AND x_lines.vendor_product_num_tbl(i) IS NOT NULL
8138: AND NOT EXISTS(
8139: SELECT 1
8140: FROM po_session_gt gt
8141: WHERE key = l_data_key
8142: AND gt.num1 < x_lines.intf_line_id_tbl(i)
8143: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
8144: AND gt.char1 = x_lines.vendor_product_num_tbl(i)
8147: NVL(gt.char2, FND_API.g_NULL_CHAR));
8148:
8149: d_position := 50;
8150:
8151: DELETE FROM po_session_gt
8152: WHERE key = p_key
8153: RETURNING num1 BULK COLLECT INTO l_index_tbl;
8154:
8155: d_position := 60;
8166: );
8167:
8168: d_position := 70;
8169:
8170: -- clean up po_session_gt
8171: PO_PDOI_UTL.remove_session_gt_records
8172: ( p_key => l_data_key
8173: );
8174:
8207: --End of Comments
8208: ------------------------------------------------------------------------
8209: PROCEDURE uniqueness_check_on_job
8210: (
8211: p_key IN po_session_gt.key%TYPE,
8212: p_group_num IN NUMBER,
8213: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
8214: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
8215: ) IS
8217: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_job';
8218: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
8219: d_position NUMBER;
8220:
8221: -- variables to hold matching result read from po_session_gt table
8222: l_index_tbl PO_TBL_NUMBER;
8223: l_po_line_id_tbl PO_TBL_NUMBER;
8224: l_line_num_tbl PO_TBL_NUMBER;
8225:
8223: l_po_line_id_tbl PO_TBL_NUMBER;
8224: l_line_num_tbl PO_TBL_NUMBER;
8225:
8226: l_index NUMBER;
8227: l_data_key po_session_gt.key%TYPE;
8228:
8229: BEGIN
8230: d_position := 0;
8231:
8234: END IF;
8235:
8236: -- check matchings on draft table
8237: FORALL i IN INDICES OF x_processing_row_tbl
8238: INSERT INTO po_session_gt
8239: (
8240: key,
8241: num1,
8242: num2,
8263: d_position := 10;
8264:
8265: -- check matching on txn table
8266: FORALL i IN INDICES OF x_processing_row_tbl
8267: INSERT INTO po_session_gt
8268: (
8269: key,
8270: num1,
8271: num2,
8294: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
8295:
8296: d_position := 20;
8297:
8298: DELETE FROM po_session_gt
8299: WHERE key = p_key
8300: RETURNING num1, num2, num3 BULK COLLECT INTO
8301: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
8302:
8366: -- find out all lines that cannot find a match
8367: -- and set their action to ADD
8368: l_data_key := PO_CORE_S.get_session_gt_nextval;
8369: FORALL i IN INDICES OF x_processing_row_tbl
8370: INSERT INTO po_session_gt
8371: (
8372: key,
8373: num1,
8374: num2,
8383:
8384: d_position := 40;
8385:
8386: FORALL i IN INDICES OF x_processing_row_tbl
8387: INSERT INTO po_session_gt
8388: (
8389: key,
8390: num1
8391: )
8396: AND x_lines.vendor_product_num_tbl(i) IS NULL
8397: AND x_lines.job_name_tbl(i) IS NOT NULL
8398: AND NOT EXISTS(
8399: SELECT 1
8400: FROM po_session_gt gt
8401: WHERE key = l_data_key
8402: AND gt.num1 < x_lines.intf_line_id_tbl(i)
8403: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
8404: AND gt.char1 = x_lines.job_name_tbl(i));
8404: AND gt.char1 = x_lines.job_name_tbl(i));
8405:
8406: d_position := 50;
8407:
8408: DELETE FROM po_session_gt
8409: WHERE key = p_key
8410: RETURNING num1 BULK COLLECT INTO l_index_tbl;
8411:
8412: -- bug5093465
8421: );
8422:
8423: d_position := 60;
8424:
8425: -- clean up po_session_gt
8426: PO_PDOI_UTL.remove_session_gt_records
8427: ( p_key => l_data_key
8428: );
8429:
8462: --End of Comments
8463: ------------------------------------------------------------------------
8464: PROCEDURE uniqueness_check_on_line_num
8465: (
8466: p_key IN po_session_gt.key%TYPE,
8467: p_group_num IN NUMBER,
8468: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
8469: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
8470: ) IS
8472: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_line_num';
8473: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
8474: d_position NUMBER;
8475:
8476: -- variables to hold matching result read from po_session_gt table
8477: l_index_tbl PO_TBL_NUMBER;
8478: l_po_line_id_tbl PO_TBL_NUMBER;
8479: l_line_num_tbl PO_TBL_NUMBER;
8480:
8478: l_po_line_id_tbl PO_TBL_NUMBER;
8479: l_line_num_tbl PO_TBL_NUMBER;
8480:
8481: l_index NUMBER;
8482: l_data_key po_session_gt.key%TYPE;
8483:
8484: BEGIN
8485: d_position := 0;
8486:
8489: END IF;
8490:
8491: -- check matchings on draft table
8492: FORALL i IN INDICES OF x_processing_row_tbl
8493: INSERT INTO po_session_gt
8494: (
8495: key,
8496: num1,
8497: num2,
8515: d_position := 10;
8516:
8517: -- check matching on txn table
8518: FORALL i IN INDICES OF x_processing_row_tbl
8519: INSERT INTO po_session_gt
8520: (
8521: key,
8522: num1,
8523: num2,
8544: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
8545:
8546: d_position := 20;
8547:
8548: DELETE FROM po_session_gt
8549: WHERE key = p_key
8550: RETURNING num1, num2, num3 BULK COLLECT INTO
8551: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
8552:
8576: -- find out all lines that cannot find a match
8577: -- and set their action to ADD
8578: l_data_key := PO_CORE_S.get_session_gt_nextval;
8579: FORALL i IN INDICES OF x_processing_row_tbl
8580: INSERT INTO po_session_gt
8581: (
8582: key,
8583: num1,
8584: num2,
8593:
8594: d_position := 40;
8595:
8596: FORALL i IN INDICES OF x_processing_row_tbl
8597: INSERT INTO po_session_gt
8598: (
8599: key,
8600: num1
8601: )
8607: AND x_lines.job_name_tbl(i) IS NULL
8608: AND x_lines.line_num_tbl(i) IS NOT NULL
8609: AND NOT EXISTS(
8610: SELECT 1
8611: FROM po_session_gt gt
8612: WHERE key = l_data_key
8613: AND gt.num1 < x_lines.intf_line_id_tbl(i)
8614: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
8615: AND gt.num3 = x_lines.line_num_tbl(i));
8615: AND gt.num3 = x_lines.line_num_tbl(i));
8616:
8617: d_position := 50;
8618:
8619: DELETE FROM po_session_gt
8620: WHERE key = p_key
8621: RETURNING num1 BULK COLLECT INTO l_index_tbl;
8622:
8623: -- bug5093465
8682: ------------------------------------------------------------------------
8683:
8684: PROCEDURE set_action_add
8685: (
8686: p_key IN po_session_gt.key%TYPE,
8687: p_group_num IN NUMBER,
8688: p_target_lines_index_tbl IN PO_TBL_NUMBER,
8689: p_check_line_num_assign IN VARCHAR2,
8690: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
8793: d_api_name CONSTANT VARCHAR2(30) := 'validate_attr_tlp';
8794: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
8795: d_position NUMBER;
8796:
8797: l_key po_session_gt.key%TYPE;
8798:
8799: -- table used to save the index of the each row
8800: l_index_tbl DBMS_SQL.NUMBER_TABLE;
8801: l_index NUMBER;
8816: x_num_list => l_index_tbl
8817: );
8818:
8819: FORALL i IN 1..x_lines.rec_count
8820: INSERT INTO po_session_gt
8821: (
8822: key,
8823: num1
8824: )
8842: );
8843:
8844: d_position := 10;
8845:
8846: DELETE FROM po_session_gt
8847: WHERE key = l_key
8848: RETURNING num1 BULK COLLECT INTO l_index_tbl;
8849:
8850: d_position := 20;
9099: --CLM PDOI Integration
9100:
9101: PROCEDURE derive_contract_type
9102: (
9103: p_key IN po_session_gt.key%TYPE,
9104: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9105: p_contract_type_display_tbl IN PO_TBL_VARCHAR240,
9106: x_contract_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR240
9107: ) IS
9121: END IF;
9122:
9123: --derive contract_type based on contract_type_display
9124: FORALL i IN 1..p_index_tbl.COUNT
9125: INSERT INTO po_session_gt(key, num1, char1)
9126: SELECT p_key,
9127: p_index_tbl(i),
9128: lookup_code
9129: FROM PO_LOOKUP_CODES
9135:
9136: d_position := 10;
9137:
9138: -- read result from temp table, and delete the records from temp table
9139: DELETE FROM po_session_gt
9140: WHERE key = p_key
9141: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9142:
9143: d_position := 20;
9170:
9171:
9172: PROCEDURE derive_cost_constraint
9173: (
9174: p_key IN po_session_gt.key%TYPE,
9175: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9176: p_cost_constraint_display_tbl IN PO_TBL_VARCHAR240,
9177: x_cost_constraint_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
9178: ) IS
9192: END IF;
9193:
9194: --derive cost_constraint based on cost_constraint_display
9195: FORALL i IN 1..p_index_tbl.COUNT
9196: INSERT INTO po_session_gt(key, num1, char1)
9197: SELECT p_key,
9198: p_index_tbl(i),
9199: lookup_code
9200: FROM PO_LOOKUP_CODES
9205:
9206: d_position := 10;
9207:
9208: -- read result from temp table, and delete the records from temp table
9209: DELETE FROM po_session_gt
9210: WHERE key = p_key
9211: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9212:
9213: d_position := 20;
9240:
9241:
9242: PROCEDURE derive_clm_idc_type
9243: (
9244: p_key IN po_session_gt.key%TYPE,
9245: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9246: p_clm_idc_type_display_tbl IN PO_TBL_VARCHAR240,
9247: x_clm_idc_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR240
9248: ) IS
9262: END IF;
9263:
9264: --derive idc_type based on clm_idc_type_display
9265: FORALL i IN 1..p_index_tbl.COUNT
9266: INSERT INTO po_session_gt(key, num1, char1)
9267: SELECT p_key,
9268: p_index_tbl(i),
9269: lookup_code
9270: FROM PO_LOOKUP_CODES
9275:
9276: d_position := 10;
9277:
9278: -- read result from temp table, and delete the records from temp table
9279: DELETE FROM po_session_gt
9280: WHERE key = p_key
9281: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9282:
9283: d_position := 20;
9310:
9311:
9312: PROCEDURE derive_clm_base_line_num
9313: (
9314: p_key IN po_session_gt.key%TYPE,
9315: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9316: p_clm_base_line_num_disp_tbl IN PO_TBL_VARCHAR240,
9317: p_intf_line_id_tbl IN PO_TBL_NUMBER,
9318: p_intf_header_id_tbl IN PO_TBL_NUMBER,
9336: END IF;
9337:
9338: --derive clm_base_line_num based on clm_base_line_num_disp
9339: FORALL i IN 1..p_index_tbl.COUNT
9340: INSERT INTO po_session_gt(key, num1, num2, num3, char1, char2, char3, num4)
9341: SELECT p_key,
9342: p_index_tbl(i),
9343: p_intf_header_id_tbl(i),
9344: p_intf_line_id_tbl(i),
9348: x_clm_base_line_num_tbl(i)
9349: FROM dual;
9350:
9351:
9352: UPDATE po_session_gt psg1
9353: SET psg1.num4 = ( SELECT psg2.num3 FROM po_session_gt psg2
9354: WHERE psg1.char2 = psg2.char1
9355: AND psg1.num2 = psg2.num2
9356: )
9349: FROM dual;
9350:
9351:
9352: UPDATE po_session_gt psg1
9353: SET psg1.num4 = ( SELECT psg2.num3 FROM po_session_gt psg2
9354: WHERE psg1.char2 = psg2.char1
9355: AND psg1.num2 = psg2.num2
9356: )
9357: WHERE psg1.char2 IS NOT NULL
9358: AND psg1.num4 IS NULL
9359: AND Nvl(psg1.char3,'*') = 'O';
9360:
9361: -- read result from temp table, and delete the records from temp table
9362: DELETE FROM po_session_gt
9363: WHERE key = p_key
9364: RETURNING num1, num4 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9365:
9366: d_position := 20;
9391:
9392:
9393: PROCEDURE derive_from_header_id
9394: (
9395: p_key IN po_session_gt.key%TYPE,
9396: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9397: p_from_header_disp_tbl IN PO_TBL_VARCHAR240,
9398: x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
9399: ) IS
9413: END IF;
9414:
9415: --derive from_header_id based on from_header_id
9416: FORALL i IN 1..p_index_tbl.COUNT
9417: INSERT INTO po_session_gt(key, num1, char1, num2)
9418: SELECT p_key,
9419: p_index_tbl(i),
9420: p_from_header_disp_tbl(i),
9421: x_from_header_id_tbl(i)
9420: p_from_header_disp_tbl(i),
9421: x_from_header_id_tbl(i)
9422: FROM dual;
9423:
9424: UPDATE po_session_gt psg
9425: SET psg.num2 = ( SELECT phi.po_header_id
9426: FROM po_headers_all phi
9427: WHERE clm_document_number = psg.char1
9428: )
9429: WHERE psg.char1 IS NOT NULL
9430: AND psg.num2 IS NULL;
9431:
9432: -- read result from temp table, and delete the records from temp table
9433: DELETE FROM po_session_gt
9434: WHERE key = p_key
9435: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9436:
9437: d_position := 20;
9465:
9466:
9467: PROCEDURE derive_from_line_id
9468: (
9469: p_key IN po_session_gt.key%TYPE,
9470: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9471: p_from_line_disp_tbl IN PO_TBL_VARCHAR240,
9472: p_from_header_id_tbl IN PO_TBL_NUMBER,
9473: x_from_line_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
9488: END IF;
9489:
9490: --derive from_line_id based on from_line_disp_tbl
9491: FORALL i IN 1..p_index_tbl.COUNT
9492: INSERT INTO po_session_gt(key, num1, char1, num2, num3)
9493: SELECT p_key,
9494: p_index_tbl(i),
9495: p_from_line_disp_tbl(i),
9496: p_from_header_id_tbl(i),
9496: p_from_header_id_tbl(i),
9497: x_from_line_id_tbl(i)
9498: FROM dual;
9499:
9500: UPDATE po_session_gt psg
9501: SET psg.num3 = ( SELECT PLI.po_line_id
9502: FROM po_lines_all pli
9503: WHERE PLI.po_header_id = psg.num2
9504: AND PLI.line_num_display = psg.char1
9507: AND psg.char1 IS NOT NULL
9508: AND psg.num3 IS NULL;
9509:
9510: -- read result from temp table, and delete the records from temp table
9511: DELETE FROM po_session_gt
9512: WHERE key = p_key
9513: RETURNING num1, num3 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9514:
9515: d_position := 20;
9541:
9542:
9543: PROCEDURE default_clm_option_indicator
9544: (
9545: p_key IN po_session_gt.key%TYPE,
9546: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9547: p_intf_line_id_tbl IN PO_TBL_NUMBER,
9548: p_clm_base_line_num_tbl IN PO_TBL_NUMBER,
9549: x_clm_option_indicator_tbl IN OUT NOCOPY PO_TBL_VARCHAR1
9564: END IF;
9565:
9566: --derive default_clm_option_indicator based on clm_base_line_num
9567: FORALL i IN 1..p_index_tbl.COUNT
9568: INSERT INTO po_session_gt(key, num1, num2, num3, char1)
9569: SELECT p_key,
9570: p_index_tbl(i),
9571: p_intf_line_id_tbl(i),
9572: p_clm_base_line_num_tbl(i),
9572: p_clm_base_line_num_tbl(i),
9573: x_clm_option_indicator_tbl(i)
9574: FROM dual;
9575:
9576: UPDATE po_session_gt psg1
9577: SET psg1.char1 = 'B'
9578: WHERE psg1.num2 IN ( SELECT psg2.num3 FROM po_session_gt psg2
9579: WHERE Nvl(psg2.char1,'*') = 'O'
9580: AND psg2.num3 IS NOT null
9574: FROM dual;
9575:
9576: UPDATE po_session_gt psg1
9577: SET psg1.char1 = 'B'
9578: WHERE psg1.num2 IN ( SELECT psg2.num3 FROM po_session_gt psg2
9579: WHERE Nvl(psg2.char1,'*') = 'O'
9580: AND psg2.num3 IS NOT null
9581: )
9582: AND psg1.char1 IS NULL;
9581: )
9582: AND psg1.char1 IS NULL;
9583:
9584: -- read result from temp table, and delete the records from temp table
9585: DELETE FROM po_session_gt
9586: WHERE key = p_key
9587: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9588:
9589: d_position := 20;
9616:
9617:
9618: PROCEDURE default_clm_option_num
9619: (
9620: p_key IN po_session_gt.key%TYPE,
9621: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9622: p_intf_header_id_tbl IN PO_TBL_NUMBER,
9623: p_intf_line_id_tbl IN PO_TBL_NUMBER,
9624: p_clm_base_line_num_tbl IN PO_TBL_NUMBER,
9642: PO_LOG.proc_begin(d_module);
9643: END IF;
9644:
9645: FORALL i IN 1..p_index_tbl.COUNT
9646: INSERT INTO po_session_gt(key, num1, num2, num3, num4, char1, num5)
9647: SELECT p_key,
9648: p_index_tbl(i),
9649: p_intf_header_id_tbl(i),
9650: p_intf_line_id_tbl(i),
9655:
9656: SELECT num1,
9657: Rank() over (PARTITION BY num4 ORDER BY num3)
9658: BULK COLLECT INTO l_index_tbl, l_option_num_tbl
9659: FROM po_session_gt
9660: WHERE num4 IS NOT NULL;
9661:
9662: FORALL i IN 1..l_index_tbl.Count
9663: UPDATE po_session_gt
9659: FROM po_session_gt
9660: WHERE num4 IS NOT NULL;
9661:
9662: FORALL i IN 1..l_index_tbl.Count
9663: UPDATE po_session_gt
9664: SET num5 = l_option_num_tbl(i)
9665: WHERE KEY = p_key
9666: AND num1 = l_index_tbl(i);
9667:
9667:
9668: l_index_tbl.DELETE;
9669:
9670: -- read result from temp table, and delete the records from temp table
9671: DELETE FROM po_session_gt
9672: WHERE key = p_key
9673: RETURNING num1, num5 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9674:
9675: d_position := 20;
9704:
9705:
9706: PROCEDURE default_from_header_id
9707: (
9708: p_key IN po_session_gt.key%TYPE,
9709: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
9710: p_intf_header_id_tbl IN PO_TBL_NUMBER,
9711: p_intf_line_id_tbl IN PO_TBL_NUMBER,
9712: x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
9728: PO_LOG.proc_begin(d_module);
9729: END IF;
9730:
9731: FORALL i IN 1..p_index_tbl.COUNT
9732: INSERT INTO po_session_gt(key, num1, num2, num3, num4)
9733: SELECT p_key,
9734: p_index_tbl(i),
9735: p_intf_header_id_tbl(i),
9736: p_intf_line_id_tbl(i),
9736: p_intf_line_id_tbl(i),
9737: x_from_header_id_tbl(i)
9738: FROM dual;
9739:
9740: UPDATE po_session_gt psg
9741: SET psg.num4 = ( SELECT phd.clm_source_document_id
9742: FROM po_headers_draft_all phd, po_lines_interface PLI, po_headers_interface phi
9743: WHERE PLI.interface_line_id = psg.num3
9744: AND PLI.interface_header_id = phi.interface_header_id
9746: )
9747: WHERE psg.num4 IS NULL;
9748:
9749: -- read result from temp table, and delete the records from temp table
9750: DELETE FROM po_session_gt
9751: WHERE key = p_key
9752: RETURNING num1, num4 BULK COLLECT INTO l_index_tbl, l_result_tbl;
9753:
9754: d_position := 20;