[Home] [Help]
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
4144: d_position := 10;
4145:
4146: uniqueness_check_on_desc
6147: x_market_price_tbl.EXTEND(p_index_tbl.COUNT);
6148: x_secondary_unit_of_meas_tbl.EXTEND(p_index_tbl.COUNT);
6149:
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)
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
6971: AND NOT EXISTS(
6972: SELECT 1
6973: FROM po_session_gt gt
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
6994: AND NOT EXISTS(
6995: SELECT 1
6996: FROM po_session_gt gt
7563: x_lines.intf_line_id_tbl(i), -- num1
7564: x_lines.hd_po_header_id_tbl(i), -- num2
7565: x_lines.item_desc_tbl(i), -- char1
7566: x_lines.category_tbl(i) -- char2
7567: FROM DUAL;
7568:
7569: d_position := 40;
7570:
7571: FORALL i IN INDICES OF x_processing_row_tbl
7575: num1
7576: )
7577: SELECT p_key,
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
7857: x_lines.item_tbl(i), -- char1
7858: x_lines.item_revision_tbl(i), -- char2
7859: x_lines.vendor_product_num_tbl(i), -- char3
7860: x_lines.supplier_part_auxid_tbl(i) -- char4
7861: FROM DUAL;
7862:
7863: d_position := 40;
7864:
7865: -- bug4930510
7872: num1
7873: )
7874: SELECT p_key,
7875: x_processing_row_tbl(i)
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
8119: x_lines.intf_line_id_tbl(i), -- num1
8120: x_lines.hd_po_header_id_tbl(i), -- num2
8121: x_lines.vendor_product_num_tbl(i), -- char1
8122: x_lines.supplier_part_auxid_tbl(i) -- char2
8123: FROM DUAL;
8124:
8125: d_position := 40;
8126:
8127: FORALL i IN INDICES OF x_processing_row_tbl
8131: num1
8132: )
8133: SELECT p_key,
8134: x_processing_row_tbl(i)
8135: FROM DUAL
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
8378: l_data_key,
8379: x_lines.intf_line_id_tbl(i), -- num1
8380: x_lines.hd_po_header_id_tbl(i), -- num2
8381: x_lines.job_name_tbl(i) -- char1
8382: FROM DUAL;
8383:
8384: d_position := 40;
8385:
8386: FORALL i IN INDICES OF x_processing_row_tbl
8390: num1
8391: )
8392: SELECT p_key,
8393: x_processing_row_tbl(i)
8394: FROM DUAL
8395: WHERE x_lines.item_tbl(i) IS NULL
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(
8588: l_data_key,
8589: x_lines.intf_line_id_tbl(i), -- num1
8590: x_lines.hd_po_header_id_tbl(i), -- num2
8591: x_lines.line_num_tbl(i) -- num3
8592: FROM DUAL;
8593:
8594: d_position := 40;
8595:
8596: FORALL i IN INDICES OF x_processing_row_tbl
8600: num1
8601: )
8602: SELECT p_key,
8603: x_processing_row_tbl(i)
8604: FROM DUAL
8605: WHERE x_lines.item_tbl(i) IS NULL
8606: AND x_lines.vendor_product_num_tbl(i) IS NULL
8607: AND x_lines.job_name_tbl(i) IS NULL
8608: AND x_lines.line_num_tbl(i) IS NOT NULL
8824: )
8825: SELECT
8826: l_key,
8827: l_index_tbl(i)
8828: FROM DUAL
8829: WHERE NOT EXISTS
8830: (
8831: SELECT 1
8832: FROM po_attr_values_tlp_interface
9345: p_line_num_display_tbl(i),
9346: p_clm_base_line_num_disp_tbl(i),
9347: p_clm_option_indicator(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
9418: SELECT p_key,
9419: p_index_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
9494: p_index_tbl(i),
9495: p_from_line_disp_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
9570: p_index_tbl(i),
9571: p_intf_line_id_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
9650: p_intf_line_id_tbl(i),
9651: p_clm_base_line_num_tbl(i),
9652: p_clm_option_indicator_tbl(i),
9653: null
9654: FROM dual;
9655:
9656: SELECT num1,
9657: Rank() over (PARTITION BY num4 ORDER BY num3)
9658: BULK COLLECT INTO l_index_tbl, l_option_num_tbl
9734: p_index_tbl(i),
9735: p_intf_header_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