[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) ; --bug 9285246 Added braces for OR conditions
527:
528: --BRC already consumed in current run
529: CURSOR c_pkt_brc IS
530: SELECT sum(NVL(gbc.burdenable_raw_cost,0))
531: FROM gms_bc_packets gbc
532: WHERE gbc.packet_id = g_packet_id
533: AND gbc.request_id = g_request_id
534: AND gbc.status_code = 'P'
535: AND gbc.document_header_id = g_xface_rec.invoice_id
1374: if g_debug_context = 'Y' THEN
1375: gms_error_pkg.gms_debug (g_error_stage, 'C');
1376: end if;
1377:
1378: -- Start of code for Commitment line record inserting in gms_bc_packets
1379: IF l_adl_fully_paid <> 'Y' THEN -- Will be YES for fully paid cash based accounting invoice
1380:
1381: l_bc_pkt.project_id := g_xface_rec.project_id;
1382: l_bc_pkt.award_id := g_xface_rec.award_id;
1436: CLOSE C_ap_bc_pkt_id;
1437:
1438: END IF ;
1439:
1440: -- End of code for Commitment line record insertion in gms_bc_packets
1441:
1442: -- Start of code for actual line record insertion in gms_bc_packets
1443:
1444: l_bc_pkt.project_id := t_project_id(i);
1438: END IF ;
1439:
1440: -- End of code for Commitment line record insertion in gms_bc_packets
1441:
1442: -- Start of code for actual line record insertion in gms_bc_packets
1443:
1444: l_bc_pkt.project_id := t_project_id(i);
1445: l_bc_pkt.award_id := t_award_id(i);
1446: l_bc_pkt.task_id := t_task_id(i);
1497: p_fck_err_stage := 'F06';
1498: COMMIT;
1499: RETURN;
1500: end if;
1501: -- End of code for actual line record insertion in gms_bc_packets
1502: end if;
1503:
1504: end if; -- source and line type check
1505:
1530: g_error_stage := 'Update the status and results codes for non-fcd txns';
1531: IF g_debug_context = 'Y' THEN
1532: gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1533: END IF;
1534: update gms_bc_packets gbc
1535: set gbc.result_code = 'P76',
1536: gbc.award_result_code = 'P76',
1537: gbc.top_task_result_code = 'P76',
1538: gbc.task_result_code = 'P76',
1615: g_xface_rec.line_type_lookup_code = 'PREPAY' ) AND
1616: l_comm_fc_req = 'N'
1617: ) THEN
1618:
1619: update gms_bc_packets
1620: set document_header_id = txn_interface_id,
1621: document_distribution_id = 1,
1622: document_type = 'EXP',
1623: actual_flag = 'A'
1625:
1626: --else -- for all other fc'd txns
1627: ELSIF l_adj_ei_populated ='Y' THEN
1628:
1629: update gms_bc_packets
1630: set document_header_id = txn_interface_id,
1631: document_distribution_id = 1,
1632: document_type = 'EXP',
1633: actual_flag = 'A'
1636:
1637: else -- ind compiled set id donot match
1638:
1639: -- update switching raw record to correct doc type and raw cost.
1640: update gms_bc_packets
1641: set document_header_id = txn_interface_id,
1642: document_distribution_id = 1,
1643: document_type = 'EXP',
1644: entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1655:
1656:
1657: -- Update bc records where entered_dr <>0 OR entered_cr <> 0
1658: -- This update is for payment with exchange rate variance in cash based accounting ,
1659: /*update gms_bc_packets
1660: set document_header_id = txn_interface_id,
1661: document_distribution_id = 1,
1662: document_type = 'EXP',
1663: actual_flag = 'A'
1665: AND bc_packet_id NOT IN (SELECT l_ap_bc_pkt_id
1666: FROM DUAL
1667: UNION ALL
1668: SELECT bc_packet_id
1669: FROM gms_bc_packets
1670: WHERE parent_bc_packet_id = l_ap_bc_pkt_id
1671: AND packet_id = g_packet_id )
1672: AND (entered_dr <> 0 OR entered_cr <>0 ) ; */
1673:
1671: AND packet_id = g_packet_id )
1672: AND (entered_dr <> 0 OR entered_cr <>0 ) ; */
1673:
1674: /* Bug 9285246 Commented the update above and rewrote the same below*/
1675: update gms_bc_packets gbp1
1676: set gbp1.document_header_id = txn_interface_id,
1677: gbp1.document_distribution_id = 1,
1678: gbp1.document_type = 'EXP',
1679: gbp1.actual_flag = 'A'
1681: AND gbp1.bc_packet_id NOT IN (SELECT l_ap_bc_pkt_id
1682: FROM DUAL
1683: UNION ALL
1684: SELECT gbp2.bc_packet_id
1685: FROM gms_bc_packets gbp2
1686: WHERE gbp2.parent_bc_packet_id = l_ap_bc_pkt_id
1687: AND gbp2.packet_id = g_packet_id )
1688: AND (gbp1.entered_dr <> 0 OR gbp1.entered_cr <>0 )
1689: AND (gbp1.parent_bc_packet_id is null
1687: AND gbp2.packet_id = g_packet_id )
1688: AND (gbp1.entered_dr <> 0 OR gbp1.entered_cr <>0 )
1689: AND (gbp1.parent_bc_packet_id is null
1690: OR exists
1691: (select 1 from gms_bc_packets gbp3
1692: where gbp3.bc_packet_id = gbp1.parent_bc_packet_id
1693: and gbp3.document_type = 'EXP'));
1694:
1695: --
1700: END IF;
1701:
1702: -- bug : 3612707 incorrect actuals in funds check burdenable cost.
1703: -- bug : 3607250 burdenable cost -ve
1704: update gms_bc_packets
1705: set document_type = 'EXP',
1706: document_header_id = txn_interface_id,
1707: document_distribution_id = 1
1708: where packet_id = g_packet_id
1707: document_distribution_id = 1
1708: where packet_id = g_packet_id
1709: and document_type = 'AP'
1710: and parent_bc_packet_id in ( select a.bc_packet_id
1711: from gms_bc_packets a
1712: where a.ind_compiled_set_id = l_new_compiled_set_id
1713: and a.document_type = 'EXP'
1714: and a.packet_id = g_packet_id )
1715:
1722: IF g_debug_context = 'Y' THEN
1723: gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1724: END IF;
1725:
1726: update gms_bc_packets
1727: set entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1728: -1, abs(g_xface_rec.acct_raw_cost), 0),
1729: entered_cr = decode(sign(g_xface_rec.acct_raw_cost),
1730: 1, g_xface_rec.acct_raw_cost, 0)
1748:
1749: End Execute_FundsCheck;
1750:
1751: -------------------------------------------------------------------------------
1752: -- Procedure to populate gms_bc_packets table. This procedure is called from
1753: -- Costing fundscheck process.
1754: -------------------------------------------------------------------------------
1755:
1756: Procedure Populate_BC_Packets is
1770: l_status_code := 'P';
1771:
1772: g_error_stage := 'Populating BC packets with costing data for FC';
1773: forall i in t_document_header_id.FIRST..t_document_header_id.LAST
1774: insert into gms_bc_packets(
1775: packet_id,
1776: project_id,
1777: award_id,
1778: task_id,
1839: t_document_header_id(i),
1840: t_document_distribution_id(i),
1841: t_entered_dr(i),
1842: t_entered_cr(i),
1843: gms_bc_packets_s.nextval,
1844: g_request_id,
1845: t_person_id(i),
1846: t_job_id(i),
1847: t_expenditure_category(i),
1851: t_transaction_source(i),
1852: t_burdenable_raw_cost(i) ); --R12 AP lines uptake :Forward port bug 4217161);
1853:
1854: IF g_debug_context = 'Y' THEN
1855: gms_error_pkg.gms_debug ('Populated gms_bc_packets with costing data for FC', 'C');
1856: END IF;
1857:
1858: end Populate_BC_Packets;
1859:
1857:
1858: end Populate_BC_Packets;
1859:
1860: -------------------------------------------------------------------------------
1861: -- Procedure to populate gms_bc_packets table for the given packet.
1862: -- This procedure is called for Supplier interface process.
1863: -------------------------------------------------------------------------------
1864: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE) is
1865:
1860: -------------------------------------------------------------------------------
1861: -- Procedure to populate gms_bc_packets table for the given packet.
1862: -- This procedure is called for Supplier interface process.
1863: -------------------------------------------------------------------------------
1864: Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE) is
1865:
1866: PRAGMA AUTONOMOUS_TRANSACTION; -- Bug 5474308
1867:
1868: begin
1867:
1868: begin
1869: g_error_stage := 'In Populate_BC_Packets for Interface data';
1870:
1871: INSERT into gms_bc_packets ( packet_id,
1872: bc_packet_id,
1873: document_header_id,
1874: document_distribution_id,
1875: Document_type,
1906: expenditure_category, --Bug: 5003642
1907: revenue_category, --Bug: 5003642
1908: txn_interface_id) values -- Bug 9929155: Added txn_interface_id
1909: (p_bc_pkt.packet_id,
1910: gms_bc_packets_s.nextval,
1911: p_bc_pkt.document_header_id,
1912: p_bc_pkt.document_distribution_id,
1913: p_bc_pkt.Document_type,
1914: p_bc_pkt.project_id,
1962:
1963: Cursor c_txn is
1964: select adjusted_document_header_id,
1965: nvl(ind_compiled_set_id,-1) ind_compiled_set_id
1966: from gms_bc_packets
1967: where packet_id = p_packetid
1968: having sum(entered_dr-entered_cr) = 0
1969: group by adjusted_document_header_id,
1970: nvl(ind_compiled_set_id,-1);
1982: IF g_debug_context = 'Y' THEN
1983: gms_error_pkg.gms_debug ('Fail adjusting txn if adjusted is not FCd', 'C');
1984: END IF;
1985:
1986: update gms_bc_packets gbc
1987: set gbc.result_code = 'F08',
1988: gbc.award_result_code = 'F08',
1989: gbc.top_task_result_code = 'F08',
1990: gbc.task_result_code = 'F08',
2011: g_error_stage := 'In Handle_Net_Zero...Net_Zero mode';
2012: for recs in c_txn
2013: loop
2014:
2015: update gms_bc_packets gbc
2016: set gbc.result_code = 'P82',
2017: gbc.award_result_code = 'P82',
2018: gbc.top_task_result_code = 'P82',
2019: gbc.task_result_code = 'P82',
2094:
2095: cursor get_failed_exps is
2096: select distinct document_header_id,
2097: result_code
2098: from gms_bc_packets gbc
2099: where packet_id = g_packet_id
2100: and substr(nvl(result_code, 'P75'), 1, 1) = 'F'
2101: and result_code not in ('F75', 'F63')
2102: and document_type = 'EXP';
2102: and document_type = 'EXP';
2103:
2104: fc_expenditure_item_id tt_document_header_id;
2105:
2106: TYPE tt_result_code is table of gms_bc_packets.result_code%TYPE;
2107: fc_result_code tt_result_code;
2108:
2109: Begin
2110: g_error_stage := 'In Mark_ExpItem_As_Failed';
2170: delete from pa_cost_distribution_lines
2171: where expenditure_item_id = fc_expenditure_item_id(i)
2172: and request_id = g_request_id
2173: and line_num in (select document_distribution_id
2174: from gms_bc_packets
2175: where document_header_id = fc_expenditure_item_id(i)
2176: and packet_id = g_packet_id
2177: and parent_bc_packet_id is null
2178: and document_type = 'EXP');
2299:
2300: return;
2301: end if;
2302:
2303: g_error_stage := 'Calling update_gms_bc_packets from FundsCheck_TieBack';
2304: IF g_debug_context = 'Y' THEN
2305: gms_error_pkg.gms_debug (g_error_stage, 'C');
2306: END IF;
2307:
2304: IF g_debug_context = 'Y' THEN
2305: gms_error_pkg.gms_debug (g_error_stage, 'C');
2306: END IF;
2307:
2308: update_gms_bc_packets(g_process, g_request_id);
2309:
2310: g_error_stage := 'Calling Create_ADLs from FundsCheck_TieBack';
2311: IF g_debug_context = 'Y' THEN
2312: gms_error_pkg.gms_debug (g_error_stage, 'C');
2311: IF g_debug_context = 'Y' THEN
2312: gms_error_pkg.gms_debug (g_error_stage, 'C');
2313: END IF;
2314:
2315: -- Note: Do not change the order of create_adls and update_gms_bc_packets ...
2316: create_adls(g_process, g_request_id);
2317:
2318: g_error_stage := 'Calling Delete_concurrency_records from FundsCheck_TieBack';
2319: IF g_debug_context = 'Y' THEN
2327:
2328: end FundsCheck_TieBack;
2329:
2330: -------------------------------------------------------------------------------
2331: -- Procedure to update the status of transactions in gms_bc_packets.
2332: -- Parameters :
2333: -- p_process : 'Costing' or 'Interface'.
2334: -- p_request_id : Request ID of the calling process.
2335: --
2334: -- p_request_id : Request ID of the calling process.
2335: --
2336: -- Notes:
2337: -- For Costing related transactions, we do the following :
2338: -- Update status_code in gms_bc_packets to Accepted or Rejected.
2339: --
2340: -- For Interface related transactions, we do the following :
2341: -- Txn_Interface_ID is updated on gms_bc_packets.document_header_id
2342: -- after Fundscheck is done. This is done so that we know which
2337: -- For Costing related transactions, we do the following :
2338: -- Update status_code in gms_bc_packets to Accepted or Rejected.
2339: --
2340: -- For Interface related transactions, we do the following :
2341: -- Txn_Interface_ID is updated on gms_bc_packets.document_header_id
2342: -- after Fundscheck is done. This is done so that we know which
2343: -- transactions are created for 'EXP' document type. Now, we update
2344: -- document_header_id with expenditure_item_id by joining with
2345: -- txn_interface_id.
2344: -- document_header_id with expenditure_item_id by joining with
2345: -- txn_interface_id.
2346: -------------------------------------------------------------------------------
2347: ---
2348: Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2,
2349: p_request_id IN NUMBER) is
2350: Begin
2351: g_error_stage := 'Update_GMS_BC_Packets...start';
2352: IF g_debug_context = 'Y' THEN
2347: ---
2348: Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2,
2349: p_request_id IN NUMBER) is
2350: Begin
2351: g_error_stage := 'Update_GMS_BC_Packets...start';
2352: IF g_debug_context = 'Y' THEN
2353: gms_error_pkg.gms_debug ('Update GMS BC Packets start..', 'C');
2354: END IF;
2355:
2349: p_request_id IN NUMBER) is
2350: Begin
2351: g_error_stage := 'Update_GMS_BC_Packets...start';
2352: IF g_debug_context = 'Y' THEN
2353: gms_error_pkg.gms_debug ('Update GMS BC Packets start..', 'C');
2354: END IF;
2355:
2356: -- If this procedure is called from interface then we need to use
2357: -- request_id for update ...
2355:
2356: -- If this procedure is called from interface then we need to use
2357: -- request_id for update ...
2358: If p_process = 'Interface' then
2359: update gms_bc_packets
2360: set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
2361: 'P', 'A',
2362: 'R')
2363: where request_id = p_request_id
2363: where request_id = p_request_id
2364: and status_code = 'P';
2365: Else
2366: -- costing, use g_packet_id for update ..
2367: update gms_bc_packets
2368: set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
2369: 'P', 'A',
2370: 'R')
2371: where packet_id = g_packet_id
2372: and status_code = 'P';
2373: End If;
2374:
2375: IF g_debug_context = 'Y' THEN
2376: gms_error_pkg.gms_debug ('Update_GMS_BC_Packets...end', 'C');
2377: END IF;
2378:
2379: End Update_GMS_BC_Packets;
2380:
2375: IF g_debug_context = 'Y' THEN
2376: gms_error_pkg.gms_debug ('Update_GMS_BC_Packets...end', 'C');
2377: END IF;
2378:
2379: End Update_GMS_BC_Packets;
2380:
2381: -------------------------------------------------------------------------------
2382: -- Procedure to create ADLs for the transactions that passed fundscheck.
2383: -- Parameters :
2390: cursor reversed_cur is
2391: select cdl.expenditure_item_id, cdl.line_num
2392: from pa_cost_distribution_lines cdl,
2393: --pa_expenditure_items_all exp,
2394: gms_bc_packets gbc
2395: where gbc.packet_id = g_packet_id
2396: and gbc.parent_bc_packet_id is null
2397: and gbc.status_code = 'A'
2398: --and exp.expenditure_item_id = gbc.document_header_id
2403: and cdl.reversed_flag = 'Y';
2404:
2405: Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2406: Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2407: Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2408: Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2409: Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2410: Type tab_row_id is table of varchar2(30);
2411:
2404:
2405: Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2406: Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2407: Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2408: Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2409: Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2410: Type tab_row_id is table of varchar2(30);
2411:
2412: v_ind_compiled_set_id tt_ind_compiled_set_id;
2405: Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2406: Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2407: Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2408: Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2409: Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2410: Type tab_row_id is table of varchar2(30);
2411:
2412: v_ind_compiled_set_id tt_ind_compiled_set_id;
2413: v_billable_flag tab_billable_flag;
2423: v_cdl_line_num tab_line_num;
2424:
2425: cursor get_xface_exp is
2426: select gbc.rowid, txn.expenditure_item_id
2427: from pa_transaction_interface_all txn, gms_bc_packets gbc
2428: where gbc.request_id = p_request_id
2429: and txn.txn_interface_id = gbc.document_header_id
2430: and nvl(txn.transaction_status_code, 'Z') <> 'R'
2431: and gbc.status_code = 'P'
2441: nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0) raw_cost,
2442: gbc.status_code,
2443: gbc.ind_compiled_set_id,
2444: gbc.burdenable_raw_cost
2445: from gms_bc_packets gbc,
2446: pa_cost_distribution_lines cdl
2447: where gbc.packet_id = g_packet_id
2448: and gbc.document_header_id = cdl.expenditure_item_id
2449: and gbc.document_distribution_id = cdl.line_num
2559: v_date,
2560: v_login
2561: from pa_cost_distribution_lines cdl,
2562: pa_expenditure_items_all exp,
2563: gms_bc_packets gbc
2564: where gbc.packet_id = g_packet_id
2565: and exp.expenditure_item_id = cdl.expenditure_item_id
2566: and cdl.expenditure_item_id = gbc.document_header_id
2567: and cdl.line_num = gbc.document_distribution_id
2593:
2594: elsif (p_process = 'Interface') then
2595:
2596: -- update expenditure_item_id from pa_transaction_interface to
2597: -- gms_bc_packets' document_header_id.
2598: -- re-sequenced code for bug :3690812
2599:
2600: open get_xface_exp;
2601: fetch get_xface_exp bulk collect into v_rowid, v_expenditure_item_id;
2603:
2604: if v_rowid.count > 0 then
2605:
2606: forall i in v_rowid.first..v_rowid.last
2607: update gms_bc_packets
2608: set document_header_id = v_expenditure_item_id(i)
2609: where rowid = v_rowid(i);
2610:
2611: g_error_stage := 'Creating ADLs for interface..';
2678: v_date,
2679: v_login
2680: from pa_cost_distribution_lines cdl,
2681: pa_expenditure_items_all exp,
2682: gms_bc_packets gbc
2683: where gbc.rowid = v_rowid(i)
2684: and exp.expenditure_item_id = cdl.expenditure_item_id
2685: and cdl.expenditure_item_id = gbc.document_header_id
2686: and cdl.line_num = gbc.document_distribution_id
3006:
3007: End FundsCheck_Supplier_Cost;
3008:
3009: -------------------------------------------------------------------------------
3010: -- Procedure to create the indirect cost entries in gms_bc_packets.
3011: -- This is called from Interface process.
3012: -------------------------------------------------------------------------------
3013:
3014: Procedure Populate_Indirect_Cost(p_packet_id IN NUMBER) IS
3018: IF g_debug_context = 'Y' THEN
3019: gms_error_pkg.gms_debug (g_error_stage, 'C');
3020: END IF;
3021:
3022: Insert into gms_bc_packets
3023: ( PACKET_ID,
3024: PROJECT_ID,
3025: AWARD_ID,
3026: TASK_ID,
3103: gbc.OVERRIDE_AMOUNT,
3104: gbc.EFFECT_ON_FUNDS_CODE ,
3105: gbc.RESULT_CODE,
3106: gbc.gl_bc_packets_rowid,
3107: gms_bc_packets_s.nextval,
3108: gbc.BC_PACKET_ID,
3109: gbc.vendor_id,
3110: gbc.request_id,
3111: gbc.ind_compiled_set_id,
3121: PA_COST_BASE_COST_CODES CBCC, /*6054504*/
3122: --pa_ind_rate_schedules_all_bg irs, /*6054504*/
3123: --pa_ind_compiled_sets ics, /*6054504*/
3124: pa_compiled_multipliers cm,
3125: gms_bc_packets gbc
3126: where et.expenditure_type = icc.expenditure_type
3127: and icc.ind_cost_code = cm.ind_cost_code
3128: and cbet.cost_base = cm.cost_base
3129: and cbet.cost_base_type = 'INDIRECT COST'
3159: ) IS
3160:
3161: cursor pkt_for_summary_update is
3162: select distinct packet_id
3163: from gms_bc_packets
3164: where request_id = p_request_id
3165: and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3166: and status_code = 'P';
3167:
3165: and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3166: and status_code = 'P';
3167:
3168: -- Get all rejected transactions from pa_transaction_interface.
3169: -- Use this to fail gms_bc_packets records.
3170:
3171: cursor get_failed_txns is
3172: select distinct gbp.packet_id,
3173: xface.transaction_rejection_code
3171: cursor get_failed_txns is
3172: select distinct gbp.packet_id,
3173: xface.transaction_rejection_code
3174: from pa_transaction_interface_all xface,
3175: gms_bc_packets gbp
3176: where to_number(gbp.gl_bc_packets_rowid) = xface.txn_interface_id
3177: and gbp.request_id = p_request_id
3178: and gbp.parent_bc_packet_id is null
3179: and xface.transaction_status_code = 'R'
3197: IF g_debug_context = 'Y' THEN
3198: gms_error_pkg.gms_debug (g_error_stage, 'C');
3199: END IF;
3200:
3201: -- check the transactions which are rejected and fail gms_bc_packets
3202: -- entries
3203: open get_failed_txns;
3204: fetch get_failed_txns bulk collect into v_packet_id,
3205: v_reject_code;
3217: end loop;
3218: END IF;
3219:
3220: forall i in v_packet_id.FIRST..v_packet_id.LAST
3221: update gms_bc_packets
3222: set result_code = 'F89',
3223: status_code = 'T',
3224: fc_error_message = 'PA_FC_ERROR: ' ||
3225: v_reject_code(i)
3277: WHERE
3278: TO_CHAR(adl.invoice_id)||'|'||TO_CHAR(adl.invoice_distribution_id) IN
3279: (SELECT
3280: (trx.cdl_system_reference2)||'|'||TO_CHAR(trx.cdl_system_reference5)
3281: FROM gms_bc_packets pkt,
3282: pa_transaction_interface_all trx
3283: WHERE pkt.request_id=p_request_id
3284: AND pkt.txn_interface_id=trx.txn_interface_id
3285: AND substr(nvl(pkt.result_code,'P65'), 1, 1)='P'
3289: AND trx.cdl_system_reference4= to_char(pa_trx_import.g_finalPaymentId)) --Final payment -- Bug 10071395: Added to_char
3290: AND adl.document_type='AP'
3291: AND adl.adl_status='A';
3292:
3293: -- update the status of gms_bc_packets. update expenditure_item_id
3294: -- from pa_transaction_interface to gms_bc_packets.
3295:
3296: if v_all_pkts_failed = 'N' then
3297:
3290: AND adl.document_type='AP'
3291: AND adl.adl_status='A';
3292:
3293: -- update the status of gms_bc_packets. update expenditure_item_id
3294: -- from pa_transaction_interface to gms_bc_packets.
3295:
3296: if v_all_pkts_failed = 'N' then
3297:
3298: g_error_stage := 'TieBack_Xface: Calling Create ADLs';
3300: gms_error_pkg.gms_debug (g_error_stage, 'C');
3301: END IF;
3302:
3303: -- Note:
3304: -- ADLs should be created before Updating status_code on gms_bc_packets to 'A'.
3305: -- create_adl package looks at records with status_code 'P'
3306:
3307: create_adls('Interface', p_request_id);
3308:
3305: -- create_adl package looks at records with status_code 'P'
3306:
3307: create_adls('Interface', p_request_id);
3308:
3309: g_error_stage := 'TieBack_Xface: Calling update_gms_bc_packets';
3310: IF g_debug_context = 'Y' THEN
3311: gms_error_pkg.gms_debug (g_error_stage, 'C');
3312: END IF;
3313:
3310: IF g_debug_context = 'Y' THEN
3311: gms_error_pkg.gms_debug (g_error_stage, 'C');
3312: END IF;
3313:
3314: update_gms_bc_packets('Interface', p_request_id);
3315:
3316: end if;
3317:
3318: p_status := FND_API.G_RET_STS_SUCCESS;
3454:
3455:
3456: -------------------------------------------------------------------------------
3457: -- Procedure marks the current packet's interface data as failed.
3458: -- Updates gms_bc_packets and pa_transaction_interface tables.
3459: -------------------------------------------------------------------------------
3460:
3461: Procedure Mark_Xface_Item_AS_Failed(p_packet_id IN NUMBER,
3462: p_status OUT NOCOPY VARCHAR2) is
3462: p_status OUT NOCOPY VARCHAR2) is
3463:
3464: cursor c1 is -- changed for performance.
3465: select distinct txn_interface_id
3466: from gms_bc_packets
3467: where packet_id = p_packet_id
3468: and substr(result_code, 1, 1) = 'F';
3469:
3470: v_txn_interface_id number;