[Home] [Help]
704: -- :po_header_id is a place holder for foreign key value.
705: -- A PL/SQL table (list of values) will be used to store data.
706: -- Procedure ece_flatfile.Find_pos will be used to locate the specific
707: -- data value in the PL/SQL table.
708: -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
709: -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
710: --
711: -- Let's use the above example:
712: --
705: -- A PL/SQL table (list of values) will be used to store data.
706: -- Procedure ece_flatfile.Find_pos will be used to locate the specific
707: -- data value in the PL/SQL table.
708: -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
709: -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
710: --
711: -- Let's use the above example:
712: --
713: -- 1. Execute dynamic SQL 1 for headers (A) data
836: -- *** This tells the database to reserve spaces
837: -- *** for the data returned by the SQL statement
838: -- ***************************************************
839: xProgress := 'POOB-10-1200';
840: header_sel_c := dbms_sql.open_cursor;
841:
842: xProgress := 'POOB-10-1210';
843: line_sel_c := dbms_sql.open_cursor;
844:
839: xProgress := 'POOB-10-1200';
840: header_sel_c := dbms_sql.open_cursor;
841:
842: xProgress := 'POOB-10-1210';
843: line_sel_c := dbms_sql.open_cursor;
844:
845: xProgress := 'POOB-10-1220';
846: shipment_sel_c := dbms_sql.open_cursor;
847:
842: xProgress := 'POOB-10-1210';
843: line_sel_c := dbms_sql.open_cursor;
844:
845: xProgress := 'POOB-10-1220';
846: shipment_sel_c := dbms_sql.open_cursor;
847:
848: -- ***************************************************
849: -- Parse each of the SELECT statement
850: -- so the database understands the command
850: -- so the database understands the command
851: -- ***************************************************
852: xProgress := 'POOB-10-1230';
853: BEGIN
854: dbms_sql.parse(header_sel_c,
855: cHeader_select,
856: dbms_sql.native);
857:
858: EXCEPTION
852: xProgress := 'POOB-10-1230';
853: BEGIN
854: dbms_sql.parse(header_sel_c,
855: cHeader_select,
856: dbms_sql.native);
857:
858: EXCEPTION
859: WHEN OTHERS THEN
860: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
856: dbms_sql.native);
857:
858: EXCEPTION
859: WHEN OTHERS THEN
860: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
861: cHeader_select);
862: app_exception.raise_exception;
863: END;
864:
863: END;
864:
865: xProgress := 'POOB-10-1240';
866: BEGIN
867: dbms_sql.parse(line_sel_c,
868: cLine_select,
869: dbms_sql.native);
870:
871: EXCEPTION
865: xProgress := 'POOB-10-1240';
866: BEGIN
867: dbms_sql.parse(line_sel_c,
868: cLine_select,
869: dbms_sql.native);
870:
871: EXCEPTION
872: WHEN OTHERS THEN
873: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
869: dbms_sql.native);
870:
871: EXCEPTION
872: WHEN OTHERS THEN
873: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
874: cLine_select);
875: app_exception.raise_exception;
876: END;
877:
876: END;
877:
878: xProgress := 'POOB-10-1250';
879: BEGIN
880: dbms_sql.parse(shipment_sel_c,
881: cShipment_select,
882: dbms_sql.native);
883:
884: EXCEPTION
878: xProgress := 'POOB-10-1250';
879: BEGIN
880: dbms_sql.parse(shipment_sel_c,
881: cShipment_select,
882: dbms_sql.native);
883:
884: EXCEPTION
885: WHEN OTHERS THEN
886: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
882: dbms_sql.native);
883:
884: EXCEPTION
885: WHEN OTHERS THEN
886: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
887: cShipment_select);
888: app_exception.raise_exception;
889: END;
890:
1274: ece_flatfile_pvt.find_pos(l_shipment_tbl,c_shipment_key_name,n_ship_common_key_pos);
1275:
1276: xProgress := 'POOB-10-1407';
1277: -- bind variables
1278: dbms_sql.bind_variable(header_sel_c,'cComm_Method',cCommunication_Method);
1279: IF cCreate_Date_From IS NOT NULL THEN
1280: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1281: END IF;
1282:
1276: xProgress := 'POOB-10-1407';
1277: -- bind variables
1278: dbms_sql.bind_variable(header_sel_c,'cComm_Method',cCommunication_Method);
1279: IF cCreate_Date_From IS NOT NULL THEN
1280: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1281: END IF;
1282:
1283: IF cCreate_Date_To IS NOT NULL THEN
1284: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1280: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_From',cCreate_Date_From);
1281: END IF;
1282:
1283: IF cCreate_Date_To IS NOT NULL THEN
1284: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1285: END IF;
1286:
1287: IF cSupplier_Name IS NOT NULL THEN
1288: dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1284: dbms_sql.bind_variable(header_sel_c,'cCreate_Dt_To',cCreate_Date_To);
1285: END IF;
1286:
1287: IF cSupplier_Name IS NOT NULL THEN
1288: dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1289: END IF;
1290:
1291: IF cSupplier_Site IS NOT NULL THEN
1292: dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1288: dbms_sql.bind_variable(header_sel_c,'cSuppl_Name',cSupplier_Name);
1289: END IF;
1290:
1291: IF cSupplier_Site IS NOT NULL THEN
1292: dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1293: END IF;
1294:
1295: IF cDocument_Type IS NOT NULL THEN
1296: dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1292: dbms_sql.bind_variable(header_sel_c,'cSuppl_Site',cSupplier_Site);
1293: END IF;
1294:
1295: IF cDocument_Type IS NOT NULL THEN
1296: dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1297: END IF;
1298:
1299: IF cPO_Number_From IS NOT NULL THEN
1300: dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1296: dbms_sql.bind_variable(header_sel_c,'cDoc_Type',cDocument_Type);
1297: END IF;
1298:
1299: IF cPO_Number_From IS NOT NULL THEN
1300: dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1301: END IF;
1302:
1303: IF cPO_Number_To IS NOT NULL THEN
1304: dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1300: dbms_sql.bind_variable(header_sel_c,'cPO_Num_From',cPO_Number_From);
1301: END IF;
1302:
1303: IF cPO_Number_To IS NOT NULL THEN
1304: dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1305: END IF;
1306:
1307: -- dbms_sql.bind_variable(line_sel_c,'l_nOrganization_ID',nOrganization_ID);
1308:
1303: IF cPO_Number_To IS NOT NULL THEN
1304: dbms_sql.bind_variable(header_sel_c,'cPO_Num_To',cPO_Number_To);
1305: END IF;
1306:
1307: -- dbms_sql.bind_variable(line_sel_c,'l_nOrganization_ID',nOrganization_ID);
1308:
1309: iKey_count := 0;
1310: if ec_debug.G_debug_level = 3 then
1311: ec_debug.pl(3,'iKey_count: ',iKey_count);
1312: end if;
1313:
1314: -- EXECUTE the SELECT statement
1315: xProgress := 'POOB-10-1408';
1316: dummy := dbms_sql.execute(header_sel_c);
1317:
1318: -- ***************************************************
1319: -- The model is:
1320: -- HEADER - LINE - SHIPMENT ...
1323: -- to the HEADER. Then get all
1324: -- SHIPMENTS that belongs to the LINE.
1325: -- ***************************************************
1326: xProgress := 'POOB-10-1430';
1327: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP -- Header
1328:
1329: -- **************************************
1330: -- store internal values in pl/sql table
1331: -- **************************************
1586: -- ***************************************************
1587:
1588: -- use the following bind_variable feature as you see fit.
1589: xProgress := 'POOB-10-1510';
1590: dbms_sql.bind_variable(line_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1591:
1592: xProgress := 'POOB-10-1515';
1593: dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1594:
1589: xProgress := 'POOB-10-1510';
1590: dbms_sql.bind_variable(line_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1591:
1592: xProgress := 'POOB-10-1515';
1593: dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1594:
1595: xProgress := 'POOB-10-1520';
1596: dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1597:
1592: xProgress := 'POOB-10-1515';
1593: dbms_sql.bind_variable(line_sel_c,'por_release_num',l_header_tbl(nRelease_num_pos).value);
1594:
1595: xProgress := 'POOB-10-1520';
1596: dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1597:
1598: xProgress := 'POOB-10-1525';
1599: dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1600:
1595: xProgress := 'POOB-10-1520';
1596: dbms_sql.bind_variable(Shipment_sel_c,'po_header_id',l_header_tbl(nHeader_key_pos).value);
1597:
1598: xProgress := 'POOB-10-1525';
1599: dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1600:
1601: xProgress := 'POOB-10-1530';
1602: dummy := dbms_sql.execute(line_sel_c);
1603:
1598: xProgress := 'POOB-10-1525';
1599: dbms_sql.bind_variable(Shipment_sel_c,'por_release_id',l_header_tbl(nRelease_id_pos).value); --2823215
1600:
1601: xProgress := 'POOB-10-1530';
1602: dummy := dbms_sql.execute(line_sel_c);
1603:
1604: -- ***************************
1605: -- Line Level Loop Starts Here
1606: -- ***************************
1604: -- ***************************
1605: -- Line Level Loop Starts Here
1606: -- ***************************
1607: xProgress := 'POOB-10-1540';
1608: WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
1609:
1610: -- ****************************
1611: -- store values in pl/sql table
1612: -- ****************************
1899: -- set LINE_NUMBER values
1900: -- **********************
1901: -- Removed based on bug:3957851
1902: -- xProgress := 'POOB-10-1627';
1903: -- dbms_sql.bind_variable(shipment_sel_c,'shipment_number',l_line_tbl(nLine_num_pos).value);
1904:
1905: xProgress := 'POOB-10-1630';
1906: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1907:
1902: -- xProgress := 'POOB-10-1627';
1903: -- dbms_sql.bind_variable(shipment_sel_c,'shipment_number',l_line_tbl(nLine_num_pos).value);
1904:
1905: xProgress := 'POOB-10-1630';
1906: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1907:
1908: xProgress := 'POOB-10-1640';
1909: dummy := dbms_sql.execute(shipment_sel_c);
1910:
1905: xProgress := 'POOB-10-1630';
1906: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',l_line_tbl(nLine_key_pos).value);
1907:
1908: xProgress := 'POOB-10-1640';
1909: dummy := dbms_sql.execute(shipment_sel_c);
1910:
1911: -- *************************
1912: -- Shipment loop starts here
1913: -- *************************
1911: -- *************************
1912: -- Shipment loop starts here
1913: -- *************************
1914: xProgress := 'POOB-10-1650';
1915: WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipment
1916:
1917: -- ****************************
1918: -- store values in pl/sql table
1919: -- ****************************
2172:
2173: END LOOP; -- SHIPMENT Level Loop
2174:
2175: xProgress := 'POOB-10-1740';
2176: IF dbms_sql.last_row_count = 0 THEN
2177: v_LevelProcessed := 'SHIPMENT';
2178: ec_debug.pl(0,
2179: 'EC',
2180: 'ECE_NO_DB_ROW_PROCESSED',
2188:
2189: END LOOP; -- LINE Level Loop
2190:
2191: xProgress := 'POOB-10-1750';
2192: IF(dbms_sql.last_row_count = 0) THEN
2193: v_LevelProcessed := 'LINE';
2194: ec_debug.pl(0,
2195: 'EC',
2196: 'ECE_NO_DB_ROW_PROCESSED',
2206:
2207: END LOOP; -- HEADER Level Loop
2208:
2209: xProgress := 'POOB-10-1760';
2210: IF(dbms_sql.last_row_count = 0) THEN
2211: v_LevelProcessed := 'HEADER';
2212: ec_debug.pl(0,
2213: 'EC',
2214: 'ECE_NO_DB_ROW_PROCESSED',
2221: END IF;
2222:
2223: xProgress := 'POOB-10-1770';
2224: if (ece_poo_transaction.project_sel_c>0) then --Bug 2819176
2225: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c); --Bug 2490109
2226: end if;
2227: xProgress := 'POOB-10-1780';
2228: dbms_sql.close_cursor(shipment_sel_c);
2229:
2224: if (ece_poo_transaction.project_sel_c>0) then --Bug 2819176
2225: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c); --Bug 2490109
2226: end if;
2227: xProgress := 'POOB-10-1780';
2228: dbms_sql.close_cursor(shipment_sel_c);
2229:
2230: xProgress := 'POOB-10-1790';
2231: dbms_sql.close_cursor(line_sel_c);
2232:
2227: xProgress := 'POOB-10-1780';
2228: dbms_sql.close_cursor(shipment_sel_c);
2229:
2230: xProgress := 'POOB-10-1790';
2231: dbms_sql.close_cursor(line_sel_c);
2232:
2233: xProgress := 'POOB-10-1800';
2234: dbms_sql.close_cursor(header_sel_c);
2235: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_POO_TRX');
2230: xProgress := 'POOB-10-1790';
2231: dbms_sql.close_cursor(line_sel_c);
2232:
2233: xProgress := 'POOB-10-1800';
2234: dbms_sql.close_cursor(header_sel_c);
2235: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_POO_TRX');
2236:
2237: EXCEPTION
2238: WHEN OTHERS THEN
2617: -- *** This tells the database to reserve spaces
2618: -- *** for the data returned by the SQL statement
2619: -- ***************************************************
2620: xProgress := 'POOB-20-1080';
2621: Header_sel_c := dbms_sql.open_cursor;
2622:
2623: xProgress := 'POOB-20-1090';
2624: line_sel_c := dbms_sql.open_cursor;
2625:
2620: xProgress := 'POOB-20-1080';
2621: Header_sel_c := dbms_sql.open_cursor;
2622:
2623: xProgress := 'POOB-20-1090';
2624: line_sel_c := dbms_sql.open_cursor;
2625:
2626: xProgress := 'POOB-20-1100';
2627: Shipment_sel_c := dbms_sql.open_cursor;
2628:
2623: xProgress := 'POOB-20-1090';
2624: line_sel_c := dbms_sql.open_cursor;
2625:
2626: xProgress := 'POOB-20-1100';
2627: Shipment_sel_c := dbms_sql.open_cursor;
2628:
2629: xProgress := 'POOB-20-1110';
2630: Header_del_c1 := dbms_sql.open_cursor;
2631:
2626: xProgress := 'POOB-20-1100';
2627: Shipment_sel_c := dbms_sql.open_cursor;
2628:
2629: xProgress := 'POOB-20-1110';
2630: Header_del_c1 := dbms_sql.open_cursor;
2631:
2632: xProgress := 'POOB-20-1120';
2633: Line_del_c1 := dbms_sql.open_cursor;
2634:
2629: xProgress := 'POOB-20-1110';
2630: Header_del_c1 := dbms_sql.open_cursor;
2631:
2632: xProgress := 'POOB-20-1120';
2633: Line_del_c1 := dbms_sql.open_cursor;
2634:
2635: xProgress := 'POOB-20-1130';
2636: Shipment_del_c1 := dbms_sql.open_cursor;
2637:
2632: xProgress := 'POOB-20-1120';
2633: Line_del_c1 := dbms_sql.open_cursor;
2634:
2635: xProgress := 'POOB-20-1130';
2636: Shipment_del_c1 := dbms_sql.open_cursor;
2637:
2638: xProgress := 'POOB-20-1140';
2639: Header_del_c2 := dbms_sql.open_cursor;
2640:
2635: xProgress := 'POOB-20-1130';
2636: Shipment_del_c1 := dbms_sql.open_cursor;
2637:
2638: xProgress := 'POOB-20-1140';
2639: Header_del_c2 := dbms_sql.open_cursor;
2640:
2641: xProgress := 'POOB-20-1150';
2642: Line_del_c2 := dbms_sql.open_cursor;
2643:
2638: xProgress := 'POOB-20-1140';
2639: Header_del_c2 := dbms_sql.open_cursor;
2640:
2641: xProgress := 'POOB-20-1150';
2642: Line_del_c2 := dbms_sql.open_cursor;
2643:
2644: xProgress := 'POOB-20-1160';
2645: Shipment_del_c2 := dbms_sql.open_cursor;
2646:
2641: xProgress := 'POOB-20-1150';
2642: Line_del_c2 := dbms_sql.open_cursor;
2643:
2644: xProgress := 'POOB-20-1160';
2645: Shipment_del_c2 := dbms_sql.open_cursor;
2646:
2647: -- *****************************************
2648: -- Parse each of the SELECT statement
2649: -- so the database understands the command
2649: -- so the database understands the command
2650: -- *****************************************
2651: xProgress := 'POOB-20-1170';
2652: BEGIN
2653: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
2654:
2655: EXCEPTION
2656: WHEN OTHERS THEN
2657: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
2653: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
2654:
2655: EXCEPTION
2656: WHEN OTHERS THEN
2657: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
2658: app_exception.raise_exception;
2659:
2660: END;
2661:
2660: END;
2661:
2662: xProgress := 'POOB-20-1180';
2663: BEGIN
2664: dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
2665:
2666: EXCEPTION
2667: WHEN OTHERS THEN
2668: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
2664: dbms_sql.parse(line_sel_c,cLine_select,dbms_sql.native);
2665:
2666: EXCEPTION
2667: WHEN OTHERS THEN
2668: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_select);
2669: app_exception.raise_exception;
2670:
2671: END;
2672:
2671: END;
2672:
2673: xProgress := 'POOB-20-1190';
2674: BEGIN
2675: dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
2676:
2677: EXCEPTION
2678: WHEN OTHERS THEN
2679: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
2675: dbms_sql.parse(shipment_sel_c,cShipment_select,dbms_sql.native);
2676:
2677: EXCEPTION
2678: WHEN OTHERS THEN
2679: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_select);
2680: app_exception.raise_exception;
2681:
2682: END;
2683:
2682: END;
2683:
2684: xProgress := 'POOB-20-1200';
2685: BEGIN
2686: dbms_sql.parse(header_del_c1,cHeader_delete1,dbms_sql.native);
2687:
2688: EXCEPTION
2689: WHEN OTHERS THEN
2690: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
2686: dbms_sql.parse(header_del_c1,cHeader_delete1,dbms_sql.native);
2687:
2688: EXCEPTION
2689: WHEN OTHERS THEN
2690: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
2691: app_exception.raise_exception;
2692:
2693: END;
2694:
2693: END;
2694:
2695: xProgress := 'POOB-20-1210';
2696: BEGIN
2697: dbms_sql.parse(line_del_c1,cLine_delete1,dbms_sql.native);
2698:
2699: EXCEPTION
2700: WHEN OTHERS THEN
2701: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
2697: dbms_sql.parse(line_del_c1,cLine_delete1,dbms_sql.native);
2698:
2699: EXCEPTION
2700: WHEN OTHERS THEN
2701: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete1);
2702: app_exception.raise_exception;
2703:
2704: END;
2705:
2704: END;
2705:
2706: xProgress := 'POOB-20-1220';
2707: BEGIN
2708: dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
2709:
2710: EXCEPTION
2711: WHEN OTHERS THEN
2712: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
2708: dbms_sql.parse(shipment_del_c1,cShipment_delete1,dbms_sql.native);
2709:
2710: EXCEPTION
2711: WHEN OTHERS THEN
2712: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete1);
2713: app_exception.raise_exception;
2714:
2715: END;
2716:
2715: END;
2716:
2717: xProgress := 'POOB-20-1230';
2718: BEGIN
2719: dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
2720:
2721: EXCEPTION
2722: WHEN OTHERS THEN
2723: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
2719: dbms_sql.parse(header_del_c2,cHeader_delete2,dbms_sql.native);
2720:
2721: EXCEPTION
2722: WHEN OTHERS THEN
2723: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
2724: app_exception.raise_exception;
2725:
2726: END;
2727:
2726: END;
2727:
2728: xProgress := 'POOB-20-1240';
2729: BEGIN
2730: dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
2731:
2732: EXCEPTION
2733: WHEN OTHERS THEN
2734: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
2730: dbms_sql.parse(line_del_c2,cLine_delete2,dbms_sql.native);
2731:
2732: EXCEPTION
2733: WHEN OTHERS THEN
2734: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cLine_delete2);
2735: app_exception.raise_exception;
2736:
2737: END;
2738:
2737: END;
2738:
2739: xProgress := 'POOB-20-1250';
2740: BEGIN
2741: dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
2742:
2743: EXCEPTION
2744: WHEN OTHERS THEN
2745: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
2741: dbms_sql.parse(shipment_del_c2,cShipment_delete2,dbms_sql.native);
2742:
2743: EXCEPTION
2744: WHEN OTHERS THEN
2745: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cShipment_delete2);
2746: app_exception.raise_exception;
2747:
2748: END;
2749:
2778: -- ***************************************************
2779: -- Need rowid for delete (Header Level)
2780: -- ***************************************************
2781: xProgress := 'POOB-20-1280';
2782: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
2783:
2784: xProgress := 'POOB-20-1290';
2785: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2786:
2781: xProgress := 'POOB-20-1280';
2782: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
2783:
2784: xProgress := 'POOB-20-1290';
2785: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2786:
2787: xProgress := 'POOB-20-1300';
2788: dbms_sql.define_column(Header_sel_c,iHeader_count + 3,n_po_header_ID);
2789:
2784: xProgress := 'POOB-20-1290';
2785: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2786:
2787: xProgress := 'POOB-20-1300';
2788: dbms_sql.define_column(Header_sel_c,iHeader_count + 3,n_po_header_ID);
2789:
2790: xProgress := 'POOB-20-1310';
2791: ece_flatfile_pvt.define_interface_column_type(line_sel_c,cLine_select,ece_flatfile_pvt.G_MaxColWidth,l_line_tbl);
2792:
2793: -- ***************************************************
2794: -- Need rowid for delete (Line Level)
2795: -- ***************************************************
2796: xProgress := 'POOB-20-1320';
2797: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 1,rLine_rowid);
2798:
2799: xProgress := 'POOB-20-1330';
2800: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2801:
2796: xProgress := 'POOB-20-1320';
2797: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 1,rLine_rowid);
2798:
2799: xProgress := 'POOB-20-1330';
2800: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2801:
2802: xProgress := 'POOB-20-1340';
2803: dbms_sql.define_column(line_sel_c,iLine_count + 3,n_po_line_ID);
2804:
2799: xProgress := 'POOB-20-1330';
2800: dbms_sql.define_column_rowid(line_sel_c,iLine_count + 2,rLine_X_rowid);
2801:
2802: xProgress := 'POOB-20-1340';
2803: dbms_sql.define_column(line_sel_c,iLine_count + 3,n_po_line_ID);
2804:
2805: xProgress := 'POOB-20-1350';
2806: ece_flatfile_pvt.define_interface_column_type(Shipment_sel_c,cShipment_select,ece_flatfile_pvt.G_MaxColWidth,l_shipment_tbl);
2807:
2808: -- ***************************************************
2809: -- Need rowid for delete (Shipment Level)
2810: -- ***************************************************
2811: xProgress := 'POOB-20-1360';
2812: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 1,rShipment_rowid);
2813:
2814: xProgress := 'POOB-20-1370';
2815: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
2816:
2811: xProgress := 'POOB-20-1360';
2812: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 1,rShipment_rowid);
2813:
2814: xProgress := 'POOB-20-1370';
2815: dbms_sql.define_column_rowid(Shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
2816:
2817: -- ************************************************************
2818: -- *** The following is custom tailored for this transaction
2819: -- *** It find the values and use them in the WHERE clause to
2823: -- To complete the Line SELECT statement,
2824: -- we will need values for the join condition.
2825: -- *******************************************
2826: xProgress := 'POOB-20-1375';
2827: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2828: dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2829: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2830:
2831: --- EXECUTE the SELECT statement
2824: -- we will need values for the join condition.
2825: -- *******************************************
2826: xProgress := 'POOB-20-1375';
2827: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2828: dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2829: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2830:
2831: --- EXECUTE the SELECT statement
2832: xProgress := 'POOB-20-1380';
2825: -- *******************************************
2826: xProgress := 'POOB-20-1375';
2827: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
2828: dbms_sql.bind_variable(line_sel_c,'Run_id',iRun_id);
2829: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2830:
2831: --- EXECUTE the SELECT statement
2832: xProgress := 'POOB-20-1380';
2833: dummy := dbms_sql.execute(Header_sel_c);
2829: dbms_sql.bind_variable(shipment_sel_c,'Run_id',iRun_id);
2830:
2831: --- EXECUTE the SELECT statement
2832: xProgress := 'POOB-20-1380';
2833: dummy := dbms_sql.execute(Header_sel_c);
2834:
2835: -- ********************************************************************
2836: -- *** With data for each HEADER line, populate the ECE_OUTPUT table
2837: -- *** then populate ECE_OUTPUT with data from all LINES that belongs
2840: -- ********************************************************************
2841:
2842: -- HEADER - LINE - SHIPMENT ...
2843: xProgress := 'POOB-20-1390';
2844: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP -- Header
2845: -- ******************************
2846: -- store values in pl/sql table
2847: -- ******************************
2848: xProgress := 'POOB-20-1400';
2848: xProgress := 'POOB-20-1400';
2849: ece_flatfile_pvt.assign_column_value_to_tbl(header_sel_c,l_header_tbl);
2850:
2851: xProgress := 'POOB-20-1410';
2852: dbms_sql.column_value(header_sel_c,iHeader_count + 1,rHeader_rowid);
2853:
2854: xProgress := 'POOB-20-1420';
2855: dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2856:
2851: xProgress := 'POOB-20-1410';
2852: dbms_sql.column_value(header_sel_c,iHeader_count + 1,rHeader_rowid);
2853:
2854: xProgress := 'POOB-20-1420';
2855: dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2856:
2857: xProgress := 'POOB-20-1430';
2858: dbms_sql.column_value(header_sel_c,iHeader_count + 3,n_po_header_id);
2859:
2854: xProgress := 'POOB-20-1420';
2855: dbms_sql.column_value(header_sel_c,iHeader_count + 2,rHeader_X_rowid);
2856:
2857: xProgress := 'POOB-20-1430';
2858: dbms_sql.column_value(header_sel_c,iHeader_count + 3,n_po_header_id);
2859:
2860: xProgress := 'POOB-20-1440';
2861: nRelease_num := l_header_tbl(nRelease_num_pos).value;
2862:
2955: -- ******************************************
2956: -- set values into binding variables
2957: -- ******************************************
2958: xProgress := 'POOB-20-1490';
2959: dbms_sql.bind_variable(line_sel_c,'po_header_id',n_po_header_ID);
2960:
2961: xProgress := 'POOB-20-1500';
2962: dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2963:
2958: xProgress := 'POOB-20-1490';
2959: dbms_sql.bind_variable(line_sel_c,'po_header_id',n_po_header_ID);
2960:
2961: xProgress := 'POOB-20-1500';
2962: dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2963:
2964: xProgress := 'POOB-20-1505';
2965: dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2966:
2961: xProgress := 'POOB-20-1500';
2962: dbms_sql.bind_variable(shipment_sel_c,'po_header_id',n_po_header_ID);
2963:
2964: xProgress := 'POOB-20-1505';
2965: dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2966:
2967: xProgress := 'POOB-20-1506';
2968: dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2969:
2964: xProgress := 'POOB-20-1505';
2965: dbms_sql.bind_variable(line_sel_c,'por_release_num',nRelease_num);
2966:
2967: xProgress := 'POOB-20-1506';
2968: dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2969:
2970: xProgress := 'POOB-20-1510';
2971: dummy := dbms_sql.execute(line_sel_c);
2972:
2967: xProgress := 'POOB-20-1506';
2968: dbms_sql.bind_variable(shipment_sel_c,'por_release_num',nRelease_num);
2969:
2970: xProgress := 'POOB-20-1510';
2971: dummy := dbms_sql.execute(line_sel_c);
2972:
2973: -- ***************************************************
2974: -- line loop starts here
2975: -- ***************************************************
2973: -- ***************************************************
2974: -- line loop starts here
2975: -- ***************************************************
2976: xProgress := 'POOB-20-1520';
2977: WHILE dbms_sql.fetch_rows(line_sel_c) > 0 LOOP --- Line
2978:
2979: -- ***************************************************
2980: -- store values in pl/sql table
2981: -- ***************************************************
2982: xProgress := 'POOB-20-1530';
2983: ece_flatfile_pvt.assign_column_value_to_tbl(line_sel_c,l_line_tbl);
2984:
2985: xProgress := 'POOB-20-1533';
2986: dbms_sql.column_value(line_sel_c,iLine_count + 1,rLine_rowid);
2987:
2988: xProgress := 'POOB-20-1535';
2989: dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2990:
2985: xProgress := 'POOB-20-1533';
2986: dbms_sql.column_value(line_sel_c,iLine_count + 1,rLine_rowid);
2987:
2988: xProgress := 'POOB-20-1535';
2989: dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2990:
2991: xProgress := 'POOB-20-1537';
2992: dbms_sql.column_value(line_sel_c,iLine_count + 3,n_po_line_id);
2993:
2988: xProgress := 'POOB-20-1535';
2989: dbms_sql.column_value(line_sel_c,iLine_count + 2,rLine_X_rowid);
2990:
2991: xProgress := 'POOB-20-1537';
2992: dbms_sql.column_value(line_sel_c,iLine_count + 3,n_po_line_id);
2993:
2994: xProgress := 'POOB-20-1540';
2995: nLine_num := l_line_tbl(nLine_num_pos).value;
2996:
3129: -- **************************
3130: -- set LINE_NUMBER values
3131: -- **************************
3132: xProgress := 'POOB-20-1560';
3133: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',n_po_line_ID);
3134:
3135: xProgress := 'POOB-20-1575';
3136: dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3137:
3132: xProgress := 'POOB-20-1560';
3133: dbms_sql.bind_variable(shipment_sel_c,'po_line_id',n_po_line_ID);
3134:
3135: xProgress := 'POOB-20-1575';
3136: dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3137:
3138: xProgress := 'POOB-20-1580';
3139: dummy := dbms_sql.execute(shipment_sel_c);
3140:
3135: xProgress := 'POOB-20-1575';
3136: dbms_sql.bind_variable(shipment_sel_c,'shipment_number',nLine_num);
3137:
3138: xProgress := 'POOB-20-1580';
3139: dummy := dbms_sql.execute(shipment_sel_c);
3140:
3141: -- ****************************
3142: -- Shipment loop starts here
3143: -- ****************************
3141: -- ****************************
3142: -- Shipment loop starts here
3143: -- ****************************
3144: xProgress := 'POCOB-10-1590';
3145: WHILE dbms_sql.fetch_rows(shipment_sel_c) > 0 LOOP --- Shipments
3146:
3147: -- *********************************
3148: -- store values in pl/sql table
3149: -- *********************************
3150: xProgress := 'POCOB-10-1600';
3151: ece_flatfile_pvt.assign_column_value_to_tbl(Shipment_sel_c,l_shipment_tbl);
3152:
3153: xProgress := 'POCOB-10-1603';
3154: dbms_sql.column_value(shipment_sel_c,iShipment_count + 1,rShipment_rowid);
3155:
3156: xProgress := 'POCOB-10-1606';
3157: dbms_sql.column_value(shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
3158:
3153: xProgress := 'POCOB-10-1603';
3154: dbms_sql.column_value(shipment_sel_c,iShipment_count + 1,rShipment_rowid);
3155:
3156: xProgress := 'POCOB-10-1606';
3157: dbms_sql.column_value(shipment_sel_c,iShipment_count + 2,rShipment_X_rowid);
3158:
3159: xProgress := 'POCOB-10-1610';
3160: nLine_Location_ID := l_shipment_tbl(nLine_Location_ID_pos).value;
3161: if ec_debug.G_debug_level = 3 then
3219: c_file_common_key);
3220: -- END IF; --bug 1891291
3221:
3222: xProgress := 'POOB-20-1640';
3223: dbms_sql.bind_variable(shipment_del_c1,'col_rowid',rShipment_rowid);
3224:
3225: xProgress := 'POOB-20-1650';
3226: dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3227:
3222: xProgress := 'POOB-20-1640';
3223: dbms_sql.bind_variable(shipment_del_c1,'col_rowid',rShipment_rowid);
3224:
3225: xProgress := 'POOB-20-1650';
3226: dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3227:
3228: xProgress := 'POOB-20-1660';
3229: dummy := dbms_sql.execute(shipment_del_c1);
3230:
3225: xProgress := 'POOB-20-1650';
3226: dbms_sql.bind_variable(shipment_del_c2,'col_rowid',rShipment_X_rowid);
3227:
3228: xProgress := 'POOB-20-1660';
3229: dummy := dbms_sql.execute(shipment_del_c1);
3230:
3231: xProgress := 'POOB-20-1670';
3232: dummy := dbms_sql.execute(shipment_del_c2);
3233:
3228: xProgress := 'POOB-20-1660';
3229: dummy := dbms_sql.execute(shipment_del_c1);
3230:
3231: xProgress := 'POOB-20-1670';
3232: dummy := dbms_sql.execute(shipment_del_c2);
3233:
3234: END LOOP; -- Shipment Level
3235:
3236: xProgress := 'POOB-20-1674';
3233:
3234: END LOOP; -- Shipment Level
3235:
3236: xProgress := 'POOB-20-1674';
3237: IF dbms_sql.last_row_count = 0 THEN
3238: v_LevelProcessed := 'SHIPMENT';
3239: ec_debug.pl(0,
3240: 'EC',
3241: 'ECE_NO_DB_ROW_PROCESSED',
3250: -- *********************
3251: -- Use rowid for delete
3252: -- *********************
3253: xProgress := 'POOB-20-1680';
3254: dbms_sql.bind_variable(line_del_c1,'col_rowid',rLine_rowid);
3255:
3256: xProgress := 'POOB-20-1690';
3257: dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3258:
3253: xProgress := 'POOB-20-1680';
3254: dbms_sql.bind_variable(line_del_c1,'col_rowid',rLine_rowid);
3255:
3256: xProgress := 'POOB-20-1690';
3257: dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3258:
3259: xProgress := 'POOB-20-1700';
3260: dummy := dbms_sql.execute(line_del_c1);
3261:
3256: xProgress := 'POOB-20-1690';
3257: dbms_sql.bind_variable(line_del_c2,'col_rowid',rLine_X_rowid);
3258:
3259: xProgress := 'POOB-20-1700';
3260: dummy := dbms_sql.execute(line_del_c1);
3261:
3262: xProgress := 'POOB-20-1710';
3263: dummy := dbms_sql.execute(line_del_c2);
3264:
3259: xProgress := 'POOB-20-1700';
3260: dummy := dbms_sql.execute(line_del_c1);
3261:
3262: xProgress := 'POOB-20-1710';
3263: dummy := dbms_sql.execute(line_del_c2);
3264:
3265: END LOOP; -- Line Level
3266:
3267: xProgress := 'POOB-20-1714';
3264:
3265: END LOOP; -- Line Level
3266:
3267: xProgress := 'POOB-20-1714';
3268: IF dbms_sql.last_row_count = 0 THEN
3269: v_LevelProcessed := 'LINE';
3270: ec_debug.pl(0,
3271: 'EC',
3272: 'ECE_NO_DB_ROW_PROCESSED',
3278: cTransaction_Type);
3279: END IF;
3280:
3281: xProgress := 'POOB-20-1720';
3282: dbms_sql.bind_variable(header_del_c1,'col_rowid',rHeader_rowid);
3283:
3284: xProgress := 'POOB-20-1730';
3285: dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3286:
3281: xProgress := 'POOB-20-1720';
3282: dbms_sql.bind_variable(header_del_c1,'col_rowid',rHeader_rowid);
3283:
3284: xProgress := 'POOB-20-1730';
3285: dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3286:
3287: xProgress := 'POOB-20-1740';
3288: dummy := dbms_sql.execute(header_del_c1);
3289:
3284: xProgress := 'POOB-20-1730';
3285: dbms_sql.bind_variable(header_del_c2,'col_rowid',rHeader_X_rowid);
3286:
3287: xProgress := 'POOB-20-1740';
3288: dummy := dbms_sql.execute(header_del_c1);
3289:
3290: xProgress := 'POOB-20-1750';
3291: dummy := dbms_sql.execute(header_del_c2);
3292:
3287: xProgress := 'POOB-20-1740';
3288: dummy := dbms_sql.execute(header_del_c1);
3289:
3290: xProgress := 'POOB-20-1750';
3291: dummy := dbms_sql.execute(header_del_c2);
3292:
3293: END LOOP; -- Header Level
3294:
3295: xProgress := 'POOB-20-1754';
3292:
3293: END LOOP; -- Header Level
3294:
3295: xProgress := 'POOB-20-1754';
3296: IF dbms_sql.last_row_count = 0 THEN
3297: v_LevelProcessed := 'HEADER';
3298: ec_debug.pl(0,
3299: 'EC',
3300: 'ECE_NO_DB_ROW_PROCESSED',
3306: cTransaction_Type);
3307: END IF;
3308:
3309: xProgress := 'POOB-20-1760';
3310: dbms_sql.close_cursor(header_sel_c);
3311:
3312: xProgress := 'POOB-20-1770';
3313: dbms_sql.close_cursor(line_sel_c);
3314:
3309: xProgress := 'POOB-20-1760';
3310: dbms_sql.close_cursor(header_sel_c);
3311:
3312: xProgress := 'POOB-20-1770';
3313: dbms_sql.close_cursor(line_sel_c);
3314:
3315: xProgress := 'POOB-20-1780';
3316: dbms_sql.close_cursor(shipment_sel_c);
3317:
3312: xProgress := 'POOB-20-1770';
3313: dbms_sql.close_cursor(line_sel_c);
3314:
3315: xProgress := 'POOB-20-1780';
3316: dbms_sql.close_cursor(shipment_sel_c);
3317:
3318: xProgress := 'POOB-20-1790';
3319: dbms_sql.close_cursor(header_del_c1);
3320:
3315: xProgress := 'POOB-20-1780';
3316: dbms_sql.close_cursor(shipment_sel_c);
3317:
3318: xProgress := 'POOB-20-1790';
3319: dbms_sql.close_cursor(header_del_c1);
3320:
3321: xProgress := 'POOB-20-1800';
3322: dbms_sql.close_cursor(line_del_c1);
3323:
3318: xProgress := 'POOB-20-1790';
3319: dbms_sql.close_cursor(header_del_c1);
3320:
3321: xProgress := 'POOB-20-1800';
3322: dbms_sql.close_cursor(line_del_c1);
3323:
3324: xProgress := 'POOB-20-1812';
3325: dbms_sql.close_cursor(shipment_del_c1);
3326:
3321: xProgress := 'POOB-20-1800';
3322: dbms_sql.close_cursor(line_del_c1);
3323:
3324: xProgress := 'POOB-20-1812';
3325: dbms_sql.close_cursor(shipment_del_c1);
3326:
3327: xProgress := 'POOB-20-1814';
3328: dbms_sql.close_cursor(header_del_c2);
3329:
3324: xProgress := 'POOB-20-1812';
3325: dbms_sql.close_cursor(shipment_del_c1);
3326:
3327: xProgress := 'POOB-20-1814';
3328: dbms_sql.close_cursor(header_del_c2);
3329:
3330: xProgress := 'POOB-20-1816';
3331: dbms_sql.close_cursor(line_del_c2);
3332:
3327: xProgress := 'POOB-20-1814';
3328: dbms_sql.close_cursor(header_del_c2);
3329:
3330: xProgress := 'POOB-20-1816';
3331: dbms_sql.close_cursor(line_del_c2);
3332:
3333: xProgress := 'POOB-20-1818';
3334: dbms_sql.close_cursor(shipment_del_c2);
3335:
3330: xProgress := 'POOB-20-1816';
3331: dbms_sql.close_cursor(line_del_c2);
3332:
3333: xProgress := 'POOB-20-1818';
3334: dbms_sql.close_cursor(shipment_del_c2);
3335:
3336: -- Bug 2490109 Closing the distribution cursors.
3337: xProgress := 'POOB-50-1819';
3338: if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3335:
3336: -- Bug 2490109 Closing the distribution cursors.
3337: xProgress := 'POOB-50-1819';
3338: if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3339: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c);
3340:
3341: xProgress := 'POOB-50-1820';
3342: dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3343:
3338: if(ece_poo_transaction.project_sel_c>0) then --Bug 2819176
3339: dbms_sql.close_cursor(ece_poo_transaction.project_sel_c);
3340:
3341: xProgress := 'POOB-50-1820';
3342: dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3343:
3344: xProgress := 'POOB-50-1821';
3345: dbms_sql.close_cursor(ece_poo_transaction.project_del_c2);
3346: end if;
3341: xProgress := 'POOB-50-1820';
3342: dbms_sql.close_cursor(ece_poo_transaction.project_del_c1);
3343:
3344: xProgress := 'POOB-50-1821';
3345: dbms_sql.close_cursor(ece_poo_transaction.project_del_c2);
3346: end if;
3347:
3348: xProgress := 'POOB-20-1820';
3349: if ec_debug.G_debug_level >= 2 then
3684: end if;
3685:
3686: -- Open the Cursor
3687: xProgress := 'POOB-40-1050';
3688: project_sel_c := dbms_sql.open_cursor;
3689:
3690: -- Parse the Cursor
3691: xProgress := 'POOB-40-1060';
3692: BEGIN
3689:
3690: -- Parse the Cursor
3691: xProgress := 'POOB-40-1060';
3692: BEGIN
3693: dbms_sql.parse(project_sel_c,
3694: cProject_select,
3695: dbms_sql.native);
3696:
3697: EXCEPTION
3691: xProgress := 'POOB-40-1060';
3692: BEGIN
3693: dbms_sql.parse(project_sel_c,
3694: cProject_select,
3695: dbms_sql.native);
3696:
3697: EXCEPTION
3698: WHEN OTHERS THEN
3699: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
3695: dbms_sql.native);
3696:
3697: EXCEPTION
3698: WHEN OTHERS THEN
3699: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
3700: app_exception.raise_exception;
3701: END;
3702:
3703: -- Set Counter
3731:
3732: if ece_poo_transaction.project_sel_c >0 then --Bug 2490109
3733: -- Bind Variables
3734: xProgress := 'POOB-40-1140';
3735: dbms_sql.bind_variable(project_sel_c,':po_header_id',NVL(cPO_Header_ID,0));
3736:
3737: xProgress := 'POOB-40-1150';
3738: dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3739:
3734: xProgress := 'POOB-40-1140';
3735: dbms_sql.bind_variable(project_sel_c,':po_header_id',NVL(cPO_Header_ID,0));
3736:
3737: xProgress := 'POOB-40-1150';
3738: dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3739:
3740: xProgress := 'POOB-40-1160';
3741: dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3742:
3737: xProgress := 'POOB-40-1150';
3738: dbms_sql.bind_variable(project_sel_c,':po_release_id',NVL(cPO_Release_ID,0));
3739:
3740: xProgress := 'POOB-40-1160';
3741: dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3742:
3743: xProgress := 'POOB-40-1170';
3744: dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3745:
3740: xProgress := 'POOB-40-1160';
3741: dbms_sql.bind_variable(project_sel_c,':po_line_id',NVL(cPO_Line_ID,0));
3742:
3743: xProgress := 'POOB-40-1170';
3744: dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3745:
3746: -- Execute the Cursor
3747: xProgress := 'POOB-40-1180';
3748: dummy := dbms_sql.execute(project_sel_c);
3744: dbms_sql.bind_variable(project_sel_c,':line_location_id',NVL(cPO_Line_Location_ID,0));
3745:
3746: -- Execute the Cursor
3747: xProgress := 'POOB-40-1180';
3748: dummy := dbms_sql.execute(project_sel_c);
3749:
3750: -- Fetch Data
3751: xProgress := 'POOB-40-1190';
3752: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
3748: dummy := dbms_sql.execute(project_sel_c);
3749:
3750: -- Fetch Data
3751: xProgress := 'POOB-40-1190';
3752: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
3753: -- Store Internal Values in the PL/SQL Table
3754: xProgress := 'POOB-40-1200';
3755: ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,iKey_count,l_project_tbl,l_key_tbl);
3756:
3890: END LOOP;
3891:
3892: -- Check to see if anything was processed
3893: xProgress := 'POOB-40-1250';
3894: IF(dbms_sql.last_row_count = 0) THEN
3895: v_LevelProcessed := 'PROJECT';
3896: ec_debug.pl(0,'EC','ECE_NO_DB_ROW_PROCESSED','LEVEL_PROCESSED',v_LevelProcessed,'PROGRESS_LEVEL',xProgress,'TRANSACTION_TYPE',cTransaction_Type);
3897: END IF;
3898:
3899: end if; --Bug 2490109
3900:
3901: -- Close the Cursor Bug 2490109
3902: -- xProgress := 'POOB-40-1260';
3903: -- dbms_sql.close_cursor(project_sel_c);
3904:
3905: -- Tell Debug that this Procedure is Done
3906: if ec_debug.G_debug_level = 3 then
3907: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_PROJECT_INFO');
4039: end if;
4040:
4041: -- Open the Cursors
4042: xProgress := 'POOB-50-1020';
4043: project_sel_c := dbms_sql.open_cursor;
4044:
4045: xProgress := 'POOB-50-1030';
4046: project_del_c1 := dbms_sql.open_cursor;
4047:
4042: xProgress := 'POOB-50-1020';
4043: project_sel_c := dbms_sql.open_cursor;
4044:
4045: xProgress := 'POOB-50-1030';
4046: project_del_c1 := dbms_sql.open_cursor;
4047:
4048: xProgress := 'POOB-50-1040';
4049: project_del_c2 := dbms_sql.open_cursor;
4050:
4045: xProgress := 'POOB-50-1030';
4046: project_del_c1 := dbms_sql.open_cursor;
4047:
4048: xProgress := 'POOB-50-1040';
4049: project_del_c2 := dbms_sql.open_cursor;
4050:
4051: -- Parse the SQL Statements
4052: xProgress := 'POOB-50-1050';
4053: BEGIN
4051: -- Parse the SQL Statements
4052: xProgress := 'POOB-50-1050';
4053: BEGIN
4054: xProgress := 'POOB-50-1060';
4055: dbms_sql.parse(project_sel_c,cProject_select,dbms_sql.native);
4056:
4057: EXCEPTION
4058: WHEN OTHERS THEN
4059: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
4055: dbms_sql.parse(project_sel_c,cProject_select,dbms_sql.native);
4056:
4057: EXCEPTION
4058: WHEN OTHERS THEN
4059: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_select);
4060: app_exception.raise_exception;
4061:
4062: END;
4063:
4063:
4064: xProgress := 'POOB-50-1070';
4065: BEGIN
4066: xProgress := 'POOB-50-1080';
4067: dbms_sql.parse(project_del_c1,cProject_delete1,dbms_sql.native);
4068:
4069: EXCEPTION
4070: WHEN OTHERS THEN
4071: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete1);
4067: dbms_sql.parse(project_del_c1,cProject_delete1,dbms_sql.native);
4068:
4069: EXCEPTION
4070: WHEN OTHERS THEN
4071: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete1);
4072: app_exception.raise_exception;
4073:
4074: END;
4075:
4075:
4076: xProgress := 'POOB-50-1090';
4077: BEGIN
4078: xProgress := 'POOB-50-1100';
4079: dbms_sql.parse(project_del_c2,cProject_delete2,dbms_sql.native);
4080:
4081: EXCEPTION
4082: WHEN OTHERS THEN
4083: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete2);
4079: dbms_sql.parse(project_del_c2,cProject_delete2,dbms_sql.native);
4080:
4081: EXCEPTION
4082: WHEN OTHERS THEN
4083: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cProject_delete2);
4084: app_exception.raise_exception;
4085:
4086: END;
4087:
4104: l_project_tbl);
4105:
4106: -- Define Additional Column Types
4107: xProgress := 'POOB-50-1130';
4108: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 1,rProject_rowid);
4109:
4110: xProgress := 'POOB-50-1140';
4111: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4112:
4107: xProgress := 'POOB-50-1130';
4108: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 1,rProject_rowid);
4109:
4110: xProgress := 'POOB-50-1140';
4111: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4112:
4113: -- Bind Variables
4114: xProgress := 'POOB-50-1150';
4115: dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4111: dbms_sql.define_column_rowid(project_sel_c,iProject_count + 2,rProject_X_rowid);
4112:
4113: -- Bind Variables
4114: xProgress := 'POOB-50-1150';
4115: dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4116:
4117: xProgress := 'POOB-50-1160';
4118: dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4119:
4114: xProgress := 'POOB-50-1150';
4115: dbms_sql.bind_variable(project_sel_c,':run_id', iRun_id);
4116:
4117: xProgress := 'POOB-50-1160';
4118: dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4119:
4120: xProgress := 'POOB-50-1170';
4121: dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4122:
4117: xProgress := 'POOB-50-1160';
4118: dbms_sql.bind_variable(project_sel_c,':po_header_id', NVL(cPO_Header_ID,0));
4119:
4120: xProgress := 'POOB-50-1170';
4121: dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4122:
4123: xProgress := 'POOB-50-1180';
4124: dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4125:
4120: xProgress := 'POOB-50-1170';
4121: dbms_sql.bind_variable(project_sel_c,':po_release_id', NVL(cPO_Release_ID,0));
4122:
4123: xProgress := 'POOB-50-1180';
4124: dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4125:
4126: xProgress := 'POOB-50-1190';
4127: dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4128:
4123: xProgress := 'POOB-50-1180';
4124: dbms_sql.bind_variable(project_sel_c,':po_line_id', NVL(cPO_Line_ID,0));
4125:
4126: xProgress := 'POOB-50-1190';
4127: dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4128:
4129: -- Execute the SQL Statement
4130: xProgress := 'POOB-50-1200';
4131: dummy := dbms_sql.execute(project_sel_c);
4127: dbms_sql.bind_variable(project_sel_c,':po_line_location_id',NVL(cPO_Line_Location_ID,0));
4128:
4129: -- Execute the SQL Statement
4130: xProgress := 'POOB-50-1200';
4131: dummy := dbms_sql.execute(project_sel_c);
4132:
4133: -- Fetch Data
4134: xProgress := 'POOB-50-1210';
4135: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
4131: dummy := dbms_sql.execute(project_sel_c);
4132:
4133: -- Fetch Data
4134: xProgress := 'POOB-50-1210';
4135: WHILE dbms_sql.fetch_rows(project_sel_c) > 0 LOOP -- Project Level Loop
4136: -- Store the Fetched Data in the PL/SQL Table
4137: xProgress := 'POOB-50-1220';
4138: ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,l_project_tbl);
4139:
4137: xProgress := 'POOB-50-1220';
4138: ece_flatfile_pvt.assign_column_value_to_tbl(project_sel_c,l_project_tbl);
4139:
4140: xProgress := 'POOB-50-1230';
4141: dbms_sql.column_value(project_sel_c,iProject_count + 1,rProject_rowid);
4142:
4143: xProgress := 'POOB-50-1240';
4144: dbms_sql.column_value(project_sel_c,iProject_count + 2,rProject_X_rowid);
4145:
4140: xProgress := 'POOB-50-1230';
4141: dbms_sql.column_value(project_sel_c,iProject_count + 1,rProject_rowid);
4142:
4143: xProgress := 'POOB-50-1240';
4144: dbms_sql.column_value(project_sel_c,iProject_count + 2,rProject_X_rowid);
4145:
4146: xProgress := 'POOB-50-1250';
4147: ece_poo_transaction.write_to_file(cTransaction_Type,
4148: cCommunication_Method,
4154: null);
4155:
4156: -- Bind the ROWIDs for Deletion
4157: xProgress := 'POOB-50-1260';
4158: dbms_sql.bind_variable(project_del_c1,':col_rowid',rProject_rowid);
4159:
4160: xProgress := 'POOB-50-1270';
4161: dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4162:
4157: xProgress := 'POOB-50-1260';
4158: dbms_sql.bind_variable(project_del_c1,':col_rowid',rProject_rowid);
4159:
4160: xProgress := 'POOB-50-1270';
4161: dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4162:
4163: -- Execute the First Delete SQL
4164: xProgress := 'POOB-50-1280';
4165: dummy := dbms_sql.execute(project_del_c1);
4161: dbms_sql.bind_variable(project_del_c2,':col_rowid',rProject_X_rowid);
4162:
4163: -- Execute the First Delete SQL
4164: xProgress := 'POOB-50-1280';
4165: dummy := dbms_sql.execute(project_del_c1);
4166:
4167: -- Execute the Second Delete SQL
4168: xProgress := 'POOB-50-1290';
4169: dummy := dbms_sql.execute(project_del_c2);
4165: dummy := dbms_sql.execute(project_del_c1);
4166:
4167: -- Execute the Second Delete SQL
4168: xProgress := 'POOB-50-1290';
4169: dummy := dbms_sql.execute(project_del_c2);
4170:
4171: END LOOP; -- Project Level
4172:
4173: -- Make a note if zero rows were processed
4171: END LOOP; -- Project Level
4172:
4173: -- Make a note if zero rows were processed
4174: xProgress := 'POOB-50-1300';
4175: IF dbms_sql.last_row_count = 0 THEN
4176: xProgress := 'POOB-50-1310';
4177: v_LevelProcessed := 'PROJECT';
4178: ec_debug.pl(0,
4179: 'EC',
4190:
4191: /* Bug 2490109
4192: -- Let's Close the Cursors
4193: xProgress := 'POOB-50-1320';
4194: dbms_sql.close_cursor(project_sel_c);
4195:
4196: xProgress := 'POOB-50-1330';
4197: dbms_sql.close_cursor(project_del_c1);
4198:
4193: xProgress := 'POOB-50-1320';
4194: dbms_sql.close_cursor(project_sel_c);
4195:
4196: xProgress := 'POOB-50-1330';
4197: dbms_sql.close_cursor(project_del_c1);
4198:
4199: xProgress := 'POOB-50-1340';
4200: dbms_sql.close_cursor(project_del_c2);
4201: */
4196: xProgress := 'POOB-50-1330';
4197: dbms_sql.close_cursor(project_del_c1);
4198:
4199: xProgress := 'POOB-50-1340';
4200: dbms_sql.close_cursor(project_del_c2);
4201: */
4202:
4203: xProgress := 'POOB-50-1350';
4204: if ec_debug.G_debug_level >= 2 then
4425: end if;
4426:
4427: -- Open Cursor.
4428: xProgress := 'POOB-60-1100';
4429: n_att_header_sel_c := dbms_sql.open_cursor;
4430:
4431: -- Parse Cursor.
4432: xProgress := 'POOB-60-1110';
4433: BEGIN
4430:
4431: -- Parse Cursor.
4432: xProgress := 'POOB-60-1110';
4433: BEGIN
4434: dbms_sql.parse(n_att_header_sel_c,v_att_header_select,dbms_sql.native);
4435:
4436: EXCEPTION
4437: WHEN OTHERS THEN
4438: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
4434: dbms_sql.parse(n_att_header_sel_c,v_att_header_select,dbms_sql.native);
4435:
4436: EXCEPTION
4437: WHEN OTHERS THEN
4438: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,
4439: v_att_header_select);
4440: app_exception.raise_exception;
4441: END;
4442:
4454: l_att_header_tbl);
4455:
4456: -- Bind Variables
4457: xProgress := 'POOB-60-1132';
4458: dbms_sql.bind_variable(n_att_header_sel_c,':cEntity_Name',cEntity_Name);
4459:
4460: xProgress := 'POOB-60-1133';
4461: dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4462:
4457: xProgress := 'POOB-60-1132';
4458: dbms_sql.bind_variable(n_att_header_sel_c,':cEntity_Name',cEntity_Name);
4459:
4460: xProgress := 'POOB-60-1133';
4461: dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4462:
4463: xProgress := 'POOB-60-1134';
4464: dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4465:
4460: xProgress := 'POOB-60-1133';
4461: dbms_sql.bind_variable(n_att_header_sel_c,':cName',cName);
4462:
4463: xProgress := 'POOB-60-1134';
4464: dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4465:
4466: IF cPK2_Value IS NOT NULL and cPK2_Value <> C_ANY_VALUE THEN
4467: xProgress := 'POOB-60-1135';
4468: dbms_sql.bind_variable(n_att_header_sel_c,':cPK2_Value',cPK2_Value);
4464: dbms_sql.bind_variable(n_att_header_sel_c,':cPK1_Value',cPK1_Value);
4465:
4466: IF cPK2_Value IS NOT NULL and cPK2_Value <> C_ANY_VALUE THEN
4467: xProgress := 'POOB-60-1135';
4468: dbms_sql.bind_variable(n_att_header_sel_c,':cPK2_Value',cPK2_Value);
4469: END IF;
4470:
4471: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4472: /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4471: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4472: /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4473: IF cPK3_Value IS NOT NULL AND cPK3_Value <> C_ANY_VALUE THEN
4474: xProgress := 'POOB-60-1136';
4475: dbms_sql.bind_variable(n_att_header_sel_c,':cPK3_Value',cPK3_Value);
4476: END IF;
4477:
4478: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4479: IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4477:
4478: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4479: IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4480: xProgress := 'POOB-60-1137';
4481: dbms_sql.bind_variable(n_att_header_sel_c,':cPK4_Value',cPK4_Value);
4482: END IF;
4483:
4484: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4485: IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4483:
4484: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4485: IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4486: xProgress := 'POOB-60-1138';
4487: dbms_sql.bind_variable(n_att_header_sel_c,':cPK5_Value',cPK5_Value);
4488: END IF;
4489: END IF;
4490:
4491: -- Execute Cursor
4489: END IF;
4490:
4491: -- Execute Cursor
4492: xProgress := 'POOB-60-1140';
4493: n_dummy := dbms_sql.execute(n_att_header_sel_c);
4494:
4495: xProgress := 'POOB-60-1150';
4496: WHILE dbms_sql.fetch_rows(n_att_header_sel_c) > 0 LOOP
4497: xProgress := 'POOB-60-1160';
4492: xProgress := 'POOB-60-1140';
4493: n_dummy := dbms_sql.execute(n_att_header_sel_c);
4494:
4495: xProgress := 'POOB-60-1150';
4496: WHILE dbms_sql.fetch_rows(n_att_header_sel_c) > 0 LOOP
4497: xProgress := 'POOB-60-1160';
4498: ece_flatfile_pvt.assign_column_value_to_tbl(n_att_header_sel_c,l_key_count,l_att_header_tbl,l_key_tbl);
4499:
4500: xProgress := 'POOB-60-1170';
4552:
4553: END LOOP;
4554:
4555: xProgress := 'POOB-60-1210';
4556: dbms_sql.close_cursor(n_att_header_sel_c);
4557: if ec_debug.G_debug_level >= 2 then
4558: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_TEXT_ATTACHMENT');
4559: end if;
4560:
4835: end if;
4836:
4837: -- Open Cursor.
4838: xProgress := 'POOB-70-1210';
4839: n_att_detail_sel_c := dbms_sql.open_cursor;
4840:
4841: -- Parse Cursor.
4842: xProgress := 'POOB-70-1220';
4843: BEGIN
4841: -- Parse Cursor.
4842: xProgress := 'POOB-70-1220';
4843: BEGIN
4844: xProgress := 'POOB-70-1230';
4845: dbms_sql.parse(n_att_detail_sel_c,v_att_detail_select,dbms_sql.native);
4846:
4847: EXCEPTION
4848: WHEN OTHERS THEN
4849: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,v_att_detail_select);
4845: dbms_sql.parse(n_att_detail_sel_c,v_att_detail_select,dbms_sql.native);
4846:
4847: EXCEPTION
4848: WHEN OTHERS THEN
4849: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,v_att_detail_select);
4850: app_exception.raise_exception;
4851:
4852: END;
4853:
4851:
4852: END;
4853:
4854: xProgress := 'POOB-70-1240';
4855: dbms_sql.bind_variable(n_att_detail_sel_c,':cEntity_name',cEntity_name);
4856:
4857: xProgress := 'POOB-70-1250';
4858: dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4859:
4854: xProgress := 'POOB-70-1240';
4855: dbms_sql.bind_variable(n_att_detail_sel_c,':cEntity_name',cEntity_name);
4856:
4857: xProgress := 'POOB-70-1250';
4858: dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4859:
4860: xProgress := 'POOB-70-1255';
4861: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4862:
4857: xProgress := 'POOB-70-1250';
4858: dbms_sql.bind_variable(n_att_detail_sel_c,':cName',cName);
4859:
4860: xProgress := 'POOB-70-1255';
4861: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4862:
4863: xProgress := 'POOB-70-1258';
4864: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4865:
4860: xProgress := 'POOB-70-1255';
4861: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
4862:
4863: xProgress := 'POOB-70-1258';
4864: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4865:
4866: xProgress := 'POOB-70-1260';
4867: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK1_Value',cPK1_Value);
4868:
4863: xProgress := 'POOB-70-1258';
4864: dbms_sql.bind_variable(n_att_detail_sel_c,':cAtt_doc_id',cAtt_doc_id);
4865:
4866: xProgress := 'POOB-70-1260';
4867: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK1_Value',cPK1_Value);
4868:
4869: xProgress := 'POOB-70-1270';
4870: /* IF cPK2_Value IS NOT NULL THEN */ -- BUG:5367903
4871: IF cPK2_Value IS NOT NULL AND cPK2_Value <> C_ANY_VALUE THEN
4868:
4869: xProgress := 'POOB-70-1270';
4870: /* IF cPK2_Value IS NOT NULL THEN */ -- BUG:5367903
4871: IF cPK2_Value IS NOT NULL AND cPK2_Value <> C_ANY_VALUE THEN
4872: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK2_Value',cPK2_Value);
4873: END IF;
4874:
4875: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4876: xProgress := 'POOB-70-1280';
4875: IF cEntity_Name <> 'MTL_SYSTEM_ITEMS' then -- 3550723
4876: xProgress := 'POOB-70-1280';
4877: /* IF cPK3_Value IS NOT NULL THEN */ -- BUG:5367903
4878: IF cPK3_Value IS NOT NULL AND cPK3_Value <> C_ANY_VALUE THEN
4879: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK3_Value',cPK3_Value);
4880: END IF;
4881:
4882: xProgress := 'POOB-70-1290';
4883: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4881:
4882: xProgress := 'POOB-70-1290';
4883: /* IF cPK4_Value IS NOT NULL THEN */ -- BUG:5367903
4884: IF cPK4_Value IS NOT NULL AND cPK4_Value <> C_ANY_VALUE THEN
4885: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK4_Value',cPK4_Value);
4886: END IF;
4887:
4888: xProgress := 'POOB-70-1300';
4889: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4887:
4888: xProgress := 'POOB-70-1300';
4889: /* IF cPK5_Value IS NOT NULL THEN */ -- BUG:5367903
4890: IF cPK5_Value IS NOT NULL AND cPK5_Value <> C_ANY_VALUE THEN
4891: dbms_sql.bind_variable(n_att_detail_sel_c,':cPK5_Value',cPK5_Value);
4892: END IF;
4893: END IF;
4894:
4895: -- Set Counter
4907: l_att_detail_tbl);
4908:
4909: IF cData_Type_ID = 1 THEN -- Short Text
4910: xProgress := 'POOB-70-1330';
4911: /* dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,ece_extract_utils_pub.G_MAXCOLWIDTH);*/
4912: /*Bug 2153310.
4913: Increased the size to 2000 since the short text attachment can have
4914: 2000 characters */
4915: dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4911: /* dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,ece_extract_utils_pub.G_MAXCOLWIDTH);*/
4912: /*Bug 2153310.
4913: Increased the size to 2000 since the short text attachment can have
4914: 2000 characters */
4915: dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4916: ELSE -- Long Text
4917: xProgress := 'POOB-70-1340';
4918: dbms_sql.define_column_long(n_att_detail_sel_c,n_detail_count + 1);
4919: END IF;
4914: 2000 characters */
4915: dbms_sql.define_column(n_att_detail_sel_c,n_detail_count + 1,v_att_detail_select,2000);
4916: ELSE -- Long Text
4917: xProgress := 'POOB-70-1340';
4918: dbms_sql.define_column_long(n_att_detail_sel_c,n_detail_count + 1);
4919: END IF;
4920:
4921: -- Execute Cursor
4922: xProgress := 'POOB-70-1350';
4919: END IF;
4920:
4921: -- Execute Cursor
4922: xProgress := 'POOB-70-1350';
4923: n_dummy := dbms_sql.execute(n_att_detail_sel_c);
4924:
4925: xProgress := 'POOB-70-1360';
4926: WHILE dbms_sql.fetch_rows(n_att_detail_sel_c) > 0 LOOP
4927: xProgress := 'POOB-70-1370';
4922: xProgress := 'POOB-70-1350';
4923: n_dummy := dbms_sql.execute(n_att_detail_sel_c);
4924:
4925: xProgress := 'POOB-70-1360';
4926: WHILE dbms_sql.fetch_rows(n_att_detail_sel_c) > 0 LOOP
4927: xProgress := 'POOB-70-1370';
4928: ec_debug.pl(3,'xProgress: ',xProgress);
4929:
4930: ece_flatfile_pvt.assign_column_value_to_tbl(n_att_detail_sel_c,l_att_detail_tbl);
4943: if ec_debug.G_debug_level = 3 then
4944: ec_debug.pl(3,'xProgress: ',xProgress);
4945: end if;
4946:
4947: dbms_sql.column_value(n_att_detail_sel_c,n_detail_count + 1,l_short_text);
4948: -- l_short_text := LTRIM(RTRIM(l_short_text)); -- Not using this one because white spaces at front may be intentional
4949: l_short_text := RTRIM(l_short_text);
4950: END IF;
4951:
4982: ec_debug.pl(3,'xProgress: ',xProgress);
4983: end if;
4984:
4985: -- Cursor(I) ,Pos(I) ,Len(I) ,Offset(I) ,Value(O) ,Value Len(O)
4986: 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);
4987: if ec_debug.G_debug_level = 3 then
4988: ec_debug.pl(3,'n_cur_pos: ',n_cur_pos);
4989: ec_debug.pl(3,'n_return_size: ',n_return_size);
4990: ec_debug.pl(3,'l_data_chunk: ',l_data_chunk);
5219: if ec_debug.G_debug_level = 3 then
5220: ec_debug.pl(3,'xProgress: ',xProgress);
5221: end if;
5222:
5223: 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);
5224: END IF;
5225:
5226: IF (n_temp_return_size = 0 AND cData_Type_ID = 2) OR (l_temp_text IS NULL AND cData_Type_ID = 1) THEN
5227: xProgress := 'POOB-70-1624';
5273:
5274: END LOOP;
5275:
5276: xProgress := 'POOB-70-1640';
5277: dbms_sql.close_cursor(n_att_detail_sel_c);
5278:
5279: if ec_debug.G_debug_level >= 2 then
5280: ec_debug.pop('ECE_POO_TRANSACTION.POPULATE_TEXT_ATT_DETAIL');
5281: end if;
5497: end if;
5498:
5499: -- Open Cursors
5500: xProgress := 'POOB-80-1090';
5501: Header_sel_c := dbms_sql.open_cursor;
5502:
5503: xProgress := 'POOB-80-1100';
5504: Header_del_c1 := dbms_sql.open_cursor;
5505:
5500: xProgress := 'POOB-80-1090';
5501: Header_sel_c := dbms_sql.open_cursor;
5502:
5503: xProgress := 'POOB-80-1100';
5504: Header_del_c1 := dbms_sql.open_cursor;
5505:
5506: xProgress := 'POOB-80-1110';
5507: Header_del_c2 := dbms_sql.open_cursor;
5508:
5503: xProgress := 'POOB-80-1100';
5504: Header_del_c1 := dbms_sql.open_cursor;
5505:
5506: xProgress := 'POOB-80-1110';
5507: Header_del_c2 := dbms_sql.open_cursor;
5508:
5509: -- Parse the SELECT SQL
5510: BEGIN
5511: xProgress := 'POOB-80-1120';
5508:
5509: -- Parse the SELECT SQL
5510: BEGIN
5511: xProgress := 'POOB-80-1120';
5512: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
5513:
5514: EXCEPTION
5515: WHEN OTHERS THEN
5516: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
5512: dbms_sql.parse(Header_sel_c,cHeader_select,dbms_sql.native);
5513:
5514: EXCEPTION
5515: WHEN OTHERS THEN
5516: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_select);
5517: app_exception.raise_exception;
5518: END;
5519:
5520:
5520:
5521: -- Parse the DELETE1 SQL
5522: BEGIN
5523: xProgress := 'POOB-80-1130';
5524: dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
5525:
5526: EXCEPTION
5527: WHEN OTHERS THEN
5528: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
5524: dbms_sql.parse(Header_del_c1,cHeader_delete1,dbms_sql.native);
5525:
5526: EXCEPTION
5527: WHEN OTHERS THEN
5528: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete1);
5529: app_exception.raise_exception;
5530: END;
5531:
5532: -- Parse the DELETE2 SQL
5531:
5532: -- Parse the DELETE2 SQL
5533: BEGIN
5534: xProgress := 'POOB-80-1140';
5535: dbms_sql.parse(Header_del_c2,cHeader_delete2,dbms_sql.native);
5536:
5537: EXCEPTION
5538: WHEN OTHERS THEN
5539: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
5535: dbms_sql.parse(Header_del_c2,cHeader_delete2,dbms_sql.native);
5536:
5537: EXCEPTION
5538: WHEN OTHERS THEN
5539: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cHeader_delete2);
5540: app_exception.raise_exception;
5541: END;
5542:
5543: --Set Counter
5553: ece_flatfile_pvt.G_MaxColWidth,
5554: l_header_tbl );
5555:
5556: xProgress := 'POOB-80-1170';
5557: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5558:
5559: xProgress := 'POOB-80-1180';
5560: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5561:
5556: xProgress := 'POOB-80-1170';
5557: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5558:
5559: xProgress := 'POOB-80-1180';
5560: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5561:
5562: -- Bug 2198707
5563: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5564: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5559: xProgress := 'POOB-80-1180';
5560: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5561:
5562: -- Bug 2198707
5563: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5564: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5565: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5566: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5567: IF cPK2_Value IS NOT NULL THEN
5560: dbms_sql.define_column_rowid(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5561:
5562: -- Bug 2198707
5563: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5564: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5565: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5566: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5567: IF cPK2_Value IS NOT NULL THEN
5568: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5561:
5562: -- Bug 2198707
5563: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5564: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5565: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5566: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5567: IF cPK2_Value IS NOT NULL THEN
5568: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5569: END IF;
5562: -- Bug 2198707
5563: dbms_sql.bind_variable(Header_sel_c,'Run_id',iRun_id);
5564: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5565: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5566: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5567: IF cPK2_Value IS NOT NULL THEN
5568: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5569: END IF;
5570: IF cPK3_Value IS NOT NULL THEN
5564: dbms_sql.bind_variable(Header_sel_c,'Entity_Name',cEntity_Name);
5565: dbms_sql.bind_variable(Header_sel_c,'Name',cName);
5566: dbms_sql.bind_variable(Header_sel_c,'PK1_Value',cPK1_Value);
5567: IF cPK2_Value IS NOT NULL THEN
5568: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5569: END IF;
5570: IF cPK3_Value IS NOT NULL THEN
5571: dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5572: END IF;
5567: IF cPK2_Value IS NOT NULL THEN
5568: dbms_sql.bind_variable(Header_sel_c,'PK2_Value',cPK2_Value);
5569: END IF;
5570: IF cPK3_Value IS NOT NULL THEN
5571: dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5572: END IF;
5573: IF cPK4_Value IS NOT NULL THEN
5574: dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5575: END IF;
5570: IF cPK3_Value IS NOT NULL THEN
5571: dbms_sql.bind_variable(Header_sel_c,'PK3_Value',cPK3_Value);
5572: END IF;
5573: IF cPK4_Value IS NOT NULL THEN
5574: dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5575: END IF;
5576: IF cPK5_Value IS NOT NULL THEN
5577: dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5578: END IF;
5573: IF cPK4_Value IS NOT NULL THEN
5574: dbms_sql.bind_variable(Header_sel_c,'PK4_Value',cPK4_Value);
5575: END IF;
5576: IF cPK5_Value IS NOT NULL THEN
5577: dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5578: END IF;
5579: --Execute the Cursor
5580: xProgress := 'POOB-80-1190';
5581: dummy := dbms_sql.execute(Header_sel_c);
5577: dbms_sql.bind_variable(Header_sel_c,'PK5_Value',cPK5_Value);
5578: END IF;
5579: --Execute the Cursor
5580: xProgress := 'POOB-80-1190';
5581: dummy := dbms_sql.execute(Header_sel_c);
5582:
5583: xProgress := 'POOB-80-1200';
5584: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP
5585: xProgress := 'POOB-80-1210';
5580: xProgress := 'POOB-80-1190';
5581: dummy := dbms_sql.execute(Header_sel_c);
5582:
5583: xProgress := 'POOB-80-1200';
5584: WHILE dbms_sql.fetch_rows(Header_sel_c) > 0 LOOP
5585: xProgress := 'POOB-80-1210';
5586: ece_flatfile_pvt.assign_column_value_to_tbl(Header_sel_c,l_header_tbl);
5587:
5588: xProgress := 'POOB-80-1220';
5585: xProgress := 'POOB-80-1210';
5586: ece_flatfile_pvt.assign_column_value_to_tbl(Header_sel_c,l_header_tbl);
5587:
5588: xProgress := 'POOB-80-1220';
5589: dbms_sql.column_value(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5590:
5591: xProgress := 'POOB-80-1230';
5592: dbms_sql.column_value(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5593:
5588: xProgress := 'POOB-80-1220';
5589: dbms_sql.column_value(Header_sel_c,iHeader_count + 1,rHeader_rowid);
5590:
5591: xProgress := 'POOB-80-1230';
5592: dbms_sql.column_value(Header_sel_c,iHeader_count + 2,rHeader_X_rowid);
5593:
5594: -- Find the Position of the Attachemnt Doc ID
5595: xProgress := 'POOB-80-1232';
5596: ece_flatfile_pvt.find_pos(l_header_tbl,'DOCUMENT_ID',nDoc_ID_pos);
5623: cFile_Common_Key,
5624: null);
5625:
5626: xProgress := 'POOB-80-1260';
5627: dbms_sql.bind_variable(Header_del_c1,'col_rowid',rHeader_rowid);
5628:
5629: xProgress := 'POOB-80-1270';
5630: dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5631:
5626: xProgress := 'POOB-80-1260';
5627: dbms_sql.bind_variable(Header_del_c1,'col_rowid',rHeader_rowid);
5628:
5629: xProgress := 'POOB-80-1270';
5630: dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5631:
5632: -- Execute the Cursor that deletes from the Interface Table
5633: xProgress := 'POOB-80-1280';
5634: dummy := dbms_sql.execute(Header_del_c1);
5630: dbms_sql.bind_variable(Header_del_c2,'col_rowid',rHeader_X_rowid);
5631:
5632: -- Execute the Cursor that deletes from the Interface Table
5633: xProgress := 'POOB-80-1280';
5634: dummy := dbms_sql.execute(Header_del_c1);
5635:
5636: -- Execute the Cursor that deletes from the Extension Table
5637: xProgress := 'POOB-80-1290';
5638: dummy := dbms_sql.execute(Header_del_c2);
5634: dummy := dbms_sql.execute(Header_del_c1);
5635:
5636: -- Execute the Cursor that deletes from the Extension Table
5637: xProgress := 'POOB-80-1290';
5638: dummy := dbms_sql.execute(Header_del_c2);
5639:
5640: xProgress := 'POOB-80-1300';
5641: put_att_detail_to_output_table(cCommunication_Method,
5642: cTransaction_Type,
5658: END LOOP;
5659:
5660: -- Close the Cursors and Cleanup...
5661: xProgress := 'POOB-80-1310';
5662: dbms_sql.close_cursor(Header_sel_c);
5663: dbms_sql.close_cursor(Header_del_c1);
5664: dbms_sql.close_cursor(Header_del_c2);
5665: if ec_debug.G_debug_level >= 2 then
5666: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5659:
5660: -- Close the Cursors and Cleanup...
5661: xProgress := 'POOB-80-1310';
5662: dbms_sql.close_cursor(Header_sel_c);
5663: dbms_sql.close_cursor(Header_del_c1);
5664: dbms_sql.close_cursor(Header_del_c2);
5665: if ec_debug.G_debug_level >= 2 then
5666: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5667: end if;
5660: -- Close the Cursors and Cleanup...
5661: xProgress := 'POOB-80-1310';
5662: dbms_sql.close_cursor(Header_sel_c);
5663: dbms_sql.close_cursor(Header_del_c1);
5664: dbms_sql.close_cursor(Header_del_c2);
5665: if ec_debug.G_debug_level >= 2 then
5666: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_TO_OUTPUT_TABLE');
5667: end if;
5668: EXCEPTION
5892: end if;
5893:
5894: -- Open Cursors
5895: xProgress := 'POOB-90-1110';
5896: Detail_sel_c := dbms_sql.open_cursor;
5897:
5898: xProgress := 'POOB-90-1120';
5899: Detail_del_c1 := dbms_sql.open_cursor;
5900:
5895: xProgress := 'POOB-90-1110';
5896: Detail_sel_c := dbms_sql.open_cursor;
5897:
5898: xProgress := 'POOB-90-1120';
5899: Detail_del_c1 := dbms_sql.open_cursor;
5900:
5901: xProgress := 'POOB-90-1130';
5902: Detail_del_c2 := dbms_sql.open_cursor;
5903:
5898: xProgress := 'POOB-90-1120';
5899: Detail_del_c1 := dbms_sql.open_cursor;
5900:
5901: xProgress := 'POOB-90-1130';
5902: Detail_del_c2 := dbms_sql.open_cursor;
5903:
5904: -- Parse the SELECT SQL
5905: BEGIN
5906: xProgress := 'POOB-90-1140';
5903:
5904: -- Parse the SELECT SQL
5905: BEGIN
5906: xProgress := 'POOB-90-1140';
5907: dbms_sql.parse(Detail_sel_c,cDetail_select,dbms_sql.native);
5908:
5909: EXCEPTION
5910: WHEN OTHERS THEN
5911: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_select);
5907: dbms_sql.parse(Detail_sel_c,cDetail_select,dbms_sql.native);
5908:
5909: EXCEPTION
5910: WHEN OTHERS THEN
5911: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_select);
5912: app_exception.raise_exception;
5913: END;
5914:
5915: -- Parse the DELETE1 SQL
5914:
5915: -- Parse the DELETE1 SQL
5916: BEGIN
5917: xProgress := 'POOB-90-1150';
5918: dbms_sql.parse(Detail_del_c1,cDetail_delete1,dbms_sql.native);
5919:
5920: EXCEPTION
5921: WHEN OTHERS THEN
5922: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete1);
5918: dbms_sql.parse(Detail_del_c1,cDetail_delete1,dbms_sql.native);
5919:
5920: EXCEPTION
5921: WHEN OTHERS THEN
5922: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete1);
5923: app_exception.raise_exception;
5924: END;
5925:
5926: -- Parse the DELETE2 SQL
5925:
5926: -- Parse the DELETE2 SQL
5927: BEGIN
5928: xProgress := 'POOB-90-1160';
5929: dbms_sql.parse(Detail_del_c2,cDetail_delete2,dbms_sql.native);
5930:
5931: EXCEPTION
5932: WHEN OTHERS THEN
5933: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete2);
5929: dbms_sql.parse(Detail_del_c2,cDetail_delete2,dbms_sql.native);
5930:
5931: EXCEPTION
5932: WHEN OTHERS THEN
5933: ece_error_handling_pvt.print_parse_error(dbms_sql.last_error_position,cDetail_delete2);
5934: app_exception.raise_exception;
5935: END;
5936:
5937: --Set Counter
5947: ece_flatfile_pvt.G_MaxColWidth,
5948: l_detail_tbl);
5949:
5950: xProgress := 'POOB-90-1190';
5951: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5952:
5953: xProgress := 'POOB-90-1200';
5954: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5955:
5950: xProgress := 'POOB-90-1190';
5951: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
5952:
5953: xProgress := 'POOB-90-1200';
5954: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5955:
5956: --Bind Variables
5957: xProgress := 'POOB-90-1201';
5958: dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5954: dbms_sql.define_column_rowid(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
5955:
5956: --Bind Variables
5957: xProgress := 'POOB-90-1201';
5958: dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5959:
5960: xProgress := 'POOB-90-1202';
5961: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5962:
5957: xProgress := 'POOB-90-1201';
5958: dbms_sql.bind_variable(Detail_sel_c,':iRun_id',iRun_id);
5959:
5960: xProgress := 'POOB-90-1202';
5961: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5962:
5963: xProgress := 'POOB-90-1202a';
5964: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5965:
5960: xProgress := 'POOB-90-1202';
5961: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Seq_Num',cAtt_Seq_Num);
5962:
5963: xProgress := 'POOB-90-1202a';
5964: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5965:
5966: xProgress := 'POOB-90-1203';
5967: dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5968:
5963: xProgress := 'POOB-90-1202a';
5964: dbms_sql.bind_variable(Detail_sel_c,':cAtt_Doc_ID',cAtt_Doc_ID);
5965:
5966: xProgress := 'POOB-90-1203';
5967: dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5968:
5969: xProgress := 'POOB-90-1204';
5970: dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5971:
5966: xProgress := 'POOB-90-1203';
5967: dbms_sql.bind_variable(Detail_sel_c,':cEntity_Name',cEntity_Name);
5968:
5969: xProgress := 'POOB-90-1204';
5970: dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5971:
5972: xProgress := 'POOB-90-1205';
5973: dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5974:
5969: xProgress := 'POOB-90-1204';
5970: dbms_sql.bind_variable(Detail_sel_c,':cName',cName);
5971:
5972: xProgress := 'POOB-90-1205';
5973: dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5974:
5975: IF cPK2_Value IS NOT NULL THEN
5976: xProgress := 'POOB-90-1206';
5977: dbms_sql.bind_variable(Detail_sel_c,':cPK2_Value',cPK2_Value);
5973: dbms_sql.bind_variable(Detail_sel_c,':cPK1_Value',cPK1_Value);
5974:
5975: IF cPK2_Value IS NOT NULL THEN
5976: xProgress := 'POOB-90-1206';
5977: dbms_sql.bind_variable(Detail_sel_c,':cPK2_Value',cPK2_Value);
5978: END IF;
5979:
5980: IF cPK3_Value IS NOT NULL THEN
5981: xProgress := 'POOB-90-1207';
5978: END IF;
5979:
5980: IF cPK3_Value IS NOT NULL THEN
5981: xProgress := 'POOB-90-1207';
5982: dbms_sql.bind_variable(Detail_sel_c,':cPK3_Value',cPK3_Value);
5983: END IF;
5984:
5985: IF cPK4_Value IS NOT NULL THEN
5986: xProgress := 'POOB-90-1208';
5983: END IF;
5984:
5985: IF cPK4_Value IS NOT NULL THEN
5986: xProgress := 'POOB-90-1208';
5987: dbms_sql.bind_variable(Detail_sel_c,':cPK4_Value',cPK4_Value);
5988: END IF;
5989:
5990: IF cPK5_Value IS NOT NULL THEN
5991: xProgress := 'POOB-90-1209';
5988: END IF;
5989:
5990: IF cPK5_Value IS NOT NULL THEN
5991: xProgress := 'POOB-90-1209';
5992: dbms_sql.bind_variable(Detail_sel_c,':cPK5_Value',cPK5_Value);
5993: END IF;
5994:
5995: --Execute the Cursor
5996: xProgress := 'POOB-90-1210';
5993: END IF;
5994:
5995: --Execute the Cursor
5996: xProgress := 'POOB-90-1210';
5997: dummy := dbms_sql.execute(Detail_sel_c);
5998:
5999: xProgress := 'POOB-90-1220';
6000: WHILE dbms_sql.fetch_rows(Detail_sel_c) > 0 LOOP
6001: xProgress := 'POOB-90-1230';
5996: xProgress := 'POOB-90-1210';
5997: dummy := dbms_sql.execute(Detail_sel_c);
5998:
5999: xProgress := 'POOB-90-1220';
6000: WHILE dbms_sql.fetch_rows(Detail_sel_c) > 0 LOOP
6001: xProgress := 'POOB-90-1230';
6002: ece_flatfile_pvt.assign_column_value_to_tbl(Detail_sel_c,l_detail_tbl);
6003:
6004: xProgress := 'POOB-90-1240';
6001: xProgress := 'POOB-90-1230';
6002: ece_flatfile_pvt.assign_column_value_to_tbl(Detail_sel_c,l_detail_tbl);
6003:
6004: xProgress := 'POOB-90-1240';
6005: dbms_sql.column_value(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
6006:
6007: xProgress := 'POOB-90-1250';
6008: dbms_sql.column_value(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
6009:
6004: xProgress := 'POOB-90-1240';
6005: dbms_sql.column_value(Detail_sel_c,iDetail_count + 1,rDetail_rowid);
6006:
6007: xProgress := 'POOB-90-1250';
6008: dbms_sql.column_value(Detail_sel_c,iDetail_count + 2,rDetail_X_rowid);
6009:
6010: xProgress := 'POOB-90-1260';
6011: ece_poo_transaction.write_to_file(cTransaction_Type,
6012: cCommunication_Method,
6017: cFile_Common_Key,
6018: null);
6019:
6020: xProgress := 'POOB-90-1270';
6021: dbms_sql.bind_variable(Detail_del_c1,'col_rowid',rDetail_rowid);
6022:
6023: xProgress := 'POOB-90-1280';
6024: dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
6025:
6020: xProgress := 'POOB-90-1270';
6021: dbms_sql.bind_variable(Detail_del_c1,'col_rowid',rDetail_rowid);
6022:
6023: xProgress := 'POOB-90-1280';
6024: dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
6025:
6026: -- Execute the Cursor that deletes from the Interface Table
6027: xProgress := 'POOB-90-1290';
6028: dummy := dbms_sql.execute(Detail_del_c1);
6024: dbms_sql.bind_variable(Detail_del_c2,'col_rowid',rDetail_X_rowid);
6025:
6026: -- Execute the Cursor that deletes from the Interface Table
6027: xProgress := 'POOB-90-1290';
6028: dummy := dbms_sql.execute(Detail_del_c1);
6029:
6030: -- Execute the Cursor that deletes from the Extension Table
6031: xProgress := 'POOB-90-1300';
6032: dummy := dbms_sql.execute(Detail_del_c2);
6028: dummy := dbms_sql.execute(Detail_del_c1);
6029:
6030: -- Execute the Cursor that deletes from the Extension Table
6031: xProgress := 'POOB-90-1300';
6032: dummy := dbms_sql.execute(Detail_del_c2);
6033: END LOOP;
6034:
6035: -- Close the Cursors and Cleanup...
6036: xProgress := 'POOB-90-1310';
6033: END LOOP;
6034:
6035: -- Close the Cursors and Cleanup...
6036: xProgress := 'POOB-90-1310';
6037: dbms_sql.close_cursor(Detail_sel_c);
6038: dbms_sql.close_cursor(Detail_del_c1);
6039: dbms_sql.close_cursor(Detail_del_c2);
6040: if ec_debug.G_debug_level >= 2 then
6041: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6034:
6035: -- Close the Cursors and Cleanup...
6036: xProgress := 'POOB-90-1310';
6037: dbms_sql.close_cursor(Detail_sel_c);
6038: dbms_sql.close_cursor(Detail_del_c1);
6039: dbms_sql.close_cursor(Detail_del_c2);
6040: if ec_debug.G_debug_level >= 2 then
6041: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6042: end if;
6035: -- Close the Cursors and Cleanup...
6036: xProgress := 'POOB-90-1310';
6037: dbms_sql.close_cursor(Detail_sel_c);
6038: dbms_sql.close_cursor(Detail_del_c1);
6039: dbms_sql.close_cursor(Detail_del_c2);
6040: if ec_debug.G_debug_level >= 2 then
6041: ec_debug.pop ('ECE_POO_TRANSACTION.PUT_ATT_DETAIL_TO_OUTPUT_TABLE');
6042: end if;
6043: EXCEPTION