[Home] [Help]
3134: l_max_line_num_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3135: -- table used to save the index of the each row
3136: l_num_list DBMS_SQL.NUMBER_TABLE;
3137: -- key of temp table used to identify the derived result
3138: l_key po_session_gt.key%TYPE;
3139: l_index_tbl PO_TBL_NUMBER;
3140: l_index NUMBER;
3141: l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
3142: max_line_num NUMBER;
3277: l_group_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3278: -- table used to save the index of the each row
3279: l_num_list DBMS_SQL.NUMBER_TABLE;
3280: -- key of temp table used to identify the derived result
3281: l_key po_session_gt.key%TYPE;
3282: l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3283: l_index NUMBER;
3284: BEGIN
3285:
3301:
3302: d_position := 10;
3303:
3304: FORALL i IN 1..l_num_list.COUNT
3305: INSERT INTO po_session_gt(KEY, num1, num2, num3, num4, char1, char2)
3306: SELECT l_key,
3307: l_num_list(i), --num1
3308: p_intf_header_id_tbl(i),--num2
3309: p_intf_line_id_tbl(i), --num3
3312: p_is_line_num_disp_valid(i)--char2
3313: FROM dual;
3314:
3315: FORALL i IN 1..l_num_list.COUNT
3316: UPDATE po_session_gt psg1
3317: SET psg1.num4 = ( SELECT psg2.num3 FROM po_session_gt psg2
3318: WHERE Nvl(psg2.char2,'Y') = 'Y'
3319: AND Length(psg2.char1) = 4
3320: AND psg2.char1 = SubStr(psg1.char1,1,4)
3313: FROM dual;
3314:
3315: FORALL i IN 1..l_num_list.COUNT
3316: UPDATE po_session_gt psg1
3317: SET psg1.num4 = ( SELECT psg2.num3 FROM po_session_gt psg2
3318: WHERE Nvl(psg2.char2,'Y') = 'Y'
3319: AND Length(psg2.char1) = 4
3320: AND psg2.char1 = SubStr(psg1.char1,1,4)
3321: AND psg2.num2 = psg1.num2
3323: WHERE Nvl(psg1.char2,'Y') = 'Y'
3324: AND Length(psg1.char1) = 6
3325: AND psg1.num3 = p_intf_line_id_tbl(i);
3326:
3327: DELETE FROM po_session_gt
3328: WHERE key = l_key
3329: RETURNING num1, num4 BULK COLLECT INTO l_index_tbl , l_group_line_id_tbl;
3330:
3331: d_position := 30;
3377: l_is_line_num_disp_valid PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
3378: -- table used to save the index of the each row
3379: l_num_list DBMS_SQL.NUMBER_TABLE;
3380: -- key of temp table used to identify the derived result
3381: l_key po_session_gt.key%TYPE;
3382: l_index NUMBER;
3383: BEGIN
3384:
3385: p_is_line_num_disp_valid := PO_TBL_VARCHAR1();
3399:
3400: d_position := 10;
3401:
3402: FORALL i IN 1..l_num_list.COUNT
3403: INSERT INTO po_session_gt(KEY, num1, num2, num3, char1, char2, char3, num4)
3404: SELECT l_key,
3405: l_num_list(i), --num1
3406: p_intf_header_id_tbl(i),--num2
3407: p_intf_line_id_tbl(i), --num3
3412: FROM dual;
3413:
3414: --check for duplicacy of line_num_display within the same header_id
3415: FORALL i IN 1..l_num_list.COUNT
3416: UPDATE po_session_gt psg1
3417: SET char2 = 'N'
3418: WHERE 1 <= ( SELECT Count(num3)
3419: FROM po_session_gt psg2
3420: WHERE psg2.char1 = psg1.char1
3415: FORALL i IN 1..l_num_list.COUNT
3416: UPDATE po_session_gt psg1
3417: SET char2 = 'N'
3418: WHERE 1 <= ( SELECT Count(num3)
3419: FROM po_session_gt psg2
3420: WHERE psg2.char1 = psg1.char1
3421: AND psg2.num2 = psg1.num2
3422: AND psg2.num3 <> psg1.num3
3423: )
3427: AND psg1.num3 = p_intf_line_id_tbl(i);
3428:
3429: d_position := 20;
3430:
3431: UPDATE po_session_gt
3432: SET char2 = 'N'
3433: WHERE Length(char1) NOT IN (4,6)
3434: OR
3435: NOT REGEXP_LIKE (SubStr(char1,1,4),'^[[:digit:]]+$') --not a clin or slin
3443: AND Nvl(char3,'N') = 'N' --priced
3444: AND NOT REGEXP_LIKE (SubStr(char1,5,2),'^[[:alpha:]]+$') --last two charcters not alphabets
3445: );
3446:
3447: DELETE FROM po_session_gt
3448: WHERE key = l_key
3449: RETURNING num1, char2 BULK COLLECT INTO l_index_tbl , l_is_line_num_disp_valid;
3450:
3451: d_position := 30;