[Home] [Help]
1: PACKAGE BODY RCV_HXT_GRP AS
2: /* $Header: RCVGHXTB.pls 120.19.12020000.3 2013/02/10 23:10:04 vegajula ship $ */
3:
4: -- record for all timecard attributes interesting to Purchasing
5: TYPE TimecardAttributesRec IS RECORD
167: TYPE assignments_cache IS TABLE OF per_all_assignments_cr INDEX BY BINARY_INTEGER;
168: TYPE rcv_transactions_cache IS TABLE OF rcv_transactions_cr INDEX BY BINARY_INTEGER;
169:
170: -- package globals
171: G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_HXT_GRP';
172: G_LOG_MODULE CONSTANT VARCHAR2(40) := 'po.plsql.' || G_PKG_NAME;
173: G_CONC_LOG VARCHAR2(32767);
174: -- bug 5976883 : Have changed the fnd logging logic according to PO standards
175: -- Now at all places we are using the module.package.procedure convention.
257: TIMECARD_NOT_APPROVED EXCEPTION;
258:
259: -- Private support procedures
260:
261: -- wrapper for RCV_HXT_GRP.string
262: -- Bug 5976883 : Rewrote string debug function according to PO standards
263: PROCEDURE string
264: ( log_level IN number
265: , module IN varchar2
363: l_api_name CONSTANT varchar2(30) := 'set_rhi_table_status';
364: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
365: l_progress VARCHAR2(3) := '000';
366: BEGIN
367: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
368: , module => l_log_head
369: , message => 'Begin set_rhi_table_status'
370: );
371: -- bug 6000903
409: -- Added logic to overcome this problem. by using .exists() function.
410:
411: p_processable_rows_exist := 'N';
412: for i in 1..p_rti_rows.count loop
413: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module =>
414: l_log_head,message => 'p_rti_rows(i).processing_status_code :'||p_rti_rows(i).processing_status_code);
415: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module =>
416: l_log_head,message => 'p_rti_rows(i).header_interface_id :'||p_rti_rows(i).header_interface_id);
417:
411: p_processable_rows_exist := 'N';
412: for i in 1..p_rti_rows.count loop
413: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module =>
414: l_log_head,message => 'p_rti_rows(i).processing_status_code :'||p_rti_rows(i).processing_status_code);
415: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module =>
416: l_log_head,message => 'p_rti_rows(i).header_interface_id :'||p_rti_rows(i).header_interface_id);
417:
418: if ((p_rti_rows(i).processing_status_code = 'ERROR') AND
419: (p_rti_rows(i).header_interface_id is not null)) THEN
417:
418: if ((p_rti_rows(i).processing_status_code = 'ERROR') AND
419: (p_rti_rows(i).header_interface_id is not null)) THEN
420: if l_temp.exists(p_rti_rows(i).header_interface_id) THEN
421: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
422: ,module => l_log_head
423: ,message => 'Setting ERROR l_temp(p_rti_rows(i).header_interface_id) :'||l_temp(p_rti_rows(i).header_interface_id));
424: if nvl(l_temp(p_rti_rows(i).header_interface_id),'ERROR') <> 'PENDING' then
425: l_temp(p_rti_rows(i).header_interface_id) := 'ERROR';
424: if nvl(l_temp(p_rti_rows(i).header_interface_id),'ERROR') <> 'PENDING' then
425: l_temp(p_rti_rows(i).header_interface_id) := 'ERROR';
426: end if;
427: ELSE
428: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
429: ,module => l_log_head
430: ,message => 'Setting ERROR l_temp header_interface_id ');
431: l_temp(p_rti_rows(i).header_interface_id) := 'ERROR';
432: end if;
435: -- rti_header_interface_id NULL
436: elsif ((p_rti_rows(i).processing_status_code <> 'ERROR') AND
437: (p_rti_rows(i).header_interface_id is not null)) then
438: l_temp(p_rti_rows(i).header_interface_id) := 'PENDING';
439: RCV_HXT_GRP.string( log_level =>FND_LOG.LEVEL_PROCEDURE
440: ,module => l_log_head
441: ,message => 'Setting Pending l_temp');
442: p_processable_rows_exist := 'Y';
443: elsif ((p_rti_rows(i).processing_status_code <> 'ERROR') AND
447: end loop;
448:
449: if p_rhi_rows.count > 0 then
450: for i in 1..p_rhi_rows.count loop
451: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
452: ,module => l_log_head
453: ,message =>'Loop 2 p_rhi_rows.header_interface_id :'||p_rhi_rows(i).header_interface_id);
454: if l_temp.exists(p_rhi_rows(i).header_interface_id) THEN
455: if l_temp(p_rhi_rows(i).header_interface_id) = 'ERROR' THEN
452: ,module => l_log_head
453: ,message =>'Loop 2 p_rhi_rows.header_interface_id :'||p_rhi_rows(i).header_interface_id);
454: if l_temp.exists(p_rhi_rows(i).header_interface_id) THEN
455: if l_temp(p_rhi_rows(i).header_interface_id) = 'ERROR' THEN
456: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
457: ,module => l_log_head
458: ,message =>'Loop 2 l_temp :'||l_temp(p_rhi_rows(i).header_interface_id));
459: p_rhi_rows(i).processing_status_code := 'ERROR';
460: end if;
462: end loop;
463: end if;
464: EXCEPTION
465: WHEN OTHERS THEN
466: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
467: , module => l_log_head
468: , message => 'Unexpected exception in set_rhi_table_status ' || SQLERRM
469: );
470: RAISE;
677: WHERE pol.po_line_id = p_po_line_id
678: AND poll.po_line_id = pol.po_line_id;
679: END IF;
680:
681: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
682: , module => G_LOG_MODULE
683: , message => 'after po_line_id ' ||g_po_line_cache(p_po_line_id).po_line_id||'**'
684: ||'po_header_id '||g_po_line_cache(p_po_line_id).po_header_id||'**'
685: ||'line_num '||g_po_line_cache(p_po_line_id).line_num||'**'
753:
754: RETURN g_po_distribution_cache(p_po_line_id);
755: EXCEPTION
756: WHEN OTHERS THEN
757: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
758: , module => l_log_head
759: , message => 'Unexpected exception deriving po_distribution_id (po_line_id=' || p_po_line_id || ', project_id=' || p_project_id || ', task_id=' || p_task_id || '): ' || SQLERRM
760: );
761: RAISE DERIVE_DISTRIBUTION_ID_FAILED;
934: ) RETURN rcv_transactions_cr IS
935: l_api_name CONSTANT varchar2(30) := 'get_rcv_transaction';
936: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
937: BEGIN
938: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
939: , module => l_log_head
940: , message => 'Finding Parent Transcations , TimeCard ID: ' || p_timecard_bb_id || ' Po line Id: ' || p_po_line_id
941: );
942: g_rcv_transactions_calls := g_rcv_transactions_calls + 1;
957: AND receive.transaction_type = 'RECEIVE';
958: EXCEPTION
959: WHEN NO_DATA_FOUND THEN
960: g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id := NULL;
961: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
962: , module => l_log_head
963: , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id
964: );
965:
974: AND deliver.transaction_type = 'DELIVER';
975: EXCEPTION
976: WHEN NO_DATA_FOUND THEN
977: g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id := NULL;
978: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
979: , module => l_log_head
980: , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id
981: );
982:
996: ) RETURN rcv_transactions_cr IS
997: l_api_name CONSTANT varchar2(30) := 'get_rcv_transaction';
998: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
999: BEGIN
1000: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1001: , module => l_log_head
1002: , message => 'Finding Parent Transcations , TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id || ' po_distribution_id: ' || p_po_distribution_id
1003: );
1004: IF p_po_distribution_id IS NULL THEN
1030: AND receive.transaction_type = 'RECEIVE';
1031: EXCEPTION
1032: WHEN NO_DATA_FOUND THEN
1033: g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id := NULL;
1034: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1035: , module => l_log_head
1036: , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id || ' po_distribution_id: ' || p_po_distribution_id
1037: );
1038: END;
1048: AND deliver.transaction_type = 'DELIVER';
1049: EXCEPTION
1050: WHEN NO_DATA_FOUND THEN
1051: g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id := NULL;
1052: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1053: , module => l_log_head
1054: , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id || ' po_distribution_id: ' || p_po_distribution_id
1055: );
1056: END;
1148: l_attribute_value VARCHAR2(20);
1149: nls_num_chars VARCHAR(2);
1150: -- Bug# 14492865 - End
1151: BEGIN
1152: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1153: , module => l_log_head
1154: , message => 'Setting attribute ' || p_attribute_name || ' with value ' || p_attribute_value || ' (p_attribute_id=' || p_attribute_id || ')'
1155: );
1156: l_attribute_name := upper(p_attribute_name);
1206: l_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
1207: l_api_name CONSTANT varchar2(30) := 'Sort_Attributes';
1208: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1209: BEGIN
1210: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1211: , module => l_log_head
1212: , message => 'Begin Sort_Attributes'
1213: );
1214:
1219: IF NOT p_all_attributes.EXISTS(l_bb_id) THEN
1220: p_all_attributes(l_bb_id).detail_bb_id := l_bb_id;
1221: END IF;
1222:
1223: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1224: , module => l_log_head
1225: , message => 'Capturing attribute_id=' || p_raw_attributes(att_idx).time_attribute_id || ', attribute_name=' || p_raw_attributes(att_idx).attribute_name
1226: );
1227:
1231: , p_raw_attributes(att_idx).time_attribute_id
1232: );
1233: END LOOP;
1234:
1235: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1236: , module => l_log_head
1237: , message => 'End Sort_Attributes'
1238: );
1239: END Sort_Attributes;
1245: l_api_name CONSTANT varchar2(30) := 'Update_Attributes';
1246: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1247: po_update_flag VARCHAR2(1) :='N'; --bug 6998132
1248: BEGIN
1249: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1250: , module => l_log_head
1251: , message => 'Begin Update_Attributes'
1252: );
1253:
1295: p_attributes.po_number := get_po_header(p_attributes.po_header_id).segment1;
1296: EXCEPTION
1297: WHEN OTHERS THEN
1298: -- unexpected exception deriving po header id
1299: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1300: , module => l_log_head
1301: , message => 'Unexpected exception deriving PO Header Id (PO Number=' || p_attributes.po_number || '): ' || SQLERRM
1302: );
1303: END;
1311: p_attributes.po_line := get_po_line(p_attributes.po_line_id).line_num;
1312: EXCEPTION
1313: WHEN OTHERS THEN
1314: -- unexpected exception deriving po line id
1315: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1316: , module => l_log_head
1317: , message => 'Unexpected exception deriving PO Line Number (PO Header Id=' || p_attributes.po_header_id || ', PO Line Id=' || p_attributes.po_line_id || '): ' || SQLERRM
1318: );
1319: END;
1329: p_attributes.po_price_type_display := get_price_type_lookup(p_attributes.po_price_type).meaning;
1330: EXCEPTION
1331: WHEN OTHERS THEN
1332: -- unexpected exception deriving price type
1333: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1334: , module => l_log_head
1335: , message => 'Unexpected exception deriving PO Price Type Display (PO Price Type=' || p_attributes.po_price_type || '): ' || SQLERRM
1336: );
1337: END;
1347: p_attributes.po_billable_amount := p_attributes.detail_measure * get_price_differentials(p_attributes.po_line_id, p_attributes.po_price_type).price;
1348: EXCEPTION
1349: WHEN OTHERS THEN
1350: -- unexpected exception deriving PO Billable Amount
1351: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1352: , module => l_log_head
1353: , message => 'Unexpected exception deriving PO Billable Amount (PO Price Type=' || p_attributes.po_price_type || ', PO Line Id=' || p_attributes.po_line_id || '): ' || SQLERRM
1354: );
1355: END;
1360: -- can be determined much more easily at retrieval time, when it is clear whether
1361: -- the receiving transaction type is RECEIVE or CORRECT
1362: p_attributes.po_receipt_date := SYSDATE;
1363:
1364: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1365: , module => l_log_head
1366: , message => 'End Update_Attributes'
1367: );
1368: END Update_Attributes;
1459: l_include_closed_po := NVL (FND_PROFILE.value('RCV_CLOSED_PO_DEFAULT_OPTION'), 'N');
1460:
1461: /*bug 6902391 Changing to single org as in 11.5.10*/
1462:
1463: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1464: , module => l_log_head
1465: , message => 'TimeCard day_start_time ' || p_attributes.day_start_time || 'Timecard resource_id = '||p_attributes.resource_id
1466: );
1467:
1464: , module => l_log_head
1465: , message => 'TimeCard day_start_time ' || p_attributes.day_start_time || 'Timecard resource_id = '||p_attributes.resource_id
1466: );
1467:
1468: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1469: , module => l_log_head
1470: , message => 'hr_organization_api.get_operating_unit = ' || hxc_timecard_properties.setup_mo_global_params(fnd_global.employee_id)
1471: );
1472:
1475: -- Modified the If condition for bug 9255870, passing p_attributes.resource_id instead of fnd_global.employee_id
1476: -- Condition removed as not required. After R12 MOAC. User can use Purchase
1477: -- Order Created in other Operating Unit.
1478: -- get_po_header(p_attributes.po_header_id).org_id <> FND_GLOBAL.org_id
1479: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1480: , module => l_log_head
1481: , message => 'PO is invalid: ' || '*'
1482: || get_po_header(p_attributes.po_header_id).user_hold_flag || '*'
1483: );
1495: get_po_line(p_attributes.po_line_id).approved_flag <> 'Y' OR
1496: get_po_line(p_attributes.po_line_id).cancel_flag <> 'N' OR
1497: get_po_line(p_attributes.po_line_id).closed_code = 'FINALLY CLOSED' OR
1498: (l_include_closed_po <> 'Y' AND get_po_line(p_attributes.po_line_id).closed_code IN ('CLOSED','CLOSED FOR RECEIVING')) THEN
1499: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1500: , module => l_log_head
1501: , message => 'Line is invalid: ' || '*'
1502: || get_po_line(p_attributes.po_line_id).matching_basis || '*'
1503: || get_po_line(p_attributes.po_line_id).purchase_basis || '*'
1690: -- be careful if moving this block of code
1691: g_po_line_cache(p_attributes.po_line_id).timecard_amount := get_po_line(p_attributes.po_line_id).timecard_amount + p_attributes.po_billable_amount;
1692: g_po_line_cache(p_attributes.po_line_id).time_attribute_id := NVL(get_po_line(p_attributes.po_line_id).time_attribute_id, p_attributes.time_attribute_id);
1693:
1694: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1695: , module => l_log_head
1696: , message => 'Amount for PO Line Id ' || p_attributes.po_line_id || ' updated to ' || get_po_line(p_attributes.po_line_id).timecard_amount
1697: );
1698: END IF;
1826: BEGIN
1827: l_po_line_id := g_po_line_cache.FIRST;
1828: WHILE l_po_line_id IS NOT NULL LOOP
1829: BEGIN
1830: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1831: , module => l_log_head
1832: , message => 'Validating amount tolerance for PO Line Id=' || l_po_line_id || ' (Billable Amount=' || get_po_line(l_po_line_id).timecard_amount || ')'
1833: );
1834:
1836: -- do not consider the received amounts because that is already included in the timecard amount below
1837: l_qty_rcv_exception_code := get_po_line(l_po_line_id).qty_rcv_exception_code;
1838: l_tolerable_amount := get_po_line(l_po_line_id).tolerable_amount;
1839:
1840: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1841: , module => l_log_head
1842: , message => 'Fetched Tolerable Amount=' || l_tolerable_amount || ', Receiving Tolerance Exception Code=' || l_qty_rcv_exception_code || ' (PO Line Id=' || l_po_line_id || ')'
1843: );
1844:
1857: IF l_timecard_amount_sum IS NULL THEN
1858: l_timecard_amount_sum := 0;
1859: END IF;
1860:
1861: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1862: , module => l_log_head
1863: , message => 'Fetched Timecard Amount Sum=' || l_timecard_amount_sum || ' (PO Line Id=' || l_po_line_id || ')'
1864: );
1865:
1891:
1892: l_po_line_id := g_po_line_cache.NEXT(l_po_line_id);
1893: EXCEPTION
1894: WHEN GET_TIMECARD_AMOUNT_FAILED THEN
1895: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1896: , module => l_log_head
1897: , message => 'PO_HXC_INTERFACE_PVT.get_timecard_amount returned error'
1898: );
1899:
1911: END;
1912: END LOOP;
1913: EXCEPTION
1914: WHEN OTHERS THEN
1915: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1916: , module => l_log_head
1917: , message => 'Unexpected exception validating amount tolerances: ' || SQLERRM
1918: );
1919:
1944: BEGIN
1945: p_rti_row.job_id := get_po_line(p_attributes.po_line_id).job_id;
1946: EXCEPTION
1947: WHEN OTHERS THEN
1948: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1949: , module => l_log_head
1950: , message => 'Unexpected exception deriving job_id (po_line_id=' || p_attributes.po_line_id || '): ' || SQLERRM
1951: );
1952: RAISE DERIVE_JOB_ID_FAILED;
1999: l_rti_row_idx BINARY_INTEGER;
2000: l_api_name CONSTANT varchar2(30) := 'Derive_Receive_Values';
2001: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2002: BEGIN
2003: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2004: , module => l_log_head
2005: , message => 'Begin Derive_Receive_Values'
2006: );
2007:
2008: -- check for existing rhi row
2009: l_rhi_row_idx := get_rhi_idx( p_attributes, p_rhi_rows, p_rti_rows );
2010: IF l_rhi_row_idx IS NOT NULL THEN
2011: -- found a match
2012: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2013: , module => l_log_head
2014: , message => 'Using existing RHI row'
2015: );
2016:
2053: -- check for existing rti row
2054: l_rti_row_idx := get_rti_idx( p_attributes, p_rti_rows );
2055: IF l_rti_row_idx IS NOT NULL THEN
2056: -- found a match
2057: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2058: , module => l_log_head
2059: , message => 'Using existing RTI row'
2060: );
2061:
2066: IF p_attributes.po_receipt_date IS NULL AND
2067: trunc(p_attributes.detail_start_time) > l_rti_row.transaction_date THEN
2068: l_rti_row.transaction_date := trunc(p_attributes.detail_start_time);
2069:
2070: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2071: , module => l_log_head
2072: , message => 'Updated transaction_date=' || l_rti_row.transaction_date
2073: );
2074: END IF;
2095:
2096: -- associate the rti rows to the building block
2097: p_attributes.receive_rti_id := l_rti_row.interface_transaction_id;
2098:
2099: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2100: , module => l_log_head
2101: , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id
2102: );
2103: ELSE -- found existing rti row
2113: Derive_Common_RTI_Values( l_rti_row
2114: , p_attributes
2115: );
2116:
2117: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2118: , module => l_log_head
2119: , message => 'Set po_distribution_id=' || l_rti_row.po_distribution_id
2120: );
2121:
2129: ELSE
2130: l_rti_row.transaction_date := trunc(p_attributes.detail_start_time);
2131: END IF;
2132:
2133: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2134: , module => l_log_head
2135: , message => 'Initialized transaction_date=' || l_rti_row.transaction_date
2136: );
2137:
2137:
2138: -- amount received
2139: l_rti_row.amount := p_attributes.po_billable_amount;
2140:
2141: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2142: , module => l_log_head
2143: , message => 'Set amount=' || l_rti_row.amount || ' from ' || p_attributes.po_billable_amount
2144: );
2145:
2158:
2159: -- associate the rti rows to the building block
2160: p_attributes.receive_rti_id := l_rti_row.interface_transaction_id;
2161:
2162: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2163: , module => l_log_head
2164: , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id
2165: );
2166: END IF; -- found existing rti row
2168: -- copy data back to the main tables
2169: p_rhi_rows(l_rhi_row_idx) := l_rhi_row;
2170: p_rti_rows(l_rti_row_idx) := l_rti_row;
2171:
2172: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2173: , module => l_log_head
2174: , message => 'End Derive_Receive_Values'
2175: );
2176: END Derive_Receive_Values;
2189: l_rti_row_idx BINARY_INTEGER;
2190: l_api_name CONSTANT varchar2(30) := 'Derive_Correction_Values';
2191: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2192: BEGIN
2193: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2194: , module => l_log_head
2195: , message => 'Begin Derive_Correction_Values'
2196: );
2197:
2197:
2198: -- determine the correction amount
2199: l_correction_amount := p_attributes.po_billable_amount - p_old_attributes.po_billable_amount;
2200:
2201: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2202: , module => l_log_head
2203: , message => 'Derived correction amount=' || l_correction_amount
2204: );
2205:
2206: -- check if we already have this correction so we can just add the amount to it
2207: l_rti_row_idx := get_rti_idx( p_attributes, p_rti_rows );
2208: IF l_rti_row_idx IS NOT NULL THEN
2209: -- found a match
2210: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2211: , module => l_log_head
2212: , message => 'Using existing RTI rows'
2213: );
2214:
2241: -- This means that one is a positive correction and the other is a negative correction.
2242: -- If this happens we need to swap the order of the correction records, because for
2243: -- negative correction the correction record for deliver xaction should go first and
2244: -- for positive correction the correction record for receipt xaction should go first.
2245: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2246: , module => l_log_head
2247: , message => 'sign(l_correction_amount)=' ||sign(l_correction_amount) || ',sign(l_old_correction_amount) ' || sign(l_old_correction_amount)||',hence swap'
2248: );
2249: l_swap:= p_rti_rows(l_rti_row_idx).interface_transaction_id;
2288: p_attributes.deliver_rti_id := p_rti_rows(l_rti_row_idx).interface_transaction_id;
2289: END IF;
2290: END IF;
2291:
2292: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2293: , module => l_log_head
2294: , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id || ', deliver_rti_id=' || p_attributes.deliver_rti_id
2295: );
2296: ELSE -- found existing rti rows
2294: , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id || ', deliver_rti_id=' || p_attributes.deliver_rti_id
2295: );
2296: ELSE -- found existing rti rows
2297: -- did not find any match, let's create a new transaction
2298: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2299: , module => l_log_head
2300: , message => 'Creating new RTI rows'
2301: );
2302:
2317: ELSE
2318: l_rcv_rti_row.transaction_date := trunc(SYSDATE);
2319: END IF;
2320:
2321: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2322: , module => l_log_head
2323: , message => 'Initialized transaction_date=' || l_rcv_rti_row.transaction_date
2324: );
2325:
2371: -- associate the rti rows to the building block
2372: p_attributes.receive_rti_id := l_rcv_rti_row.interface_transaction_id;
2373: p_attributes.deliver_rti_id := l_del_rti_row.interface_transaction_id;
2374:
2375: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2376: , module => l_log_head
2377: , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id || ', deliver_rti_id=' || p_attributes.deliver_rti_id
2378: );
2379: END IF; -- found existing rti rows
2377: , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id || ', deliver_rti_id=' || p_attributes.deliver_rti_id
2378: );
2379: END IF; -- found existing rti rows
2380:
2381: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2382: , module => l_log_head
2383: , message => 'End Derive_Correction_Values'
2384: );
2385: END Derive_Correction_Values;
2396: l_new_attributes TimecardAttributesRec;
2397: l_api_name CONSTANT varchar2(30) := 'Derive_Delete_Values';
2398: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2399: BEGIN
2400: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2401: , module => l_log_head
2402: , message => 'Begin Derive_Delete_Values'
2403: );
2404:
2421: -- derive_correction_values only sets the rti-bb relationship in the new attributes
2422: p_attributes.receive_rti_id := l_new_attributes.receive_rti_id;
2423: p_attributes.deliver_rti_id := l_new_attributes.deliver_rti_id;
2424:
2425: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2426: , module => l_log_head
2427: , message => 'End Derive_Delete_Values'
2428: );
2429: END Derive_Delete_Values;
2436: ) IS
2437: l_api_name CONSTANT varchar2(30) := 'Derive_New_Block_Values';
2438: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2439: BEGIN
2440: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2441: , module => l_log_head
2442: , message => 'Begin Derive_New_Block_Values'
2443: );
2444:
2470: -- save the transaction type
2471: p_attributes.transaction_type := 'RECEIVE';
2472: END IF;
2473:
2474: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2475: , module => l_log_head
2476: , message => 'End Derive_New_Block_Values'
2477: );
2478: END Derive_New_Block_Values;
2487: l_api_name CONSTANT varchar2(30) := 'Derive_Interface_Values';
2488: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2489: l_progress VARCHAR2(3) := '000';
2490: BEGIN
2491: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2492: , module => l_log_head
2493: , message => 'Begin Derive_Interface_Values'
2494: );
2495:
2501:
2502: p_old_attributes.lpn_group_id := p_attributes.lpn_group_id;
2503: EXCEPTION
2504: WHEN OTHERS THEN
2505: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2506: , module => l_log_head
2507: , message => 'Unexpected exception setting lpn_group_id: ' || SQLERRM
2508: );
2509: RAISE DERIVE_ROI_VALUES_FAILED;
2509: RAISE DERIVE_ROI_VALUES_FAILED;
2510: END;
2511: IF g_debug_stmt THEN
2512: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Printing p_attributes');
2513: RCV_HXT_GRP.debug_TimecardAttributesRec(l_log_head, p_attributes,'NEW');
2514: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Printing p_old_attributes');
2515: RCV_HXT_GRP.debug_TimecardAttributesRec(l_log_head, p_old_attributes,'OLD');
2516: END IF;
2517: -- find the parent transactions, assume the old block has the same parents
2511: IF g_debug_stmt THEN
2512: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Printing p_attributes');
2513: RCV_HXT_GRP.debug_TimecardAttributesRec(l_log_head, p_attributes,'NEW');
2514: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Printing p_old_attributes');
2515: RCV_HXT_GRP.debug_TimecardAttributesRec(l_log_head, p_old_attributes,'OLD');
2516: END IF;
2517: -- find the parent transactions, assume the old block has the same parents
2518: BEGIN
2519: /* Bug 14609848 */
2534: p_old_attributes.parent_receive_txn_id := p_attributes.parent_receive_txn_id;
2535: p_old_attributes.parent_deliver_txn_id := p_attributes.parent_deliver_txn_id;
2536: EXCEPTION
2537: WHEN OTHERS THEN
2538: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2539: , module => l_log_head
2540: , message => 'Unexpected exception querying for parent transactions in Derive_Interface_Values (po_line_id=' || p_attributes.po_line_id || ', timecard_bb_id=' || p_attributes.timecard_bb_id || '): ' || SQLERRM
2541: );
2542: RAISE DERIVE_ROI_VALUES_FAILED;
2557:
2558: -- if the user changed po information we need to correct
2559: -- the old receipt to zero and either create a new receipt
2560: -- or correct an existing one
2561: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2562: , module => l_log_head
2563: , message => 'Correction : Either PO Line OR Project/Task information changed '
2564: );
2565: -- in this case the old block does not have the same parents
2588: p_old_attributes.task_id).deliver_transaction_id;
2589:
2590: EXCEPTION
2591: WHEN OTHERS THEN
2592: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2593: , module => l_log_head
2594: , message => 'Unexpected exception querying for parent transactions for the old block in Derive_Interface_Values ('
2595: || 'po_line_id=' || p_old_attributes.po_line_id
2596: || ', timecard_bb_id=' || p_old_attributes.timecard_bb_id
2622: -- save the transaction type
2623: p_attributes.transaction_type := 'DELETE ' || p_attributes.transaction_type;
2624:
2625: ELSE -- po line changed
2626: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2627: , module => l_log_head
2628: , message => 'Correction : Either Rate type or the hours worked changed '
2629: );
2630: -- if the user only changed the rate type or the hours worked
2655: p_attributes.transaction_type := 'CORRECT';
2656: END IF;
2657: END IF; -- po line changed
2658: ELSE -- detail_changed
2659: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2660: , module => l_log_head
2661: , message => 'Receive : Block is newly created.'
2662: );
2663: Derive_New_Block_Values( p_rhi_rows
2665: , p_attributes
2666: );
2667: END IF; -- detail_changed
2668:
2669: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2670: , module => l_log_head
2671: , message => 'Done deriving ROI values. Calling iSP to store timecard detail information...'
2672: );
2673:
2681: l_rti_idx BINARY_INTEGER;
2682: l_rhi_row rcv_headers_interface%ROWTYPE;
2683: l_rti_row rcv_transactions_interface%ROWTYPE;
2684: BEGIN
2685: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2686: , module => l_log_head
2687: , message => 'Finding ROI rows: p_rhi_rows.COUNT=' || p_rhi_rows.COUNT || ' p_rti_rows.COUNT=' || p_rti_rows.COUNT || ' p_attributes.po_distribution_id=' || p_attributes.po_distribution_id
2688: );
2689:
2688: );
2689:
2690: l_rhi_idx := get_rhi_idx(p_attributes, p_rhi_rows, p_rti_rows);
2691: IF l_rhi_idx IS NOT NULL THEN
2692: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2693: , module => l_log_head
2694: , message => 'rhi_idx=' || l_rhi_idx
2695: );
2696: l_rhi_row := p_rhi_rows(l_rhi_idx);
2696: l_rhi_row := p_rhi_rows(l_rhi_idx);
2697: END IF;
2698:
2699: l_rti_idx := get_rti_idx(p_attributes, p_rti_rows);
2700: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2701: , module => l_log_head
2702: , message => 'rti_idx=' || l_rti_idx
2703: );
2704: l_rti_row := p_rti_rows(l_rti_idx);
2702: , message => 'rti_idx=' || l_rti_idx
2703: );
2704: l_rti_row := p_rti_rows(l_rti_idx);
2705:
2706: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2707: , module => l_log_head
2708: , message => 'Setting p_action for store_timecard_details'
2709: );
2710:
2717: ELSE
2718: l_action := 'INSERT';
2719: END IF;
2720:
2721: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2722: , module => l_log_head
2723: , message => 'p_action=' || l_action
2724: );
2725:
2754: , p_action => l_action
2755: , p_interface_transaction_id => l_rti_row.interface_transaction_id
2756: );
2757:
2758: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2759: , module => l_log_head
2760: , message => 'After store_timecard_details'
2761: );
2762:
2764: RAISE ISP_STORE_TIMECARD_FAILED;
2765: END IF;
2766: EXCEPTION
2767: WHEN ISP_STORE_TIMECARD_FAILED THEN
2768: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
2769: , module => l_log_head
2770: , message => 'iSP store_timecard_details failed: x_return_status=' || l_return_status || ', x_msg_data=' || l_msg_data
2771: );
2772: RAISE DERIVE_ROI_VALUES_FAILED;
2770: , message => 'iSP store_timecard_details failed: x_return_status=' || l_return_status || ', x_msg_data=' || l_msg_data
2771: );
2772: RAISE DERIVE_ROI_VALUES_FAILED;
2773: WHEN OTHERS THEN
2774: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
2775: , module => l_log_head
2776: , message => 'Exception trying to call iSP store_timecard_details: x_return_status=' || l_return_status || ', x_msg_data=' || l_msg_data || ', sqlerrm=' || sqlerrm
2777: );
2778: RAISE DERIVE_ROI_VALUES_FAILED;
2777: );
2778: RAISE DERIVE_ROI_VALUES_FAILED;
2779: END; -- end of isp integration
2780:
2781: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2782: , module => l_log_head
2783: , message => 'End Derive_Interface_Values'
2784: );
2785: EXCEPTION
2918: l_api_name CONSTANT varchar2(30) := 'Insert_Interface_Values';
2919: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2920: BEGIN
2921: -- save new ROI data to the database
2922: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2923: , module => l_log_head
2924: , message => 'Begin Insert_Interface_Values'
2925: );
2926:
2923: , module => l_log_head
2924: , message => 'Begin Insert_Interface_Values'
2925: );
2926:
2927: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2928: , module => l_log_head
2929: , message => 'RHI rows: ' || p_rhi_rows.COUNT || ' RTI rows: '|| p_rti_rows.COUNT
2930: );
2931:
2928: , module => l_log_head
2929: , message => 'RHI rows: ' || p_rhi_rows.COUNT || ' RTI rows: '|| p_rti_rows.COUNT
2930: );
2931:
2932: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2933: , module => l_log_head
2934: , message => 'Coping the RHI Records to Local PL/SQL tables'
2935: );
2936: -- transfer data from table of records to tables of each column
2963: -- also in error.
2964: p_rhi_rows(i).processing_status_code := 'ERROR';
2965: l_rhi_stat(p_rhi_rows(i).header_interface_id) := 'ERROR';
2966: row_idx := row_idx - 1;
2967: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2968: , module => l_log_head
2969: , message => 'Exception while populating RHI plsql table '
2970: ||p_rhi_rows(i).header_interface_id
2971: || ' Error '||SQLERRM
2973: END;
2974: END IF;
2975: END LOOP;
2976:
2977: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2978: , module => l_log_head
2979: , message => 'Inserting ' || rhi_header_interface_id.COUNT || ' rows into RHI'
2980: );
2981:
3018: );
3019:
3020: -- transfer data from table of records to tables of each column
3021:
3022: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3023: , module => l_log_head
3024: , message => 'Inserted ' || rhi_header_interface_id.COUNT || ' rows into RHI'
3025: );
3026:
3024: , message => 'Inserted ' || rhi_header_interface_id.COUNT || ' rows into RHI'
3025: );
3026:
3027: -- bug 6031665
3028: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3029: , module => l_log_head
3030: , message => 'Copying the RTI Records to Local PL/SQL tables'
3031: );
3032: FOR i IN 1..p_rti_rows.COUNT LOOP
3085: -- from RTI record.
3086:
3087: row_idx := row_idx - 1;
3088: p_rti_rows(i).processing_status_code := 'ERROR';
3089: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3090: , module => l_log_head
3091: , message => 'Exception while populating RTI plsql table. Interface transaction id :'
3092: || p_rti_rows(i).interface_transaction_id
3093: ||' Time card id : '||p_rti_rows(i).timecard_id|| ' Error '||SQLERRM
3107: p_rti_status(p_rti_rows(i).interface_transaction_id) := 1;
3108: ELSE
3109: -- No Need to decrease the Counter here as we didnt increased.
3110: p_rti_rows(i).processing_status_code := 'ERROR';
3111: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3112: , module => l_log_head
3113: , message => 'Exception while propogating fake success. Interface transaction id is NULL:'
3114: ||' Time card id : '||p_rti_rows(i).timecard_id|| ' Error '||SQLERRM
3115: );
3115: );
3116: END IF;*/
3117: END IF;
3118: END LOOP;
3119: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3120: , module => l_log_head
3121: , message => 'Inserting '|| rti_interface_transaction_id.COUNT || ' rows into RHI'
3122: );
3123:
3186: );
3187:
3188: COMMIT;
3189:
3190: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3191: , module => l_log_head
3192: , message => 'Inserted ' || rti_interface_transaction_id.COUNT || ' rows into RTI'
3193: );
3194:
3191: , module => l_log_head
3192: , message => 'Inserted ' || rti_interface_transaction_id.COUNT || ' rows into RTI'
3193: );
3194:
3195: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3196: , module => l_log_head
3197: , message => 'End Insert_Interface_Values'
3198: );
3199: END Insert_Interface_Values;
3207: l_api_name CONSTANT varchar2(30) := 'Capture_Timecard_Info';
3208: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3209: l_progress varchar2(3) := '000';
3210: BEGIN
3211: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3212: , module => l_log_head
3213: , message => 'Begin Capture_Timecard_Info'
3214: );
3215:
3212: , module => l_log_head
3213: , message => 'Begin Capture_Timecard_Info'
3214: );
3215:
3216: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3217: , module => l_log_head
3218: , message => 'Capturing info from block record for detail bb_id=' || p_block.bb_id || ' ovn=' || p_block.ovn
3219: );
3220:
3233: p_dst_attributes.timecard_bb_ovn := p_block.timecard_ovn;
3234: p_dst_attributes.timecard_comment := p_block.comment_text;
3235:
3236: -- add timecard info
3237: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3238: , module => l_log_head
3239: , message => 'Deriving timecard info for timecard bb_id=' || p_dst_attributes.timecard_bb_id || ' ovn=' || p_dst_attributes.timecard_bb_ovn
3240: );
3241:
3242: -- certain timecard information is not provided so we need to query them up
3243: DECLARE
3244: l_timecard_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3245: BEGIN
3246: l_timecard_block := RCV_HXT_GRP.build_block( p_dst_attributes.timecard_bb_id
3247: , p_dst_attributes.timecard_bb_ovn
3248: );
3249: p_dst_attributes.timecard_start_time := l_timecard_block.start_time;
3250: p_dst_attributes.timecard_stop_time := l_timecard_block.stop_time;
3259: PO_DEBUG.debug_var(l_log_head,l_progress,'timecard_approval_status', p_dst_attributes.timecard_approval_status);
3260: END IF;
3261: END;
3262:
3263: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3264: , module => l_log_head
3265: , message => 'Capturing info from attribute records for detail bb_id=' || p_dst_attributes.detail_bb_id || ' ovn=' || p_dst_attributes.detail_bb_ovn
3266: );
3267:
3273: );
3274: p_att_idx := p_att_idx + 1;
3275: END LOOP;
3276:
3277: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3278: , module => l_log_head
3279: , message => 'End Capture_Timecard_Info'
3280: );
3281: END Capture_Timecard_Info;
3288: ) IS
3289: l_api_name CONSTANT varchar2(30) := 'Capture_Old_Timecard_Info';
3290: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3291: BEGIN
3292: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3293: , module => l_log_head
3294: , message => 'Begin Capture_Old_Timecard_Info'
3295: );
3296:
3293: , module => l_log_head
3294: , message => 'Begin Capture_Old_Timecard_Info'
3295: );
3296:
3297: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3298: , module => l_log_head
3299: , message => 'Capturing info from block record for old detail bb_id=' || p_block.bb_id || ' ovn=' || p_block.ovn
3300: );
3301:
3312: p_dst_attributes.detail_stop_time := p_block.stop_time;
3313: p_dst_attributes.detail_measure := p_block.measure;
3314: p_dst_attributes.resource_id := p_block.resource_id;
3315:
3316: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3317: , module => l_log_head
3318: , message => 'Deriving day/timecard info for old detail bb_id=' || p_dst_attributes.detail_bb_id || ' ovn=' || p_dst_attributes.detail_bb_ovn
3319: );
3320:
3322: DECLARE
3323: l_detail_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3324: l_day_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3325: BEGIN
3326: l_detail_block := RCV_HXT_GRP.build_block( p_dst_attributes.detail_bb_id
3327: , p_dst_attributes.detail_bb_ovn
3328: );
3329: l_day_block := RCV_HXT_GRP.build_block( l_detail_block.parent_building_block_id
3330: , l_detail_block.parent_building_block_ovn
3325: BEGIN
3326: l_detail_block := RCV_HXT_GRP.build_block( p_dst_attributes.detail_bb_id
3327: , p_dst_attributes.detail_bb_ovn
3328: );
3329: l_day_block := RCV_HXT_GRP.build_block( l_detail_block.parent_building_block_id
3330: , l_detail_block.parent_building_block_ovn
3331: );
3332:
3333: p_dst_attributes.day_bb_id := l_day_block.time_building_block_id;
3333: p_dst_attributes.day_bb_id := l_day_block.time_building_block_id;
3334: p_dst_attributes.timecard_bb_id := l_day_block.parent_building_block_id;
3335: p_dst_attributes.timecard_bb_ovn := l_day_block.parent_building_block_ovn;
3336:
3337: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3338: , module => l_log_head
3339: , message => 'Derived for old detail block: day_bb_id=' || p_dst_attributes.day_bb_id || ', timecard_bb_id=' || p_dst_attributes.timecard_bb_id || ', timecard_bb_ovn=' || p_dst_attributes.timecard_bb_ovn
3340: );
3341: END;
3339: , message => 'Derived for old detail block: day_bb_id=' || p_dst_attributes.day_bb_id || ', timecard_bb_id=' || p_dst_attributes.timecard_bb_id || ', timecard_bb_ovn=' || p_dst_attributes.timecard_bb_ovn
3340: );
3341: END;
3342:
3343: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3344: , module => l_log_head
3345: , message => 'Capturing info from attribute records for old detail bb_id=' || p_dst_attributes.detail_bb_id || ' ovn=' || p_dst_attributes.detail_bb_ovn
3346: );
3347:
3354:
3355: p_att_idx := p_att_idx + 1;
3356: END LOOP;
3357:
3358: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3359: , module => l_log_head
3360: , message => 'End Capture_Old_Timecard_Info'
3361: );
3362: END Capture_Old_Timecard_Info;
3370: l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3371: l_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3372: l_attributes HXC_USER_TYPE_DEFINITION_GRP.attribute_info;
3373: BEGIN
3374: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3375: , module => l_log_head
3376: , message => 'Querying bb_id=' || p_bb_id || ' bb_ovn=' || p_bb_ovn
3377: );
3378:
3376: , message => 'Querying bb_id=' || p_bb_id || ' bb_ovn=' || p_bb_ovn
3377: );
3378:
3379: -- query timecard block
3380: l_block := RCV_HXT_GRP.build_block( p_bb_id
3381: , p_bb_ovn
3382: );
3383:
3384: p_attributes_rec.detail_bb_id := l_block.time_building_block_id;
3391: p_attributes_rec.detail_date_from := l_block.date_from;
3392: p_attributes_rec.detail_date_to := l_block.date_to;
3393:
3394: -- manually pull up attributes
3395: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3396: , module => l_log_head
3397: , message => 'Querying timecard attributes'
3398: );
3399:
3396: , module => l_log_head
3397: , message => 'Querying timecard attributes'
3398: );
3399:
3400: l_attributes := RCV_HXT_GRP.build_attribute( p_bb_id
3401: , p_bb_ovn
3402: , 'PURCHASING'
3403: );
3404: p_attributes_rec.po_number := l_attributes.attribute1;
3413:
3414: /* bug 13850458 NO_DATA_FOUND Exception is raised when no PO attribute
3415: details were entered in the timecard */
3416: WHEN NO_DATA_FOUND THEN
3417: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3418: , module => l_log_head
3419: , message => 'No data found exception in Query_Timecard_Info (bb_id='
3420: || p_bb_id || ', bb_ovn=' || p_bb_ovn || '): ' || SQLERRM
3421: );
3420: || p_bb_id || ', bb_ovn=' || p_bb_ovn || '): ' || SQLERRM
3421: );
3422:
3423: WHEN OTHERS THEN
3424: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3425: , module => l_log_head
3426: , message => 'Unexpected exception in Query_Timecard_Info (bb_id=' || p_bb_id || ', bb_ovn=' || p_bb_ovn || '): ' || SQLERRM
3427: );
3428: RAISE;
3482: -- and get the Details blocks for the same timecards which are processed and make them
3483: -- with errors.
3484: l_exp_idx BINARY_INTEGER;
3485: BEGIN
3486: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3487: , module => l_log_head
3488: , message => 'Begin Retrieve_Timecards_Body'
3489: );
3490: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3486: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3487: , module => l_log_head
3488: , message => 'Begin Retrieve_Timecards_Body'
3489: );
3490: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3491: , module => l_log_head
3492: , message => 'last_blk_idx=' || last_blk_idx || ' last_old_blk_idx=' || last_old_blk_idx || ' last_att_idx=' || last_att_idx || ' last_old att_idx=' || last_old_att_idx
3493: );
3494:
3514: --
3515: -- Need to Verified this message is stored in local variable
3516: -- which might not get into OTL in Retrieval Program Crash...
3517: g_txn_msg := 'Stage 01 - ISP Reconcile Action is been called for First Time';
3518: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3519: , module => l_log_head
3520: , message => 'Calling iSP to reconcile actions'
3521: );
3522:
3524: , x_return_status => l_return_status
3525: , x_msg_data => l_msg_data
3526: );
3527:
3528: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3529: , module => l_log_head
3530: , message => 'Done with iSP reconcile actions'
3531: );
3532:
3534: RAISE ISP_RECONCILE_ACTIONS_FAILED;
3535: END IF;
3536: EXCEPTION
3537: WHEN ISP_RECONCILE_ACTIONS_FAILED THEN
3538: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3539: ,module=>l_log_head
3540: , message => 'iSP reconcile actions failed: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data
3541: );
3542: RAISE;
3540: , message => 'iSP reconcile actions failed: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data
3541: );
3542: RAISE;
3543: WHEN OTHERS THEN
3544: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3545: , module => l_log_head
3546: , message => 'Unexpected exception while calling iSP reconcile actions: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data || ' sqlerrm=' || SQLERRM
3547: );
3548: RAISE ISP_RECONCILE_ACTIONS_FAILED;
3547: );
3548: RAISE ISP_RECONCILE_ACTIONS_FAILED;
3549: END;
3550:
3551: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3552: , module => l_log_head
3553: , message => 'Starting main loop through detail building blocks...'
3554: );
3555:
3556: -- loop through the detail building blocks
3557: g_txn_msg := 'Stage 02 - Start Processing the Blocks one by one before inserting in ROI';
3558: WHILE blk_idx <= last_blk_idx LOOP
3559: BEGIN
3560: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3561: , module => l_log_head
3562: , message => '|------------------ blk_idx = ' || blk_idx || ' ------------------|'
3563: );
3564:
3631:
3632: -- maintain the old block index
3633: old_blk_idx := old_blk_idx + 1;
3634: END IF;
3635: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3636: , module => l_log_head
3637: , message => 'fill the po_distribution_id and line_location_id for NEW attributes'
3638: );
3639: --Bug6357273
3659: -- Hence get_rti_idx fails in case of a po-projects OTL setup, since in this case we use distribution_id
3660: -- to get the rti_idx, before calling PO_STORE_TIMECARD_PKG_GRP.store_timecard_details
3661:
3662: IF p_blocks(blk_idx).changed = 'Y' THEN
3663: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3664: , module => l_log_head
3665: , message => 'fill the po_distribution_id and line_location_id for old attributes'
3666: );
3667: HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-90- Get the PO line for Old block - Start';
3699:
3700: HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3701: HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3702: || 'Unexpected exception while processing results from Generic Retrieval: ' || SQLERRM;
3703: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3704: , module => l_log_head
3705: , message =>HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3706: );
3707:
3751: l_processable_rows_exist := 'N';
3752: g_txn_msg := 'Stage 03 - Done with Capture TimeCard Block Process, going to insert valid one to Receiving interface table';
3753: set_rhi_table_status(l_rhi_rows, l_rti_rows,l_processable_rows_exist);
3754:
3755: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3756: , module => l_log_head
3757: , message => 'l_processable_rows_exist' || l_processable_rows_exist
3758: );
3759:
3783: l_outcome VARCHAR2(240);
3784:
3785: RVCTP_FAILED EXCEPTION;
3786: BEGIN
3787: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3788: , module => l_log_head
3789: , message => 'Calling Receiving Transaction Processor with group_id=' || g_group_id
3790: );
3791:
3798: , argument2 => g_group_id
3799: );
3800:
3801: IF (g_req_id = 0) THEN
3802: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3803: , module => l_log_head
3804: , message => 'Concurrent request submission failed'
3805: );
3806:
3808: ELSE
3809: COMMIT;
3810: END IF;
3811:
3812: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3813: , module => l_log_head
3814: , message => 'Request ID for RVCTP: ' || TO_CHAR(g_req_id)
3815: );
3816:
3826:
3827: g_receiving_stop := SYSDATE;
3828: g_receiving_time := g_receiving_time + ( g_receiving_stop - g_receiving_start );
3829:
3830: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3831: , module => l_log_head
3832: , message => 'RVCTP done: ' || '*' || l_phase || '*' || l_status || '*' || l_dev_phase || '*' || l_dev_status || '*' || l_message || '*'
3833: );
3834:
3832: , message => 'RVCTP done: ' || '*' || l_phase || '*' || l_status || '*' || l_dev_phase || '*' || l_dev_status || '*' || l_message || '*'
3833: );
3834:
3835: IF NOT l_success THEN
3836: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3837: , module => l_log_head
3838: , message => 'Receiving Transaction Processor returned false.'
3839: );
3840:
3855:
3856: COMMIT;
3857:
3858: WHEN OTHERS THEN
3859: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3860: , module => l_log_head
3861: , message => 'Exception trying to run Receiving Transaction Processor: ' || SQLERRM
3862: );
3863: RAISE TRANSACTION_FAILED;
3863: RAISE TRANSACTION_FAILED;
3864: END;
3865: END IF;
3866:
3867: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3868: , module => l_log_head
3869: , message => 'Finding successful receiving transactions'
3870: );
3871: g_txn_msg := 'Stage 04 - RTP is Sucess, Looping through RCV Transaction for Getting Success Records.';
3871: g_txn_msg := 'Stage 04 - RTP is Sucess, Looping through RCV Transaction for Getting Success Records.';
3872:
3873: -- get the successful transactions we just created
3874: FOR l_rt_row IN new_rt_rows( l_rti_rows(1).group_id ) LOOP
3875: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3876: , module => l_log_head
3877: , message => 'Successful RTI id ' || l_rt_row.interface_transaction_id
3878: );
3879: l_rti_status(l_rt_row.interface_transaction_id) := 1;
3878: );
3879: l_rti_status(l_rt_row.interface_transaction_id) := 1;
3880: END LOOP;
3881:
3882: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3883: , module => l_log_head
3884: , message => 'Setting Detail Statuses'
3885: );
3886:
3885: );
3886:
3887: -- update detail statuses
3888: FOR blk_idx IN 1..last_blk_idx LOOP
3889: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3890: , module => l_log_head
3891: , message => 'Setting Detail Status for blk_idx=' || blk_idx || ' bb_id=' || p_blocks(blk_idx).bb_id
3892: );
3893:
3900: l_delete_deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
3901: l_message VARCHAR2(1000);
3902: DETAIL_NOT_PROCESSED EXCEPTION;
3903: BEGIN
3904: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3905: , module => l_log_head
3906: , message => 'Finding RTI ids'
3907: );
3908:
3917: l_deliver_rti_id := l_all_attributes(l_bb_id).deliver_rti_id;
3918: l_delete_receive_rti_id := l_all_attributes(l_bb_id).delete_receive_rti_id;
3919: l_delete_deliver_rti_id := l_all_attributes(l_bb_id).delete_deliver_rti_id;
3920:
3921: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3922: , module => l_log_head
3923: , message => 'Found bb_id=' || l_bb_id
3924: || ' transaction_type=' || l_transaction_type
3925: || ' receive_rti_id=' || l_receive_rti_id
3948:
3949: HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'SUCCESS';
3950: g_successful_details := g_successful_details + 1;
3951:
3952: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3953: , module => l_log_head
3954: , message => 'Detail success'
3955: );
3956:
3960: -- Propogating RTP errors back to the OTL.
3961: -- Logic : We are looping through all the records from rcv_transaction_interface
3962: -- for the Transaction Line id which is not there in the l_rti_status table which
3963: -- is means this records have failed and can have RTP Error Message.
3964: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3965: , module => l_log_head
3966: , message => 'Stamping RTP errors to the Timecards '
3967: );
3968:
3971: WHERE interface_line_id IN (l_receive_rti_id,l_deliver_rti_id,
3972: l_delete_receive_rti_id,l_delete_deliver_rti_id)
3973: AND table_name = 'RCV_TRANSACTIONS_INTERFACE') LOOP
3974:
3975: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3976: , module => l_log_head
3977: , message => 'RTP errors : ' || rec.msg
3978: );
3979:
3986: HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) || ' Receipt not created : '||l_message;
3987: END IF;
3988: g_failed_details := g_failed_details + 1;
3989:
3990: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3991: , module => l_log_head
3992: , message => 'Detail error '||HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3993: );
3994: END IF;
4003: || ' Detail block not processed';
4004: END IF;
4005: g_failed_details := g_failed_details + 1;
4006:
4007: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4008: , module => l_log_head
4009: , message => 'Detail block not processed'
4010: );
4011:
4017: HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception (blk_idx) || 'Exception while checking receipt for detail block: ' || SQLERRM;
4018: END IF;
4019: g_failed_details := g_failed_details + 1;
4020:
4021: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4022: , module => l_log_head
4023: , message => 'Unexpected exception while checking receipt for detail block: ' || SQLERRM
4024: );
4025: END;
4030: l_msg_data VARCHAR2(2000);
4031:
4032: ISP_RECONCILE_ACTIONS_FAILED EXCEPTION;
4033: BEGIN
4034: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4035: , module => l_log_head
4036: , message => 'Calling iSP to reconcile actions'
4037: );
4038:
4040: , x_return_status => l_return_status
4041: , x_msg_data => l_msg_data
4042: );
4043:
4044: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4045: , module => l_log_head
4046: , message => 'Done with iSP reconcile actions'
4047: );
4048:
4050: RAISE ISP_RECONCILE_ACTIONS_FAILED;
4051: END IF;
4052: EXCEPTION
4053: WHEN ISP_RECONCILE_ACTIONS_FAILED THEN
4054: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
4055: , module => l_log_head
4056: , message => 'iSP reconcile actions failed: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data
4057: );
4058: WHEN OTHERS THEN
4055: , module => l_log_head
4056: , message => 'iSP reconcile actions failed: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data
4057: );
4058: WHEN OTHERS THEN
4059: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4060: , module => l_log_head
4061: , message => 'Unexpected exception while calling iSP reconcile actions: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data || ' sqlerrm=' || SQLERRM
4062: );
4063: END;
4061: , message => 'Unexpected exception while calling iSP reconcile actions: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data || ' sqlerrm=' || SQLERRM
4062: );
4063: END;
4064:
4065: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4066: , module => l_log_head
4067: , message => 'Setting Day/Timecard Statuses'
4068: );
4069:
4069:
4070: -- update status for day and timecard building blocks
4071: HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4072:
4073: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4074: , module => l_log_head
4075: , message => 'Setting Transaction Status'
4076: );
4077:
4074: , module => l_log_head
4075: , message => 'Setting Transaction Status'
4076: );
4077:
4078: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4079: , module => l_log_head
4080: , message => 'RT status count = ' || l_rti_status.COUNT
4081: );
4082:
4095: , p_status => g_txn_status
4096: , p_exception_description => g_txn_msg
4097: );
4098:
4099: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4100: , module => l_log_head
4101: , message => 'Retrieval Transaction ' || g_txn_status || ': ' || g_txn_msg
4102: );
4103:
4102: );
4103:
4104: COMMIT;
4105:
4106: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4107: , module => l_log_head
4108: , message => 'Transaction committed'
4109: );
4110:
4107: , module => l_log_head
4108: , message => 'Transaction committed'
4109: );
4110:
4111: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4112: , module => l_log_head
4113: , message => 'End Retrieve_Timecards_Body'
4114: );
4115: EXCEPTION
4123: p_process => 'Purchasing Retrieval Process'
4124: , p_status => 'ERRORS'
4125: , p_exception_description => g_txn_msg || 'Hit Breakpoint. Ending process in error, because we are still debugging.'
4126: );
4127: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
4128: , module => l_log_head
4129: , message => g_txn_msg || 'Hit Breakpoint. Ending process in error, because we are still debugging.'
4130: );
4131: COMMIT;
4182: p_process => 'Purchasing Retrieval Process'
4183: , p_status => 'ERRORS'
4184: , p_exception_description => SUBSTR(g_txn_msg || 'Unexpected exception in Retrieve_Timecards_Body: ' || SQLERRM, 1, 2000)
4185: );
4186: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4187: , module => l_log_head
4188: , message => SUBSTR(g_txn_msg || 'Unexpected exception in Retrieve_Timecards_Body: ' || SQLERRM, 1, 2000)
4189: );
4190: COMMIT;
4215: g_update_start := SYSDATE;
4216: initialize_cache_statistics;
4217: initialize_caches;
4218:
4219: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4220: , module => l_log_head
4221: , message => 'Begin Update_Timecard'
4222: );
4223:
4273: p_building_blocks => l_blocks,
4274: p_app_attributes => l_attributes,
4275: p_messages => l_messages);
4276:
4277: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4278: , module => l_log_head
4279: , message => 'End Update_Timecard'
4280: );
4281:
4302: /* Bug 5401262: Procedure Validate_Amount_Tolerances() validates all rows in po
4303: cache. We need to clear cache to prevent false errors/warnings */
4304: g_po_line_cache.delete;
4305:
4306: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4307: , module => l_log_head
4308: , message => 'Begin Validate_Timecard'
4309: );
4310:
4343: ELSE
4344: l_attributes_rec.detail_deleted := 'N';
4345: END IF;
4346:
4347: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4348: , module => l_log_head
4349: , message => 'Detail block flags *'
4350: || l_attributes_rec.detail_changed || '*'
4351: || l_attributes_rec.detail_new || '*'
4416: -- validate the old attributes if relevant
4417: /*IF l_all_attributes(l_bb_id).detail_new = 'N' AND
4418: l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4419: l_old_attributes(l_bb_id).timecard_approval_status = 'SUBMITTED' THEN
4420: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4421: , module => G_LOG_MODULE
4422: , message => 'Validating old block'
4423: );
4424: Validate_Attributes(l_old_attributes(l_bb_id), l_messages);
4433: ( l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4434: l_all_attributes(l_bb_id).detail_deleted = 'N' AND
4435: l_old_attributes(l_bb_id).validation_status IN ('SUCCESS','SKIPPED')
4436: ) THEN
4437: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4438: , module => l_log_head
4439: , message => 'Validating new block'
4440: );
4441: Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4446: IF l_all_attributes(l_bb_id).detail_new = 'N' AND
4447: l_old_attributes(l_bb_id).timecard_approval_status = 'SUBMITTED' THEN
4448:
4449: IF (-1*(l_old_attributes(l_bb_id).po_billable_amount) <> l_all_attributes(l_bb_id).po_billable_amount ) THEN
4450: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4451: , module => G_LOG_MODULE
4452: , message => 'Validating old block'
4453: );
4454: Validate_Attributes(l_old_attributes(l_bb_id), l_messages);
4457: IF l_old_attributes(l_bb_id).po_line_id IS NOT NULL THEN
4458: IF NOT(get_po_line(l_old_attributes(l_bb_id).po_line_id).closed_code = 'FINALLY CLOSED' OR
4459: (NVL(FND_PROFILE.value('RCV_CLOSED_PO_DEFAULT_OPTION'), 'N')<> 'Y' AND
4460: get_po_line(l_old_attributes(l_bb_id).po_line_id).closed_code IN ('CLOSED','CLOSED FOR RECEIVING'))) THEN
4461: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4462: , module => G_LOG_MODULE
4463: , message => 'Validating old block'
4464: );
4465: Validate_Attributes(l_old_attributes(l_bb_id), l_messages);
4467: /* bug 13850458 Validation required if old block has been changed
4468: and old PO attributes were null */
4469: ELSIF ( l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4470: l_all_attributes(l_bb_id).detail_deleted = 'N') THEN
4471: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4472: , module => l_log_head
4473: , message => 'Validating old block'
4474: );
4475: Validate_Attributes(l_old_attributes(l_bb_id), l_messages);
4481: l_old_attributes(l_bb_id).validation_status := 'SKIPPED';
4482: END IF;
4483:
4484: -- validate the new attributes if block is not deleted and old block is good/irrelevant
4485: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4486: , module => l_log_head
4487: , message => 'l_old_attributes(l_bb_id).validation_status'
4488: ||l_old_attributes(l_bb_id).validation_status
4489: );
4490: IF l_all_attributes(l_bb_id).detail_new = 'Y' OR
4491: l_all_attributes(l_bb_id).detail_deleted = 'N' AND
4492: l_old_attributes(l_bb_id).validation_status IN ('SUCCESS','SKIPPED') THEN
4493: IF (-1*(l_old_attributes(l_bb_id).po_billable_amount) <> l_all_attributes(l_bb_id).po_billable_amount ) THEN
4494: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4495: , module => l_log_head
4496: , message => 'Validating new block'
4497: );
4498: Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4501: IF l_old_attributes(l_bb_id).po_line_id IS NOT NULL THEN
4502: IF NOT(get_po_line(l_old_attributes(l_bb_id).po_line_id).closed_code = 'FINALLY CLOSED' OR
4503: (NVL(FND_PROFILE.value('RCV_CLOSED_PO_DEFAULT_OPTION'), 'N')<> 'Y' AND
4504: get_po_line(l_old_attributes(l_bb_id).po_line_id).closed_code IN ('CLOSED','CLOSED FOR RECEIVING'))) THEN
4505: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4506: , module => l_log_head
4507: , message => 'Validating new block'
4508: );
4509: Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4511: /* bug 13850458 Validation required if old block has been changed
4512: and old PO attributes were null */
4513: ELSIF ( l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4514: l_all_attributes(l_bb_id).detail_deleted = 'N') THEN
4515: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4516: , module => l_log_head
4517: , message => 'Validating new block'
4518: );
4519: Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4533: IF l_all_attributes(l_bb_id).detail_new = 'N' AND
4534: ( l_all_attributes(l_bb_id).detail_changed = 'N' AND
4535: l_all_attributes(l_bb_id).detail_deleted = 'Y'
4536: ) THEN
4537: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4538: , module => l_log_head
4539: , message => 'Validating block that is deleted '||l_all_attributes(l_bb_id).old_block
4540: );
4541: Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4546: IF l_all_attributes(l_bb_id).detail_new = 'N' AND
4547: ( l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4548: l_all_attributes(l_bb_id).detail_deleted = 'N' AND
4549: l_old_attributes(l_bb_id).validation_status IN ('ERROR')) THEN
4550: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4551: , module => l_log_head
4552: , message => 'Validating block that errored earlier'
4553: );
4554: Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4565: p_building_blocks => l_blocks,
4566: p_app_attributes => l_attributes,
4567: p_messages => l_messages);
4568:
4569: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4570: , module => l_log_head
4571: , message => 'End Validate_Timecard'
4572: );
4573:
4572: );
4573:
4574: g_validate_stop := SYSDATE;
4575:
4576: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4577: , module => l_log_head
4578: , message => 'Cache hit rates: ' || FND_GLOBAL.local_chr(10)
4579: || ' build_block: ' || (g_build_block_calls - g_build_block_misses) || '/' || g_build_block_calls || FND_GLOBAL.local_chr(10)
4580: || ' build_attribute: ' || (g_build_attribute_calls - g_build_attribute_misses) || '/' || g_build_attribute_calls || FND_GLOBAL.local_chr(10)
4665: initialize_timing_statistics;
4666:
4667: G_CONC_LOG := '';
4668:
4669: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4670: , module => l_log_head
4671: , message => 'Begin Retrieve_Timecards'
4672: );
4673:
4670: , module => l_log_head
4671: , message => 'Begin Retrieve_Timecards'
4672: );
4673:
4674: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4675: , module => l_log_head
4676: , message => 'Parameters: p_vendor_id=' || p_vendor_id
4677: || ', p_start_date=' || p_start_date
4678: || ', p_end_date=' || p_end_date
4702: FROM po_lines pol
4703: WHERE pol.order_type_lookup_code in (''RATE'',''FIXED PRICE''))}');
4704: END IF;
4705:
4706: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4707: , module => l_log_head
4708: , message => 'Calling Generic Retrieval with p_where_clause: '
4709: || l_where_clause
4710: );
4729: g_generic_stop := SYSDATE;
4730: g_generic_time := g_generic_time + (g_generic_stop - g_generic_start);
4731: EXCEPTION
4732: WHEN OTHERS THEN
4733: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
4734: , module => l_log_head
4735: , message => 'Generic Retrieval failed: ' || SQLERRM
4736: );
4737: IF SQLERRM like 'ORA-20001: HXC_0013_GNRET_NO_BLD_BLKS%' OR
4748: END;
4749:
4750: g_retrieved_details := g_retrieved_details + HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT;
4751:
4752: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4753: , module => l_log_head
4754: , message => 'Returned from Generic Retrieval with '
4755: || HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT
4756: || ' detail blocks'
4757: );
4758:
4759: -- are there any more timecard blocks to process?
4760: IF HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT > 0 THEN
4761: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4762: , module => l_log_head
4763: , message => 'Calling Retrieval Body...'
4764: );
4765:
4776: , p_receipt_date => l_receipt_date
4777: );
4778: EXCEPTION
4779: WHEN OTHERS THEN
4780: RCV_HXT_GRP.string ( log_level => FND_LOG.LEVEL_UNEXPECTED , module => l_log_head , message => 'Retrieve_Timecards_Body failed. Error:'|| sqlerrm );
4781: END;
4782:
4783: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4784: , module => l_log_head
4779: WHEN OTHERS THEN
4780: RCV_HXT_GRP.string ( log_level => FND_LOG.LEVEL_UNEXPECTED , module => l_log_head , message => 'Retrieve_Timecards_Body failed. Error:'|| sqlerrm );
4781: END;
4782:
4783: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4784: , module => l_log_head
4785: , message => 'Returned from Retrieval Body'
4786: );
4787: ELSE
4797:
4798: g_retrieval_stop := SYSDATE;
4799: g_retrieval_time := g_retrieval_stop - g_retrieval_start;
4800:
4801: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4802: , module => l_log_head
4803: , message => 'End Retrieve_Timecards'
4804: );
4805:
4862: , p_status => 'ERRORS'
4863: , p_exception_description => G_CONC_LOG
4864: );
4865: WHEN OTHERS THEN
4866: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4867: , module => l_log_head
4868: , message => 'Unexpected exception in RCV_HXT_GRP.Retrieve_Timecards: ' || SQLERRM
4869: );
4870: errbuf := G_CONC_LOG;
4864: );
4865: WHEN OTHERS THEN
4866: RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4867: , module => l_log_head
4868: , message => 'Unexpected exception in RCV_HXT_GRP.Retrieve_Timecards: ' || SQLERRM
4869: );
4870: errbuf := G_CONC_LOG;
4871: retcode := 2;
4872: -- 13612527: Updating Transaction status to ERRORS which allows other request to run.
4876: , p_exception_description => G_CONC_LOG
4877: );
4878: END;
4879:
4880: END RCV_HXT_GRP;
4881: