[Home] [Help]
76: --
77: Procedure Delete_Concurrency_Records;
78:
79:
80: -- Populate GMS_BC_PACKETS with Costed Expenditure Items for fundscheck.
81: Procedure Populate_BC_Packets;
82:
83: -- Populate GMS_BC_PACKETS with AP Interface txns
84: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE);
79:
80: -- Populate GMS_BC_PACKETS with Costed Expenditure Items for fundscheck.
81: Procedure Populate_BC_Packets;
82:
83: -- Populate GMS_BC_PACKETS with AP Interface txns
84: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE);
85:
86: -- Populate GMS Concurrency table. This is to control concurrent running of
87: -- costing processes.
80: -- Populate GMS_BC_PACKETS with Costed Expenditure Items for fundscheck.
81: Procedure Populate_BC_Packets;
82:
83: -- Populate GMS_BC_PACKETS with AP Interface txns
84: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE);
85:
86: -- Populate GMS Concurrency table. This is to control concurrent running of
87: -- costing processes.
88: Procedure Populate_Concurrency_Table(p_system_linkage in VARCHAR2);
101: -- summary table.
102: --Procedure Summarize_Costs;
103:
104: -- Procedure to update status of packet entries.
105: Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2, p_request_id IN NUMBER);
106:
107: -- Procedure to create ADLs for successfully fundschecked expenditure items.
108: Procedure Create_ADLs(p_process IN VARCHAR2, p_request_id IN NUMBER);
109:
106:
107: -- Procedure to create ADLs for successfully fundschecked expenditure items.
108: Procedure Create_ADLs(p_process IN VARCHAR2, p_request_id IN NUMBER);
109:
110: -- Procedure to populate the indirect cost data into gms_bc_packets table.
111: -- Used for interface process.
112: Procedure Populate_Indirect_Cost(p_packet_id IN NUMBER);
113:
114: -- Procedure to mark interfacing item as failed. This is used for
132: -- GMS debug options are set to 'Y'.
133: -- 2. Query CDLs based on the request id passed and see if there are any
134: -- to be processed. If no CDLs are found then return to calling point.
135: -- Else process them.
136: -- 3. If there are CDLs to be processed populate gms_bc_packets table and
137: -- call gms_funds_control_pkg.gms_fck in 'X' mode for fundschecking.
138: -- 4. If packet fails fundscheck, mark the expenditures with rejection code
139: -- and delete the corresponding CDLs.
140: --
427: l_system_linkage varchar2(3);
428: l_fc_mode varchar2(1);
429: l_partial_mode varchar2(1);
430:
431: l_bc_pkt gms_bc_packets%ROWTYPE;
432: l_packet_id number;
433:
434: l_fc_required varchar2(1) := 'N';
435: l_je_source_name varchar2(30);
453: --REL12 : AP lines uptake enhancement : Added below variables
454: l_old_award_id gms_awards_all.award_id%TYPE;
455: l_old_project_id pa_projects_all.project_id%TYPE;
456: l_old_task_id pa_tasks.task_id%TYPE;
457: l_bud_task_id gms_bc_packets.bud_task_id%TYPE;
458: l_adj_ei_populated VARCHAR2(1);
459:
460: -- Cash based accounting variables
461: l_adl_fully_paid VARCHAR2(1); -- Flag indicating if invoice is flly paid
466: l_ap_bc_pkt_id NUMBER; -- Variable to store bc_packet_id of the AP RAW record created during FC
467:
468: CURSOR C_ap_bc_pkt_id (p_packet_id NUMBER) IS
469: SELECT bc_packet_id
470: FROM gms_bc_packets gbc
471: WHERE packet_id = p_packet_id
472: AND document_type = 'AP'
473: AND parent_bc_packet_id IS NULL
474: AND entered_dr <> 0 OR entered_cr <> 0 ;
517:
518: --BRC already consumed in current run
519: CURSOR c_pkt_brc IS
520: SELECT sum(NVL(gbc.burdenable_raw_cost,0))
521: FROM gms_bc_packets gbc
522: WHERE gbc.packet_id = g_packet_id
523: AND gbc.request_id = g_request_id
524: AND gbc.status_code = 'P'
525: AND gbc.document_header_id = g_xface_rec.invoice_id
1343: if g_debug_context = 'Y' THEN
1344: gms_error_pkg.gms_debug (g_error_stage, 'C');
1345: end if;
1346:
1347: -- Start of code for Commitment line record inserting in gms_bc_packets
1348: IF l_adl_fully_paid <> 'Y' THEN -- Will be YES for fully paid cash based accounting invoice
1349:
1350: l_bc_pkt.project_id := g_xface_rec.project_id;
1351: l_bc_pkt.award_id := g_xface_rec.award_id;
1405: CLOSE C_ap_bc_pkt_id;
1406:
1407: END IF ;
1408:
1409: -- End of code for Commitment line record insertion in gms_bc_packets
1410:
1411: -- Start of code for actual line record insertion in gms_bc_packets
1412:
1413: l_bc_pkt.project_id := t_project_id(i);
1407: END IF ;
1408:
1409: -- End of code for Commitment line record insertion in gms_bc_packets
1410:
1411: -- Start of code for actual line record insertion in gms_bc_packets
1412:
1413: l_bc_pkt.project_id := t_project_id(i);
1414: l_bc_pkt.award_id := t_award_id(i);
1415: l_bc_pkt.task_id := t_task_id(i);
1466: p_fck_err_stage := 'F06';
1467: COMMIT;
1468: RETURN;
1469: end if;
1470: -- End of code for actual line record insertion in gms_bc_packets
1471: end if;
1472:
1473: end if; -- source and line type check
1474:
1499: g_error_stage := 'Update the status and results codes for non-fcd txns';
1500: IF g_debug_context = 'Y' THEN
1501: gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1502: END IF;
1503: update gms_bc_packets gbc
1504: set gbc.result_code = 'P76',
1505: gbc.award_result_code = 'P76',
1506: gbc.top_task_result_code = 'P76',
1507: gbc.task_result_code = 'P76',
1584: g_xface_rec.line_type_lookup_code = 'PREPAY' ) AND
1585: l_comm_fc_req = 'N'
1586: ) THEN
1587:
1588: update gms_bc_packets
1589: set document_header_id = txn_interface_id,
1590: document_distribution_id = 1,
1591: document_type = 'EXP',
1592: actual_flag = 'A'
1594:
1595: --else -- for all other fc'd txns
1596: ELSIF l_adj_ei_populated ='Y' THEN
1597:
1598: update gms_bc_packets
1599: set document_header_id = txn_interface_id,
1600: document_distribution_id = 1,
1601: document_type = 'EXP',
1602: actual_flag = 'A'
1606: else -- ind compiled set id donot match
1607:
1608: -- Update bc records where entered_dr <>0 OR entered_cr <> 0
1609: -- This update is for payment with exchange rate variance in cash based accounting ,
1610: update gms_bc_packets
1611: set document_header_id = txn_interface_id,
1612: document_distribution_id = 1,
1613: document_type = 'EXP',
1614: actual_flag = 'A'
1616: AND bc_packet_id NOT IN (SELECT l_ap_bc_pkt_id
1617: FROM DUAL
1618: UNION ALL
1619: SELECT bc_packet_id
1620: FROM gms_bc_packets
1621: WHERE parent_bc_packet_id = l_ap_bc_pkt_id
1622: AND packet_id = g_packet_id )
1623: AND (entered_dr <> 0 OR entered_cr <>0 ) ;
1624:
1622: AND packet_id = g_packet_id )
1623: AND (entered_dr <> 0 OR entered_cr <>0 ) ;
1624:
1625: -- update switching raw record to correct doc type and raw cost.
1626: update gms_bc_packets
1627: set document_header_id = txn_interface_id,
1628: document_distribution_id = 1,
1629: document_type = 'EXP',
1630: entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1647: END IF;
1648:
1649: -- bug : 3612707 incorrect actuals in funds check burdenable cost.
1650: -- bug : 3607250 burdenable cost -ve
1651: update gms_bc_packets
1652: set document_type = 'EXP',
1653: document_header_id = txn_interface_id,
1654: document_distribution_id = 1
1655: where packet_id = g_packet_id
1654: document_distribution_id = 1
1655: where packet_id = g_packet_id
1656: and document_type = 'AP'
1657: and parent_bc_packet_id in ( select a.bc_packet_id
1658: from gms_bc_packets a
1659: where a.ind_compiled_set_id = l_new_compiled_set_id
1660: and a.document_type = 'EXP'
1661: and a.packet_id = g_packet_id )
1662:
1669: IF g_debug_context = 'Y' THEN
1670: gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1671: END IF;
1672:
1673: update gms_bc_packets
1674: set entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1675: -1, abs(g_xface_rec.acct_raw_cost), 0),
1676: entered_cr = decode(sign(g_xface_rec.acct_raw_cost),
1677: 1, g_xface_rec.acct_raw_cost, 0)
1695:
1696: End Execute_FundsCheck;
1697:
1698: -------------------------------------------------------------------------------
1699: -- Procedure to populate gms_bc_packets table. This procedure is called from
1700: -- Costing fundscheck process.
1701: -------------------------------------------------------------------------------
1702:
1703: Procedure Populate_BC_Packets is
1717: l_status_code := 'P';
1718:
1719: g_error_stage := 'Populating BC packets with costing data for FC';
1720: forall i in t_document_header_id.FIRST..t_document_header_id.LAST
1721: insert into gms_bc_packets(
1722: packet_id,
1723: project_id,
1724: award_id,
1725: task_id,
1786: t_document_header_id(i),
1787: t_document_distribution_id(i),
1788: t_entered_dr(i),
1789: t_entered_cr(i),
1790: gms_bc_packets_s.nextval,
1791: g_request_id,
1792: t_person_id(i),
1793: t_job_id(i),
1794: t_expenditure_category(i),
1798: t_transaction_source(i),
1799: t_burdenable_raw_cost(i) ); --R12 AP lines uptake :Forward port bug 4217161);
1800:
1801: IF g_debug_context = 'Y' THEN
1802: gms_error_pkg.gms_debug ('Populated gms_bc_packets with costing data for FC', 'C');
1803: END IF;
1804:
1805: end Populate_BC_Packets;
1806:
1804:
1805: end Populate_BC_Packets;
1806:
1807: -------------------------------------------------------------------------------
1808: -- Procedure to populate gms_bc_packets table for the given packet.
1809: -- This procedure is called for Supplier interface process.
1810: -------------------------------------------------------------------------------
1811: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE) is
1812:
1807: -------------------------------------------------------------------------------
1808: -- Procedure to populate gms_bc_packets table for the given packet.
1809: -- This procedure is called for Supplier interface process.
1810: -------------------------------------------------------------------------------
1811: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE) is
1812:
1813: PRAGMA AUTONOMOUS_TRANSACTION; -- Bug 5474308
1814:
1815: begin
1814:
1815: begin
1816: g_error_stage := 'In Populate_BC_Packets for Interface data';
1817:
1818: INSERT into gms_bc_packets ( packet_id,
1819: bc_packet_id,
1820: document_header_id,
1821: document_distribution_id,
1822: Document_type,
1852: vendor_id,
1853: expenditure_category, --Bug: 5003642
1854: revenue_category) values --Bug: 5003642
1855: (p_bc_pkt.packet_id,
1856: gms_bc_packets_s.nextval,
1857: p_bc_pkt.document_header_id,
1858: p_bc_pkt.document_distribution_id,
1859: p_bc_pkt.Document_type,
1860: p_bc_pkt.project_id,
1907:
1908: Cursor c_txn is
1909: select adjusted_document_header_id,
1910: nvl(ind_compiled_set_id,-1) ind_compiled_set_id
1911: from gms_bc_packets
1912: where packet_id = p_packetid
1913: having sum(entered_dr-entered_cr) = 0
1914: group by adjusted_document_header_id,
1915: nvl(ind_compiled_set_id,-1);
1927: IF g_debug_context = 'Y' THEN
1928: gms_error_pkg.gms_debug ('Fail adjusting txn if adjusted is not FCd', 'C');
1929: END IF;
1930:
1931: update gms_bc_packets gbc
1932: set gbc.result_code = 'F08',
1933: gbc.award_result_code = 'F08',
1934: gbc.top_task_result_code = 'F08',
1935: gbc.task_result_code = 'F08',
1956: g_error_stage := 'In Handle_Net_Zero...Net_Zero mode';
1957: for recs in c_txn
1958: loop
1959:
1960: update gms_bc_packets gbc
1961: set gbc.result_code = 'P82',
1962: gbc.award_result_code = 'P82',
1963: gbc.top_task_result_code = 'P82',
1964: gbc.task_result_code = 'P82',
2039:
2040: cursor get_failed_exps is
2041: select distinct document_header_id,
2042: result_code
2043: from gms_bc_packets gbc
2044: where packet_id = g_packet_id
2045: and substr(nvl(result_code, 'P75'), 1, 1) = 'F'
2046: and result_code not in ('F75', 'F63')
2047: and document_type = 'EXP';
2047: and document_type = 'EXP';
2048:
2049: fc_expenditure_item_id tt_document_header_id;
2050:
2051: TYPE tt_result_code is table of gms_bc_packets.result_code%TYPE;
2052: fc_result_code tt_result_code;
2053:
2054: Begin
2055: g_error_stage := 'In Mark_ExpItem_As_Failed';
2115: delete from pa_cost_distribution_lines
2116: where expenditure_item_id = fc_expenditure_item_id(i)
2117: and request_id = g_request_id
2118: and line_num in (select document_distribution_id
2119: from gms_bc_packets
2120: where document_header_id = fc_expenditure_item_id(i)
2121: and packet_id = g_packet_id
2122: and parent_bc_packet_id is null
2123: and document_type = 'EXP');
2219:
2220: return;
2221: end if;
2222:
2223: g_error_stage := 'Calling update_gms_bc_packets from FundsCheck_TieBack';
2224: IF g_debug_context = 'Y' THEN
2225: gms_error_pkg.gms_debug (g_error_stage, 'C');
2226: END IF;
2227:
2224: IF g_debug_context = 'Y' THEN
2225: gms_error_pkg.gms_debug (g_error_stage, 'C');
2226: END IF;
2227:
2228: update_gms_bc_packets(g_process, g_request_id);
2229:
2230: g_error_stage := 'Calling Create_ADLs from FundsCheck_TieBack';
2231: IF g_debug_context = 'Y' THEN
2232: gms_error_pkg.gms_debug (g_error_stage, 'C');
2231: IF g_debug_context = 'Y' THEN
2232: gms_error_pkg.gms_debug (g_error_stage, 'C');
2233: END IF;
2234:
2235: -- Note: Do not change the order of create_adls and update_gms_bc_packets ...
2236: create_adls(g_process, g_request_id);
2237:
2238: g_error_stage := 'Calling Delete_concurrency_records from FundsCheck_TieBack';
2239: IF g_debug_context = 'Y' THEN
2247:
2248: end FundsCheck_TieBack;
2249:
2250: -------------------------------------------------------------------------------
2251: -- Procedure to update the status of transactions in gms_bc_packets.
2252: -- Parameters :
2253: -- p_process : 'Costing' or 'Interface'.
2254: -- p_request_id : Request ID of the calling process.
2255: --
2254: -- p_request_id : Request ID of the calling process.
2255: --
2256: -- Notes:
2257: -- For Costing related transactions, we do the following :
2258: -- Update status_code in gms_bc_packets to Accepted or Rejected.
2259: --
2260: -- For Interface related transactions, we do the following :
2261: -- Txn_Interface_ID is updated on gms_bc_packets.document_header_id
2262: -- after Fundscheck is done. This is done so that we know which
2257: -- For Costing related transactions, we do the following :
2258: -- Update status_code in gms_bc_packets to Accepted or Rejected.
2259: --
2260: -- For Interface related transactions, we do the following :
2261: -- Txn_Interface_ID is updated on gms_bc_packets.document_header_id
2262: -- after Fundscheck is done. This is done so that we know which
2263: -- transactions are created for 'EXP' document type. Now, we update
2264: -- document_header_id with expenditure_item_id by joining with
2265: -- txn_interface_id.
2264: -- document_header_id with expenditure_item_id by joining with
2265: -- txn_interface_id.
2266: -------------------------------------------------------------------------------
2267: ---
2268: Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2,
2269: p_request_id IN NUMBER) is
2270: Begin
2271: g_error_stage := 'Update_GMS_BC_Packets...start';
2272: IF g_debug_context = 'Y' THEN
2267: ---
2268: Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2,
2269: p_request_id IN NUMBER) is
2270: Begin
2271: g_error_stage := 'Update_GMS_BC_Packets...start';
2272: IF g_debug_context = 'Y' THEN
2273: gms_error_pkg.gms_debug ('Update GMS BC Packets start..', 'C');
2274: END IF;
2275:
2269: p_request_id IN NUMBER) is
2270: Begin
2271: g_error_stage := 'Update_GMS_BC_Packets...start';
2272: IF g_debug_context = 'Y' THEN
2273: gms_error_pkg.gms_debug ('Update GMS BC Packets start..', 'C');
2274: END IF;
2275:
2276: -- If this procedure is called from interface then we need to use
2277: -- request_id for update ...
2275:
2276: -- If this procedure is called from interface then we need to use
2277: -- request_id for update ...
2278: If p_process = 'Interface' then
2279: update gms_bc_packets
2280: set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
2281: 'P', 'A',
2282: 'R')
2283: where request_id = p_request_id
2283: where request_id = p_request_id
2284: and status_code = 'P';
2285: Else
2286: -- costing, use g_packet_id for update ..
2287: update gms_bc_packets
2288: set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
2289: 'P', 'A',
2290: 'R')
2291: where packet_id = g_packet_id
2292: and status_code = 'P';
2293: End If;
2294:
2295: IF g_debug_context = 'Y' THEN
2296: gms_error_pkg.gms_debug ('Update_GMS_BC_Packets...end', 'C');
2297: END IF;
2298:
2299: End Update_GMS_BC_Packets;
2300:
2295: IF g_debug_context = 'Y' THEN
2296: gms_error_pkg.gms_debug ('Update_GMS_BC_Packets...end', 'C');
2297: END IF;
2298:
2299: End Update_GMS_BC_Packets;
2300:
2301: -------------------------------------------------------------------------------
2302: -- Procedure to create ADLs for the transactions that passed fundscheck.
2303: -- Parameters :
2310: cursor reversed_cur is
2311: select cdl.expenditure_item_id, cdl.line_num
2312: from pa_cost_distribution_lines cdl,
2313: --pa_expenditure_items_all exp,
2314: gms_bc_packets gbc
2315: where gbc.packet_id = g_packet_id
2316: and gbc.parent_bc_packet_id is null
2317: and gbc.status_code = 'A'
2318: --and exp.expenditure_item_id = gbc.document_header_id
2323: and cdl.reversed_flag = 'Y';
2324:
2325: Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2326: Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2327: Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2328: Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2329: Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2330: Type tab_row_id is table of varchar2(30);
2331:
2324:
2325: Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2326: Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2327: Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2328: Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2329: Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2330: Type tab_row_id is table of varchar2(30);
2331:
2332: v_ind_compiled_set_id tt_ind_compiled_set_id;
2325: Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2326: Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2327: Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2328: Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2329: Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2330: Type tab_row_id is table of varchar2(30);
2331:
2332: v_ind_compiled_set_id tt_ind_compiled_set_id;
2333: v_billable_flag tab_billable_flag;
2343: v_cdl_line_num tab_line_num;
2344:
2345: cursor get_xface_exp is
2346: select gbc.rowid, txn.expenditure_item_id
2347: from pa_transaction_interface_all txn, gms_bc_packets gbc
2348: where gbc.request_id = p_request_id
2349: and txn.txn_interface_id = gbc.document_header_id
2350: and nvl(txn.transaction_status_code, 'Z') <> 'R'
2351: and gbc.status_code = 'P'
2361: nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0) raw_cost,
2362: gbc.status_code,
2363: gbc.ind_compiled_set_id,
2364: gbc.burdenable_raw_cost
2365: from gms_bc_packets gbc,
2366: pa_cost_distribution_lines cdl
2367: where gbc.packet_id = g_packet_id
2368: and gbc.document_header_id = cdl.expenditure_item_id
2369: and gbc.document_distribution_id = cdl.line_num
2479: v_date,
2480: v_login
2481: from pa_cost_distribution_lines cdl,
2482: pa_expenditure_items_all exp,
2483: gms_bc_packets gbc
2484: where gbc.packet_id = g_packet_id
2485: and exp.expenditure_item_id = cdl.expenditure_item_id
2486: and cdl.expenditure_item_id = gbc.document_header_id
2487: and cdl.line_num = gbc.document_distribution_id
2513:
2514: elsif (p_process = 'Interface') then
2515:
2516: -- update expenditure_item_id from pa_transaction_interface to
2517: -- gms_bc_packets' document_header_id.
2518: -- re-sequenced code for bug :3690812
2519:
2520: open get_xface_exp;
2521: fetch get_xface_exp bulk collect into v_rowid, v_expenditure_item_id;
2523:
2524: if v_rowid.count > 0 then
2525:
2526: forall i in v_rowid.first..v_rowid.last
2527: update gms_bc_packets
2528: set document_header_id = v_expenditure_item_id(i)
2529: where rowid = v_rowid(i);
2530:
2531: g_error_stage := 'Creating ADLs for interface..';
2598: v_date,
2599: v_login
2600: from pa_cost_distribution_lines cdl,
2601: pa_expenditure_items_all exp,
2602: gms_bc_packets gbc
2603: where gbc.rowid = v_rowid(i)
2604: and exp.expenditure_item_id = cdl.expenditure_item_id
2605: and cdl.expenditure_item_id = gbc.document_header_id
2606: and cdl.line_num = gbc.document_distribution_id
2926:
2927: End FundsCheck_Supplier_Cost;
2928:
2929: -------------------------------------------------------------------------------
2930: -- Procedure to create the indirect cost entries in gms_bc_packets.
2931: -- This is called from Interface process.
2932: -------------------------------------------------------------------------------
2933:
2934: Procedure Populate_Indirect_Cost(p_packet_id IN NUMBER) IS
2938: IF g_debug_context = 'Y' THEN
2939: gms_error_pkg.gms_debug (g_error_stage, 'C');
2940: END IF;
2941:
2942: Insert into gms_bc_packets
2943: ( PACKET_ID,
2944: PROJECT_ID,
2945: AWARD_ID,
2946: TASK_ID,
3023: gbc.OVERRIDE_AMOUNT,
3024: gbc.EFFECT_ON_FUNDS_CODE ,
3025: gbc.RESULT_CODE,
3026: gbc.gl_bc_packets_rowid,
3027: gms_bc_packets_s.nextval,
3028: gbc.BC_PACKET_ID,
3029: gbc.vendor_id,
3030: gbc.request_id,
3031: gbc.ind_compiled_set_id,
3041: PA_COST_BASE_COST_CODES CBCC, /*6054504*/
3042: --pa_ind_rate_schedules_all_bg irs, /*6054504*/
3043: --pa_ind_compiled_sets ics, /*6054504*/
3044: pa_compiled_multipliers cm,
3045: gms_bc_packets gbc
3046: where et.expenditure_type = icc.expenditure_type
3047: and icc.ind_cost_code = cm.ind_cost_code
3048: and cbet.cost_base = cm.cost_base
3049: and cbet.cost_base_type = 'INDIRECT COST'
3079: ) IS
3080:
3081: cursor pkt_for_summary_update is
3082: select distinct packet_id
3083: from gms_bc_packets
3084: where request_id = p_request_id
3085: and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3086: and status_code = 'P';
3087:
3085: and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3086: and status_code = 'P';
3087:
3088: -- Get all rejected transactions from pa_transaction_interface.
3089: -- Use this to fail gms_bc_packets records.
3090:
3091: cursor get_failed_txns is
3092: select distinct gbp.packet_id,
3093: xface.transaction_rejection_code
3091: cursor get_failed_txns is
3092: select distinct gbp.packet_id,
3093: xface.transaction_rejection_code
3094: from pa_transaction_interface_all xface,
3095: gms_bc_packets gbp
3096: where to_number(gbp.gl_bc_packets_rowid) = xface.txn_interface_id
3097: and gbp.request_id = p_request_id
3098: and gbp.parent_bc_packet_id is null
3099: and xface.transaction_status_code = 'R'
3117: IF g_debug_context = 'Y' THEN
3118: gms_error_pkg.gms_debug (g_error_stage, 'C');
3119: END IF;
3120:
3121: -- check the transactions which are rejected and fail gms_bc_packets
3122: -- entries
3123: open get_failed_txns;
3124: fetch get_failed_txns bulk collect into v_packet_id,
3125: v_reject_code;
3137: end loop;
3138: END IF;
3139:
3140: forall i in v_packet_id.FIRST..v_packet_id.LAST
3141: update gms_bc_packets
3142: set result_code = 'F89',
3143: status_code = 'T',
3144: fc_error_message = 'PA_FC_ERROR: ' ||
3145: v_reject_code(i)
3194: UPDATE gms_award_distributions adl
3195: SET adl.payment_status_flag = 'Y'
3196: WHERE (adl.invoice_id ,adl.invoice_distribution_id) IN
3197: (SELECT trx.cdl_system_reference2,trx.cdl_system_reference5
3198: FROM gms_bc_packets pkt,
3199: pa_transaction_interface_all trx
3200: WHERE pkt.request_id = p_request_id
3201: AND pkt.txn_interface_id = trx.txn_interface_id
3202: AND substr(nvl(pkt.result_code, 'P65'), 1, 1) = 'P'
3206: AND pa_trx_import.g_finalPaymentId = trx.cdl_system_reference4) -- Final payment
3207: AND adl.document_type ='AP'
3208: AND adl.adl_status = 'A' ;
3209:
3210: -- update the status of gms_bc_packets. update expenditure_item_id
3211: -- from pa_transaction_interface to gms_bc_packets.
3212:
3213: if v_all_pkts_failed = 'N' then
3214:
3207: AND adl.document_type ='AP'
3208: AND adl.adl_status = 'A' ;
3209:
3210: -- update the status of gms_bc_packets. update expenditure_item_id
3211: -- from pa_transaction_interface to gms_bc_packets.
3212:
3213: if v_all_pkts_failed = 'N' then
3214:
3215: g_error_stage := 'TieBack_Xface: Calling Create ADLs';
3217: gms_error_pkg.gms_debug (g_error_stage, 'C');
3218: END IF;
3219:
3220: -- Note:
3221: -- ADLs should be created before Updating status_code on gms_bc_packets to 'A'.
3222: -- create_adl package looks at records with status_code 'P'
3223:
3224: create_adls('Interface', p_request_id);
3225:
3222: -- create_adl package looks at records with status_code 'P'
3223:
3224: create_adls('Interface', p_request_id);
3225:
3226: g_error_stage := 'TieBack_Xface: Calling update_gms_bc_packets';
3227: IF g_debug_context = 'Y' THEN
3228: gms_error_pkg.gms_debug (g_error_stage, 'C');
3229: END IF;
3230:
3227: IF g_debug_context = 'Y' THEN
3228: gms_error_pkg.gms_debug (g_error_stage, 'C');
3229: END IF;
3230:
3231: update_gms_bc_packets('Interface', p_request_id);
3232:
3233: end if;
3234:
3235: p_status := FND_API.G_RET_STS_SUCCESS;
3371:
3372:
3373: -------------------------------------------------------------------------------
3374: -- Procedure marks the current packet's interface data as failed.
3375: -- Updates gms_bc_packets and pa_transaction_interface tables.
3376: -------------------------------------------------------------------------------
3377:
3378: Procedure Mark_Xface_Item_AS_Failed(p_packet_id IN NUMBER,
3379: p_status OUT NOCOPY VARCHAR2) is
3379: p_status OUT NOCOPY VARCHAR2) is
3380:
3381: cursor c1 is -- changed for performance.
3382: select distinct txn_interface_id
3383: from gms_bc_packets
3384: where packet_id = p_packet_id
3385: and substr(result_code, 1, 1) = 'F';
3386:
3387: v_txn_interface_id number;