[Home] [Help]
703: -- :po_header_id is a place holder for foreign key value.
704: -- A PL/SQL table (list of values) will be used to store data.
705: -- Procedure ece_flatfile.Find_pos will be used to locate the specific
706: -- data value in the PL/SQL table.
707: -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
708: -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
709: --
710: -- Let's use the above example:
711: --
704: -- A PL/SQL table (list of values) will be used to store data.
705: -- Procedure ece_flatfile.Find_pos will be used to locate the specific
706: -- data value in the PL/SQL table.
707: -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
708: -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
709: --
710: -- Let's use the above example:
711: --
712: -- 1. Execute dynamic SQL 1 for headers (A) data
835: -- *** This tells the database to reserve spaces
836: -- *** for the data returned by the SQL statement
837: -- ***************************************************
838: xProgress := 'POOB-10-1200';
839: header_sel_c := dbms_sql.open_cursor;
840:
841: xProgress := 'POOB-10-1210';
842: line_sel_c := dbms_sql.open_cursor;
843:
838: xProgress := 'POOB-10-1200';
839: header_sel_c := dbms_sql.open_cursor;
840:
841: xProgress := 'POOB-10-1210';
842: line_sel_c := dbms_sql.open_cursor;
843:
844: xProgress := 'POOB-10-1220';
845: shipment_sel_c := dbms_sql.open_cursor;
846:
841: xProgress := 'POOB-10-1210';
842: line_sel_c := dbms_sql.open_cursor;
843:
844: xProgress := 'POOB-10-1220';
845: shipment_sel_c := dbms_sql.open_cursor;
846:
847: -- ***************************************************
848: -- Parse each of the SELECT statement
849: -- so the database understands the command
849: -- so the database understands the command
850: -- ***************************************************
851: xProgress := 'POOB-10-1230';
852: BEGIN
853: dbms_sql.parse(header_sel_c,
854: cHeader_select,
855: dbms_sql.native);
856:
857: EXCEPTION
851: xProgress := 'POOB-10-1230';
852: BEGIN
853: dbms_sql.parse(header_sel_c,
854: cHeader_select,
855: dbms_sql.native);
856:
857: EXCEPTION
858: WHEN OTHERS THEN
859: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
855: dbms_sql.native);
856:
857: EXCEPTION
858: WHEN OTHERS THEN
859: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
860: cHeader_select);
861: app_exception.raise_exception;
862: END;
863:
862: END;
863:
864: xProgress := 'POOB-10-1240';
865: BEGIN
866: dbms_sql.parse(line_sel_c,
867: cLine_select,
868: dbms_sql.native);
869:
870: EXCEPTION
864: xProgress := 'POOB-10-1240';
865: BEGIN
866: dbms_sql.parse(line_sel_c,
867: cLine_select,
868: dbms_sql.native);
869:
870: EXCEPTION
871: WHEN OTHERS THEN
872: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
868: dbms_sql.native);
869:
870: EXCEPTION
871: WHEN OTHERS THEN
872: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
873: cLine_select);
874: app_exception.raise_exception;
875: END;
876:
875: END;
876:
877: xProgress := 'POOB-10-1250';
878: BEGIN
879: dbms_sql.parse(shipment_sel_c,
880: cShipment_select,
881: dbms_sql.native);
882:
883: EXCEPTION
877: xProgress := 'POOB-10-1250';
878: BEGIN
879: dbms_sql.parse(shipment_sel_c,
880: cShipment_select,
881: dbms_sql.native);
882:
883: EXCEPTION
884: WHEN OTHERS THEN
885: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
881: dbms_sql.native);
882:
883: EXCEPTION
884: WHEN OTHERS THEN
885: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
886: cShipment_select);
887: app_exception.raise_exception;
888: END;
889:
1270: ece_flatfile_pvt.find_pos(l_shipment_tbl,c_shipment_key_name,n_ship_common_key_pos);
1271:
1272: xProgress := 'POOB-10-1407';
1273: -- bind variables
1274: dbms_sql.bind_variable(header_sel_c,'cComm_Method',cCommunication_Method);
1275: IF cCreate_Date_From IS NOT NULL THEN
1276: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1277: END IF;
1278:
1272: xProgress := 'POOB-10-1407';
1273: -- bind variables
1274: dbms_sql.bind_variable(header_sel_c,'cComm_Method',cCommunication_Method);
1275: IF cCreate_Date_From IS NOT NULL THEN
1276: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1277: END IF;
1278:
1279: IF cCreate_Date_To IS NOT NULL THEN
1280: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1276: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1277: END IF;
1278:
1279: IF cCreate_Date_To IS NOT NULL THEN
1280: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1281: END IF;
1282:
1283: IF cSupplier_Name IS NOT NULL THEN
1284: dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1280: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1281: END IF;
1282:
1283: IF cSupplier_Name IS NOT NULL THEN
1284: dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1285: END IF;
1286:
1287: IF cSupplier_Site IS NOT NULL THEN
1288: dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1284: dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1285: END IF;
1286:
1287: IF cSupplier_Site IS NOT NULL THEN
1288: dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1289: END IF;
1290:
1291: IF cDocument_Type IS NOT NULL THEN
1292: dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1288: dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1289: END IF;
1290:
1291: IF cDocument_Type IS NOT NULL THEN
1292: dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1293: END IF;
1294:
1295: IF cPO_Number_From IS NOT NULL THEN
1296: dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1292: dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1293: END IF;
1294:
1295: IF cPO_Number_From IS NOT NULL THEN
1296: dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1297: END IF;
1298:
1299: IF cPO_Number_To IS NOT NULL THEN
1300: dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1296: dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1297: END IF;
1298:
1299: IF cPO_Number_To IS NOT NULL THEN
1300: dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1301: END IF;
1302:
1303: -- dbms_sql.bind_variable(line_sel_c,'l_nOrganization_ID',nOrganization_ID);
1304:
1299: IF cPO_Number_To IS NOT NULL THEN
1300: dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1301: END IF;
1302:
1303: -- dbms_sql.bind_variable(line_sel_c,'l_nOrganization_ID',nOrganization_ID);
1304:
1305: iKey_count := 0;
1306: if ec_debug.G_debug_level = 3 then
1307: ec_debug.pl(3,'iKey_count: ',iKey_count);
1308: end if;
1309:
1310: -- EXECUTE the SELECT statement
1311: xProgress := 'POOB-10-1408';
1312: dummy := dbms_sql.execute(header_sel_c);
1313:
1314: -- ***************************************************
1315: -- The model is:
1316: -- HEADER - LINE - SHIPMENT ...
1319: -- to the HEADER. Then get all
1320: -- SHIPMENTS that belongs to the LINE.
1321: -- ***************************************************
1322: xProgress := 'POOB-10-1430';
1323: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP -- Header
1324:
1325: -- **************************************
1326: -- store internal values in pl/sql table
1327: -- **************************************
1553: -- ***************************************************
1554:
1555: -- use the following bind_variable feature as you see fit.
1556: xProgress := 'POOB-10-1510';
1557: dbms_sql.bind_variable(line_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1558:
1559: xProgress := 'POOB-10-1515';
1560: dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1561:
1556: xProgress := 'POOB-10-1510';
1557: dbms_sql.bind_variable(line_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1558:
1559: xProgress := 'POOB-10-1515';
1560: dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1561:
1562: xProgress := 'POOB-10-1520';
1563: dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1564:
1559: xProgress := 'POOB-10-1515';
1560: dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1561:
1562: xProgress := 'POOB-10-1520';
1563: dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1564:
1565: xProgress := 'POOB-10-1525';
1566: dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1567:
1562: xProgress := 'POOB-10-1520';
1563: dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1564:
1565: xProgress := 'POOB-10-1525';
1566: dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1567:
1568: xProgress := 'POOB-10-1530';
1569: dummy := dbms_sql.execute(line_sel_c);
1570:
1565: xProgress := 'POOB-10-1525';
1566: dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1567:
1568: xProgress := 'POOB-10-1530';
1569: dummy := dbms_sql.execute(line_sel_c);
1570:
1571: -- ***************************
1572: -- Line Level Loop Starts Here
1573: -- ***************************
1571: -- ***************************
1572: -- Line Level Loop Starts Here
1573: -- ***************************
1574: xProgress := 'POOB-10-1540';
1575: WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
1576:
1577: -- ****************************
1578: -- store values in pl/sql table
1579: -- ****************************
1866: -- set LINE_NUMBER values
1867: -- **********************
1868: -- Removed based on bug:3957851
1869: -- xProgress := 'POOB-10-1627';
1870: -- dbms_sql.bind_variable(shipment_sel_c,'shipment_number',l_line_tbl(nLine_num_pos).value);
1871:
1872: xProgress := 'POOB-10-1630';
1873: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1874:
1869: -- xProgress := 'POOB-10-1627';
1870: -- dbms_sql.bind_variable(shipment_sel_c,'shipment_number',l_line_tbl(nLine_num_pos).value);
1871:
1872: xProgress := 'POOB-10-1630';
1873: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1874:
1875: xProgress := 'POOB-10-1640';
1876: dummy := dbms_sql.execute(shipment_sel_c);
1877:
1872: xProgress := 'POOB-10-1630';
1873: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1874:
1875: xProgress := 'POOB-10-1640';
1876: dummy := dbms_sql.execute(shipment_sel_c);
1877:
1878: -- *************************
1879: -- Shipment loop starts here
1880: -- *************************
1878: -- *************************
1879: -- Shipment loop starts here
1880: -- *************************
1881: xProgress := 'POOB-10-1650';
1882: WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipment
1883:
1884: -- ****************************
1885: -- store values in pl/sql table
1886: -- ****************************
2139:
2140: END LOOP; -- SHIPMENT Level Loop
2141:
2142: xProgress := 'POOB-10-1740';
2143: IF dbms_sql.last_row_count = 0 THEN
2144: v_LevelProcessed := 'SHIPMENT';
2145: ec_debug.pl(0,
2146: 'EC',
2147: 'ECE_NO_DB_ROW_PROCESSED',
2155:
2156: END LOOP; -- LINE Level Loop
2157:
2158: xProgress := 'POOB-10-1750';
2159: IF(dbms_sql.last_row_count = 0) THEN
2160: v_LevelProcessed := 'LINE';
2161: ec_debug.pl(0,
2162: 'EC',
2163: 'ECE_NO_DB_ROW_PROCESSED',
2173:
2174: END LOOP; -- HEADER Level Loop
2175:
2176: xProgress := 'POOB-10-1760';
2177: IF(dbms_sql.last_row_count = 0) THEN
2178: v_LevelProcessed := 'HEADER';
2179: ec_debug.pl(0,
2180: 'EC',
2181: 'ECE_NO_DB_ROW_PROCESSED',
2188: END IF;
2189:
2190: xProgress := 'POOB-10-1770';
2191: if (ece_poo_transaction.project_sel_c>0) then --Bug 2819176
2192: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c); --Bug 2490109
2193: end if;
2194: xProgress := 'POOB-10-1780';
2195: dbms_sql.close_cursor(shipment_sel_c);
2196:
2191: if (ece_poo_transaction.project_sel_c>0) then --Bug 2819176
2192: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c); --Bug 2490109
2193: end if;
2194: xProgress := 'POOB-10-1780';
2195: dbms_sql.close_cursor(shipment_sel_c);
2196:
2197: xProgress := 'POOB-10-1790';
2198: dbms_sql.close_cursor(line_sel_c);
2199:
2194: xProgress := 'POOB-10-1780';
2195: dbms_sql.close_cursor(shipment_sel_c);
2196:
2197: xProgress := 'POOB-10-1790';
2198: dbms_sql.close_cursor(line_sel_c);
2199:
2200: xProgress := 'POOB-10-1800';
2201: dbms_sql.close_cursor(header_sel_c);
2202: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_POO_TRX');
2197: xProgress := 'POOB-10-1790';
2198: dbms_sql.close_cursor(line_sel_c);
2199:
2200: xProgress := 'POOB-10-1800';
2201: dbms_sql.close_cursor(header_sel_c);
2202: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_POO_TRX');
2203:
2204: EXCEPTION
2205: WHEN OTHERS THEN
2584: -- *** This tells the database to reserve spaces
2585: -- *** for the data returned by the SQL statement
2586: -- ***************************************************
2587: xProgress := 'POOB-20-1080';
2588: Header_sel_c := dbms_sql.open_cursor;
2589:
2590: xProgress := 'POOB-20-1090';
2591: line_sel_c := dbms_sql.open_cursor;
2592:
2587: xProgress := 'POOB-20-1080';
2588: Header_sel_c := dbms_sql.open_cursor;
2589:
2590: xProgress := 'POOB-20-1090';
2591: line_sel_c := dbms_sql.open_cursor;
2592:
2593: xProgress := 'POOB-20-1100';
2594: Shipment_sel_c := dbms_sql.open_cursor;
2595:
2590: xProgress := 'POOB-20-1090';
2591: line_sel_c := dbms_sql.open_cursor;
2592:
2593: xProgress := 'POOB-20-1100';
2594: Shipment_sel_c := dbms_sql.open_cursor;
2595:
2596: xProgress := 'POOB-20-1110';
2597: Header_del_c1 := dbms_sql.open_cursor;
2598:
2593: xProgress := 'POOB-20-1100';
2594: Shipment_sel_c := dbms_sql.open_cursor;
2595:
2596: xProgress := 'POOB-20-1110';
2597: Header_del_c1 := dbms_sql.open_cursor;
2598:
2599: xProgress := 'POOB-20-1120';
2600: Line_del_c1 := dbms_sql.open_cursor;
2601:
2596: xProgress := 'POOB-20-1110';
2597: Header_del_c1 := dbms_sql.open_cursor;
2598:
2599: xProgress := 'POOB-20-1120';
2600: Line_del_c1 := dbms_sql.open_cursor;
2601:
2602: xProgress := 'POOB-20-1130';
2603: Shipment_del_c1 := dbms_sql.open_cursor;
2604:
2599: xProgress := 'POOB-20-1120';
2600: Line_del_c1 := dbms_sql.open_cursor;
2601:
2602: xProgress := 'POOB-20-1130';
2603: Shipment_del_c1 := dbms_sql.open_cursor;
2604:
2605: xProgress := 'POOB-20-1140';
2606: Header_del_c2 := dbms_sql.open_cursor;
2607:
2602: xProgress := 'POOB-20-1130';
2603: Shipment_del_c1 := dbms_sql.open_cursor;
2604:
2605: xProgress := 'POOB-20-1140';
2606: Header_del_c2 := dbms_sql.open_cursor;
2607:
2608: xProgress := 'POOB-20-1150';
2609: Line_del_c2 := dbms_sql.open_cursor;
2610:
2605: xProgress := 'POOB-20-1140';
2606: Header_del_c2 := dbms_sql.open_cursor;
2607:
2608: xProgress := 'POOB-20-1150';
2609: Line_del_c2 := dbms_sql.open_cursor;
2610:
2611: xProgress := 'POOB-20-1160';
2612: Shipment_del_c2 := dbms_sql.open_cursor;
2613:
2608: xProgress := 'POOB-20-1150';
2609: Line_del_c2 := dbms_sql.open_cursor;
2610:
2611: xProgress := 'POOB-20-1160';
2612: Shipment_del_c2 := dbms_sql.open_cursor;
2613:
2614: -- *****************************************
2615: -- Parse each of the SELECT statement
2616: -- so the database understands the command
2616: -- so the database understands the command
2617: -- *****************************************
2618: xProgress := 'POOB-20-1170';
2619: BEGIN
2620: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
2621:
2622: EXCEPTION
2623: WHEN OTHERS THEN
2624: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
2620: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
2621:
2622: EXCEPTION
2623: WHEN OTHERS THEN
2624: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
2625: app_exception.raise_exception;
2626:
2627: END;
2628:
2627: END;
2628:
2629: xProgress := 'POOB-20-1180';
2630: BEGIN
2631: dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
2632:
2633: EXCEPTION
2634: WHEN OTHERS THEN
2635: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
2631: dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
2632:
2633: EXCEPTION
2634: WHEN OTHERS THEN
2635: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
2636: app_exception.raise_exception;
2637:
2638: END;
2639:
2638: END;
2639:
2640: xProgress := 'POOB-20-1190';
2641: BEGIN
2642: dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
2643:
2644: EXCEPTION
2645: WHEN OTHERS THEN
2646: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
2642: dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
2643:
2644: EXCEPTION
2645: WHEN OTHERS THEN
2646: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
2647: app_exception.raise_exception;
2648:
2649: END;
2650:
2649: END;
2650:
2651: xProgress := 'POOB-20-1200';
2652: BEGIN
2653: dbms_sql.parse(header_del_c1,cHeader_delete1,dbms_sql.native);
2654:
2655: EXCEPTION
2656: WHEN OTHERS THEN
2657: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
2653: dbms_sql.parse(header_del_c1,cHeader_delete1,dbms_sql.native);
2654:
2655: EXCEPTION
2656: WHEN OTHERS THEN
2657: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
2658: app_exception.raise_exception;
2659:
2660: END;
2661:
2660: END;
2661:
2662: xProgress := 'POOB-20-1210';
2663: BEGIN
2664: dbms_sql.parse(line_del_c1,cLine_delete1,dbms_sql.native);
2665:
2666: EXCEPTION
2667: WHEN OTHERS THEN
2668: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
2664: dbms_sql.parse(line_del_c1,cLine_delete1,dbms_sql.native);
2665:
2666: EXCEPTION
2667: WHEN OTHERS THEN
2668: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
2669: app_exception.raise_exception;
2670:
2671: END;
2672:
2671: END;
2672:
2673: xProgress := 'POOB-20-1220';
2674: BEGIN
2675: dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
2676:
2677: EXCEPTION
2678: WHEN OTHERS THEN
2679: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
2675: dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
2676:
2677: EXCEPTION
2678: WHEN OTHERS THEN
2679: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
2680: app_exception.raise_exception;
2681:
2682: END;
2683:
2682: END;
2683:
2684: xProgress := 'POOB-20-1230';
2685: BEGIN
2686: dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
2687:
2688: EXCEPTION
2689: WHEN OTHERS THEN
2690: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
2686: dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
2687:
2688: EXCEPTION
2689: WHEN OTHERS THEN
2690: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
2691: app_exception.raise_exception;
2692:
2693: END;
2694:
2693: END;
2694:
2695: xProgress := 'POOB-20-1240';
2696: BEGIN
2697: dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
2698:
2699: EXCEPTION
2700: WHEN OTHERS THEN
2701: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
2697: dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
2698:
2699: EXCEPTION
2700: WHEN OTHERS THEN
2701: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
2702: app_exception.raise_exception;
2703:
2704: END;
2705:
2704: END;
2705:
2706: xProgress := 'POOB-20-1250';
2707: BEGIN
2708: dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
2709:
2710: EXCEPTION
2711: WHEN OTHERS THEN
2712: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
2708: dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
2709:
2710: EXCEPTION
2711: WHEN OTHERS THEN
2712: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
2713: app_exception.raise_exception;
2714:
2715: END;
2716:
2745: -- ***************************************************
2746: -- Need rowid for delete (Header Level)
2747: -- ***************************************************
2748: xProgress := 'POOB-20-1280';
2749: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
2750:
2751: xProgress := 'POOB-20-1290';
2752: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2753:
2748: xProgress := 'POOB-20-1280';
2749: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
2750:
2751: xProgress := 'POOB-20-1290';
2752: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2753:
2754: xProgress := 'POOB-20-1300';
2755: dbms_sql.define_column(Header_sel_c,iHeader_count + 3,n_po_header_ID);
2756:
2751: xProgress := 'POOB-20-1290';
2752: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2753:
2754: xProgress := 'POOB-20-1300';
2755: dbms_sql.define_column(Header_sel_c,iHeader_count + 3,n_po_header_ID);
2756:
2757: xProgress := 'POOB-20-1310';
2758: ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_flatfile_pvt.G_MaxColWidth,l_line_tbl);
2759:
2760: -- ***************************************************
2761: -- Need rowid for delete (Line Level)
2762: -- ***************************************************
2763: xProgress := 'POOB-20-1320';
2764: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 1,rLine_rowid);
2765:
2766: xProgress := 'POOB-20-1330';
2767: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2768:
2763: xProgress := 'POOB-20-1320';
2764: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 1,rLine_rowid);
2765:
2766: xProgress := 'POOB-20-1330';
2767: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2768:
2769: xProgress := 'POOB-20-1340';
2770: dbms_sql.define_column(line_sel_c,iLine_count + 3,n_po_line_ID);
2771:
2766: xProgress := 'POOB-20-1330';
2767: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2768:
2769: xProgress := 'POOB-20-1340';
2770: dbms_sql.define_column(line_sel_c,iLine_count + 3,n_po_line_ID);
2771:
2772: xProgress := 'POOB-20-1350';
2773: ece_flatfile_pvt.define_interface_column_type(Shipment_sel_c,cShipment_select,ece_flatfile_pvt.G_MaxColWidth,l_shipment_tbl);
2774:
2775: -- ***************************************************
2776: -- Need rowid for delete (Shipment Level)
2777: -- ***************************************************
2778: xProgress := 'POOB-20-1360';
2779: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 1,rShipment_rowid);
2780:
2781: xProgress := 'POOB-20-1370';
2782: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
2783:
2778: xProgress := 'POOB-20-1360';
2779: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 1,rShipment_rowid);
2780:
2781: xProgress := 'POOB-20-1370';
2782: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
2783:
2784: -- ************************************************************
2785: -- *** The following is custom tailored for this transaction
2786: -- *** It find the values and use them in the WHERE clause to
2790: -- To complete the Line SELECT statement,
2791: -- we will need values for the join condition.
2792: -- *******************************************
2793: xProgress := 'POOB-20-1375';
2794: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2795: dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2796: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2797:
2798: --- EXECUTE the SELECT statement
2791: -- we will need values for the join condition.
2792: -- *******************************************
2793: xProgress := 'POOB-20-1375';
2794: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2795: dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2796: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2797:
2798: --- EXECUTE the SELECT statement
2799: xProgress := 'POOB-20-1380';
2792: -- *******************************************
2793: xProgress := 'POOB-20-1375';
2794: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2795: dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2796: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2797:
2798: --- EXECUTE the SELECT statement
2799: xProgress := 'POOB-20-1380';
2800: dummy := dbms_sql.execute(Header_sel_c);
2796: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2797:
2798: --- EXECUTE the SELECT statement
2799: xProgress := 'POOB-20-1380';
2800: dummy := dbms_sql.execute(Header_sel_c);
2801:
2802: -- ********************************************************************
2803: -- *** With data for each HEADER line, populate the ECE_OUTPUT table
2804: -- *** then populate ECE_OUTPUT with data from all LINES that belongs
2807: -- ********************************************************************
2808:
2809: -- HEADER - LINE - SHIPMENT ...
2810: xProgress := 'POOB-20-1390';
2811: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP -- Header
2812: -- ******************************
2813: -- store values in pl/sql table
2814: -- ******************************
2815: xProgress := 'POOB-20-1400';
2815: xProgress := 'POOB-20-1400';
2816: ece_flatfile_pvt.assign_column_value_to_tbl(header_sel_c,l_header_tbl);
2817:
2818: xProgress := 'POOB-20-1410';
2819: dbms_sql.column_value(header_sel_c,iHeader_count + 1,rHeader_rowid);
2820:
2821: xProgress := 'POOB-20-1420';
2822: dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2823:
2818: xProgress := 'POOB-20-1410';
2819: dbms_sql.column_value(header_sel_c,iHeader_count + 1,rHeader_rowid);
2820:
2821: xProgress := 'POOB-20-1420';
2822: dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2823:
2824: xProgress := 'POOB-20-1430';
2825: dbms_sql.column_value(header_sel_c,iHeader_count + 3,n_po_header_id);
2826:
2821: xProgress := 'POOB-20-1420';
2822: dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2823:
2824: xProgress := 'POOB-20-1430';
2825: dbms_sql.column_value(header_sel_c,iHeader_count + 3,n_po_header_id);
2826:
2827: xProgress := 'POOB-20-1440';
2828: nRelease_num := l_header_tbl(nRelease_num_pos).value;
2829:
2922: -- ******************************************
2923: -- set values into binding variables
2924: -- ******************************************
2925: xProgress := 'POOB-20-1490';
2926: dbms_sql.bind_variable(line_sel_c,'po_header_id',n_po_header_ID);
2927:
2928: xProgress := 'POOB-20-1500';
2929: dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2930:
2925: xProgress := 'POOB-20-1490';
2926: dbms_sql.bind_variable(line_sel_c,'po_header_id',n_po_header_ID);
2927:
2928: xProgress := 'POOB-20-1500';
2929: dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2930:
2931: xProgress := 'POOB-20-1505';
2932: dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2933:
2928: xProgress := 'POOB-20-1500';
2929: dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2930:
2931: xProgress := 'POOB-20-1505';
2932: dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2933:
2934: xProgress := 'POOB-20-1506';
2935: dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2936:
2931: xProgress := 'POOB-20-1505';
2932: dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2933:
2934: xProgress := 'POOB-20-1506';
2935: dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2936:
2937: xProgress := 'POOB-20-1510';
2938: dummy := dbms_sql.execute(line_sel_c);
2939:
2934: xProgress := 'POOB-20-1506';
2935: dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2936:
2937: xProgress := 'POOB-20-1510';
2938: dummy := dbms_sql.execute(line_sel_c);
2939:
2940: -- ***************************************************
2941: -- line loop starts here
2942: -- ***************************************************
2940: -- ***************************************************
2941: -- line loop starts here
2942: -- ***************************************************
2943: xProgress := 'POOB-20-1520';
2944: WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
2945:
2946: -- ***************************************************
2947: -- store values in pl/sql table
2948: -- ***************************************************
2949: xProgress := 'POOB-20-1530';
2950: ece_flatfile_pvt.assign_column_value_to_tbl(line_sel_c,l_line_tbl);
2951:
2952: xProgress := 'POOB-20-1533';
2953: dbms_sql.column_value(line_sel_c,iLine_count + 1,rLine_rowid);
2954:
2955: xProgress := 'POOB-20-1535';
2956: dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2957:
2952: xProgress := 'POOB-20-1533';
2953: dbms_sql.column_value(line_sel_c,iLine_count + 1,rLine_rowid);
2954:
2955: xProgress := 'POOB-20-1535';
2956: dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2957:
2958: xProgress := 'POOB-20-1537';
2959: dbms_sql.column_value(line_sel_c,iLine_count + 3,n_po_line_id);
2960:
2955: xProgress := 'POOB-20-1535';
2956: dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2957:
2958: xProgress := 'POOB-20-1537';
2959: dbms_sql.column_value(line_sel_c,iLine_count + 3,n_po_line_id);
2960:
2961: xProgress := 'POOB-20-1540';
2962: nLine_num := l_line_tbl(nLine_num_pos).value;
2963:
3096: -- **************************
3097: -- set LINE_NUMBER values
3098: -- **************************
3099: xProgress := 'POOB-20-1560';
3100: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',n_po_line_ID);
3101:
3102: xProgress := 'POOB-20-1575';
3103: dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3104:
3099: xProgress := 'POOB-20-1560';
3100: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',n_po_line_ID);
3101:
3102: xProgress := 'POOB-20-1575';
3103: dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3104:
3105: xProgress := 'POOB-20-1580';
3106: dummy := dbms_sql.execute(shipment_sel_c);
3107:
3102: xProgress := 'POOB-20-1575';
3103: dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3104:
3105: xProgress := 'POOB-20-1580';
3106: dummy := dbms_sql.execute(shipment_sel_c);
3107:
3108: -- ****************************
3109: -- Shipment loop starts here
3110: -- ****************************
3108: -- ****************************
3109: -- Shipment loop starts here
3110: -- ****************************
3111: xProgress := 'POCOB-10-1590';
3112: WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipments
3113:
3114: -- *********************************
3115: -- store values in pl/sql table
3116: -- *********************************
3117: xProgress := 'POCOB-10-1600';
3118: ece_flatfile_pvt.assign_column_value_to_tbl(Shipment_sel_c,l_shipment_tbl);
3119:
3120: xProgress := 'POCOB-10-1603';
3121: dbms_sql.column_value(shipment_sel_c,iShipment_count + 1,rShipment_rowid);
3122:
3123: xProgress := 'POCOB-10-1606';
3124: dbms_sql.column_value(shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
3125:
3120: xProgress := 'POCOB-10-1603';
3121: dbms_sql.column_value(shipment_sel_c,iShipment_count + 1,rShipment_rowid);
3122:
3123: xProgress := 'POCOB-10-1606';
3124: dbms_sql.column_value(shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
3125:
3126: xProgress := 'POCOB-10-1610';
3127: nLine_Location_ID := l_shipment_tbl(nLine_Location_ID_pos).value;
3128: if ec_debug.G_debug_level = 3 then
3186: c_file_common_key);
3187: -- END IF; --bug 1891291
3188:
3189: xProgress := 'POOB-20-1640';
3190: dbms_sql.bind_variable(shipment_del_c1,'col_rowid',rShipment_rowid);
3191:
3192: xProgress := 'POOB-20-1650';
3193: dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3194:
3189: xProgress := 'POOB-20-1640';
3190: dbms_sql.bind_variable(shipment_del_c1,'col_rowid',rShipment_rowid);
3191:
3192: xProgress := 'POOB-20-1650';
3193: dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3194:
3195: xProgress := 'POOB-20-1660';
3196: dummy := dbms_sql.execute(shipment_del_c1);
3197:
3192: xProgress := 'POOB-20-1650';
3193: dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3194:
3195: xProgress := 'POOB-20-1660';
3196: dummy := dbms_sql.execute(shipment_del_c1);
3197:
3198: xProgress := 'POOB-20-1670';
3199: dummy := dbms_sql.execute(shipment_del_c2);
3200:
3195: xProgress := 'POOB-20-1660';
3196: dummy := dbms_sql.execute(shipment_del_c1);
3197:
3198: xProgress := 'POOB-20-1670';
3199: dummy := dbms_sql.execute(shipment_del_c2);
3200:
3201: END LOOP; -- Shipment Level
3202:
3203: xProgress := 'POOB-20-1674';
3200:
3201: END LOOP; -- Shipment Level
3202:
3203: xProgress := 'POOB-20-1674';
3204: IF dbms_sql.last_row_count = 0 THEN
3205: v_LevelProcessed := 'SHIPMENT';
3206: ec_debug.pl(0,
3207: 'EC',
3208: 'ECE_NO_DB_ROW_PROCESSED',
3217: -- *********************
3218: -- Use rowid for delete
3219: -- *********************
3220: xProgress := 'POOB-20-1680';
3221: dbms_sql.bind_variable(line_del_c1,'col_rowid',rLine_rowid);
3222:
3223: xProgress := 'POOB-20-1690';
3224: dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3225:
3220: xProgress := 'POOB-20-1680';
3221: dbms_sql.bind_variable(line_del_c1,'col_rowid',rLine_rowid);
3222:
3223: xProgress := 'POOB-20-1690';
3224: dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3225:
3226: xProgress := 'POOB-20-1700';
3227: dummy := dbms_sql.execute(line_del_c1);
3228:
3223: xProgress := 'POOB-20-1690';
3224: dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3225:
3226: xProgress := 'POOB-20-1700';
3227: dummy := dbms_sql.execute(line_del_c1);
3228:
3229: xProgress := 'POOB-20-1710';
3230: dummy := dbms_sql.execute(line_del_c2);
3231:
3226: xProgress := 'POOB-20-1700';
3227: dummy := dbms_sql.execute(line_del_c1);
3228:
3229: xProgress := 'POOB-20-1710';
3230: dummy := dbms_sql.execute(line_del_c2);
3231:
3232: END LOOP; -- Line Level
3233:
3234: xProgress := 'POOB-20-1714';
3231:
3232: END LOOP; -- Line Level
3233:
3234: xProgress := 'POOB-20-1714';
3235: IF dbms_sql.last_row_count = 0 THEN
3236: v_LevelProcessed := 'LINE';
3237: ec_debug.pl(0,
3238: 'EC',
3239: 'ECE_NO_DB_ROW_PROCESSED',
3245: cTransaction_Type);
3246: END IF;
3247:
3248: xProgress := 'POOB-20-1720';
3249: dbms_sql.bind_variable(header_del_c1,'col_rowid',rHeader_rowid);
3250:
3251: xProgress := 'POOB-20-1730';
3252: dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3253:
3248: xProgress := 'POOB-20-1720';
3249: dbms_sql.bind_variable(header_del_c1,'col_rowid',rHeader_rowid);
3250:
3251: xProgress := 'POOB-20-1730';
3252: dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3253:
3254: xProgress := 'POOB-20-1740';
3255: dummy := dbms_sql.execute(header_del_c1);
3256:
3251: xProgress := 'POOB-20-1730';
3252: dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3253:
3254: xProgress := 'POOB-20-1740';
3255: dummy := dbms_sql.execute(header_del_c1);
3256:
3257: xProgress := 'POOB-20-1750';
3258: dummy := dbms_sql.execute(header_del_c2);
3259:
3254: xProgress := 'POOB-20-1740';
3255: dummy := dbms_sql.execute(header_del_c1);
3256:
3257: xProgress := 'POOB-20-1750';
3258: dummy := dbms_sql.execute(header_del_c2);
3259:
3260: END LOOP; -- Header Level
3261:
3262: xProgress := 'POOB-20-1754';
3259:
3260: END LOOP; -- Header Level
3261:
3262: xProgress := 'POOB-20-1754';
3263: IF dbms_sql.last_row_count = 0 THEN
3264: v_LevelProcessed := 'HEADER';
3265: ec_debug.pl(0,
3266: 'EC',
3267: 'ECE_NO_DB_ROW_PROCESSED',
3273: cTransaction_Type);
3274: END IF;
3275:
3276: xProgress := 'POOB-20-1760';
3277: dbms_sql.close_cursor(header_sel_c);
3278:
3279: xProgress := 'POOB-20-1770';
3280: dbms_sql.close_cursor(line_sel_c);
3281:
3276: xProgress := 'POOB-20-1760';
3277: dbms_sql.close_cursor(header_sel_c);
3278:
3279: xProgress := 'POOB-20-1770';
3280: dbms_sql.close_cursor(line_sel_c);
3281:
3282: xProgress := 'POOB-20-1780';
3283: dbms_sql.close_cursor(shipment_sel_c);
3284:
3279: xProgress := 'POOB-20-1770';
3280: dbms_sql.close_cursor(line_sel_c);
3281:
3282: xProgress := 'POOB-20-1780';
3283: dbms_sql.close_cursor(shipment_sel_c);
3284:
3285: xProgress := 'POOB-20-1790';
3286: dbms_sql.close_cursor(header_del_c1);
3287:
3282: xProgress := 'POOB-20-1780';
3283: dbms_sql.close_cursor(shipment_sel_c);
3284:
3285: xProgress := 'POOB-20-1790';
3286: dbms_sql.close_cursor(header_del_c1);
3287:
3288: xProgress := 'POOB-20-1800';
3289: dbms_sql.close_cursor(line_del_c1);
3290:
3285: xProgress := 'POOB-20-1790';
3286: dbms_sql.close_cursor(header_del_c1);
3287:
3288: xProgress := 'POOB-20-1800';
3289: dbms_sql.close_cursor(line_del_c1);
3290:
3291: xProgress := 'POOB-20-1812';
3292: dbms_sql.close_cursor(shipment_del_c1);
3293:
3288: xProgress := 'POOB-20-1800';
3289: dbms_sql.close_cursor(line_del_c1);
3290:
3291: xProgress := 'POOB-20-1812';
3292: dbms_sql.close_cursor(shipment_del_c1);
3293:
3294: xProgress := 'POOB-20-1814';
3295: dbms_sql.close_cursor(header_del_c2);
3296:
3291: xProgress := 'POOB-20-1812';
3292: dbms_sql.close_cursor(shipment_del_c1);
3293:
3294: xProgress := 'POOB-20-1814';
3295: dbms_sql.close_cursor(header_del_c2);
3296:
3297: xProgress := 'POOB-20-1816';
3298: dbms_sql.close_cursor(line_del_c2);
3299:
3294: xProgress := 'POOB-20-1814';
3295: dbms_sql.close_cursor(header_del_c2);
3296:
3297: xProgress := 'POOB-20-1816';
3298: dbms_sql.close_cursor(line_del_c2);
3299:
3300: xProgress := 'POOB-20-1818';
3301: dbms_sql.close_cursor(shipment_del_c2);
3302:
3297: xProgress := 'POOB-20-1816';
3298: dbms_sql.close_cursor(line_del_c2);
3299:
3300: xProgress := 'POOB-20-1818';
3301: dbms_sql.close_cursor(shipment_del_c2);
3302:
3303: -- Bug 2490109 Closing the distribution cursors.
3304: xProgress := 'POOB-50-1819';
3305: if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3302:
3303: -- Bug 2490109 Closing the distribution cursors.
3304: xProgress := 'POOB-50-1819';
3305: if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3306: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c);
3307:
3308: xProgress := 'POOB-50-1820';
3309: dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3310:
3305: if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3306: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c);
3307:
3308: xProgress := 'POOB-50-1820';
3309: dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3310:
3311: xProgress := 'POOB-50-1821';
3312: dbms_sql.close_cursor(ece_poo_transaction.project_del_c2);
3313: end if;
3308: xProgress := 'POOB-50-1820';
3309: dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3310:
3311: xProgress := 'POOB-50-1821';
3312: dbms_sql.close_cursor(ece_poo_transaction.project_del_c2);
3313: end if;
3314:
3315: xProgress := 'POOB-20-1820';
3316: if ec_debug.G_debug_level >= 2 then
3651: end if;
3652:
3653: -- Open the Cursor
3654: xProgress := 'POOB-40-1050';
3655: project_sel_c := dbms_sql.open_cursor;
3656:
3657: -- Parse the Cursor
3658: xProgress := 'POOB-40-1060';
3659: BEGIN
3656:
3657: -- Parse the Cursor
3658: xProgress := 'POOB-40-1060';
3659: BEGIN
3660: dbms_sql.parse(project_sel_c,
3661: cProject_select,
3662: dbms_sql.native);
3663:
3664: EXCEPTION
3658: xProgress := 'POOB-40-1060';
3659: BEGIN
3660: dbms_sql.parse(project_sel_c,
3661: cProject_select,
3662: dbms_sql.native);
3663:
3664: EXCEPTION
3665: WHEN OTHERS THEN
3666: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
3662: dbms_sql.native);
3663:
3664: EXCEPTION
3665: WHEN OTHERS THEN
3666: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
3667: app_exception.raise_exception;
3668: END;
3669:
3670: -- Set Counter
3698:
3699: if ece_poo_transaction.project_sel_c >0 then --Bug 2490109
3700: -- Bind Variables
3701: xProgress := 'POOB-40-1140';
3702: dbms_sql.bind_variable(project_sel_c,':po_header_id',NVL(cPO_Header_ID,0));
3703:
3704: xProgress := 'POOB-40-1150';
3705: dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3706:
3701: xProgress := 'POOB-40-1140';
3702: dbms_sql.bind_variable(project_sel_c,':po_header_id',NVL(cPO_Header_ID,0));
3703:
3704: xProgress := 'POOB-40-1150';
3705: dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3706:
3707: xProgress := 'POOB-40-1160';
3708: dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3709:
3704: xProgress := 'POOB-40-1150';
3705: dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3706:
3707: xProgress := 'POOB-40-1160';
3708: dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3709:
3710: xProgress := 'POOB-40-1170';
3711: dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3712:
3707: xProgress := 'POOB-40-1160';
3708: dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3709:
3710: xProgress := 'POOB-40-1170';
3711: dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3712:
3713: -- Execute the Cursor
3714: xProgress := 'POOB-40-1180';
3715: dummy := dbms_sql.execute(project_sel_c);
3711: dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3712:
3713: -- Execute the Cursor
3714: xProgress := 'POOB-40-1180';
3715: dummy := dbms_sql.execute(project_sel_c);
3716:
3717: -- Fetch Data
3718: xProgress := 'POOB-40-1190';
3719: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
3715: dummy := dbms_sql.execute(project_sel_c);
3716:
3717: -- Fetch Data
3718: xProgress := 'POOB-40-1190';
3719: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
3720: -- Store Internal Values in the PL/SQL Table
3721: xProgress := 'POOB-40-1200';
3722: ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,iKey_count,l_project_tbl,l_key_tbl);
3723:
3857: END LOOP;
3858:
3859: -- Check to see if anything was processed
3860: xProgress := 'POOB-40-1250';
3861: IF(dbms_sql.last_row_count = 0) THEN
3862: v_LevelProcessed := 'PROJECT';
3863: ec_debug.pl(0,'EC','ECE_NO_DB_ROW_PROCESSED','LEVEL_PROCESSED',v_LevelProcessed,'PROGRESS_LEVEL',xProgress,'TRANSACTION_TYPE',cTransaction_Type);
3864: END IF;
3865:
3866: end if; --Bug 2490109
3867:
3868: -- Close the Cursor Bug 2490109
3869: -- xProgress := 'POOB-40-1260';
3870: -- dbms_sql.close_cursor(project_sel_c);
3871:
3872: -- Tell Debug that this Procedure is Done
3873: if ec_debug.G_debug_level = 3 then
3874: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_PROJECT_INFO');
4006: end if;
4007:
4008: -- Open the Cursors
4009: xProgress := 'POOB-50-1020';
4010: project_sel_c := dbms_sql.open_cursor;
4011:
4012: xProgress := 'POOB-50-1030';
4013: project_del_c1 := dbms_sql.open_cursor;
4014:
4009: xProgress := 'POOB-50-1020';
4010: project_sel_c := dbms_sql.open_cursor;
4011:
4012: xProgress := 'POOB-50-1030';
4013: project_del_c1 := dbms_sql.open_cursor;
4014:
4015: xProgress := 'POOB-50-1040';
4016: project_del_c2 := dbms_sql.open_cursor;
4017:
4012: xProgress := 'POOB-50-1030';
4013: project_del_c1 := dbms_sql.open_cursor;
4014:
4015: xProgress := 'POOB-50-1040';
4016: project_del_c2 := dbms_sql.open_cursor;
4017:
4018: -- Parse the SQL Statements
4019: xProgress := 'POOB-50-1050';
4020: BEGIN
4018: -- Parse the SQL Statements
4019: xProgress := 'POOB-50-1050';
4020: BEGIN
4021: xProgress := 'POOB-50-1060';
4022: dbms_sql.parse(project_sel_c,cProject_select,dbms_sql.native);
4023:
4024: EXCEPTION
4025: WHEN OTHERS THEN
4026: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
4022: dbms_sql.parse(project_sel_c,cProject_select,dbms_sql.native);
4023:
4024: EXCEPTION
4025: WHEN OTHERS THEN
4026: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
4027: app_exception.raise_exception;
4028:
4029: END;
4030:
4030:
4031: xProgress := 'POOB-50-1070';
4032: BEGIN
4033: xProgress := 'POOB-50-1080';
4034: dbms_sql.parse(project_del_c1,cProject_delete1,dbms_sql.native);
4035:
4036: EXCEPTION
4037: WHEN OTHERS THEN
4038: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete1);
4034: dbms_sql.parse(project_del_c1,cProject_delete1,dbms_sql.native);
4035:
4036: EXCEPTION
4037: WHEN OTHERS THEN
4038: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete1);
4039: app_exception.raise_exception;
4040:
4041: END;
4042:
4042:
4043: xProgress := 'POOB-50-1090';
4044: BEGIN
4045: xProgress := 'POOB-50-1100';
4046: dbms_sql.parse(project_del_c2,cProject_delete2,dbms_sql.native);
4047:
4048: EXCEPTION
4049: WHEN OTHERS THEN
4050: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete2);
4046: dbms_sql.parse(project_del_c2,cProject_delete2,dbms_sql.native);
4047:
4048: EXCEPTION
4049: WHEN OTHERS THEN
4050: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete2);
4051: app_exception.raise_exception;
4052:
4053: END;
4054:
4071: l_project_tbl);
4072:
4073: -- Define Additional Column Types
4074: xProgress := 'POOB-50-1130';
4075: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 1,rProject_rowid);
4076:
4077: xProgress := 'POOB-50-1140';
4078: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4079:
4074: xProgress := 'POOB-50-1130';
4075: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 1,rProject_rowid);
4076:
4077: xProgress := 'POOB-50-1140';
4078: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4079:
4080: -- Bind Variables
4081: xProgress := 'POOB-50-1150';
4082: dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4078: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4079:
4080: -- Bind Variables
4081: xProgress := 'POOB-50-1150';
4082: dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4083:
4084: xProgress := 'POOB-50-1160';
4085: dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4086:
4081: xProgress := 'POOB-50-1150';
4082: dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4083:
4084: xProgress := 'POOB-50-1160';
4085: dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4086:
4087: xProgress := 'POOB-50-1170';
4088: dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4089:
4084: xProgress := 'POOB-50-1160';
4085: dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4086:
4087: xProgress := 'POOB-50-1170';
4088: dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4089:
4090: xProgress := 'POOB-50-1180';
4091: dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4092:
4087: xProgress := 'POOB-50-1170';
4088: dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4089:
4090: xProgress := 'POOB-50-1180';
4091: dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4092:
4093: xProgress := 'POOB-50-1190';
4094: dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4095:
4090: xProgress := 'POOB-50-1180';
4091: dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4092:
4093: xProgress := 'POOB-50-1190';
4094: dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4095:
4096: -- Execute the SQL Statement
4097: xProgress := 'POOB-50-1200';
4098: dummy := dbms_sql.execute(project_sel_c);
4094: dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4095:
4096: -- Execute the SQL Statement
4097: xProgress := 'POOB-50-1200';
4098: dummy := dbms_sql.execute(project_sel_c);
4099:
4100: -- Fetch Data
4101: xProgress := 'POOB-50-1210';
4102: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
4098: dummy := dbms_sql.execute(project_sel_c);
4099:
4100: -- Fetch Data
4101: xProgress := 'POOB-50-1210';
4102: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
4103: -- Store the Fetched Data in the PL/SQL Table
4104: xProgress := 'POOB-50-1220';
4105: ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,l_project_tbl);
4106:
4104: xProgress := 'POOB-50-1220';
4105: ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,l_project_tbl);
4106:
4107: xProgress := 'POOB-50-1230';
4108: dbms_sql.column_value(project_sel_c,iProject_count + 1,rProject_rowid);
4109:
4110: xProgress := 'POOB-50-1240';
4111: dbms_sql.column_value(project_sel_c,iProject_count + 2,rProject_X_rowid);
4112:
4107: xProgress := 'POOB-50-1230';
4108: dbms_sql.column_value(project_sel_c,iProject_count + 1,rProject_rowid);
4109:
4110: xProgress := 'POOB-50-1240';
4111: dbms_sql.column_value(project_sel_c,iProject_count + 2,rProject_X_rowid);
4112:
4113: xProgress := 'POOB-50-1250';
4114: ece_poo_transaction.write_to_file(cTransaction_Type,
4115: cCommunication_Method,
4121: null);
4122:
4123: -- Bind the ROWIDs for Deletion
4124: xProgress := 'POOB-50-1260';
4125: dbms_sql.bind_variable(project_del_c1,':col_rowid',rProject_rowid);
4126:
4127: xProgress := 'POOB-50-1270';
4128: dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4129:
4124: xProgress := 'POOB-50-1260';
4125: dbms_sql.bind_variable(project_del_c1,':col_rowid',rProject_rowid);
4126:
4127: xProgress := 'POOB-50-1270';
4128: dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4129:
4130: -- Execute the First Delete SQL
4131: xProgress := 'POOB-50-1280';
4132: dummy := dbms_sql.execute(project_del_c1);
4128: dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4129:
4130: -- Execute the First Delete SQL
4131: xProgress := 'POOB-50-1280';
4132: dummy := dbms_sql.execute(project_del_c1);
4133:
4134: -- Execute the Second Delete SQL
4135: xProgress := 'POOB-50-1290';
4136: dummy := dbms_sql.execute(project_del_c2);
4132: dummy := dbms_sql.execute(project_del_c1);
4133:
4134: -- Execute the Second Delete SQL
4135: xProgress := 'POOB-50-1290';
4136: dummy := dbms_sql.execute(project_del_c2);
4137:
4138: END LOOP; -- Project Level
4139:
4140: -- Make a note if zero rows were processed
4138: END LOOP; -- Project Level
4139:
4140: -- Make a note if zero rows were processed
4141: xProgress := 'POOB-50-1300';
4142: IF dbms_sql.last_row_count = 0 THEN
4143: xProgress := 'POOB-50-1310';
4144: v_LevelProcessed := 'PROJECT';
4145: ec_debug.pl(0,
4146: 'EC',
4157:
4158: /* Bug 2490109
4159: -- Let's Close the Cursors
4160: xProgress := 'POOB-50-1320';
4161: dbms_sql.close_cursor(project_sel_c);
4162:
4163: xProgress := 'POOB-50-1330';
4164: dbms_sql.close_cursor(project_del_c1);
4165:
4160: xProgress := 'POOB-50-1320';
4161: dbms_sql.close_cursor(project_sel_c);
4162:
4163: xProgress := 'POOB-50-1330';
4164: dbms_sql.close_cursor(project_del_c1);
4165:
4166: xProgress := 'POOB-50-1340';
4167: dbms_sql.close_cursor(project_del_c2);
4168: */
4163: xProgress := 'POOB-50-1330';
4164: dbms_sql.close_cursor(project_del_c1);
4165:
4166: xProgress := 'POOB-50-1340';
4167: dbms_sql.close_cursor(project_del_c2);
4168: */
4169:
4170: xProgress := 'POOB-50-1350';
4171: if ec_debug.G_debug_level >= 2 then
4392: end if;
4393:
4394: -- Open Cursor.
4395: xProgress := 'POOB-60-1100';
4396: n_att_header_sel_c := dbms_sql.open_cursor;
4397:
4398: -- Parse Cursor.
4399: xProgress := 'POOB-60-1110';
4400: BEGIN
4397:
4398: -- Parse Cursor.
4399: xProgress := 'POOB-60-1110';
4400: BEGIN
4401: dbms_sql.parse(n_att_header_sel_c,v_att_header_select,dbms_sql.native);
4402:
4403: EXCEPTION
4404: WHEN OTHERS THEN
4405: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
4401: dbms_sql.parse(n_att_header_sel_c,v_att_header_select,dbms_sql.native);
4402:
4403: EXCEPTION
4404: WHEN OTHERS THEN
4405: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
4406: v_att_header_select);
4407: app_exception.raise_exception;
4408: END;
4409:
4421: l_att_header_tbl);
4422:
4423: -- Bind Variables
4424: xProgress := 'POOB-60-1132';
4425: dbms_sql.bind_variable(n_att_header_sel_c,':cEntity_Name',cEntity_Name);
4426:
4427: xProgress := 'POOB-60-1133';
4428: dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4429:
4424: xProgress := 'POOB-60-1132';
4425: dbms_sql.bind_variable(n_att_header_sel_c,':cEntity_Name',cEntity_Name);
4426:
4427: xProgress := 'POOB-60-1133';
4428: dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4429:
4430: xProgress := 'POOB-60-1134';
4431: dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4432:
4427: xProgress := 'POOB-60-1133';
4428: dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4429:
4430: xProgress := 'POOB-60-1134';
4431: dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4432:
4433: IF cPK2_Value IS NOT NULL and cPK2_Value <> C_ANY_VALUE THEN
4434: xProgress := 'POOB-60-1135';
4435: dbms_sql.bind_variable(n_att_header_sel_c,':cPK2_Value',cPK2_Value);
4431: dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4432:
4433: IF cPK2_Value IS NOT NULL and cPK2_Value <> C_ANY_VALUE THEN
4434: xProgress := 'POOB-60-1135';
4435: dbms_sql.bind_variable(n_att_header_sel_c,':cPK2_Value',cPK2_Value);
4436: END IF;
4437:
4438: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4439: /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4438: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4439: /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4440: IF cPK3_Value IS NOT NULL AND cPK3_Value <> C_ANY_VALUE THEN
4441: xProgress := 'POOB-60-1136';
4442: dbms_sql.bind_variable(n_att_header_sel_c,':cPK3_Value',cPK3_Value);
4443: END IF;
4444:
4445: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4446: IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4444:
4445: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4446: IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4447: xProgress := 'POOB-60-1137';
4448: dbms_sql.bind_variable(n_att_header_sel_c,':cPK4_Value',cPK4_Value);
4449: END IF;
4450:
4451: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4452: IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4450:
4451: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4452: IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4453: xProgress := 'POOB-60-1138';
4454: dbms_sql.bind_variable(n_att_header_sel_c,':cPK5_Value',cPK5_Value);
4455: END IF;
4456: END IF;
4457:
4458: -- Execute Cursor
4456: END IF;
4457:
4458: -- Execute Cursor
4459: xProgress := 'POOB-60-1140';
4460: n_dummy := dbms_sql.execute(n_att_header_sel_c);
4461:
4462: xProgress := 'POOB-60-1150';
4463: WHILE dbms_sql.fetch_rows(n_att_header_sel_c) > 0 LOOP
4464: xProgress := 'POOB-60-1160';
4459: xProgress := 'POOB-60-1140';
4460: n_dummy := dbms_sql.execute(n_att_header_sel_c);
4461:
4462: xProgress := 'POOB-60-1150';
4463: WHILE dbms_sql.fetch_rows(n_att_header_sel_c) > 0 LOOP
4464: xProgress := 'POOB-60-1160';
4465: ece_flatfile_pvt.assign_column_value_to_tbl(n_att_header_sel_c,l_key_count,l_att_header_tbl,l_key_tbl);
4466:
4467: xProgress := 'POOB-60-1170';
4519:
4520: END LOOP;
4521:
4522: xProgress := 'POOB-60-1210';
4523: dbms_sql.close_cursor(n_att_header_sel_c);
4524: if ec_debug.G_debug_level >= 2 then
4525: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_TEXT_ATTACHMENT');
4526: end if;
4527:
4802: end if;
4803:
4804: -- Open Cursor.
4805: xProgress := 'POOB-70-1210';
4806: n_att_detail_sel_c := dbms_sql.open_cursor;
4807:
4808: -- Parse Cursor.
4809: xProgress := 'POOB-70-1220';
4810: BEGIN
4808: -- Parse Cursor.
4809: xProgress := 'POOB-70-1220';
4810: BEGIN
4811: xProgress := 'POOB-70-1230';
4812: dbms_sql.parse(n_att_detail_sel_c,v_att_detail_select,dbms_sql.native);
4813:
4814: EXCEPTION
4815: WHEN OTHERS THEN
4816: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,v_att_detail_select);
4812: dbms_sql.parse(n_att_detail_sel_c,v_att_detail_select,dbms_sql.native);
4813:
4814: EXCEPTION
4815: WHEN OTHERS THEN
4816: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,v_att_detail_select);
4817: app_exception.raise_exception;
4818:
4819: END;
4820:
4818:
4819: END;
4820:
4821: xProgress := 'POOB-70-1240';
4822: dbms_sql.bind_variable(n_att_detail_sel_c,':cEntity_name',cEntity_name);
4823:
4824: xProgress := 'POOB-70-1250';
4825: dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4826:
4821: xProgress := 'POOB-70-1240';
4822: dbms_sql.bind_variable(n_att_detail_sel_c,':cEntity_name',cEntity_name);
4823:
4824: xProgress := 'POOB-70-1250';
4825: dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4826:
4827: xProgress := 'POOB-70-1255';
4828: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4829:
4824: xProgress := 'POOB-70-1250';
4825: dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4826:
4827: xProgress := 'POOB-70-1255';
4828: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4829:
4830: xProgress := 'POOB-70-1258';
4831: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4832:
4827: xProgress := 'POOB-70-1255';
4828: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4829:
4830: xProgress := 'POOB-70-1258';
4831: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4832:
4833: xProgress := 'POOB-70-1260';
4834: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK1_Value',cPK1_Value);
4835:
4830: xProgress := 'POOB-70-1258';
4831: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4832:
4833: xProgress := 'POOB-70-1260';
4834: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK1_Value',cPK1_Value);
4835:
4836: xProgress := 'POOB-70-1270';
4837: /* IF cPK2_Value IS NOT NULL THEN */ -- BUG:5367903
4838: IF cPK2_Value IS NOT NULL AND cPK2_Value <> C_ANY_VALUE THEN
4835:
4836: xProgress := 'POOB-70-1270';
4837: /* IF cPK2_Value IS NOT NULL THEN */ -- BUG:5367903
4838: IF cPK2_Value IS NOT NULL AND cPK2_Value <> C_ANY_VALUE THEN
4839: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK2_Value',cPK2_Value);
4840: END IF;
4841:
4842: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4843: xProgress := 'POOB-70-1280';
4842: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4843: xProgress := 'POOB-70-1280';
4844: /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4845: IF cPK3_Value IS NOT NULL AND cPK3_Value <> C_ANY_VALUE THEN
4846: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK3_Value',cPK3_Value);
4847: END IF;
4848:
4849: xProgress := 'POOB-70-1290';
4850: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4848:
4849: xProgress := 'POOB-70-1290';
4850: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4851: IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4852: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK4_Value',cPK4_Value);
4853: END IF;
4854:
4855: xProgress := 'POOB-70-1300';
4856: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4854:
4855: xProgress := 'POOB-70-1300';
4856: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4857: IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4858: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK5_Value',cPK5_Value);
4859: END IF;
4860: END IF;
4861:
4862: -- Set Counter
4874: l_att_detail_tbl);
4875:
4876: IF cData_Type_ID = 1 THEN -- Short Text
4877: xProgress := 'POOB-70-1330';
4878: /* dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,ece_extract_utils_pub.G_MAXCOLWIDTH);*/
4879: /*Bug 2153310.
4880: Increased the size to 2000 since the short text attachment can have
4881: 2000 characters */
4882: dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4878: /* dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,ece_extract_utils_pub.G_MAXCOLWIDTH);*/
4879: /*Bug 2153310.
4880: Increased the size to 2000 since the short text attachment can have
4881: 2000 characters */
4882: dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4883: ELSE -- Long Text
4884: xProgress := 'POOB-70-1340';
4885: dbms_sql.define_column_long(n_att_detail_sel_c,n_detail_count + 1);
4886: END IF;
4881: 2000 characters */
4882: dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4883: ELSE -- Long Text
4884: xProgress := 'POOB-70-1340';
4885: dbms_sql.define_column_long(n_att_detail_sel_c,n_detail_count + 1);
4886: END IF;
4887:
4888: -- Execute Cursor
4889: xProgress := 'POOB-70-1350';
4886: END IF;
4887:
4888: -- Execute Cursor
4889: xProgress := 'POOB-70-1350';
4890: n_dummy := dbms_sql.execute(n_att_detail_sel_c);
4891:
4892: xProgress := 'POOB-70-1360';
4893: WHILE dbms_sql.fetch_rows(n_att_detail_sel_c) > 0 LOOP
4894: xProgress := 'POOB-70-1370';
4889: xProgress := 'POOB-70-1350';
4890: n_dummy := dbms_sql.execute(n_att_detail_sel_c);
4891:
4892: xProgress := 'POOB-70-1360';
4893: WHILE dbms_sql.fetch_rows(n_att_detail_sel_c) > 0 LOOP
4894: xProgress := 'POOB-70-1370';
4895: ec_debug.pl(3,'xProgress: ',xProgress);
4896:
4897: ece_flatfile_pvt.assign_column_value_to_tbl(n_att_detail_sel_c,l_att_detail_tbl);
4910: if ec_debug.G_debug_level = 3 then
4911: ec_debug.pl(3,'xProgress: ',xProgress);
4912: end if;
4913:
4914: dbms_sql.column_value(n_att_detail_sel_c,n_detail_count + 1,l_short_text);
4915: -- l_short_text := LTRIM(RTRIM(l_short_text)); -- Not using this one because white spaces at front may be intentional
4916: l_short_text := RTRIM(l_short_text);
4917: END IF;
4918:
4949: ec_debug.pl(3,'xProgress: ',xProgress);
4950: end if;
4951:
4952: -- Cursor(I) ,Pos(I) ,Len(I) ,Offset(I) ,Value(O) ,Value Len(O)
4953: dbms_sql.column_value_long(n_att_detail_sel_c,n_detail_count + 1,cSegment_Size,n_cur_pos - 1,l_data_chunk,n_return_size);
4954: if ec_debug.G_debug_level = 3 then
4955: ec_debug.pl(3,'n_cur_pos: ',n_cur_pos);
4956: ec_debug.pl(3,'n_return_size: ',n_return_size);
4957: ec_debug.pl(3,'l_data_chunk: ',l_data_chunk);
5186: if ec_debug.G_debug_level = 3 then
5187: ec_debug.pl(3,'xProgress: ',xProgress);
5188: end if;
5189:
5190: dbms_sql.column_value_long(n_att_detail_sel_c,n_detail_count + 1,cSegment_Size,n_cur_pos - 1,l_temp_text,n_temp_return_size);
5191: END IF;
5192:
5193: IF (n_temp_return_size = 0 AND cData_Type_ID = 2) OR (l_temp_text IS NULL AND cData_Type_ID = 1) THEN
5194: xProgress := 'POOB-70-1624';
5240:
5241: END LOOP;
5242:
5243: xProgress := 'POOB-70-1640';
5244: dbms_sql.close_cursor(n_att_detail_sel_c);
5245:
5246: if ec_debug.G_debug_level >= 2 then
5247: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_TEXT_ATT_DETAIL');
5248: end if;
5464: end if;
5465:
5466: -- Open Cursors
5467: xProgress := 'POOB-80-1090';
5468: Header_sel_c := dbms_sql.open_cursor;
5469:
5470: xProgress := 'POOB-80-1100';
5471: Header_del_c1 := dbms_sql.open_cursor;
5472:
5467: xProgress := 'POOB-80-1090';
5468: Header_sel_c := dbms_sql.open_cursor;
5469:
5470: xProgress := 'POOB-80-1100';
5471: Header_del_c1 := dbms_sql.open_cursor;
5472:
5473: xProgress := 'POOB-80-1110';
5474: Header_del_c2 := dbms_sql.open_cursor;
5475:
5470: xProgress := 'POOB-80-1100';
5471: Header_del_c1 := dbms_sql.open_cursor;
5472:
5473: xProgress := 'POOB-80-1110';
5474: Header_del_c2 := dbms_sql.open_cursor;
5475:
5476: -- Parse the SELECT SQL
5477: BEGIN
5478: xProgress := 'POOB-80-1120';
5475:
5476: -- Parse the SELECT SQL
5477: BEGIN
5478: xProgress := 'POOB-80-1120';
5479: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
5480:
5481: EXCEPTION
5482: WHEN OTHERS THEN
5483: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
5479: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
5480:
5481: EXCEPTION
5482: WHEN OTHERS THEN
5483: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
5484: app_exception.raise_exception;
5485: END;
5486:
5487:
5487:
5488: -- Parse the DELETE1 SQL
5489: BEGIN
5490: xProgress := 'POOB-80-1130';
5491: dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
5492:
5493: EXCEPTION
5494: WHEN OTHERS THEN
5495: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
5491: dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
5492:
5493: EXCEPTION
5494: WHEN OTHERS THEN
5495: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
5496: app_exception.raise_exception;
5497: END;
5498:
5499: -- Parse the DELETE2 SQL
5498:
5499: -- Parse the DELETE2 SQL
5500: BEGIN
5501: xProgress := 'POOB-80-1140';
5502: dbms_sql.parse(Header_del_c2,cHeader_delete2,dbms_sql.native);
5503:
5504: EXCEPTION
5505: WHEN OTHERS THEN
5506: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
5502: dbms_sql.parse(Header_del_c2,cHeader_delete2,dbms_sql.native);
5503:
5504: EXCEPTION
5505: WHEN OTHERS THEN
5506: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
5507: app_exception.raise_exception;
5508: END;
5509:
5510: --Set Counter
5520: ece_flatfile_pvt.G_MaxColWidth,
5521: l_header_tbl );
5522:
5523: xProgress := 'POOB-80-1170';
5524: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5525:
5526: xProgress := 'POOB-80-1180';
5527: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5528:
5523: xProgress := 'POOB-80-1170';
5524: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5525:
5526: xProgress := 'POOB-80-1180';
5527: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5528:
5529: -- Bug 2198707
5530: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5531: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5526: xProgress := 'POOB-80-1180';
5527: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5528:
5529: -- Bug 2198707
5530: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5531: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5532: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5533: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5534: IF cPK2_Value IS NOT NULL THEN
5527: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5528:
5529: -- Bug 2198707
5530: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5531: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5532: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5533: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5534: IF cPK2_Value IS NOT NULL THEN
5535: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5528:
5529: -- Bug 2198707
5530: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5531: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5532: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5533: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5534: IF cPK2_Value IS NOT NULL THEN
5535: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5536: END IF;
5529: -- Bug 2198707
5530: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5531: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5532: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5533: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5534: IF cPK2_Value IS NOT NULL THEN
5535: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5536: END IF;
5537: IF cPK3_Value IS NOT NULL THEN
5531: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5532: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5533: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5534: IF cPK2_Value IS NOT NULL THEN
5535: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5536: END IF;
5537: IF cPK3_Value IS NOT NULL THEN
5538: dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5539: END IF;
5534: IF cPK2_Value IS NOT NULL THEN
5535: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5536: END IF;
5537: IF cPK3_Value IS NOT NULL THEN
5538: dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5539: END IF;
5540: IF cPK4_Value IS NOT NULL THEN
5541: dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5542: END IF;
5537: IF cPK3_Value IS NOT NULL THEN
5538: dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5539: END IF;
5540: IF cPK4_Value IS NOT NULL THEN
5541: dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5542: END IF;
5543: IF cPK5_Value IS NOT NULL THEN
5544: dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5545: END IF;
5540: IF cPK4_Value IS NOT NULL THEN
5541: dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5542: END IF;
5543: IF cPK5_Value IS NOT NULL THEN
5544: dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5545: END IF;
5546: --Execute the Cursor
5547: xProgress := 'POOB-80-1190';
5548: dummy := dbms_sql.execute(Header_sel_c);
5544: dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5545: END IF;
5546: --Execute the Cursor
5547: xProgress := 'POOB-80-1190';
5548: dummy := dbms_sql.execute(Header_sel_c);
5549:
5550: xProgress := 'POOB-80-1200';
5551: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP
5552: xProgress := 'POOB-80-1210';
5547: xProgress := 'POOB-80-1190';
5548: dummy := dbms_sql.execute(Header_sel_c);
5549:
5550: xProgress := 'POOB-80-1200';
5551: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP
5552: xProgress := 'POOB-80-1210';
5553: ece_flatfile_pvt.assign_column_value_to_tbl(Header_sel_c,l_header_tbl);
5554:
5555: xProgress := 'POOB-80-1220';
5552: xProgress := 'POOB-80-1210';
5553: ece_flatfile_pvt.assign_column_value_to_tbl(Header_sel_c,l_header_tbl);
5554:
5555: xProgress := 'POOB-80-1220';
5556: dbms_sql.column_value(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5557:
5558: xProgress := 'POOB-80-1230';
5559: dbms_sql.column_value(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5560:
5555: xProgress := 'POOB-80-1220';
5556: dbms_sql.column_value(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5557:
5558: xProgress := 'POOB-80-1230';
5559: dbms_sql.column_value(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5560:
5561: -- Find the Position of the Attachemnt Doc ID
5562: xProgress := 'POOB-80-1232';
5563: ece_flatfile_pvt.find_pos(l_header_tbl,'DOCUMENT_ID',nDoc_ID_pos);
5590: cFile_Common_Key,
5591: null);
5592:
5593: xProgress := 'POOB-80-1260';
5594: dbms_sql.bind_variable(Header_del_c1,'col_rowid',rHeader_rowid);
5595:
5596: xProgress := 'POOB-80-1270';
5597: dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5598:
5593: xProgress := 'POOB-80-1260';
5594: dbms_sql.bind_variable(Header_del_c1,'col_rowid',rHeader_rowid);
5595:
5596: xProgress := 'POOB-80-1270';
5597: dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5598:
5599: -- Execute the Cursor that deletes from the Interface Table
5600: xProgress := 'POOB-80-1280';
5601: dummy := dbms_sql.execute(Header_del_c1);
5597: dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5598:
5599: -- Execute the Cursor that deletes from the Interface Table
5600: xProgress := 'POOB-80-1280';
5601: dummy := dbms_sql.execute(Header_del_c1);
5602:
5603: -- Execute the Cursor that deletes from the Extension Table
5604: xProgress := 'POOB-80-1290';
5605: dummy := dbms_sql.execute(Header_del_c2);
5601: dummy := dbms_sql.execute(Header_del_c1);
5602:
5603: -- Execute the Cursor that deletes from the Extension Table
5604: xProgress := 'POOB-80-1290';
5605: dummy := dbms_sql.execute(Header_del_c2);
5606:
5607: xProgress := 'POOB-80-1300';
5608: put_att_detail_to_output_table(cCommunication_Method,
5609: cTransaction_Type,
5625: END LOOP;
5626:
5627: -- Close the Cursors and Cleanup...
5628: xProgress := 'POOB-80-1310';
5629: dbms_sql.close_cursor(Header_sel_c);
5630: dbms_sql.close_cursor(Header_del_c1);
5631: dbms_sql.close_cursor(Header_del_c2);
5632: if ec_debug.G_debug_level >= 2 then
5633: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5626:
5627: -- Close the Cursors and Cleanup...
5628: xProgress := 'POOB-80-1310';
5629: dbms_sql.close_cursor(Header_sel_c);
5630: dbms_sql.close_cursor(Header_del_c1);
5631: dbms_sql.close_cursor(Header_del_c2);
5632: if ec_debug.G_debug_level >= 2 then
5633: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5634: end if;
5627: -- Close the Cursors and Cleanup...
5628: xProgress := 'POOB-80-1310';
5629: dbms_sql.close_cursor(Header_sel_c);
5630: dbms_sql.close_cursor(Header_del_c1);
5631: dbms_sql.close_cursor(Header_del_c2);
5632: if ec_debug.G_debug_level >= 2 then
5633: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5634: end if;
5635: EXCEPTION
5859: end if;
5860:
5861: -- Open Cursors
5862: xProgress := 'POOB-90-1110';
5863: Detail_sel_c := dbms_sql.open_cursor;
5864:
5865: xProgress := 'POOB-90-1120';
5866: Detail_del_c1 := dbms_sql.open_cursor;
5867:
5862: xProgress := 'POOB-90-1110';
5863: Detail_sel_c := dbms_sql.open_cursor;
5864:
5865: xProgress := 'POOB-90-1120';
5866: Detail_del_c1 := dbms_sql.open_cursor;
5867:
5868: xProgress := 'POOB-90-1130';
5869: Detail_del_c2 := dbms_sql.open_cursor;
5870:
5865: xProgress := 'POOB-90-1120';
5866: Detail_del_c1 := dbms_sql.open_cursor;
5867:
5868: xProgress := 'POOB-90-1130';
5869: Detail_del_c2 := dbms_sql.open_cursor;
5870:
5871: -- Parse the SELECT SQL
5872: BEGIN
5873: xProgress := 'POOB-90-1140';
5870:
5871: -- Parse the SELECT SQL
5872: BEGIN
5873: xProgress := 'POOB-90-1140';
5874: dbms_sql.parse(Detail_sel_c,cDetail_select,dbms_sql.native);
5875:
5876: EXCEPTION
5877: WHEN OTHERS THEN
5878: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_select);
5874: dbms_sql.parse(Detail_sel_c,cDetail_select,dbms_sql.native);
5875:
5876: EXCEPTION
5877: WHEN OTHERS THEN
5878: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_select);
5879: app_exception.raise_exception;
5880: END;
5881:
5882: -- Parse the DELETE1 SQL
5881:
5882: -- Parse the DELETE1 SQL
5883: BEGIN
5884: xProgress := 'POOB-90-1150';
5885: dbms_sql.parse(Detail_del_c1,cDetail_delete1,dbms_sql.native);
5886:
5887: EXCEPTION
5888: WHEN OTHERS THEN
5889: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete1);
5885: dbms_sql.parse(Detail_del_c1,cDetail_delete1,dbms_sql.native);
5886:
5887: EXCEPTION
5888: WHEN OTHERS THEN
5889: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete1);
5890: app_exception.raise_exception;
5891: END;
5892:
5893: -- Parse the DELETE2 SQL
5892:
5893: -- Parse the DELETE2 SQL
5894: BEGIN
5895: xProgress := 'POOB-90-1160';
5896: dbms_sql.parse(Detail_del_c2,cDetail_delete2,dbms_sql.native);
5897:
5898: EXCEPTION
5899: WHEN OTHERS THEN
5900: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete2);
5896: dbms_sql.parse(Detail_del_c2,cDetail_delete2,dbms_sql.native);
5897:
5898: EXCEPTION
5899: WHEN OTHERS THEN
5900: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete2);
5901: app_exception.raise_exception;
5902: END;
5903:
5904: --Set Counter
5914: ece_flatfile_pvt.G_MaxColWidth,
5915: l_detail_tbl);
5916:
5917: xProgress := 'POOB-90-1190';
5918: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5919:
5920: xProgress := 'POOB-90-1200';
5921: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5922:
5917: xProgress := 'POOB-90-1190';
5918: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5919:
5920: xProgress := 'POOB-90-1200';
5921: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5922:
5923: --Bind Variables
5924: xProgress := 'POOB-90-1201';
5925: dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5921: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5922:
5923: --Bind Variables
5924: xProgress := 'POOB-90-1201';
5925: dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5926:
5927: xProgress := 'POOB-90-1202';
5928: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5929:
5924: xProgress := 'POOB-90-1201';
5925: dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5926:
5927: xProgress := 'POOB-90-1202';
5928: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5929:
5930: xProgress := 'POOB-90-1202a';
5931: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5932:
5927: xProgress := 'POOB-90-1202';
5928: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5929:
5930: xProgress := 'POOB-90-1202a';
5931: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5932:
5933: xProgress := 'POOB-90-1203';
5934: dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5935:
5930: xProgress := 'POOB-90-1202a';
5931: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5932:
5933: xProgress := 'POOB-90-1203';
5934: dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5935:
5936: xProgress := 'POOB-90-1204';
5937: dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5938:
5933: xProgress := 'POOB-90-1203';
5934: dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5935:
5936: xProgress := 'POOB-90-1204';
5937: dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5938:
5939: xProgress := 'POOB-90-1205';
5940: dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5941:
5936: xProgress := 'POOB-90-1204';
5937: dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5938:
5939: xProgress := 'POOB-90-1205';
5940: dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5941:
5942: IF cPK2_Value IS NOT NULL THEN
5943: xProgress := 'POOB-90-1206';
5944: dbms_sql.bind_variable(Detail_sel_c,':cPK2_Value',cPK2_Value);
5940: dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5941:
5942: IF cPK2_Value IS NOT NULL THEN
5943: xProgress := 'POOB-90-1206';
5944: dbms_sql.bind_variable(Detail_sel_c,':cPK2_Value',cPK2_Value);
5945: END IF;
5946:
5947: IF cPK3_Value IS NOT NULL THEN
5948: xProgress := 'POOB-90-1207';
5945: END IF;
5946:
5947: IF cPK3_Value IS NOT NULL THEN
5948: xProgress := 'POOB-90-1207';
5949: dbms_sql.bind_variable(Detail_sel_c,':cPK3_Value',cPK3_Value);
5950: END IF;
5951:
5952: IF cPK4_Value IS NOT NULL THEN
5953: xProgress := 'POOB-90-1208';
5950: END IF;
5951:
5952: IF cPK4_Value IS NOT NULL THEN
5953: xProgress := 'POOB-90-1208';
5954: dbms_sql.bind_variable(Detail_sel_c,':cPK4_Value',cPK4_Value);
5955: END IF;
5956:
5957: IF cPK5_Value IS NOT NULL THEN
5958: xProgress := 'POOB-90-1209';
5955: END IF;
5956:
5957: IF cPK5_Value IS NOT NULL THEN
5958: xProgress := 'POOB-90-1209';
5959: dbms_sql.bind_variable(Detail_sel_c,':cPK5_Value',cPK5_Value);
5960: END IF;
5961:
5962: --Execute the Cursor
5963: xProgress := 'POOB-90-1210';
5960: END IF;
5961:
5962: --Execute the Cursor
5963: xProgress := 'POOB-90-1210';
5964: dummy := dbms_sql.execute(Detail_sel_c);
5965:
5966: xProgress := 'POOB-90-1220';
5967: WHILE dbms_sql.fetch_rows(Detail_sel_c) > 0 LOOP
5968: xProgress := 'POOB-90-1230';
5963: xProgress := 'POOB-90-1210';
5964: dummy := dbms_sql.execute(Detail_sel_c);
5965:
5966: xProgress := 'POOB-90-1220';
5967: WHILE dbms_sql.fetch_rows(Detail_sel_c) > 0 LOOP
5968: xProgress := 'POOB-90-1230';
5969: ece_flatfile_pvt.assign_column_value_to_tbl(Detail_sel_c,l_detail_tbl);
5970:
5971: xProgress := 'POOB-90-1240';
5968: xProgress := 'POOB-90-1230';
5969: ece_flatfile_pvt.assign_column_value_to_tbl(Detail_sel_c,l_detail_tbl);
5970:
5971: xProgress := 'POOB-90-1240';
5972: dbms_sql.column_value(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5973:
5974: xProgress := 'POOB-90-1250';
5975: dbms_sql.column_value(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5976:
5971: xProgress := 'POOB-90-1240';
5972: dbms_sql.column_value(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5973:
5974: xProgress := 'POOB-90-1250';
5975: dbms_sql.column_value(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5976:
5977: xProgress := 'POOB-90-1260';
5978: ece_poo_transaction.write_to_file(cTransaction_Type,
5979: cCommunication_Method,
5984: cFile_Common_Key,
5985: null);
5986:
5987: xProgress := 'POOB-90-1270';
5988: dbms_sql.bind_variable(Detail_del_c1,'col_rowid',rDetail_rowid);
5989:
5990: xProgress := 'POOB-90-1280';
5991: dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
5992:
5987: xProgress := 'POOB-90-1270';
5988: dbms_sql.bind_variable(Detail_del_c1,'col_rowid',rDetail_rowid);
5989:
5990: xProgress := 'POOB-90-1280';
5991: dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
5992:
5993: -- Execute the Cursor that deletes from the Interface Table
5994: xProgress := 'POOB-90-1290';
5995: dummy := dbms_sql.execute(Detail_del_c1);
5991: dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
5992:
5993: -- Execute the Cursor that deletes from the Interface Table
5994: xProgress := 'POOB-90-1290';
5995: dummy := dbms_sql.execute(Detail_del_c1);
5996:
5997: -- Execute the Cursor that deletes from the Extension Table
5998: xProgress := 'POOB-90-1300';
5999: dummy := dbms_sql.execute(Detail_del_c2);
5995: dummy := dbms_sql.execute(Detail_del_c1);
5996:
5997: -- Execute the Cursor that deletes from the Extension Table
5998: xProgress := 'POOB-90-1300';
5999: dummy := dbms_sql.execute(Detail_del_c2);
6000: END LOOP;
6001:
6002: -- Close the Cursors and Cleanup...
6003: xProgress := 'POOB-90-1310';
6000: END LOOP;
6001:
6002: -- Close the Cursors and Cleanup...
6003: xProgress := 'POOB-90-1310';
6004: dbms_sql.close_cursor(Detail_sel_c);
6005: dbms_sql.close_cursor(Detail_del_c1);
6006: dbms_sql.close_cursor(Detail_del_c2);
6007: if ec_debug.G_debug_level >= 2 then
6008: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6001:
6002: -- Close the Cursors and Cleanup...
6003: xProgress := 'POOB-90-1310';
6004: dbms_sql.close_cursor(Detail_sel_c);
6005: dbms_sql.close_cursor(Detail_del_c1);
6006: dbms_sql.close_cursor(Detail_del_c2);
6007: if ec_debug.G_debug_level >= 2 then
6008: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6009: end if;
6002: -- Close the Cursors and Cleanup...
6003: xProgress := 'POOB-90-1310';
6004: dbms_sql.close_cursor(Detail_sel_c);
6005: dbms_sql.close_cursor(Detail_del_c1);
6006: dbms_sql.close_cursor(Detail_del_c2);
6007: if ec_debug.G_debug_level >= 2 then
6008: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6009: end if;
6010: EXCEPTION