[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,
1526: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1527: d_position NUMBER;
1528:
1529: -- key of temp table used to identify the derived result
1530: l_key po_session_gt.key%TYPE;
1531:
1532: -- table used to save the index of the each row
1533: l_index_tbl DBMS_SQL.NUMBER_TABLE;
1534: BEGIN
2024: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2025: d_position NUMBER;
2026:
2027: -- key of temp table used to identify the derived result
2028: l_key po_session_gt.key%TYPE;
2029:
2030: -- table used to save the index of the each row
2031: l_index_tbl DBMS_SQL.NUMBER_TABLE;
2032: BEGIN
2235: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2236: d_position NUMBER;
2237:
2238: -- key of temp table used to identify the derived result
2239: l_key po_session_gt.key%TYPE;
2240:
2241: -- table used to save the index of the each row
2242: l_index_tbl DBMS_SQL.NUMBER_TABLE;
2243:
2272: END IF;
2273:
2274: PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_DEFAULT);
2275:
2276: -- get key value to identify rows in po_session_gt table
2277: l_key := PO_CORE_S.get_session_gt_nextval;
2278:
2279: -- default line_type_id that will be used in other defaulting logic;
2280: -- l_index_tbl table is initialized as well
2649: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2650: d_position NUMBER;
2651:
2652: -- key of temp table used to identify the derived result
2653: l_key po_session_gt.key%TYPE;
2654:
2655: -- table used to save the index of the each row
2656: l_num_list DBMS_SQL.NUMBER_TABLE;
2657:
2685: d_position := 10;
2686:
2687: -- get values from draft tables
2688: FORALL i IN 1..l_num_list.COUNT
2689: INSERT INTO po_session_gt(key, num1, char1, num2, num3)
2690: SELECT l_key,
2691: l_num_list(i),
2692: order_type_lookup_code,
2693: item_id,
2699: d_position := 20;
2700:
2701: -- get values from txn table if no draft line exist
2702: FORALL i IN 1..l_num_list.COUNT
2703: INSERT INTO po_session_gt(key, num1, char1, num2, num3)
2704: SELECT l_key,
2705: l_num_list(i),
2706: order_type_lookup_code,
2707: item_id,
2714: AND draft_id = x_lines.draft_id_tbl(i));
2715:
2716: d_position := 30;
2717:
2718: DELETE FROM po_session_gt
2719: WHERE key = l_key
2720: RETURNING num1, char1, num2, num3 BULK COLLECT INTO
2721: l_index_tbl, l_order_type_tbl, l_item_id_tbl, l_job_id_tbl;
2722:
3208: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3209: d_position NUMBER;
3210:
3211: -- key of temp table used to identify the records
3212: l_key po_session_gt.key%TYPE;
3213:
3214: -- table storing the index of records within the batch
3215: l_index_tbl DBMS_SQL.NUMBER_TABLE;
3216: l_index NUMBER;
3236: END IF;
3237:
3238: PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_UNIQUENESS_CHECK);
3239:
3240: -- get key value for po_session_gt table
3241: l_key := PO_CORE_S.get_session_gt_nextval;
3242:
3243: -- call individual uniqueness check procedure for each unique criteria
3244: IF (p_type = PO_PDOI_CONSTANTS.g_LINE_CSR_SYNC_ON_DESC) THEN
3360: d_position := 90;
3361:
3362: -- check whether uom changed for a line that has a release shipment
3363: FORALL i IN 1..x_lines.rec_count
3364: INSERT INTO po_session_gt(key, num1)
3365: SELECT l_key,
3366: l_index_tbl(i)
3367: FROM po_lines txn_lines
3368: WHERE txn_lines.po_line_id = x_lines.po_line_id_tbl(i)
3379: FROM po_lines_all
3380: WHERE from_line_id = txn_lines.po_line_id)
3381: );
3382:
3383: DELETE FROM po_session_gt
3384: WHERE key = l_key
3385: RETURNING num1 BULK COLLECT INTO l_expire_line_index_tbl;
3386:
3387: FOR i IN 1..l_expire_line_index_tbl.COUNT
3755: --Function: derive item_id from item or vendor_product_num
3756: --Parameters:
3757: --IN:
3758: -- p_key
3759: -- key used to identify rows in po_session_gt
3760: -- p_index_tbl
3761: -- table containging the indexes of all rows
3762: -- p_vendor_id_tbl
3763: -- list of vendor_ids read from the header
3781: --End of Comments
3782: ------------------------------------------------------------------------
3783: PROCEDURE derive_item_id
3784: (
3785: p_key IN po_session_gt.key%TYPE,
3786: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3787: p_vendor_id_tbl IN PO_TBL_NUMBER,
3788: p_intf_header_id_tbl IN PO_TBL_NUMBER,
3789: p_intf_line_id_tbl IN PO_TBL_NUMBER,
3822: END IF;
3823:
3824: -- derive based on item_num
3825: FORALL i IN 1..p_index_tbl.COUNT
3826: INSERT INTO po_session_gt(key, num1, num2)
3827: SELECT p_key,
3828: p_index_tbl(i),
3829: inventory_item_id
3830: FROM mtl_system_items_kfv
3840: -- sql so that matched category for the vendor product number
3841: -- item id will be derived.
3842:
3843: FORALL i IN 1.. p_index_tbl.COUNT
3844: INSERT INTO po_session_gt(key, num1, num2)
3845: SELECT DISTINCT
3846: p_key,
3847: p_index_tbl(i),
3848: txn_lines.item_id
3861:
3862: d_position := 20;
3863:
3864: -- read result from temp table, and delete the records from temp table
3865: DELETE FROM po_session_gt
3866: WHERE key = p_key
3867: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3868:
3869: d_position := 30;
3923: -- and create_item parameter value
3924: --Parameters:
3925: --IN:
3926: -- p_key
3927: -- key used to identify rows in po_session_gt
3928: -- p_item_id_tbl
3929: -- list of item_ids read within the batch
3930: --IN OUT:
3931: -- x_item_revision_tbl
3935: --End of Comments
3936: ------------------------------------------------------------------------
3937: PROCEDURE derive_item_revision
3938: (
3939: p_key IN po_session_gt.key%TYPE,
3940: p_item_id_tbl IN PO_TBL_NUMBER,
3941: x_item_revision_tbl IN OUT NOCOPY PO_TBL_VARCHAR5
3942: ) IS
3943:
3988: -- job_business_group_name
3989: --Parameters:
3990: --IN:
3991: -- p_key
3992: -- key used to identify rows in po_session_gt
3993: -- p_index_tbl
3994: -- table containging the indexes of all rows
3995: -- p_job_business_group_name_tbl
3996: -- list of job_business_group_names read within the batch
4004: --End of Comments
4005: ------------------------------------------------------------------------
4006: PROCEDURE derive_job_business_group_id
4007: (
4008: p_key IN po_session_gt.key%TYPE,
4009: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4010: p_job_business_group_name_tbl IN PO_TBL_VARCHAR2000,
4011: x_job_business_group_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4012: ) IS
4029: END IF;
4030:
4031: -- derive id from name
4032: FORALL i IN 1.. p_index_tbl.COUNT
4033: INSERT INTO po_session_gt(key, num1, num2)
4034: SELECT p_key,
4035: p_index_tbl(i),
4036: business_group_id
4037: FROM per_business_groups_perf
4043:
4044: d_position := 10;
4045:
4046: -- read result from temp table, and delete the records from temp table
4047: DELETE FROM po_session_gt
4048: WHERE key = p_key
4049: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4050:
4051: d_position := 20;
4081: --Function: derive job_id from job_business_group_name and job_name
4082: --Parameters:
4083: --IN:
4084: -- p_key
4085: -- key used to identify rows in po_session_gt
4086: -- p_index_tbl
4087: -- table containging the indexes of all rows
4088: -- p_file_line_language_tbl
4089: -- list of line level languages
4104: --End of Comments
4105: ------------------------------------------------------------------------
4106: PROCEDURE derive_job_id
4107: (
4108: p_key IN po_session_gt.key%TYPE,
4109: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4110: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
4111: p_job_business_group_name_tbl IN PO_TBL_VARCHAR2000,
4112: p_job_name_tbl IN PO_TBL_VARCHAR2000,
4157: d_position := 10;
4158:
4159: -- derive job_id from job_name
4160: FORALL i IN 1..p_index_tbl.COUNT
4161: INSERT INTO po_session_gt(key, num1, num2)
4162: SELECT p_key,
4163: p_index_tbl(i),
4164: jobs_b.job_id
4165: FROM per_jobs jobs_b,
4202: -- END OF info added for debugging purpose */
4203:
4204: -- derive job_id for lines with job_business_group_id = null
4205: FORALL i IN 1..p_index_tbl.COUNT
4206: INSERT INTO po_session_gt(key, num1, num2)
4207: SELECT p_key,
4208: p_index_tbl(i),
4209: jobs_b.job_id
4210: FROM per_jobs jobs_b,
4226: d_position := 30;
4227:
4228: -- derive job_id for lines with job_business_group_id <> null
4229: FORALL i IN 1..p_index_tbl.COUNT
4230: INSERT INTO po_session_gt(key, num1, num2)
4231: SELECT p_key,
4232: p_index_tbl(i),
4233: jobs_b.job_id
4234: FROM per_jobs jobs_b,
4250:
4251: d_position := 40;
4252:
4253: -- retrive result from temp table and delete the records in temp table
4254: DELETE FROM po_session_gt
4255: WHERE key = p_key
4256: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4257:
4258: d_position := 50;
4310: --Function: derive po category_id from po category name
4311: --Parameters:
4312: --IN:
4313: -- p_key
4314: -- key used to identify rows in po_session_gt
4315: -- p_category_tbl
4316: -- list of categories read within the batch
4317: --IN OUT:
4318: -- x_category_id_tbl
4324: --End of Comments
4325: ------------------------------------------------------------------------
4326: PROCEDURE derive_category_id
4327: (
4328: p_key IN po_session_gt.key%TYPE,
4329: p_category_tbl IN PO_TBL_VARCHAR2000,
4330: x_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4331: ) IS
4332:
4388: --Function: derive po category_id from po category name
4389: --Parameters:
4390: --IN:
4391: -- p_key
4392: -- key used to identify rows in po_session_gt
4393: -- p_index_tbl
4394: -- table containging the indexes of all rows
4395: -- p_file_line_language_tbl
4396: -- list of line level languages
4406: --End of Comments
4407: ------------------------------------------------------------------------
4408: PROCEDURE derive_ip_category_id
4409: (
4410: p_key IN po_session_gt.key%TYPE,
4411: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4412: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
4413: p_ip_category_tbl IN PO_TBL_VARCHAR2000,
4414: x_ip_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4431: END IF;
4432:
4433: -- 1. derive ip_category_id based on category key
4434: FORALL i IN 1.. p_index_tbl.COUNT
4435: INSERT INTO po_session_gt(key, num1, char1)
4436: SELECT p_key,
4437: p_index_tbl(i),
4438: cat.rt_category_id
4439: FROM icx_cat_categories_v cat
4443:
4444: d_position := 10;
4445:
4446: -- read result from temp table, and delete the records from temp table
4447: DELETE FROM po_session_gt
4448: WHERE key = p_key
4449: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4450:
4451: d_position := 20;
4467: -- bug 5489942: derivation is based on line level language
4468: -- 2. derive ip_category_id based on category name,
4469: -- name is language specific
4470: FORALL i IN 1.. p_index_tbl.COUNT
4471: INSERT INTO po_session_gt(key, num1, char1)
4472: SELECT p_key,
4473: p_index_tbl(i),
4474: rt_category_id
4475: FROM icx_cat_categories_v
4481:
4482: d_position := 40;
4483:
4484: -- read result from temp table, and delete the records from temp table
4485: DELETE FROM po_session_gt
4486: WHERE key = p_key
4487: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4488:
4489: d_position := 50;
4520: --Function: derive unit_of_measure from uom_code
4521: --Parameters:
4522: --IN:
4523: -- p_key
4524: -- key used to identify rows in po_session_gt
4525: -- p_index_tbl
4526: -- table containging the indexes of all rows
4527: -- p_uom_code_tbl
4528: -- list of uom_codes read within the batch
4536: --End of Comments
4537: ------------------------------------------------------------------------
4538: PROCEDURE derive_unit_of_measure
4539: (
4540: p_key IN po_session_gt.key%TYPE,
4541: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4542: p_uom_code_tbl IN PO_TBL_VARCHAR5,
4543: x_unit_of_measure_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
4544: ) IS
4559: END IF;
4560:
4561: -- derive unit_of_measure from uom_code
4562: FORALL i IN 1.. p_index_tbl.COUNT
4563: INSERT INTO po_session_gt(key, num1, char1)
4564: SELECT p_key,
4565: p_index_tbl(i),
4566: unit_of_measure
4567: FROM po_units_of_measure_val_v
4571:
4572: d_position := 10;
4573:
4574: -- read result from temp table, and delete the records from temp table
4575: DELETE FROM po_session_gt
4576: WHERE key = p_key
4577: RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4578:
4579: d_position := 20;
4610: --Function: derive line_type_id from line_type
4611: --Parameters:
4612: --IN:
4613: -- p_key
4614: -- key used to identify rows in po_session_gt
4615: -- p_index_tbl
4616: -- table containging the indexes of all rows
4617: -- p_file_line_language_tbl
4618: -- list of line level languages
4628: --End of Comments
4629: ------------------------------------------------------------------------
4630: PROCEDURE derive_line_type_id
4631: (
4632: p_key IN po_session_gt.key%TYPE,
4633: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4634: p_file_line_language_tbl IN PO_TBL_VARCHAR5,
4635: p_line_type_tbl IN PO_TBL_VARCHAR30,
4636: x_line_type_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4654:
4655: -- bug 5489942: derivation is based on line level language
4656: -- derive line_type_id from line_type
4657: FORALL i IN 1..p_index_tbl.COUNT
4658: INSERT INTO po_session_gt(key, num1, num2)
4659: SELECT p_key,
4660: p_index_tbl(i),
4661: b.line_type_id
4662: FROM po_line_types_b b,
4671:
4672: d_position := 10;
4673:
4674: -- read result from temp table, and delete the records from temp table
4675: DELETE FROM po_session_gt
4676: WHERE key = p_key
4677: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4678:
4679: d_position := 20;
4709: --Function: derive un_number_id from un_number
4710: --Parameters:
4711: --IN:
4712: -- p_key
4713: -- key used to identify rows in po_session_gt
4714: -- p_index_tbl
4715: -- table containging the indexes of all rows
4716: -- p_un_number_tbl
4717: -- list of un_numbers read within the batch
4725: --End of Comments
4726: ------------------------------------------------------------------------
4727: PROCEDURE derive_un_number_id
4728: (
4729: p_key IN po_session_gt.key%TYPE,
4730: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4731: p_un_number_tbl IN PO_TBL_VARCHAR30,
4732: x_un_number_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4733: ) IS
4748: END IF;
4749:
4750: -- derive un_number_id from un_number
4751: FORALL i IN 1.. p_index_tbl.COUNT
4752: INSERT INTO po_session_gt(key, num1, num2)
4753: SELECT p_key,
4754: p_index_tbl(i),
4755: un_number_id
4756: FROM po_un_numbers_vl
4761:
4762: d_position := 10;
4763:
4764: -- read result from temp table, and delete the records from temp table
4765: DELETE FROM po_session_gt
4766: WHERE key = p_key
4767: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4768:
4769: d_position := 20;
4799: --Function: derive hazard_class_id from hazard_class
4800: --Parameters:
4801: --IN:
4802: -- p_key
4803: -- key used to identify rows in po_session_gt
4804: -- p_index_tbl
4805: -- table containging the indexes of all rows
4806: -- p_hazard_class_tbl
4807: -- list of hazard_classes read within the batch
4815: --End of Comments
4816: ------------------------------------------------------------------------
4817: PROCEDURE derive_hazard_class_id
4818: (
4819: p_key IN po_session_gt.key%TYPE,
4820: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4821: p_hazard_class_tbl IN PO_TBL_VARCHAR100,
4822: x_hazard_class_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4823: ) IS
4838: END IF;
4839:
4840: -- derive hazard_class_id from hazard_class
4841: FORALL i IN 1..p_index_tbl.COUNT
4842: INSERT INTO po_session_gt(key, num1, num2)
4843: SELECT p_key,
4844: p_index_tbl(i),
4845: hazard_class_id
4846: FROM po_hazard_classes_val_v
4850:
4851: d_position := 10;
4852:
4853: -- read result from temp table, and delete the records from temp table
4854: DELETE FROM po_session_gt
4855: WHERE key = p_key
4856: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4857:
4858: d_position := 20;
4888: --Function: derive template_id from template_name
4889: --Parameters:
4890: --IN:
4891: -- p_key
4892: -- key used to identify rows in po_session_gt
4893: -- p_index_tbl
4894: -- table containging the indexes of all rows
4895: -- p_template_name_tbl
4896: -- list of template_names read within the batch
4904: --End of Comments
4905: ------------------------------------------------------------------------
4906: PROCEDURE derive_template_id
4907: (
4908: p_key IN po_session_gt.key%TYPE,
4909: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4910: p_template_name_tbl IN PO_TBL_VARCHAR30,
4911: x_template_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4912: ) IS
4927: END IF;
4928:
4929: -- derive template_id from template_name
4930: FORALL i IN 1..p_index_tbl.COUNT
4931: INSERT INTO po_session_gt(key, num1, num2)
4932: SELECT p_key,
4933: p_index_tbl(i),
4934: template_id
4935: FROM mtl_item_templates
4942:
4943: d_position := 10;
4944:
4945: -- read result from temp table, and delete the records from temp table
4946: DELETE FROM po_session_gt
4947: WHERE key = p_key
4948: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4949:
4950: d_position := 20;
4987: -- 6. unit_price
4988: --Parameters:
4989: --IN:
4990: -- p_key
4991: -- key used to identify rows in po_session_gt
4992: -- p_index_tbl
4993: -- table containging the indexes of all rows
4994: -- p_line_type_id_tbl
4995: -- list of line_type_ids read within the batch
5010: --End of Comments
5011: ------------------------------------------------------------------------
5012: PROCEDURE default_info_from_line_type
5013: (
5014: p_key IN po_session_gt.key%TYPE,
5015: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5016: p_line_type_id_tbl IN PO_TBL_NUMBER,
5017: x_order_type_lookup_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30,
5018: x_purchase_basis_tbl IN OUT NOCOPY PO_TBL_VARCHAR30,
5053: x_unit_price_tbl.EXTEND(p_index_tbl.COUNT);
5054:
5055: -- retrieve the values based on line type id
5056: FORALL i IN 1..p_index_tbl.COUNT
5057: INSERT INTO po_session_gt(key, num1, char1, char2, char3, num2, char4, num3)
5058: SELECT p_key,
5059: p_index_tbl(i),
5060: order_type_lookup_code,
5061: purchase_basis,
5068:
5069: d_position := 10;
5070:
5071: -- read result from temp table and delete the records at the same time
5072: DELETE FROM po_session_gt
5073: WHERE key = p_key
5074: RETURNING num1, char1, char2, char3, num2, char4, num3
5075: BULK COLLECT INTO
5076: l_index_tbl,
5142: -- 8. secondary_unit_of_measure
5143: --Parameters:
5144: --IN:
5145: -- p_key
5146: -- key used to identify rows in po_session_gt
5147: -- p_index_tbl
5148: -- table containging the indexes of all rows
5149: -- p_item_id_tbl
5150: -- list of item_ids read within the batch
5169: --End of Comments
5170: ------------------------------------------------------------------------
5171: PROCEDURE default_info_from_item
5172: (
5173: p_key IN po_session_gt.key%TYPE,
5174: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5175: p_item_id_tbl IN PO_TBL_NUMBER,
5176: x_item_desc_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
5177: x_unit_of_measure_tbl OUT NOCOPY PO_TBL_VARCHAR30,
5228: -- retrieve the values based on item id and default inv org id
5229: -- bug 4723323: get secondary_unit_of_measure value for dual-um
5230: -- control item, used in default logic
5231: FORALL i IN 1..p_index_tbl.COUNT
5232: INSERT INTO po_session_gt(key, num1, char1, char2, num2, num3,
5233: num4, num5, num6, char3)
5234: SELECT p_key,
5235: p_index_tbl(i),
5236: item_tl.description,
5257:
5258: d_position := 10;
5259:
5260: -- read result from temp table and delete the records at the same time
5261: DELETE FROM po_session_gt
5262: WHERE key = p_key
5263: RETURNING num1, char1, char2, num2, num3, num4, num5, num6, char3
5264: BULK COLLECT INTO
5265: l_index_tbl,
5333: -- 2. category_id
5334: --Parameters:
5335: --IN:
5336: -- p_key
5337: -- key used to identify rows in po_session_gt
5338: -- p_index_tbl
5339: -- table containging the indexes of all rows
5340: -- p_job_id_tbl
5341: -- list of job_ids read within the batch
5348: --End of Comments
5349: ------------------------------------------------------------------------
5350: PROCEDURE default_info_from_job
5351: (
5352: p_key IN po_session_gt.key%TYPE,
5353: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5354: p_job_id_tbl IN PO_TBL_NUMBER,
5355: x_item_desc_tbl OUT NOCOPY PO_TBL_VARCHAR2000,
5356: x_category_id_tbl OUT NOCOPY PO_TBL_NUMBER
5380: x_category_id_tbl.EXTEND(p_index_tbl.COUNT);
5381:
5382: -- retrieve the values based on line type id
5383: FORALL i IN 1..p_index_tbl.COUNT
5384: INSERT INTO po_session_gt(key, num1, char1, num2)
5385: SELECT p_key,
5386: p_index_tbl(i),
5387: association.job_description,
5388: association.category_id
5396:
5397: d_position := 10;
5398:
5399: -- read result from temp table and delete the records at the same time
5400: DELETE FROM po_session_gt
5401: WHERE key = p_key
5402: RETURNING num1, char1, num2
5403: BULK COLLECT INTO
5404: l_index_tbl,
5442: --Function: default po category_id from ip_category_id
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_ip_category_id_tbl
5450: -- list of ip_category_ids read within the batch
5455: --End of Comments
5456: ------------------------------------------------------------------------
5457: PROCEDURE default_po_cat_id_from_ip
5458: (
5459: p_key IN po_session_gt.key%TYPE,
5460: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5461: p_ip_category_id_tbl IN PO_TBL_NUMBER,
5462: x_po_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5463: ) IS
5479: END IF;
5480:
5481: -- retrieve the values based on ip category id
5482: FORALL i IN 1..p_index_tbl.COUNT
5483: INSERT INTO po_session_gt(key, num1, num2)
5484: SELECT p_key,
5485: p_index_tbl(i),
5486: po_category_id
5487: FROM icx_cat_shopping_cat_map_v
5491:
5492: d_position := 10;
5493:
5494: -- read result from temp table and delete the records at the same time
5495: DELETE FROM po_session_gt
5496: WHERE key = p_key
5497: RETURNING num1, num2
5498: BULK COLLECT INTO l_index_tbl, l_result_tbl;
5499:
5530: --Function: default ip_category_id from po category_id
5531: --Parameters:
5532: --IN:
5533: -- p_key
5534: -- key used to identify rows in po_session_gt
5535: -- p_index_tbl
5536: -- table containging the indexes of all rows
5537: -- p_po_category_id_tbl
5538: -- list of po_category_ids read within the batch
5543: --End of Comments
5544: ------------------------------------------------------------------------
5545: PROCEDURE default_ip_cat_id_from_po
5546: (
5547: p_key IN po_session_gt.key%TYPE,
5548: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5549: p_po_category_id_tbl IN PO_TBL_NUMBER,
5550: x_ip_category_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5551: ) IS
5567: END IF;
5568:
5569: -- retrieve the values based on po category id
5570: FORALL i IN 1..p_index_tbl.COUNT
5571: INSERT INTO po_session_gt(key, num1, num2)
5572: SELECT p_key,
5573: p_index_tbl(i),
5574: shopping_category_id
5575: FROM icx_cat_purchasing_cat_map_v
5579:
5580: d_position := 10;
5581:
5582: -- read result from temp table and delete the records at the same time
5583: DELETE FROM po_session_gt
5584: WHERE key = p_key
5585: RETURNING num1, num2
5586: BULK COLLECT INTO l_index_tbl, l_result_tbl;
5587:
5618: --Function: default hazard_class_id from un_number
5619: --Parameters:
5620: --IN:
5621: -- p_key
5622: -- key used to identify rows in po_session_gt
5623: -- p_index_tbl
5624: -- table containging the indexes of all rows
5625: -- p_un_number_tbl
5626: -- list of un_numbers read within the batch
5631: --End of Comments
5632: ------------------------------------------------------------------------
5633: PROCEDURE default_hc_id_from_un_number
5634: (
5635: p_key IN po_session_gt.key%TYPE,
5636: p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
5637: p_un_number_tbl IN PO_TBL_VARCHAR30,
5638: x_hazard_class_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
5639: ) IS
5655: END IF;
5656:
5657: -- retrieve the values based on line type id
5658: FORALL i IN 1..p_index_tbl.COUNT
5659: INSERT INTO po_session_gt(key, num1, num2)
5660: SELECT p_key,
5661: p_index_tbl(i),
5662: hazard_class_id
5663: FROM po_un_numbers_vl
5668:
5669: d_position := 10;
5670:
5671: -- read result from temp table and delete the records at the same time
5672: DELETE FROM po_session_gt
5673: WHERE key = p_key
5674: RETURNING num1, num2
5675: BULK COLLECT INTO l_index_tbl, l_result_tbl;
5676:
5729: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
5730: d_position NUMBER;
5731:
5732: -- hold the key value which is used to identify rows in temp table
5733: l_key po_session_gt.key%TYPE;
5734:
5735: -- po line that has been found as a match for current line
5736: l_index_tbl PO_TBL_NUMBER;
5737: l_result_tbl PO_TBL_NUMBER;
5751: IF (PO_LOG.d_proc) THEN
5752: PO_LOG.proc_begin(d_module);
5753: END IF;
5754:
5755: -- get new key value for po_session_gt table
5756: l_key := PO_CORE_S.get_session_gt_nextval;
5757:
5758: -- match lines whose line_num is not null
5759: -- first, try to find matching line in draft table
5757:
5758: -- match lines whose line_num is not null
5759: -- first, try to find matching line in draft table
5760: FORALL i IN 1..x_lines.rec_count
5761: INSERT INTO po_session_gt(key, num1, num2)
5762: SELECT l_key,
5763: p_index_tbl(i),
5764: po_line_id
5765: FROM po_lines_draft_all
5769: AND NVL(delete_flag, 'N') <> 'Y';
5770:
5771: d_position := 10;
5772:
5773: DELETE FROM po_session_gt
5774: WHERE key = l_key
5775: RETURNING num1, num2
5776: BULK COLLECT INTO l_index_tbl, l_result_tbl;
5777:
5876: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
5877: d_position NUMBER;
5878:
5879: -- hold the key value which is used to identify rows in temp table
5880: l_key po_session_gt.key%TYPE;
5881: l_data_key po_session_gt.key%TYPE;
5882:
5883: -- identify lines that are going to be processed
5884: l_processing_line_tbl DBMS_SQL.NUMBER_TABLE;
5877: d_position NUMBER;
5878:
5879: -- hold the key value which is used to identify rows in temp table
5880: l_key po_session_gt.key%TYPE;
5881: l_data_key po_session_gt.key%TYPE;
5882:
5883: -- identify lines that are going to be processed
5884: l_processing_line_tbl DBMS_SQL.NUMBER_TABLE;
5885: l_num_list DBMS_SQL.NUMBER_TABLE;
5899: IF (PO_LOG.d_proc) THEN
5900: PO_LOG.proc_begin(d_module);
5901: END IF;
5902:
5903: -- get key value for po_session_gt table
5904: l_key := PO_CORE_S.get_session_gt_nextval;
5905:
5906: -- First, get lines qualified. The qualified lines must satisfy the following conditions:
5907: -- 1. x_lines.line_num_tbl(i) IS NULL
5941: -- perform matching logic for qualified lines
5942: -- a. match on draft table
5943: -- a.1 match on draft table when item_id is not null
5944: FORALL i IN INDICES OF l_processing_line_tbl
5945: INSERT INTO po_session_gt(key, num1, num2, num3)
5946: SELECT l_key,
5947: l_processing_line_tbl(i),
5948: po_line_id,
5949: line_num
5963: d_position := 20;
5964:
5965: -- a.2 match on draft table when item_id is null
5966: FORALL i IN INDICES OF l_processing_line_tbl
5967: INSERT INTO po_session_gt(key, num1, num2, num3)
5968: SELECT l_key,
5969: l_processing_line_tbl(i),
5970: po_line_id,
5971: line_num
5981:
5982: d_position := 30;
5983:
5984: -- get result from temp table
5985: DELETE FROM po_session_gt
5986: WHERE key = l_key
5987: RETURNING num1, num2, num3
5988: BULK COLLECT INTO l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
5989:
6017: x_num_list => l_num_list
6018: );
6019:
6020: FORALL i IN 1..x_lines.rec_count
6021: INSERT INTO po_session_gt(key, num1, num2, num3, char1, char2,
6022: num4, num5, char3, char4, num6)
6023: VALUES (l_data_key,
6024: x_lines.intf_line_id_tbl(i), -- num1
6025: x_lines.po_header_id_tbl(i), -- num2
6037:
6038: -- find lines that cannot find a match in the batch
6039: -- a. check lines where item_id is not null
6040: FORALL i IN INDICES OF l_processing_line_tbl
6041: INSERT INTO po_session_gt(key, num1)
6042: SELECT l_key,
6043: l_processing_line_tbl(i)
6044: FROM DUAL
6045: WHERE x_lines.item_id_tbl(i) IS NOT NULL
6044: FROM DUAL
6045: WHERE x_lines.item_id_tbl(i) IS NOT NULL
6046: AND NOT EXISTS(
6047: SELECT 1
6048: FROM po_session_gt gt
6049: WHERE gt.num2 = x_lines.po_header_id_tbl(i)
6050: AND gt.num1 < x_lines.intf_line_id_tbl(i)
6051: AND gt.num3 = x_lines.item_id_tbl(i)
6052: AND (gt.char1 = x_lines.item_revision_tbl(i) OR
6060: d_position := 60;
6061:
6062: -- b. check lines when item_id = null
6063: FORALL i IN INDICES OF l_processing_line_tbl
6064: INSERT INTO po_session_gt(key, num1)
6065: SELECT l_key,
6066: l_processing_line_tbl(i)
6067: FROM DUAL
6068: WHERE x_lines.item_id_tbl(i) IS NULL
6067: FROM DUAL
6068: WHERE x_lines.item_id_tbl(i) IS NULL
6069: AND NOT EXISTS(
6070: SELECT 1
6071: FROM po_session_gt gt
6072: WHERE gt.num2 = x_lines.po_header_id_tbl(i)
6073: AND gt.num1 < x_lines.intf_line_id_tbl(i)
6074: AND gt.char3 = x_lines.item_desc_tbl(i)
6075: AND gt.char2 = x_lines.unit_of_measure_tbl(i)
6079:
6080: d_position := 70;
6081:
6082: -- get result from temp table
6083: DELETE FROM po_session_gt
6084: WHERE key = l_key
6085: RETURNING num1 BULK COLLECT INTO l_index_tbl;
6086:
6087: -- assign a new po_line_id and line_num to these lines
6107:
6108: -- Last, for lines that can find at least one match in current batch,
6109: -- return one matching line that has the smallest interface_line_id
6110: FORALL i IN INDICES OF l_processing_line_tbl
6111: INSERT INTO po_session_gt(key, num1, num2)
6112: SELECT l_key,
6113: l_processing_line_tbl(i),
6114: v.min_index
6115: FROM (SELECT min(num6) AS min_index
6112: SELECT l_key,
6113: l_processing_line_tbl(i),
6114: v.min_index
6115: FROM (SELECT min(num6) AS min_index
6116: FROM po_session_gt
6117: WHERE x_lines.item_id_tbl(i) IS NOT NULL
6118: AND num2 = x_lines.po_header_id_tbl(i)
6119: AND num1 < x_lines.intf_line_id_tbl(i)
6120: AND num3 = x_lines.item_id_tbl(i)
6128:
6129: d_position := 90;
6130:
6131: FORALL i IN INDICES OF l_processing_line_tbl
6132: INSERT INTO po_session_gt(key, num1, num2)
6133: SELECT l_key,
6134: l_processing_line_tbl(i),
6135: v.min_index
6136: FROM (SELECT min(num6) AS min_index
6133: SELECT l_key,
6134: l_processing_line_tbl(i),
6135: v.min_index
6136: FROM (SELECT min(num6) AS min_index
6137: FROM po_session_gt
6138: WHERE x_lines.item_id_tbl(i) IS NULL
6139: AND num2 = x_lines.po_header_id_tbl(i)
6140: AND num1 < x_lines.intf_line_id_tbl(i)
6141: AND char3 = x_lines.item_desc_tbl(i)
6145: WHERE v.min_index IS NOT NULL;
6146:
6147: d_position := 100;
6148:
6149: DELETE FROM po_session_gt
6150: WHERE key = l_key
6151: RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_match_index_tbl;
6152:
6153: IF (PO_LOG.d_stmt) THEN
6174: END LOOP;
6175:
6176: d_position := 110;
6177:
6178: -- clean up po_session_gt
6179: PO_PDOI_UTL.remove_session_gt_records
6180: ( p_key => l_data_key
6181: );
6182:
6426: --End of Comments
6427: ------------------------------------------------------------------------
6428: PROCEDURE uniqueness_check_on_desc
6429: (
6430: p_key IN po_session_gt.key%TYPE,
6431: p_group_num IN NUMBER,
6432: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
6433: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
6434: ) IS
6436: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_desc';
6437: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
6438: d_position NUMBER;
6439:
6440: -- variables to hold matching result read from po_session_gt table
6441: l_index_tbl PO_TBL_NUMBER;
6442: l_po_line_id_tbl PO_TBL_NUMBER;
6443: l_line_num_tbl PO_TBL_NUMBER;
6444:
6442: l_po_line_id_tbl PO_TBL_NUMBER;
6443: l_line_num_tbl PO_TBL_NUMBER;
6444:
6445: l_index NUMBER;
6446: l_data_key po_session_gt.key%TYPE;
6447:
6448: BEGIN
6449: d_position := 0;
6450:
6453: END IF;
6454:
6455: -- check matchings on draft table
6456: FORALL i IN INDICES OF x_processing_row_tbl
6457: INSERT INTO po_session_gt
6458: (
6459: key,
6460: num1,
6461: num2,
6480: d_position := 10;
6481:
6482: -- check matching on txn table
6483: FORALL i IN INDICES OF x_processing_row_tbl
6484: INSERT INTO po_session_gt
6485: (
6486: key,
6487: num1,
6488: num2,
6510: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
6511:
6512: d_position := 20;
6513:
6514: DELETE FROM po_session_gt
6515: WHERE key = p_key
6516: RETURNING num1, num2, num3 BULK COLLECT INTO
6517: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
6518:
6582: -- find out all lines that cannot find a match
6583: -- and set their action to ADD
6584: l_data_key := PO_CORE_S.get_session_gt_nextval;
6585: FORALL i IN INDICES OF x_processing_row_tbl
6586: INSERT INTO po_session_gt
6587: (
6588: key,
6589: num1,
6590: num2,
6601:
6602: d_position := 40;
6603:
6604: FORALL i IN INDICES OF x_processing_row_tbl
6605: INSERT INTO po_session_gt
6606: (
6607: key,
6608: num1
6609: )
6611: x_processing_row_tbl(i)
6612: FROM DUAL
6613: WHERE NOT EXISTS(
6614: SELECT 1
6615: FROM po_session_gt gt
6616: WHERE key = l_data_key
6617: AND gt.num1 < x_lines.intf_line_id_tbl(i)
6618: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
6619: AND gt.char1 = x_lines.item_desc_tbl(i)
6621: NVL(gt.char2, -99));
6622:
6623: d_position := 50;
6624:
6625: DELETE FROM po_session_gt
6626: WHERE key = p_key
6627: RETURNING num1 BULK COLLECT INTO l_index_tbl;
6628:
6629: -- bug5093465
6638: );
6639:
6640: d_position := 60;
6641:
6642: -- clean up po_session_gt
6643: PO_PDOI_UTL.remove_session_gt_records
6644: ( p_key => l_data_key
6645: );
6646:
6680: --End of Comments
6681: ------------------------------------------------------------------------
6682: PROCEDURE uniqueness_check_on_item
6683: (
6684: p_key IN po_session_gt.key%TYPE,
6685: p_group_num IN NUMBER,
6686: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
6687: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
6688: ) IS
6692: d_position NUMBER;
6693:
6694: l_def_master_org_id NUMBER;
6695:
6696: -- variables to hold matching result read from po_session_gt table
6697: l_index_tbl PO_TBL_NUMBER;
6698: l_po_line_id_tbl PO_TBL_NUMBER;
6699: l_line_num_tbl PO_TBL_NUMBER;
6700:
6698: l_po_line_id_tbl PO_TBL_NUMBER;
6699: l_line_num_tbl PO_TBL_NUMBER;
6700:
6701: l_index NUMBER;
6702: l_data_key po_session_gt.key%TYPE;
6703:
6704: BEGIN
6705: d_position := 0;
6706:
6727: END IF;
6728:
6729: -- check matching on draft table
6730: FORALL i IN INDICES OF x_processing_row_tbl
6731: INSERT INTO po_session_gt
6732: (
6733: key,
6734: num1,
6735: num2,
6762: d_position := 10;
6763:
6764: -- check matching on txn table
6765: FORALL i IN INDICES OF x_processing_row_tbl
6766: INSERT INTO po_session_gt
6767: (
6768: key,
6769: num1,
6770: num2,
6800: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
6801:
6802: d_position := 20;
6803:
6804: DELETE FROM po_session_gt
6805: WHERE key = p_key
6806: RETURNING num1, num2, num3 BULK COLLECT INTO
6807: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
6808:
6872: -- find out all lines that cannot find a match
6873: -- and set their action to ADD
6874: l_data_key := PO_CORE_S.get_session_gt_nextval;
6875: FORALL i IN INDICES OF x_processing_row_tbl
6876: INSERT INTO po_session_gt
6877: (
6878: key,
6879: num1,
6880: num2,
6898: -- bug4930510
6899: -- Take away the table mtl_system_items from the query since it's
6900: -- never used.
6901: FORALL i IN INDICES OF x_processing_row_tbl
6902: INSERT INTO po_session_gt
6903: (
6904: key,
6905: num1
6906: )
6909: FROM DUAL
6910: WHERE x_lines.item_tbl(i) IS NOT NULL
6911: AND NOT EXISTS(
6912: SELECT 1
6913: FROM po_session_gt gt
6914: WHERE key = l_data_key
6915: AND gt.num1 < x_lines.intf_line_id_tbl(i)
6916: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
6917: AND gt.char1 = x_lines.item_tbl(i)
6924: NVL(gt.char4, FND_API.g_NULL_CHAR));
6925:
6926: d_position := 50;
6927:
6928: DELETE FROM po_session_gt
6929: WHERE key = p_key
6930: RETURNING num1 BULK COLLECT INTO l_index_tbl;
6931:
6932: -- bug5093465
6941: );
6942:
6943: d_position := 60;
6944:
6945: -- clean up po_session_gt
6946: PO_PDOI_UTL.remove_session_gt_records
6947: ( p_key => l_data_key
6948: );
6949:
6982: --End of Comments
6983: ------------------------------------------------------------------------
6984: PROCEDURE uniqueness_check_on_vpn
6985: (
6986: p_key IN po_session_gt.key%TYPE,
6987: p_group_num IN NUMBER,
6988: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
6989: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
6990: ) IS
6992: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_vpn';
6993: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
6994: d_position NUMBER;
6995:
6996: -- variables to hold matching result read from po_session_gt table
6997: l_index_tbl PO_TBL_NUMBER;
6998: l_po_line_id_tbl PO_TBL_NUMBER;
6999: l_line_num_tbl PO_TBL_NUMBER;
7000:
6998: l_po_line_id_tbl PO_TBL_NUMBER;
6999: l_line_num_tbl PO_TBL_NUMBER;
7000:
7001: l_index NUMBER;
7002: l_data_key po_session_gt.key%TYPE;
7003:
7004: BEGIN
7005: d_position := 0;
7006:
7009: END IF;
7010:
7011: -- check matchings on draft table
7012: FORALL i IN INDICES OF x_processing_row_tbl
7013: INSERT INTO po_session_gt
7014: (
7015: key,
7016: num1,
7017: num2,
7036: d_position := 10;
7037:
7038: -- check matching on txn table
7039: FORALL i IN INDICES OF x_processing_row_tbl
7040: INSERT INTO po_session_gt
7041: (
7042: key,
7043: num1,
7044: num2,
7066: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
7067:
7068: d_position := 20;
7069:
7070: DELETE FROM po_session_gt
7071: WHERE key = p_key
7072: RETURNING num1, num2, num3 BULK COLLECT INTO
7073: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
7074:
7138: -- find out all lines that cannot find a match
7139: -- and set their action to ADD
7140: l_data_key := PO_CORE_S.get_session_gt_nextval;
7141: FORALL i IN INDICES OF x_processing_row_tbl
7142: INSERT INTO po_session_gt
7143: (
7144: key,
7145: num1,
7146: num2,
7157:
7158: d_position := 40;
7159:
7160: FORALL i IN INDICES OF x_processing_row_tbl
7161: INSERT INTO po_session_gt
7162: (
7163: key,
7164: num1
7165: )
7169: WHERE x_lines.item_tbl(i) IS NULL
7170: AND x_lines.vendor_product_num_tbl(i) IS NOT NULL
7171: AND NOT EXISTS(
7172: SELECT 1
7173: FROM po_session_gt gt
7174: WHERE key = l_data_key
7175: AND gt.num1 < x_lines.intf_line_id_tbl(i)
7176: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
7177: AND gt.char1 = x_lines.vendor_product_num_tbl(i)
7180: NVL(gt.char2, FND_API.g_NULL_CHAR));
7181:
7182: d_position := 50;
7183:
7184: DELETE FROM po_session_gt
7185: WHERE key = p_key
7186: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7187:
7188: d_position := 60;
7199: );
7200:
7201: d_position := 70;
7202:
7203: -- clean up po_session_gt
7204: PO_PDOI_UTL.remove_session_gt_records
7205: ( p_key => l_data_key
7206: );
7207:
7240: --End of Comments
7241: ------------------------------------------------------------------------
7242: PROCEDURE uniqueness_check_on_job
7243: (
7244: p_key IN po_session_gt.key%TYPE,
7245: p_group_num IN NUMBER,
7246: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
7247: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
7248: ) IS
7250: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_job';
7251: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
7252: d_position NUMBER;
7253:
7254: -- variables to hold matching result read from po_session_gt table
7255: l_index_tbl PO_TBL_NUMBER;
7256: l_po_line_id_tbl PO_TBL_NUMBER;
7257: l_line_num_tbl PO_TBL_NUMBER;
7258:
7256: l_po_line_id_tbl PO_TBL_NUMBER;
7257: l_line_num_tbl PO_TBL_NUMBER;
7258:
7259: l_index NUMBER;
7260: l_data_key po_session_gt.key%TYPE;
7261:
7262: BEGIN
7263: d_position := 0;
7264:
7267: END IF;
7268:
7269: -- check matchings on draft table
7270: FORALL i IN INDICES OF x_processing_row_tbl
7271: INSERT INTO po_session_gt
7272: (
7273: key,
7274: num1,
7275: num2,
7296: d_position := 10;
7297:
7298: -- check matching on txn table
7299: FORALL i IN INDICES OF x_processing_row_tbl
7300: INSERT INTO po_session_gt
7301: (
7302: key,
7303: num1,
7304: num2,
7327: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
7328:
7329: d_position := 20;
7330:
7331: DELETE FROM po_session_gt
7332: WHERE key = p_key
7333: RETURNING num1, num2, num3 BULK COLLECT INTO
7334: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
7335:
7399: -- find out all lines that cannot find a match
7400: -- and set their action to ADD
7401: l_data_key := PO_CORE_S.get_session_gt_nextval;
7402: FORALL i IN INDICES OF x_processing_row_tbl
7403: INSERT INTO po_session_gt
7404: (
7405: key,
7406: num1,
7407: num2,
7416:
7417: d_position := 40;
7418:
7419: FORALL i IN INDICES OF x_processing_row_tbl
7420: INSERT INTO po_session_gt
7421: (
7422: key,
7423: num1
7424: )
7429: AND x_lines.vendor_product_num_tbl(i) IS NULL
7430: AND x_lines.job_name_tbl(i) IS NOT NULL
7431: AND NOT EXISTS(
7432: SELECT 1
7433: FROM po_session_gt gt
7434: WHERE key = l_data_key
7435: AND gt.num1 < x_lines.intf_line_id_tbl(i)
7436: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
7437: AND gt.char1 = x_lines.job_name_tbl(i));
7437: AND gt.char1 = x_lines.job_name_tbl(i));
7438:
7439: d_position := 50;
7440:
7441: DELETE FROM po_session_gt
7442: WHERE key = p_key
7443: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7444:
7445: -- bug5093465
7454: );
7455:
7456: d_position := 60;
7457:
7458: -- clean up po_session_gt
7459: PO_PDOI_UTL.remove_session_gt_records
7460: ( p_key => l_data_key
7461: );
7462:
7495: --End of Comments
7496: ------------------------------------------------------------------------
7497: PROCEDURE uniqueness_check_on_line_num
7498: (
7499: p_key IN po_session_gt.key%TYPE,
7500: p_group_num IN NUMBER,
7501: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
7502: x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
7503: ) IS
7505: d_api_name CONSTANT VARCHAR2(30) := 'uniqueness_check_on_line_num';
7506: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
7507: d_position NUMBER;
7508:
7509: -- variables to hold matching result read from po_session_gt table
7510: l_index_tbl PO_TBL_NUMBER;
7511: l_po_line_id_tbl PO_TBL_NUMBER;
7512: l_line_num_tbl PO_TBL_NUMBER;
7513:
7511: l_po_line_id_tbl PO_TBL_NUMBER;
7512: l_line_num_tbl PO_TBL_NUMBER;
7513:
7514: l_index NUMBER;
7515: l_data_key po_session_gt.key%TYPE;
7516:
7517: BEGIN
7518: d_position := 0;
7519:
7522: END IF;
7523:
7524: -- check matchings on draft table
7525: FORALL i IN INDICES OF x_processing_row_tbl
7526: INSERT INTO po_session_gt
7527: (
7528: key,
7529: num1,
7530: num2,
7548: d_position := 10;
7549:
7550: -- check matching on txn table
7551: FORALL i IN INDICES OF x_processing_row_tbl
7552: INSERT INTO po_session_gt
7553: (
7554: key,
7555: num1,
7556: num2,
7577: AND NVL(txn_lines.cancel_flag, 'N') <> 'Y';
7578:
7579: d_position := 20;
7580:
7581: DELETE FROM po_session_gt
7582: WHERE key = p_key
7583: RETURNING num1, num2, num3 BULK COLLECT INTO
7584: l_index_tbl, l_po_line_id_tbl, l_line_num_tbl;
7585:
7609: -- find out all lines that cannot find a match
7610: -- and set their action to ADD
7611: l_data_key := PO_CORE_S.get_session_gt_nextval;
7612: FORALL i IN INDICES OF x_processing_row_tbl
7613: INSERT INTO po_session_gt
7614: (
7615: key,
7616: num1,
7617: num2,
7626:
7627: d_position := 40;
7628:
7629: FORALL i IN INDICES OF x_processing_row_tbl
7630: INSERT INTO po_session_gt
7631: (
7632: key,
7633: num1
7634: )
7640: AND x_lines.job_name_tbl(i) IS NULL
7641: AND x_lines.line_num_tbl(i) IS NOT NULL
7642: AND NOT EXISTS(
7643: SELECT 1
7644: FROM po_session_gt gt
7645: WHERE key = l_data_key
7646: AND gt.num1 < x_lines.intf_line_id_tbl(i)
7647: AND gt.num2 = x_lines.hd_po_header_id_tbl(i)
7648: AND gt.num3 = x_lines.line_num_tbl(i));
7648: AND gt.num3 = x_lines.line_num_tbl(i));
7649:
7650: d_position := 50;
7651:
7652: DELETE FROM po_session_gt
7653: WHERE key = p_key
7654: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7655:
7656: -- bug5093465
7715: ------------------------------------------------------------------------
7716:
7717: PROCEDURE set_action_add
7718: (
7719: p_key IN po_session_gt.key%TYPE,
7720: p_group_num IN NUMBER,
7721: p_target_lines_index_tbl IN PO_TBL_NUMBER,
7722: p_check_line_num_assign IN VARCHAR2,
7723: x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
7826: d_api_name CONSTANT VARCHAR2(30) := 'validate_attr_tlp';
7827: d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
7828: d_position NUMBER;
7829:
7830: l_key po_session_gt.key%TYPE;
7831:
7832: -- table used to save the index of the each row
7833: l_index_tbl DBMS_SQL.NUMBER_TABLE;
7834: l_index NUMBER;
7849: x_num_list => l_index_tbl
7850: );
7851:
7852: FORALL i IN 1..x_lines.rec_count
7853: INSERT INTO po_session_gt
7854: (
7855: key,
7856: num1
7857: )
7875: );
7876:
7877: d_position := 10;
7878:
7879: DELETE FROM po_session_gt
7880: WHERE key = l_key
7881: RETURNING num1 BULK COLLECT INTO l_index_tbl;
7882:
7883: d_position := 20;